おもしろwebサービス開発日記

Ruby や Rails を中心に、web技術について書いています

MySQLのメモリ構成まとめ

一度マインドマップとして紙にまとめたけど、結局なかなか見返さないのでブログに書いて復習する試み。

インスタンス全体で共有するメモリ領域

クエリキャッシュ
  • SELECT文の実行結果をキャッシュする
  • query_cache_sizeで容量を指定
  • テーブルが更新されるとキャッシュは無効になる
  • query_cache_imit を超えるデータを返すSELECT文はテーブルキャッシュの対象外となる
  • SHOW STATUS LIKE 'Qcache%'; で統計情報を参照できる

一部のSQLをクエリキャッシュの対象外にしたいとき

一部のSQLをクエリキャッシュの対象にしたいときは

  • query_cache_type = 2
  • SELECT SQL_CACHE ...
スレッドキャッシュ
  • 接続終了後のサーバスレッドを再利用するためのキャッシュ。スレッド生成のオーバーヘッドを削減する。
  • thread_cache_size で指定
  • max_connections の三分の一程度の値が推奨らしい
  • thread_crated の値が急に増えるようなら設定を見直した方がいいらしい
テーブルキャッシュ
  • 一度開いたファイルのファイルポインタを格納するキャッシュ
  • テーブル数×同時接続数の値が推奨らしい
InnoDBテーブル用のキャッシュ

InnoDBバッファプール

  • InnoDB使うなら最重要のキャッシュ
  • innodb_buffer_pool_size で容量を指定
  • CRUD操作時のレコードデータのキャッシュ領域(インデックスもキャッシュする)
  • DB専用サーバであれば、メモリの60%〜80%を割り当てるのが推奨らしい

InnoDBログバッファ

InnoDB追加バッファ

MyISAMテーブル用のキャッシュ

キーバッファ

  • MyISAMテーブルのインデックス情報を格納する領域
  • MyISAMを多用する場合は容量を増やす

サーバスレッド専用のメモリ領域

  • 情報がメモリに収まりきらないときには、一時ファイルが使われる
バイナリログキャッシュ
リードバッファ
  • 全件検索に使用される
    • インデックスを使う場合も使わない場合も使われる
  • テーブル全体をスキャンするSQLをよく使うなら大きくするべき
ジョインバッファ
  • フルジョインにおいて、レコードデータのキャッシュに使われる
  • インデックスを使わない結合で使われる
  • 一回のジョインで一つのバッファが確保され、SQLが終了すると解放される
  • そもそもインデックスを使わない結合をやるべきでないので、大きくする必要はない
ソートバッファ
  • ソートの際に使用される
通信バッファ
  • SQLによりやりとりで使われるデータの最大長
  • max_allowed_packet で容量を指定
  • BLOB型やバルクINSERTなどを使う場合は大きくする
    • LOAD DATA は関係ない
スレッドスタック
  • スレッド固有の領域
  • thread_stack で容量を指定
  • 基本的に、デフォルト値のままでいいらしい

MySQLがトータルで使うメモリの最大値

  • 32ビット版Linuxでは最大で2Gを超えないようにする
  • インスタンス全体のメモリ + max_connections × セッション単位のメモリ