MySQL 서버 튜닝

MySQL 의 튜닝은 MySQL의 데이터베이스 시스템 관련 파라미터들에 대한 튜닝과 각각의 스토리지 엔진 관련 튜닝으로 나눠진다. 이번 호에서는 MySQL의 데이터베이스 시스템 즉 MySQL 전체 성능에 영향을 미치는 튜닝에 대해 알아보고 각각의 스토리지 엔진에 대한 튜닝과 최적화는 다음 호에 알아보자. MySQL의 시스템 관련 튜닝은 MySQL의 설정 파일인 my.cnf(윈도우의 경우는 my.ini) 파일을 수정하게 되며 MySQL 커넥션에 관한 부분과 메모리에 관한 부분으로 나눌 수 있다. 먼저 커넥션에 관한 부분부터 살펴보자.

MySQL 커넥션 튜닝

실제적으로 MySQL이 가장 많이 사용되는 분야를 꼽는다면 역시 인터넷 분야라고 할 수 있다. 포탈 사이트나 게임 사이트 등 부하가 매우 많이 발생하는 사이트에서 가장 문제되는 것은 MySQL의 커넥션에 관련된 문제이다. 커넥션에 관련된 모니터링은 SHOW STATUS LIKE ‘%CONNECT%’로 알아 볼 수 있다.

mysql> SHOW STATUS LIKE ‘%CONNECT%’;
+-------------------------------+---------------+
| Variable_name | Value |
+-------------------------------+---------------+
| Aborted_connects | 12 |
| Connections | 212 |
| Max_used_connections | 112176 |
| Threads_connected | 168 |
+-------------------------------+---------------+
4 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE ‘%CLIENT%’;
+-------------------------------+---------------+
| Variable_name | Value |
+-------------------------------+---------------+
| Aborted_clients | 2 |
+-------------------------------+---------------+
1 row in set (0.00 sec)

connect_timeout/interactive_timeout/wait_timeout
connect_timeout 은 MySQL이 클라이언트로부터 접속 요청을 받는 경우 몇 초까지 기다릴지를 설정하는 변수이다. 기본 값은 5초이며 일반적으로 수정할 필요는 없다. Interactive_timeout은 ‘mysql>’과 같은 콘솔이나 터미널 상에서의 클라이언트 접속을 말한다. 기본 값으로 8시간이 잡혀 있으나 1시간 정도로 낮추는 것이 좋다. 이런 접속은 그다지 빈번하지 않으며 작업을 위해 접속하는 경우가 많기에 따로 설정하지 않아도 큰 영향은 없다. 가장 중요한 것은 wait_ timeout으로 wait_timeout은 접속한 후 쿼리가 들어올 때까지 기다리는 시간이다. 접속이 많은 데이터베이스 시스템에서는 이 값을 낮춰 sleep 상태로 커넥션만 유지하고 있는 클라이언트들의 접속을 빠르게 끊어줘 동시 접속을 낮추는 것으로 전체 성능을 크게 향상시킬 수 있다.

하지만 주의할 점은 너무 낮추게 되면 실제로 서비스를 하기도 전에 끊어진다든지 지나치게 잦은 커넥션이 발생한다는 것이다. 일반적으로 15~20 사이의 값이 적당하며 SHOW STATUS를 통해 aborted_client가 가장 적게 발생하도록 값을 맞춰야 한다. Aborted client는 2% 아래인 것이 바람직하며 물론 없는 것이 가장 좋은 상태이다.

net_buffer_length/max_allowed_packet
MySQL 의 커넥션은 쓰레드 단위로 일어나는데 각 쓰레드가 생성되면서 메시지 전송을 위한 버퍼를 생성하게 된다. 일반적으로 max_allowed_packet만을 정해 놓는 경우가 많은데 net_buffer_ length를 설정해 두면 그 용량을 넘는 메시지를 전달해야 할 경우 자동으로 이 값을 늘리게 된다. 그러므로 가장 효율을 높이기 위해서는 net_buffer_length를 일반적인 쿼리에서 전송되는 바이트 값의 평균 정도를 생각하여 충분히 낮은 값을 설정해두고 max_allowed_ packet은 최대로 전송될 수 있는 높은 값을 설정하는 것이 좋다. max_allowed_packet은 1GB까지 설정할 수 있다.

