大規模サイトの作り方 database 編

2012-02-13

フロントエンドのサーバが完成したので、次はバックエンドのデータベースサーバですね。
今回はレプリケーション機能も随分前から実装されていて実績のある MySQL で構築します。

MySQL の設定である my.cnf について簡単に解説していきます。

全体の設定

[mysqld]
default-character-set   = utf8

文字コードを明示的に UTF-8 にします。
本番サーバとテストサーバと開発サーバと……
みたいな場合に設定がぬけてたりすると混乱の元になりますので。

server-id               = 100
log-bin                 = mysql-bin
log-bin-index           = mysql-bin
relay-log               = relay-bin
relay-log-index         = relay-bin

レプリケーション関係の設定です。server-id は一意である必要があります。
バイナリログ(log-bin, log-bin-index)についてはマスタだけでいいのですが、スレーブがマスタに昇格する際にないと困るのでスレーブでも設定しておきます。

最適化(global)

global buffer と呼ばれる共通の設定なので、搭載メモリに合わせてガッツリ確保しましょう。

key_buffer              = 256M

MyISAM のキー(index)をメモリ上にキャッシュする領域。

table_cache             = 512

キャッシュするテーブル数。
ただし InnoDB はほとんどの場合依存しません。

query_cache_size        = 32M

クエリキャッシュに使用する領域。
大きすぎると不具合が出るので最大でも 256M 以下にすべきでしょう。

max_connections         = 100

最大接続数。
接続ごとに後述する thread buffer を消費するので、搭載メモリ量による限界があります。
使用メモリ量 = global buffers + (thread buffers * max_connections)

global buffers = key_buffer_size +
                 innodb_buffer_pool_size +
                 innodb_log_buffer_size +
                 innodb_additional_mem_pool_size +
                 net_buffer_length
thread buffers = sort_buffer_size +
                 myisam_sort_buffer_size +
                 read_buffer_size +
                 join_buffer_size +
                 read_rnd_buffer_size

ちなみに起動したまま MySQL コマンドで以下のように変更する事も可能です。

set global max_connections=100;

thread_cache_size       = 10

接続に関連づけされておらず接続に利用できるスレッドの保持数。
Threads_Connected 変数を監視して、増減の差を設定するのがよさそうです。

起動したまま変更するには以下のようにします。

set global thread_cache_size=10;

thread_concurrency      = 4

論理 CPU 数の2倍を割り当てておくと良いらしいです。

最適化(thread)

thread buffer は接続ごとに確保されるものですので、不要な物はとにかく小さく設定した方が良いです。
前述の max_connections の項目をご参照下さい。

myisam_sort_buffer_size = 1M

MyISAM で REPAIR TABLE / CREATE INDEX / ALTER INDEX の際に使われる。
通常のクエリでは使われないものです。

sort_buffer_size        = 2M

ORDER BY や GROUP BY の時に使われるメモリ上の領域。

read_buffer_size        = 2M

index を用いないテーブルスキャンの時に使われるメモリ上の領域。
そもそもそのようなクエリは避けるべきです。

read_rnd_buffer_size    = 8M

ソート後にレコードを読むときに使われるメモリ上の領域。
ディスク I/O が減るので ORDER BY の性能向上が期待できます。

join_buffer_size        = 56K

index を用いないテーブル結合の時に使われるメモリ上の領域。
そもそもそのようなクエリは避けるべきです。

最適化(InnoDB 専用)

innodb_file_per_table

テーブルごとにファイルをわけます。
必ずしも最適化に必要ではありません。

innodb_buffer_pool_size = 4G

メモリの50%～80%を割り当てると公式にはありますが、割り当てすぎると不具合が出るようなので要調整。

innodb_additional_mem_pool_size = 20M

InnoDB の内部データを保持するための領域。
エラーログに足りなくなった旨が表示されるようになってから増やしても良い。

innodb_log_buffer_size  = 64M

InnoDB の更新ログを記録するメモリ上の領域。
COMMIT ごとにディスクにフラッシュされるのであまり大きくしても意味がないようです。

innodb_log_file_size    = 2047M

InnoDB の更新ログを記録するディスク上の領域。
innodb_buffer_pool_size の25%を割り当てると公式にはありますが、以下のような制限があるようです。
innodb_buffer_pool_size / innodb_log_files_in_group(default:2) より小さくなければならない
32bit システムでは 4GB を越えてはならない
また試した限りでは、以下の制限があるように見えました。
innodb_log_file_size * innodb_log_files_in_group(default:2) が4GBを越えてはならない

innodb_flush_log_at_trx_commit = 1

デフォルト。COMMIT ごとに innodb_log_buffer から innodb_log_file にフラッシュする。

innodb_lock_wait_timeout= 50

ロックされてから待機する秒数。
この時間待機してもロックが解除されない場合、検出できないデッドロックが発生したと見なして ROLLBACK する。

最適化のための調査ログ

log-slow-queries        = /var/log/mysqld-slow.log

遅いクエリをログに残す。
MySQL 起動前に touch /var/log/mysqld-slow.log を実行する必要があります。

long-query-time         = 10

遅いと判断する秒数。
ここで設定した秒数を超過した SQL 文について、
log-slow-queries で指定したファイルに書き出されるようになります。

log-slow-admin-statements

管理系のコマンドについても遅いクエリはログに残す。

まだまだやりようがあると思いますが、とりあえずはこの辺を設定して運用してから少しずつ追い込む形が良さそうです。

今回の内容については以下の図書が参考になります。