[Mysql] DB 성능 튜닝 설정 값

오늘은 DB의 성능 튜닝을 위한 값들에 대해 알아보겠습니다.

제일 쉬운 방법은 하드웨어 스펙을 개선하는 것입니다. 그러나 비용이들겠죠 ㅠ

이 비용을 아끼기 위해서는 설정 값을 튜닝해서 최적의 값을 찾아내야됩니다.

설정 값들은 정답이 없습니다. 직접 사용하면서 맞추어가야됩니다.

왜냐하면 사용하는 장비의 스펙은 다르고, 데이터의 양도 천차 만별이기 때문입니다.

이런 설정값을 잘못 세팅하여 문제가 발생할 수도 있습니다.

 

 

 

 

 

설정에 대한 것은 참고만 하시고, 운용중인 환경에 맞추어 설정하시기 바랍니다.

-key_buffer_size

메모리에 있는 인덱스 블록에서 사용되는 버퍼의 크기를 나타냅니다.

또한, 이 값은 MyISAM 테이블에서 사용하고 있는 모든 쓰레드에서 공유하는 버퍼 크기이기도합니다.

key_buffer_size는 읽기 속도에 영향을 줍니다.

mariadb에서는 아래와 같이 값을 권장합니다.

MyISAM만을 사용하는 경우, key_buffer_size=RAM의 20%, innodb_buffer_pool_size=0 으로 설정

InnoDB만을 사용하는 경우, key_buffer_size=10M( 작아도 되고, 0이 아닌 값), inodb_buffer_pool_size=RAM의 70% 으로 설정

-innodb_buffer_pool_size

InnoDB에서 사용하는 버퍼 사이즈입니다.

InnoDB는 이 버퍼 풀에서 모든 캐싱을 수행합니다.

캐싱을 위해서는 반드시 이 값이 필요하고, key_buffer_size에 나온 권장 사항대로 설정하시기 바랍니다.

조금더 상세히 말하자면, 버퍼 풀은 데이터 파일과 로그 파일이 기록되는 순서를 조정합니다.

또한, 디스크 액세스를 줄이기 위한 캐시 역할을 합니다.

OS에서 캐시의 크기가 클수록 성능이 좋아 지듯이, 버퍼 풀의 크기가 적당히 클수록 성능에 유리합니다.

이유는 디스크 I/O를 줄이기 때문입니다.

-innodb_log_file_size

InnoDB의 로그 사이즈입니다.

적어도, 최대 1 시간 정도의 로그를 보유할 수 있을정도로 커야됩니다.

보통 128M ~ 2G로 설정합니다.

 

 

 

 

-innodb_flush_log_at_trx_commit

커밋 작업에 대해 ACID를 준수하고, I/O 작업에 대하여 일괄 처리할 때 최대한 균형을 제어해줍니다.

0으로 설정하면 초당 로그가 기록이 되고, 디스크에 플러시 됩니다. 로그가 플러시되지 않은 트랜잭션은 충돌로 인해 손실될 수 있습니다.

1로 설정하면 각 트랜잭션 커밋마다 로그에 기록이 되고, 디스크에 플러시 됩니다.

2로 설정하면 각 트랜잭션 커밋 후에 로그가 기록이 되고, 초당 한번씩 디스크에 플러시됩니다. 로그가 플러시되지 않은 트랜잭션은 충돌로 인해 손실될 수 있습니다.

안전성을 위해서라면 1을 사용하고, 성능을 위해서라면 2를 사용합니다.

mysql에서는 완전한 ACID 준수를 위해서는 기본값은 1을 사용하라고 명시되어 있습니다.

1외의 값을 사용하면, 성능을 늘릴 수 있지만, 트랜잭션을 수행하지 못할 수도 있으며, 충돌이 발생할 수 있습니다.

-innodb_flush_method

InnoDB가 데이터 파일을 어떤 방식으로 읽고 쓰기를 할 건지에 대한 설정을 할 수 있습니다.

O_DSYNC : O_DSYNC는 로그를 열고, 플러시합니다. fsync 함수는 데이터 파일을 플러시하는데 사용됩니다.

O_DIRECT : O_DIRECT 또는 directio 함수는 데이터 파일을 여는데 사용되고, fsync 함수는 데이터와 로그를 플러시하는데 사용됩니다.

fsync : 기본값입니다.

이 값을 O_DIRECT로 설정하면 이중 버퍼링으로 인한 성능 저하를 피할 수 있습니다. 왜냐하면 OS 캐시를 사용하지 않기 때문입니다.

즉, OS 캐시를 사용하지 않고 DB가 데이터 파일에 바로 접근합니다.

이상입니다.

감사합니다.