max_connections/back_log
max_connections 는 서버가 허용하는 최대한의 커넥션 수이다. MySQL 데이터베이스를 운영하고 있는 서버의 사양에 따라 달라질 수 있으며 일반적으로 120~250개 정도로 설정하는 것이 보통이다. 하지만 접속이 많고 고용량 서버의 경우 1000개 정도의 높은 값을 설정하는 것도 가능하다. Too many connection 에러가 발생하지 않도록 적절한 값을 설정하는 것이 중요하다. Back_log의 경우 max_connection 이상의 접속이 발생할 때 얼마만큼의 커넥션을 큐에 보관할지에 대한 설정 값이다. 기본 값은 50이며 접속이 많은 서버의 경우 이 값을 늘릴 필요가 있다.

skip-name-resolve
외 부로부터 접속 요청을 받을 경우 인증을 위해 IP를 호스트네임으로 바꾸는 과정이 수행된다. 말하자면 hostname lookup 과정이 수행되는데 접속이 많은 서버에서는 이 과정에서 상당히 많은 과부하가 발생한다. 그러므로 인증 부분을 호스트 기반이 아닌 IP 기반으로 변경하고 이 같은 옵션을 통해 hostname lookup 과정을 생략하면 눈에 띄는 성능 향상을 경험할 수 있을 것이다.

MySQL 메모리 튜닝

사실 데이터베이스 시스템 튜닝은 메모리 관련 파라미터를 조정하는 것이 90% 정도를 차지한다고 할 수 있을 정도로 데이터베이스 시스템의 성능은 메모리 관련 설정들에 큰 영향을 받는다. MySQL의 메모리 부분 튜닝은 사실 대부분 스토리지 엔진에 특화된 부분이다. 하지만 시스템 전체에 영향을 미치는 메모리 설정이 있는데 쓰레드 관련 메모리 설정과 쿼리 캐시관련 메모리 설정이 그러하다. 먼저 쓰레드 관련된 메모리 설정부터 살펴보자

쓰레드 관련 메모리 튜닝
MySQL 은 커넥션마다 하나의 쓰레드를 생성시켜 요청을 처리하게 된다. 그래서 쓰레드가 생성되는 시점에 쓰레드에 메모리가 할당되며 많은 쓰레드가 생성되고 사라지면서 과부하가 발생한다. 일반적인 시스템에서는 쓰레드 관련 파라미터들의 조정할 필요는 없지만 부하가 심한 서버에서는 모니터링 결과에 따라 이 설정을 변경해 성능 향상을 이룰 수 있다. 먼저 현재 쓰레드와 관련된 상태를 알아보자.

mysql> SHOW STATUS LIKE ‘%THREAD%’;
+-------------------------------+---------------+
| Variable_name | Value |
+-------------------------------+---------------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 2 |
| Threads_running | 1 |
+-------------------------------+---------------+
6 rows in set (0.00 sec)

앞에서 볼 수 있는 항목 중 Threads_connected가 Threads_ cached에 비해 매우 높다면 thread_cache_size를 높여줄 필요가 있다. Thread_cache_size는 지나치게 높여둘 필요는 없으며 일반적으로 threads_connected의 피크 치보다 약간 낮은 수치 정도를 설정하는 것이 좋다. 이를 통해 쓰레드가 생성되고 소멸되면서 겪게 되는 메모리, 각종 자원, 시간 등의 낭비를 줄일 수 있다. 쓰레드와 관련해 또 하나 설정할 수 있는 옵션은 thread_concurrency인데 이 옵션은 솔라리스 외의 시스템에서는 신경 쓸 필요가 없으며 솔라리스에서는 CPU 수에 2를 곱한 값을 넣어주면 된다.

캐싱 관련 메모리 튜닝
MySQL 데이터베이스 시스템에서 메모리 관련 주요 설정들은 대부분 캐싱과 관련된 파라미터들이다. 버퍼 풀(buffer pool) 크기, 키 캐시(key cache) 크기, 쿼리 캐시 크기 등이 있는데, 이 중 앞의 두 개는 InnoDB와 MyISAM의 핵심 파라미터이기에 다음 호에 설명하게 되며 여기서 살펴볼 항목은 바로 쿼리 캐시에 관한 부분이다.

쿼리 캐시란?

쿼리 캐시란 빈번하게 수행되는 Select 관련 쿼리와 쿼리의 결과를 임시 저장하는 캐시 메모리이다. 데이터베이스 시스템에서 가장 시간이 많이 걸리는 것은 바로 디스크를 액세스하는 작업이다. 그러므로 디스크를 액세스하는 작업을 줄이는 것이 가장 크게 성능을 올리는 것이다. 쿼리 캐시는 Select 쿼리에만 해당되며 쿼리 캐시를 사용하지 않게 되거나 쿼리 캐시에 저장된 내용을 초기화하게 되는 경우는 다음과 같다.

