출처 : http://mssql.tistory.com/entry/MySQL-데이터베이스-최적화-MySQL-성능을-200로-1-MySQL-모니터링과-서버-튜닝

MySQL 데이터베이스 최적화, MySQL 성능을 200%로 1

MySQL 모니터링과 서버 튜닝

MySQL은 그 동안 이른바 APM으로 일컬어지는 아파치, PHP, MySQL 환경으로 소형 시스템이나 웹 환경에 주로 적용되어 왔지만 최근 기업들의 오픈소스 적용 바람을 타고 업무 시스템에 광범위하게 도입되고 있다. 하지만 우리나라에는 MySQL만을 다루는 책이 거의 전무할 정도로 MySQL 데이터베이스 자체에 대한 정보나 이해가 부족한 실정이다. 이번 연재를 통해 MySQL의 진정한 성능을 이끌어내자.

김병준│아이티브릿지

MySQL AB의 국내 골드 파트너인 아이티브릿지(www.itbridge.co.kr)의 MySQL 기술지원 팀장으로 MySQL을 비롯한 오픈소스에 대한 컨설팅과 튜닝 업무를 맡고 있다. 오픈소스 애플리케이션들을 기업 환경에 적절히 적용하는 것에 관심이 많다.

MySQL이 오픈소스이기 때문일까? 오라클이나 MS-SQL의 경우 적절한 하드웨어에 온갖 튜닝이 다 된 상태로 사용하는 반면 MySQL은 그저 설치만 한 상태로 사용하는 경우가 많다. MySQL에 문제가 있다고 해서 기술지원을 나가보면 기본적인 설정에도 문제가 있는 경우도 허다하다. 우선 현재 시스템에 대한 모니터링을 통해 MySQL이 적절히 작동하고 있는지와 문제가 무엇인지부터 파악하자.

MySQL 데이터베이스 모니터링

튜닝의 시작은 현재 시스템의 상태와 문제점을 파악하는 것이 가장 우선일 것이다. 이를 위해 여러 가지 방법을 통해 시스템을 모니터링하는 것이다. 현재 MySQL을 모니터링하는 방법은 3가지가 있다. 첫째로 커맨드라인 명령어들을 이용해 모니터링하는 것이며 두 번째는 GUI 기반의 관리 툴인 MySQL Administrator를 통한 모니터링하는 것이다. 마지막으로 MySQL이 남긴 각종 로그를 통한 모니터링이 있다. 먼저 가장 기본적인 모니터링 방법인 커맨드라인 명령어들을 통한 모니터링에 대해 알아보자.

커맨드라인 명령어들을 통한 모니터링

커맨드라인 명령어들을 통한 모니터링의 가장 큰 장점은 어떤 환경에서도 수행이 가능하며 가장 빠르고 정확하게 자신이 원하는 바를 알아낼 수 있다는 것이다. MySQL의 커맨드라인 프로그램과 각종 SHOW 명령어들에 대해 자세히 살펴보자.

mysqladmin
mysqladmin은 MySQL 데이터베이스의 커맨드라인 기반인 관리자 프로그램이다. Mysqladmin을 통해 시스템의 현재 설정 상황과 동작 상황을 모니터링할 수 있다. <표 1>은 mysqladmin을 통해 수행할 수 있는 성능관련 명령어들이다.

SHOW ENGINES
MySQL의 가장 큰 특징 중 하나는 여러 가지 스토리지 엔진을 가지고 있다는 것이다. 이 명령은 현재 MySQL의 시스템이 어떤 스토리지 엔진을 사용할 수 있는지 보여준다.

mysql >SHOW ENGINES
+—————-+———+—————————————————————-+
| Engine | Support | Comment |
+—————-+———+—————————————————————-+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| HEAP | YES | Alias for MEMORY |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| MERGE | YES | Collection of identical MyISAM tables |
| MRG_MYISAM | YES | Alias for MERGE |
| ISAM | NO | Obsolete storage engine, now replaced by MyISAM |
| MRG_ISAM | NO | Obsolete storage engine, now replaced by MERGE |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| INNOBASE | YES | Alias for INNODB |
| BDB | NO | Supports transactions and page-level locking |
| BERKELEYDB | NO | Alias for BDB |
| NDBCLUSTER | NO | Clustered, fault-tolerant, memory-based tables |
| NDB | NO | Alias for NDBCLUSTER |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | NO | CSV storage engine |
| BLACKHOLE | NO | Storage engine designed to act as null storage |
+—————-+———+—————————————————————-+

SHOW VARIABLES
MySQL은 설정 가능한 값들을 엄청나게 많이 가지고 있으며 SHOW VARIABLE 명령을 통해 현재 설정되어 있는 모든 값을 볼 수 있다. <화면 1>은 SHOW VARIABLES로 통해 살펴본 설정이다.
SHOW VARIABLES로 볼 경우 총 207개 정도의 변수가 표시된다. 오히려 너무 많아서 원하는 값을 찾기가 힘들 정도이다. 그래서 SHOW VARIABLES 명령 뒤에 LIKE ‘%키워드%’를 사용하면 원하는 값만을 볼 수 있다.

SHOW STATUS
MySQL은 내부적으로 동작 상황에 대한 실시간 통계 정보를 가지고 있다. SHOW STATUS는 이러한 통계 정보를 보기 위한 명령이다. 모니터링할 때 가장 기본이 되는 것이 바로 앞에서 설명한 SHOW VARIABLES의 정보와 SHOW STATUS의 정보이다. 웹 기반의 모니터링 툴을 비롯한 각종 모니터링 툴들이 바로 이 두 명령어를 통해 나온 정보를 조합해 사용하는 것이다. SHOW STATUS도 SHOW VARIABLES와 마찬가지로 LIKE ‘%키워드%’ 사용해 원하는 값만을 볼 수 있다.

