日報 #73 - MySQLメモリ設定について
3連休だったそうです。
休みが3日というより、平日が4日になることのほうが色々問題だったりしますが。
ゆっくりゆっくりではございますが、
宿題だなぁと思っていたことについて着手したり、考えたりする余裕が増えてきております。
特にDB周り。
特にDBメモリ周り。
本当はテーブル設計のほうが圧倒的に重要なんですが、
改めてメモリ設計を見直してみたので、それについて少し。
割りと古い話ですけどご参考程度に。
僕が設定している(重要だと思っている)メモリ設定項目は、
全部で13個ほどあります。
その意味について、僕なりのイメージを書きつつ、大体の目安値を列挙していこうと思います。
ご参考になればどんじょ。
ちなみにメモリ15GB程度(RDSのdb.m1.xlargeでの設定)を想定して計算してみます。
そもそも、MySQLのDBメモリ周りはざっくりいって、グローバル領域と、スレッド領域にわかれるそうです。
グローバル領域は各コネクションが共通で使うメモリ領域で、
スレッドはスタックのような領域。
だもんで、合計メモリとしては、
グローバル+スレッド✕最大接続数
であって、これを超えるとスワップするか死ぬかします。
ということで、まずグローバル領域について。
1. key_buffer_size : 64MB
インデックスキーの領域。
要は辞書のようにあいうえお順でデータをまとめとくような領域。
これが広けりゃ、でっかいインデックスをメモリに乗っけられます。
思い切って64MBあててみるぜ。
2. query_cache_size : 16MB
クエリのキャッシュ領域。
同じクエリがあったら使いまわすために保存する領域。
グッと大きくしてみてテストしてみてもいいが、
そもそもあんまり大きいreturnをさせるようなクエリを書いちゃイカンので、、、
この程度に止めておきます。
3. table_open_cache : 16 KB
テーブルファイルへのポインタキャッシュ。
同じテーブル開くときいちいち探さないためにポインタを置いとく場所。
joinを含む、一度に開くテーブルの数とセッション数が重要な値になります。
とはいえ、そんなに大きくなくていいです。
4. innodb_buffer_pool_size : 1125 MB
InnoDBエンジンが走り回る領域。
これはとっても重要で、多分動かさないほうがいいですたい。
RDSの場合は全体の3/4を当てるような設計に自動的になっています。
(DBInstanceClassMemory*3/4 とParameter Groupsに記載があります。)
5. innodb_log_buffer_size : 8 MB
6. innodb_additional_mem_pool_size : 2 MB
7. thread_cache_size :
あ、ごめんこの辺はどーでもいいや。
以下今度はスレッド。
8. sort_buffer_size : 4 MB
ソートするときに利用する机の広さ。
Sort_merge_passesを見ながら決めるが妥当だが、
激しいソートを想定するようなクエリは元々やめましょうということでこの程度。
とはいえ、スレッド領域では一番おっきくしておきます。
怖いからね。
9. join_buffer_size : 512 KB
ジョインする時に利用する机の広さ。
256KBでもいい気がする…とてもする…
10. read_buffer_size : 512 KB
インデックスキーを使わないで頑張るときの机の広さ。
要はテーブルフルスキャン。
基本的にこれを使うシーンなんてあってはならないんだ!
だが、怖いからこんなに用意しちゃう俺。
11. read_rnd_buffer_size : 512 KB
ソートした後にgroup byとかする時に使う机の広さ。
結構大事っちゃ大事。
12. net_buffer_length : 32 KB
あ、ごめん。どーでもいい。
13. max_connections : 300
グローバル+スレッド✕最大接続数
この辺から逆算せい。
こうしておくと、大体グローバル領域で11.2GB、ローカル領域で1.6GB合わせて12.8GBということで、
いい具合で15GBに入ります。
ま、大体0.9〜0.8がけくらいの範囲に入れば御の字だと判断しています。
100%ふるふるは危険なので避けてます。
こんな感じでした。
メモリ設定項目の一部の領域は、RDSにおいて「再起動を促してくる」可能性があるので、
最初の時点で努々注意して設定することを勧めいたしまする。
かなり乱暴でしたが、この値は結構前にRoomClipで採用していた数値です。 もちろん最適解ではないし、クエリの都合プランでしたが、 メモリ周りで目立ったエラーは無かったです。 ご参考になれば、、、