◆ 데이터나 테이블 구조가 변경되었을 때
◆ 쿼리 캐시에 저장된 것과 다른 쿼리가 접수되었을 때
◆ 하나의 트랜잭션이 commit과 함께 마무리되었을 때
◆ 쿼리가 내부적으로 임시 테이블을 생성해야 할 때

현실적으로 어려운 이야기지만 이 같은 경우는 줄이면 줄일수록 쿼리 캐시의 사용률과 효율을 높여 더 빠른 성능을 기대할 수 있다.

쿼리 캐시의 사용

먼저 현재 사용하고 있는 MySQL이 쿼리 캐시를 지원하는 버전인지 아닌지 확인하자.

mysql> SHOW VARIABLES LIKE ‘HAVE_QUERY_CACHE’;
+-------------------------------+---------------+
| Variable_name | Value |
+-------------------------------+---------------+
| have_query_cache | YES |
+-------------------------------+---------------+
1 row in set (0.00 sec)

만약 쿼리 캐시가 없는 MySQL 버전을 사용하고 있다면 가능하면 업그레이드를 하도록 한다. 가장 쉽고 확실한 성능 향상법은 최신 버전의 소프트웨어를 사용하는 것이라는 것을 잊지 말자. MySQL의 경우 특히 4.1 버전 이후로 많은 부분에 있어 성능과 기능이 향상되었다. 아직도 3.x 버전을 사용하고 있다면 이번 기회에 업그레이드를 고려해 보는 것이 좋다.

쿼리 캐시를 지원하는 버전일 경우 ‘query_cache_size=64M’와 같은 방식으로 정확한 쿼리 캐시 크기를 정해 주는 것만으로 쿼리 캐시를 사용하게 된다. 그리고 쿼리 캐시의 동작 방식을 정해주는 옵션으로 query_cache_type이라는 옵션이 있는데 0은 쿼리 캐시를 비활성화시키게 되고 1은 사용 가능한 모든 쿼리가 쿼리 캐시를 이용하게 되며, 2는 쿼리 캐시를 이용하라고 정해주는 쿼리만 쿼리 캐시를 이용하게 된다. 2의 경우는 쿼리문 뒤에 SQL_CACHE라고 덧붙여주면 된다.

쿼리 캐시 최적화

데이터베이스 관련 모든 메모리 설정은 높다고 다 좋은 것이 아니다. 중요한 것은 균형 값을 찾아내는 것이다. 왜냐하면 쿼리 캐시와 MyISAM의 키 캐시, InnoDB의 버퍼 풀은 소중한 메모리 공간을 놓고 서로 경쟁하는 관계이기 때문이다.

먼 저 쿼리 캐시 크기를 결정해야 한다. 일반적으로 시스템 전체 메모리의 5%에서 10% 사이를 사용하는 것이 보통이다. 일단 이 사이의 값으로 설정한 후 모니터링을 통해 쿼리 캐시 사용률이 100%에 가깝도록 하는 것이 좋다. 이를 모니터링하는 가장 좋은 방법은 MySQL Administrator를 사용하는 것으로 MySQL Administ rator의 Health 부분에서 쿼리 캐시의 효율을 지속적으로 모니터링할 수 있기 때문이다.
다음으로 쿼리 캐시에서 받아들일 쿼리의 최대 크기를 설정하는 것이 필요하다. Query_cache_limit 옵션으로써 기본 값은 1MB이나 이는 너무 큰 값일 경우가 많다. 빈번하게 사용되는 쿼리의 용량이 어느 정도인지 살펴본 후 이보다 10% 정도 높은 값을 설정하자.

DB 튜닝은 과학이 아니라 예술

데 이터베이스의 튜닝은 무조건 높고 가장 좋은 것을 찾는 과학이라기보다는 균형의 미를 찾는 예술이라고 할 수 있다. 하나를 높이면 그만큼 다른 부분에서 손해를 보는 만큼 그 사이의 최적의 값을 찾는 것이 중요하며 이는 지속적인 모니터링으로 얻어질 수 있는 부분이다. 이번 호에서는 MySQL의 모니터링과 서버 전반에 대한 튜닝에 대해 알아봤다. 다음 호에는 MySQL의 성능과 바로 직결되는 부분인 MyISAM과 InnoDB 두 스토리지 엔진의 튜닝에 대해 알아보기로 하며 이번 연재를 통해 많은 분들이 MySQL의 진정한 성능을 느껴 볼 수 있길 바란다.