InnoDBのバッフアプールとMyISAMのキーバッファについて

概要

• InnoDBとMyISAMの違いについて解説
• その上で、それぞれのキャッシュの取り扱いと、キャッシュに関する値の最適化について解説

そもそも、InnoDBとMyISAMの違い

どちらも ストレージエンジン

MySQLは、リクエストから以下のような流れで、データのread/writeが行われる

コネクションプール
　　　　↓
　　　パーサー(SQLをパース)
　　　　↓
ストレージエンジン(SQLを元にストレージを読み書きする)
　　　　↓
　　ストレージ

MySQLではこの ストレージエンジン を MyISAMとInnoDBの2つから選ぶことができる

で、InnoDBとMyISAMどっちが良いの?

以前はMyISAMとInnoDBの棲み分けがあったが、今は概ねInnoDB

MySQL 5.5.5 からは、InnoDBがデフォルト

公式でもInnoDBの利点がいろいろ書かれて推奨されている

dev.mysql.com

InnoDBとMyISAMのキャッシュの取り扱い

InnoDBとMyISAMのおおきな違いの一つ

MyISAMの場合

MyISAMでは、indexのキャッシュはMySQLが管理。データのキャッシュはOSに任せている

• MyISAMは1テーブル1ファイル
• それにより、頻繁にアクセスされるファイルは、OSのキャッシュメモリに乗る
• そのため、全テーブルがキャッシュメモリに乗るようにメモリサイズを調整する必要がある

インデックスの情報は MySQL 側の key_buffer に格納

key_buffer のサイズは key_buffer_size で設定。最適な値について公式ドキュメントでは、以下のように書かれている

• マシンの合計メモリーの 25% 程度の値を推奨(残りはOSのキャッシュメモリに使わせる)
• SHOW STATUS して Key_reads/Key_read_requests > 0.01 or Key_writes/Key_write_requests < 1 ならば、インデックスが効率よくキャッシュされていないので、値を増やす

dev.mysql.com

InnoDBの場合

InnoDBでは、indexのキャッシュも、データのキャッシュもMySQLが管理

もうちょっと厳密に話すと、InnoDBではストレージの読み書きの前に バッファプール を経由する

• readのとき バッファプール にデータがあれば、読み込みはメモリ上で完結する
• writeのときも バッファプール のデータを更新して、非同期でストレージを更新する

そのため、バッファプールが小さいと頻繁にディスクアクセスが発生して処理が遅くなる

バッファプールは、innodb_buffer_pool_size でキャッシュサイズを設定可能

公式ドキュメントでは、innodb_buffer_pool_size はマシンのメモリの80%を推奨

dev.mysql.com

まとめ

• MyISAMでは、indexのキャッシュはMySQLが key_buffer で管理。データのキャッシュはOSに任せている
  • key_buffer_size の最適値はマシンの合計メモリーの 25%
• InnoDBでは、indexのキャッシュも、データのキャッシュもMySQLが innodb_buffer_pool_size で管理
  • innodb_buffer_pool_size の最適値はマシンのメモリの 80%