SHOW PROCESSLIST
현재 동작하고 있는 MySQL 데이터베이스 서버의 동작중인 모든 쓰레드와 유저 커넥션 정보를 보기 위한 명령어이다. 이를 통해 얻어진 정보로 시스템 자원을 지나치게 많이 사용하거나 잘못된 수행을 하고 있는 프로세스를 죽일 수 있다.

SHOW TABLE/TABLE STATUS/INDEX/INNODB STATUS
SHOW TABLE 명령은 현재 데이터베이스에 존재하는 테이블에 대한 기본적인 정보를 보여주며 SHOW TABLE STATUS는 각 테이블의 생성 일자, 테이블 크기, 인덱스 크기 등 구체적인 정보를 보여준다. 하지만 이 때 주의할 점이 하나 있는데 바로 SHOW TABLE STATUS의 경우 테이블의 스토리지 엔진이 MyISAM인 경우에만 정확한 정보를 표시하며 InnoDB의 경우에는 부정확한 정보를 보여준다는 것이다. InnoDB 스토리지 엔진으로 되어 있는 테이블은 SHOW INNODB STATUS로 구체적인 정보를 확인할 수 있으며 SHOW INDEX를 통해 테이블의 인덱스에 대한 각종 정보를 볼 수 있다.

GUI 기반의 모니터링
그동안 MySQL의 경우에는 GUI 기반의 관리 툴에 대한 지원이 매우 미약했던 것이 사실이다. 하지만 올해 초 4.1 버전 발표 이후 연속적으로 GUI 기반의 관리 툴을 발표되었으며 그 완성도 또한 이전의 여러 GUI 프로그램들에 비해 비약적인 향상을 가져왔다. 빠르고 정확한 정보의 확인을 위해 커맨드라인 관리 툴들이 유용하지만 사실 일반적인 모니터링에는 GUI 기반의 모니터링 툴의 사용이 훨씬 편하다. MySQL이 새롭게 내놓은 GUI 기반 툴 중 모니터링을 위해 이용할 수 있는 툴은 MySQL Administrator와 MySQL Query Browser이다.

MySQL Administrator
MySQL의 GUI 기반 관리 툴인 MySQL Administrator는 기존의 GUI 관리 툴과는 달리 매우 다양한 관리 업무와 모니터링 작업을 편리하게 지원한다. 이 중 가장 돋보이는 기능은 모니터링 기능인데 이 툴로 인해 MySQL 튜닝 작업이 두 배는 편리해졌다고 말할 수 있을 정도이다. MySQL Administrator의 모니터링 관련 메뉴는 Server Connections, Health, Server Logs 등 이렇게 세 가지가 있다. <화면 3>과 같이 Server Connection은 커맨드라인 명령 중 SHOW PROCESSLIST와 같은 역할과 함께 각 유저 별 접속 현황을 알 수 있다.

MySQL Administrator의 모니터링 기능의 백미는 바로 Health 메뉴이다. Health 메뉴에서는 기본적으로 Connection Health, Memory Health, Status Variables, System Variables 등 네 가지 항목을 가지고 있으며 이전 커맨드라인 모니터링에서 하던 대부분의 모니터링 작업을 여기서 수행할 수 있다. 그리고 가장 큰 특징이라면 기본적으로 보여주는 주요 사항에 대한 모니터링 외에도 SHOW STATUS를 통해 볼 수 있는 모든 항목에 대한 모니터링 그래프를 추가할 수 있다는 것이다. 이를 통해 직관적인 화면상의 변화를 보며 사용자 수의 변화나 시간대별 변화에 대한 쉽고 편한 모니터링을 할 수 있게 되었다.

MySQL Query Browser
MySQL Query Browser는 기존의 MySQL 관리자나 프로그래머들이 많이 이용하던 SQLGate와 비슷한 역할을 수행하는 프로그램이다. GUI 상에서 MySQL 쿼리들을 수행할 수 있으며 여러 탭을 이용해 빠른 작업을 할 수 있게 되었다. 또한 도움말과 명령어들에 대한 하일라이팅을 지원함으로써 편리하고 정확한 작업을 할 수 있다. 앞의 커맨드라인 명령어들을 여기에서 모두 실행해 볼 수 있다. 초기 버전에서는 한글을 입력하면 다운되는 등의 치명적인 버그가 있었으나 지금은 수정되어 우리나라의 사용자들도 자유롭게 사용할 여건이 되었다.

로그를 통한 모니터링

적절한 수준의 로그를 남기는 것은 빠르고 건강한 MySQL을 유지하는 비결이다. 일반적으로 운영되는 서버라면 에러 로그와 슬로우 쿼리 로그를 남기는 정도로 충분하지만 서비스를 위한 시험 기간이거나 문제를 찾는 시점이라면 일반 쿼리 로그(General Query Log)를 남겨 어떤 쿼리가 가장 많이 사용되는지 파악하고 그 쿼리를 더 빠르게 할 수 있는 방법이 없는지를 찾는 것은 데이터베이스 최적화하는 좋은 방법 중 하나이다. 일반적으로 MySQL을 사용하는 사용자들의 경우 기본적으로 지원하는 에러 로그만을 남기고 슬로우 쿼리 로그를 남기지 않는 경우가 많은데 슬로우 쿼리는 MySQL의 성능을 떨어뜨리는 주범이다. 반드시 슬로우 쿼리 로그를 남기고 확인해 개선점을 찾도록 하자.

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의 진정한 성능을 느껴 볼 수 있길 바란다.

제공 : DB포탈사이트 DBguide.net