前の会社ではmy.cnfの設定なんて全く触ってなかったですが、
どういう設定がいいのか調べてみたら、結構いろいろ出てきた。
一応設定例を以下に示す。
ここにあったのをまるっと頂いてきました。
OLTPでメモリ8GB/InnoDBがメインの場合
[mysqld]
# basic settings
port = 3306
socket = /tmp/mysql.sock
user = mysql
character-set-server = utf8
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp/mysql
core-file
# buffers
key_buffer = 128M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
max_connections = 512
thread_cache_size = 128
query_cache_size = 0
# binlog and replication
log-bin = mysql-bin
server-id = 1
max_binlog_size = 256M
# if the system is the master, comment out the following line.
# sync_binlog=1
# InnoDB configurations
innodb_buffer_pool_size=6G
innodb_additional_mem_pool_size=20M
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_log_files_in_group=2
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1
OLTPでメモリ2GB/InnoDBがメインの場合
[mysqld]
# basic settings
port = 3306
socket = /tmp/mysql.sock
user = mysql
character-set-server = utf8
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp/mysql
core-file
# buffers
key_buffer = 64M
max_allowed_packet = 10M
table_cache = 512
sort_buffer_size = 1M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
max_connections = 300
thread_cache_size = 100
query_cache_size = 0
# binlog and replication
log-bin = mysql-bin
server-id = 1
max_binlog_size = 256M
# if the system is the master, comment out the following line.
# sync_binlog=1
# InnoDB configurations
innodb_buffer_pool_size=1400M
innodb_additional_mem_pool_size=20M
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_log_files_in_group=2
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1
Webのバックエンドでメモリ2GB/MyISAMのみの場合
[mysqld]
# basic settings
port = 3306
socket = /tmp/mysql.sock
user = mysql
character-set-server = utf8
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp/mysql
core-file
# buffers
key_buffer = 512M
max_allowed_packet = 10M
table_cache = 4096
sort_buffer_size = 1M
read_buffer_size = 256K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
max_connections = 512
thread_cache_size = 128
query_cache_size = 64
query_cache_type = ON
# binlog and replication
log-bin = mysql-bin
server-id = 1
max_binlog_size = 256M
# if the system is the master, comment out the following line.
# sync_binlog=1
skip-innodb
Linuxの場合は、sysctl.confにおいて以下の設定をすること。
といった感じ。
どういった基準でこうなったのかは全く書いてないので、
とりあえず今回話題に上がったinnodb_buffer_pool_sizeに関して調べてみた。
グローバルバッファ
InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域
OS自身がキャッシュしない。
実装メモリの70-80％を目安に設定
innodb_buffer_pool_size=XXX
テーブルやインデックスデータをメモリに展開する際の上限
これを適正な値に引き上げることにより
Disk I/O が減らせ全体的なパフォーマンスアップにつながる。
ってことらしい。
http://www.sapa.ne.jp/manual-mysql51/storage-engines.html
このページ見ると「専用のデータベース サーバ上で、これをマシンの物理的メモリ サイズの最大80% に設定すると良いでしょう。」って書いてあるけど、
「物理的メモリの競合が OS 内でページングを引き起こす可能性があるので、あまり大きく設定しないでください。」とも書いてあって、結局どの位が適正なのかはわからない。
