|
MySQL은 그 동안 이른바 APM으로 일컬어지는 아파치, PHP, MySQL 환경으로 소형 시스템이나 웹 환경에 주로 적용되어 왔지만 최근 기업들의 오픈소스 적용 바람을 타고 업무 시스템에 광범위하게 도입되고 있다. 하지만 우리나라에는 MySQL만을 다루는 책이 거의 전무할 정도로 MySQL 데이터베이스 자체에 대한 정보나 이해가 부족한 실정이다.
1. MySQL 데이터베이스 최적화, MySQL 성능을 200%로
MySQL 모니터링과 서버 튜닝
MySQL이 오픈소스이기 때문일까? 오라클이나 MS-SQL의 경우 적절한 하드웨어에 온갖 튜닝이 다 된 상태로 사용하는 반면 MySQL은 그저 설치만 한 상태로 사용하는 경우가 많다. MySQL에 문제가 있다고 해서 기술지원을 나가보면 기본적인 설정에도 문제가 있는 경우도 허다하다. 우선 현재 시스템에 대한 모니터링을 통해 MySQL이 적절히 작동하고 있는지와 문제가 무엇인지부터 파악하자.
MySQL 데이터베이스 모니터링
튜닝의 시작은 현재 시스템의 상태와 문제점을 파악하는 것이 가장 우선일 것이다. 이를 위해 여러 가지 방법을 통해 시스템을 모니터링하는 것이다. 현재 MySQL을 모니터링하는 방법은 3가지가 있다. 첫째로 커맨드라인 명령어들을 이용해 모니터링하는 것이며 두 번째는 GUI 기반의 관리 툴인 MySQL Administrator를 통한 모니터링하는 것이다. 마지막으로 MySQL이 남긴 각종 로그를 통한 모니터링이 있다. 먼저 가장 기본적인 모니터링 방법인 커맨드라인 명령어들을 통한 모니터링에 대해 알아보자.
커맨드라인 명령어들을 통한 모니터링 커맨드라인 명령어들을 통한 모니터링의 가장 큰 장점은 어떤 환경에서도 수행이 가능하며 가장 빠르고 정확하게 자신이 원하는 바를 알아낼 수 있다는 것이다. MySQL의 커맨드라인 프로그램과 각종 SHOW 명령어들에 대해 자세히 살펴보자.
mysqladmin
SHOW ENGINES mysql >SHOW ENGINES
SHOW VARIABLES
SHOW STATUS
SHOW PROCESSLIST
SHOW TABLE/TABLE STATUS/INDEX/INNODB STATUS
GUI 기반의 모니터링
MySQL Administrator MySQL Administrator의 모니터링 기능의 백미는 바로 Health 메뉴이다. Health 메뉴에서는 기본적으로 Connection Health, Memory Health, Status Variables, System Variables 등 네 가지 항목을 가지고 있으며 이전 커맨드라인 모니터링에서 하던 대부분의 모니터링 작업을 여기서 수행할 수 있다. 그리고 가장 큰 특징이라면 기본적으로 보여주는 주요 사항에 대한 모니터링 외에도 SHOW STATUS를 통해 볼 수 있는 모든 항목에 대한 모니터링 그래프를 추가할 수 있다는 것이다. 이를 통해 직관적인 화면상의 변화를 보며 사용자 수의 변화나 시간대별 변화에 대한 쉽고 편한 모니터링을 할 수 있게 되었다.
MySQL Query Browser 로그를 통한 모니터링 적절한 수준의 로그를 남기는 것은 빠르고 건강한 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%’;
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
max_connections/back_log
skip-name-resolve
MySQL 메모리 튜닝
사실 데이터베이스 시스템 튜닝은 메모리 관련 파라미터를 조정하는 것이 90% 정도를 차지한다고 할 수 있을 정도로 데이터베이스 시스템의 성능은 메모리 관련 설정들에 큰 영향을 받는다. MySQL의 메모리 부분 튜닝은 사실 대부분 스토리지 엔진에 특화된 부분이다. 하지만 시스템 전체에 영향을 미치는 메모리 설정이 있는데 쓰레드 관련 메모리 설정과 쿼리 캐시관련 메모리 설정이 그러하다. 먼저 쓰레드 관련된 메모리 설정부터 살펴보자
쓰레드 관련 메모리 튜닝 MySQL은 커넥션마다 하나의 쓰레드를 생성시켜 요청을 처리하게 된다. 그래서 쓰레드가 생성되는 시점에 쓰레드에 메모리가 할당되며 많은 쓰레드가 생성되고 사라지면서 과부하가 발생한다. 일반적인 시스템에서는 쓰레드 관련 파라미터들의 조정할 필요는 없지만 부하가 심한 서버에서는 모니터링 결과에 따라 이 설정을 변경해 성능 향상을 이룰 수 있다. 먼저 현재 쓰레드와 관련된 상태를 알아보자.
mysql> SHOW STATUS LIKE ‘%THREAD%’;
앞에서 볼 수 있는 항목 중 Threads_connected가 Threads_ cached에 비해 매우 높다면 thread_cache_size를 높여줄 필요가 있다. Thread_cache_size는 지나치게 높여둘 필요는 없으며 일반적으로 threads_connected의 피크 치보다 약간 낮은 수치 정도를 설정하는 것이 좋다. 이를 통해 쓰레드가 생성되고 소멸되면서 겪게 되는 메모리, 각종 자원, 시간 등의 낭비를 줄일 수 있다. 쓰레드와 관련해 또 하나 설정할 수 있는 옵션은 thread_concurrency인데 이 옵션은 솔라리스 외의 시스템에서는 신경 쓸 필요가 없으며 솔라리스에서는 CPU 수에 2를 곱한 값을 넣어주면 된다.
캐싱 관련 메모리 튜닝
쿼리 캐시란? 쿼리 캐시란 빈번하게 수행되는 Select 관련 쿼리와 쿼리의 결과를 임시 저장하는 캐시 메모리이다. 데이터베이스 시스템에서 가장 시간이 많이 걸리는 것은 바로 디스크를 액세스하는 작업이다. 그러므로 디스크를 액세스하는 작업을 줄이는 것이 가장 크게 성능을 올리는 것이다. 쿼리 캐시는 Select 쿼리에만 해당되며 쿼리 캐시를 사용하지 않게 되거나 쿼리 캐시에 저장된 내용을 초기화하게 되는 경우는 다음과 같다.
◆ 데이터나 테이블 구조가 변경되었을 때
현실적으로 어려운 이야기지만 이 같은 경우는 줄이면 줄일수록 쿼리 캐시의 사용률과 효율을 높여 더 빠른 성능을 기대할 수 있다.
쿼리 캐시의 사용 먼저 현재 사용하고 있는 MySQL이 쿼리 캐시를 지원하는 버전인지 아닌지 확인하자.
mysql> SHOW VARIABLES LIKE ‘HAVE_QUERY_CACHE’;
만약 쿼리 캐시가 없는 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의 진정한 성능을 느껴 볼 수 있길 바란다.
저자 : 김병준│아이티브릿지 MySQL AB의 국내 골드 파트너인 아이티브릿지(www.itbridge.co.kr)의 MySQL 기술지원 팀장으로 MySQL을 비롯한 오픈소스에 대한 컨설팅과 튜닝 업무를 맡고 있다. 오픈소스 애플리케이션들을 기업 환경에 적절히 적용하는 것에 관심이 많다.
|
