MySQL 서버 튜닝, 최적화 검토 사항 (MySQL Server Tuning)

모든 운영체제에서 가장 중요한 모니터링 지표 중 하나인 CPU 사용률,

mysql 서버를 운영한다면 DBA는 현재 mysql  서버가 성능적으로 안정적인 상태인지 자주 확인해야 한다. 모니터링을 통해 운영 중인 mysql 서버가 불안정한 상태를 보이거나 시스템 자원 부족으로 서비스에 문제가 발생할 가능성이 있다면 이를 인지하고 최적화까지 고려해야 한다.

 

1. CPU Iowait이 높은 케이스

출처: https://www.toofishes.net/blog/mysql-and-tmp-tmpfs/

CPU Iowait의 사용률이 높은 경우, 사용량의 절대량이 높거나 사용이 전체적으로 높다면 그중 CPU Iowait 사용량의 비율이 상대적으로 높으면 디스크의 IO 오퍼레이션에 의한 병목, 지연이 발생하고 있다고 볼 수 있다.

 

일반적인 원인

1. 전체 쿼리 트래픽에 대한 서버의 처리량을 디스크 성능이 뒷받침하지 못하는 경우: 쿼리 최적화와 시스템 변수도 적절하게 되어 있다면, 디스크를 고성능으로 교체하거나 물리적인 디스크 분할을 검토해야 한다.
2. 최적화되지 않은 쿼리의 빈번한 실행: 디스크 의존도가 높은 쿼리가 빈번히 실행 중이라 할 수 있다. 이 경우가 디스크 IO 지연 대부분을 차지한다. 전체 테이블 FULL SCAN, GROUP BY, ORDER BY 등의 정렬 작업이 메모리에서 이뤄지지 못하고 디스크를 사용하는 쿼리를 실행, MySQL은 서브 쿼리로 조회된 결과 데이터를 임시 테이블에 적재해서 처리를 하는데 이 데이터의 양이 많아서 메모리에서 처리할 수 없을 때 임시 테이블을 생성해서 처리하게 된다.
3. MySQL 시스템의 변수가 서비스 유형과 하드웨어 자원을 최적으로 사용할 수 있게 설정하지 못한 경우. 최적의 시스템 변수 설정값은 MySQL 서버가 처리하는 쿼리의 양, 저장된 데이터의 양, 하드웨어 자원 사용률, DB 모델링 특성, 서비스 유형 등에 따라 적절히 고려해서 설정해야 한다.

 

2. CPU User 사용률이 높은 케이스

 

Iowait과 CPU System의 사용률이 극히 낮은데, CPU User 사용량이 높은 경우, MySQL 서버의 서버 프로세스가 거의 CPU를 사용하고 있는 상태라 할 수 있다. CPU 전체 사용률이 낮은 상태에서 CPU User의 비율이 높다면 일반적으로 MySQL 서버 부하나 병목 없이 안정적으로 운영되는 경우이다. 혹시나 무거운 쿼리가 발생하고 있다고 해도 전체적인 부하에는 크게 영향이 없다는 것이다. 하지만 전체적인 CPU 사용량이 높은 상황에서 CPU User의 비율이 높다면 무거운 쿼리가 발생하고 있을 가능성이 높다. 만약 실행 쿼리가 성능적으로 최적화된 상태라면 MySQL 서버가 처리할 수 있는 쿼리 트래픽의 상한선에 가까운 수의 쿼리가 실행되고 있거나 최대 동시 쿼리 처리량인 MAX CONCURRENT QUERY THROUGHPUT이 서버의 하드웨어가 수용할 수 있는 한계점에 도달했다고 볼 수 있다. 관련 Application 튜닝을 통해 DB 의존도를 낮추거나 메모리를 증설해서 CPU 의존도를 낮추는 것도 방법이다.

 

3.CPU System의 사용률이 높은 케이스

 

리눅스 커널 프로세스의 CPU 사용률이 높다는 것으로 볼 수 있다. 서버를 MySQL DB 용도로만 사용하지 않고 웹 서버, 파일 서버 등 사용한다면 서버가 다른 프로세스에 의해 커널 프로세스의 사용률이 높아지는 것은 아닌지 확인이 필요하다.

리눅스는 메모리의 효율성을 위해 전체 메모리에서 미리 Buffer + Cached 값을 자동으로 할당해 놓는다. Application에서 메모리가 필요한 경우에는 Cached에 할당한 메모리를 자동으로 반환하여 할당한다.

메모리는 크게 전역적 메모리, 지역적 메모리로 나눌 수 있다. 전역적 메모리 할당 공간의 데이터는 서버에 접속한 모든 커넥션 또는 스레드에서 공유해서 사용하는 영역이다. 지역적 메모리는 Sort Buffer, Read Buffer 등과 같이 세션별로 할당되고 다른 세션과 공유되지 않는다. 서버에 사용하는 전체 메모리를 예상하고 각 메모리를 할당해야 한다면 다음을 참고한다.

 

전역적 메모리 사용량의 합 + (지역적 메모리 사용량의 합 * 최대 동시 커넥션 개수)

 

만약 물리 메모리에 비해 InnoDB 버퍼 풀의 크기를 과도하게 높게 설정했을 경우, 활성 DB 연결 수 (Active DB Connections)가 증가해 세션 단위로 메모리 사용량이 증가한다면 MySQL 서버가 사용하는 전체 메모리 사용량이 물리 메모리를 초과하게 될 수 있다. 즉, 메모리 누수(Leak)나 메모리 Swap 현상이 발생할 수 있으며, 결과적으로 성능 저하가 발생한다. 서버의 메모리 사용량을 설정할 때는 다음과 같은 순서로 설정하는 것이 좋다.

1. 물리 메모리에서 OS가 사용해야 하는 적정 메모리 사용량을 설정
2. MySQL 서버가 처리해야 하는 최대 연결 수 (Max Connections)를 정한다.
3. 최대 연결 수만큼 동시 세션이 증가할 수 있음을 고려하여 MySQL 서버가 세션별로 사용해야 하는 메모리 영역에 대한 사용량을 정한다.
4. 남은 여유 물리 메모리 양에서 MySQL 서버가 전역적으로 사용해야 하는 메모리 영역에 대한 최대 사용량을 설정한다.