MySQLのメモリの設定…
MySQLのメモリ周りの設定についてのメモ。
現在、自宅のサーバ上でblog(WordPress)とcacti(ネットワーク関連監視)のタメにMySQLを動かしている。
MySQLはデフォルト設定だと結構メモリを喰うのだが…何しろ動作させているDB自体がちゃちいワケで、そんなにメモリを使わせるのは全くもってエコではないと言える(スワップしてなきゃいいだろ、というのもある意味真理なのだが、ウチのサーバは他に開発用のMS SQL Serverも動いているので、メモリの取り合いは避けたい…)。
[設定対象]
MySQLインストールフォルダのmy.ini
[設定項目]
-全体に影響するもの
max_connection
最大同時接続数。トータルのメモリ使用量は(グローバルのメモリ使用量+接続スレッド毎のメモリ使用量*接続数)になるため、これが実装メモリ量を超えないように設定する。ウチのようにそもそもがちゃちいサーバなら思い切り絞ってもいい…。
thread_cache_size
生成した接続スレッドをメモリ上に保持する数。
MySQLは一度作成した接続スレッドを、次のクライアントアクセスのためにメモリ上に保持することが可能で、これにより多量の接続要求が来たときにスレッド作成のオーバヘッドを減らすことが可能。大量に接続がくるサーバでは多めに設定するといいらしい…。
table_cache
一度開かれたテーブルをメモリ上に保持する数。メモリ上のキャッシュを再利用することで同テーブルに対する複数のクエリの処理時のパフォーマンスを向上することが可能。
tmp_table_size
サブクエリ実行時にメモリ上に作成するテーブルの最大サイズ。サブクエリ実行時、テンポラリテーブルがこのサイズよりも大きくなると、ディスク上にMyISAMでテンポラリを書き出すためパフォーマンスが大きく低下する。サブクエリを多用するシステムでは大きめにとる必要がある。テンポラリテーブルはヒープテーブルとしてメモリ上に作成されるため、同時にmax_heap_table_sizeパラメータの調整が必要。
-MyISAM関連
myisam_max_sort_file
MyISAMのデータベースのインデックス構築を行う場合にソート用のバッファとして利用するファイルのサイズ上限…らしい。”CREATE INDEX”, “ALTER INDEX”, “REPAIR TABLE”, 及び”LOAD DATA”など多量のインデックスを生成する作業を実行する場合に拡大しておくといい模様。
myisam_sort_buffer_size
上記と同目的で利用されるメモリバッファサイズ。グローバルバッファ。通常のクエリでは使われないので、目的によって設定を調整しよう。
key_buffer_size
グローバルバッファ。MyISAMのインデックスをキャッシュするメモリサイズ。MyISAMを多用する場合は大きめにとるとパフォーマンスの向上が望める。
read_buffer_size
スレッドバッファ。インデックスを利用しないクエリ実行時にテーブルスキャンに利用されるメモリバッファ。インデックスを使わないクエリを発行する場合は大きめにとる必要がある…？
sort_buffer_size
スレッドバッファ。クエリ実行時にソート(ORDER BY, GROUP BY)に利用されるメモリ領域の上限。ドでかいテーブルにソートをかけた場合、このバッファをはみ出るとテンポラリに書き出しが発生してパフォーマンスが低下する…？
read_rnd_buffer_size
スレッドバッファ。ソート後のレコード読み込みに利用するメモリ領域の上限。上記と同様、ソートのパフォーマンスに影響する。
join_buffer_size
スレッドバッファ。インデックスを利用しないJOINの時に利用するメモリ領域の上限値。インデックスを利用しないJOIN自体使うべきではないと思われるが…。
-InnoDB関連
我が家ではInnoDBが利用されていないので…skip-innodb のみ記述。
設定変更後はmysqlサービスの再起動が必要。
ということで設定後のメモリ使用量は…
max_connections=32
query_cache_size=16M
table_cache=32
tmp_table_size=32M
thread_cache_size=8
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=32M
key_buffer_size=32M
read_buffer_size=256K
read_rnd_buffer_size=512K
sort_buffer_size=512K
join_buffer_size=256K
skip-innodb
これで、最大メモリ使用量は
32 * (512K + 256K + 256K + 512K + 16M) + 32M + 16M = 608M
ということになる…のかな。
メモリ使用量の計算は以下のエントリを参考にさせていただきました。
MySQL メモリ使用量の計算式