MySQL Tuning for innodb #1

writtn by 배권한 a.k.a 다즐링 at 2008.06.19

== Chapter 3. open mind , open my.cnf #1 ==

my.cnf 를 열어보도록 합시다.
최신버젼을 깐다면 대부분의 경우 내용이 없습니다.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

자 이제 여기부터 mysql version 과 OS에 따라 옵션들이 바뀌기 시작합니다.
모든 설정은 중요 테이블들이 innodb 임을 가정하고 설정에 대해서 이야기를 드립니다.
mysql 5.0 을 기준으로 합니다.

innodb_buffer_pool_size:가장 중요한 값입니다. 기본값(default) 는 8메가정도인데 이러면 디비가 죽어납니다.
전체 메모리의 50~80% 를 권장드립니다. 단 64비트 OS에서 입니다. -_-;;; 32비트는 2기가를 넘지 못합니다.
( 대략 1600M 정도로 잡아주시면 좋습니다. ). 이 사이즈는 인덱스와 자주쓰는 테이블의 내용을 캐슁합니다.
메모리가 8기가일 경우 5G 혹은 6G 정도를 권장드립니다.

innodb_flush_method=O_DIRECT : 더블 버퍼링을 막아줍니다. 무슨 말이냐면 원래 linux상에서 disk io 는 남는
메모리에 캐슁을 합니다. 그러면 innodb_buffer_pool 과 중복이 됩니다. 그부분을 막아줍니다. 단 32비트에서
메모리가 2기가 이상이면 이 옵션을 꺼줍시다.(4기가머신이면)
innodb_buffer_pool 이 작은 경우에는 옵션을 꺼줌이 옳습니다. buffer_pool 이 모자라면 disk 접속을
하는 경우가 생기기때문에 그런경우를 위함입니다.
그리고 ext3 파일 시스템을 쓰더라도 O_DIRECT 옵션을 주는 경우는 저널링이 되지 않는것으로 알고 있습니다.
캐쉬가 있는 레이드카드를 쓰고 레이드카드에 배터리가 달려 있는 경우에 ( 혹은 데이터가 조금 날아갈수도..; )
추천드립니다. 어느정도 하드웨어가 설정이 되어 있어야 가능한 옵션입니다.
오라클에서 DIRECT_IO를 쓰는것과 같은 이유입니다.

innodb_file_per_table : 4.1에서는 문제가 많았던걸로 로그에 나오는데 5.0 부터는 아주 괜찬아진것으로
알고 있습니다. 단 테이블이 많은 경우는 기존의 방식대로 하나의 파일에 저장하는것이 좋습니다.
이것의 장점은 disk 추가시에 접속이 많은 테이블이라던가 사용량이 많은 테이블을 분리 가능합니다.
단 처음에 디비에 데이터를 넣기전에 ( create table 문이 실행되기전에 ) 설정되어 있어야합니다.

innodb_flush_log_at_trx_commit=2 innodb 가 커밋을 할때마다 커밋로그를 디스크에 기록하는것이 아니라 1초마다 기록합니다. mysqladmin status 를 보시면 avg query 가 나오는데 1초에 그만큼 io 가 일어나는 것은 좋치 않겠죠 ^_^;
1일 경우는 커밋이 일어날때마다 0일 경우는 log 가 꽉차거나 flush logs 를 할때까지 커밋을 하지 않습니다.
( 또 다른 경우도 있던거 같은데 기억에 없군요;) 값은 2를 추천드립니다.

innodb_log_file_size=256M 쿼리로그의 사이즈를 이야기 합니다. 256M 정도가 적절합니다.
innodb_log_buffer_size=4M blob 을 안쓰는 경우 ( 쿼리가 커지지 않는 경우 ) 4메가가 적절합니다.

innodb_thread_concurrency= CPU CORE 갯수 * 2 정도가 적정합니다.

몇부분 중요한 부분이 남았는데 그부분은 #3에서 다시금 이야기를 드리겠습니다.

== Chapter 4. do not believe yourself , but explain ==

디비튜닝에 있어서 가장 중요한점은.. 실시간성이 되겠습니다. 물론 그러지 않은 경우도 많치만
mysql 같은 통상적으로 "저렴한" 디비에서는 그런걸 요구하지 않으니까요. -_-;

그럼 결론은 쿼리튜닝이 제일 중요합니다.
먼저 느린 쿼리를 로깅하기 위해 my.cnf 를 열어봅시다.

long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log

물론 /var/log/mysql 디렉을 만들어주시고 mysql 권한으로 바꾸어 줍니다.

그런데 잘안되는 경우가 있더군요. 그래서 safe_mysqld 를 여시고 수정해야하는 경우도 있습니다.

일단 로깅이 되면 쿼리들을 보고 수행을 해봅니다.

explain query 를 날리시면 다음과 같습니다.

mysql> explain SELECT * FROM `posts`   WHERE (links_count > 0)  ORDER BY registered DESC LIMIT 0, 10;
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys                       | key                                 | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | posts | range | index_posts_on_links_count          | index_posts_on_links_count     | 5       | NULL | 196100 | Using where; Using filesort |
+----+-------------+-------+-------+-------------------------------------+-------------------------------------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

글이 너무 길어져서 다음으로 넘어갑니다. -_-;; =3 ( 감질맛나게 )