더 나은 MySQL 성능을 위한 7가지 방법

원문보기

1. EXPLAIN 의 사용법을 배워라. 
– 슬로우 쿼리나, 사이트가 느려질때 processlist에 잔뜩 떠 있는 쿼리,  processlist 에 1초 이상 실행되는걸로 자주 보이는 쿼리가 있다면,
– explain 으로 쿼리의 index 와 실행형태를 보셔야 합니다.  적어도 쿼리가 index를 제대로 사용했는지는 볼수 있어야 합니다.

2. 제대로된 index를 생성하라 
– 1번에서 주로 문제가 되는건 index를 잘못 생성했거나, 너무 많이 생성한 경우입니다.
– index 는 많아도 문제가 됩니다. index가 많은 경우에 오히려 느려지는 경우도 있습니다.(대표적으로 merged intersection)

3.  디폴트값을 쓰지마라. 
– 이건 mysql 튜닝과 관련된 부분입니다. mysql 서버를 처음 설치하게 되면 my.cnf 가 디폴트값으로 들어갑니다.
디폴트값은 장비의 성능을 고려하여 책정된게 아닙니다. 특히 메모리와 버퍼 메모리에 관련된값은 시스템의 메모리를 고려하여 적정값으로 설정하여야 합니다. 적정값을 찾는데는 mysqltunner 가 도움이 될수 있습니다.

4. 가능한 메모리에 db를 유지하라 
– mysql은 데이타를 가능한 memory 에 로딩하여 처리합니다. innodb는 innodb_buffer_pool 에 설정된 크기만큼 메모리를 할당하여 로딩을 해둡니다. 이 값이 작으면, 디스크에서 메모리로 로딩하는 경우가 많아짐으로 성능이 저하되게 됩니다.
가급적 데이타베이스용량만큼은 설정되는게 좋습니다.(자주 안쓰는 테이블의 경우는 계산에서 빼도 됩니다.)
– db의 용량보다 메모리가 부족하다면 증설하는것도 고려해볼만합니다.
– 참고로 innodb_buffer_pool 외에도 버퍼를 많이 사용함으로 innodb_buffer_pool 은 현재 가용 메모리의 50~60%로 잡는게 좋습니다.)

5. SSD 를 사용하라 
– 당연한 애기지만, mysql은 disk read/write 가 많습니다. 4에서 메모리가 충분하더라도 로그를 기록하거나 bin 파일에 대한 write/purge 를 수행하게 됨으로 디스크속도가 느린경우 해당 잡이 실행될때 전체적인 성능저하가 일어납니다. 요즘은 mysql엔 대부분 ssd를 사용합니다.
–  mysql 은 장비 투자 순서는  memory > disk(ssd가 아니라면) > cpu(멀티코어보다는 cpu 클럭이 우선) 입니다.

6.  Scale out 
– 5번에서 하드웨어 성능을 더 이상 높힐수 없는 경우에, mysql을 스케일아웃 할수 있습니다.
– 여러가지 복잡한 방법들이 많지만, 가장 쉽게 스케일할수 있는 방법은 master /slave 방식입니다.
– 실행시간이 오래걸리는 배치잡이나 통계관련 쿼리, 관리자 페이지 select 쿼리들은 slave에서 요청하고
master 에서는 사용자 페이지 쿼리를 수행하게 합니다. 이렇게만 해도 피크시간대 db가 느려지는 문제를 많이 해결할수 있습니다.
– 게다가 슬레이브는 2대 3대로 늘릴수 있습니다.

7. 모니터링을 해라 
– mysql 모니터링 툴은 mysql 엔터프라즈 모니터링(유료), monyog(유료) 기타 다른 버전들 모두 유료입니다. pecona 툴은 무료인가 보네요.
– 국내 업체 whatap.io 에서 mysql 모니터링을 한달 5천(1만원인가) 사용할수 있습니다만, 어짜피 유료입니다.
– 유료툴이 아니더라도  mysql slow log만 잘 관찰하고 튜닝하더라도 많은 문제를 조기에 해결할수 있습니다.