主なストレージエンジン種類(テーブル毎に選択可能)
-
MyISA
- トランザクション無し
- 主にログ系でDWHとして使用
-
InnoDB
- トランザクション有り
- 運用テーブルはInnoDB一択
ストレージエンジンの仕組み
- クライアントからの接続受付。スレッドバッファを確保し、切断時にキャッシュ保存等
- クエリキャッシュヒット確認
- SQLがクエリキャッシュされてたら返す
- パーサ解析
- クエリ構文チェック
- SQL実行計画の決定(オプティマイザ)
- indexの可否、indexの選択等
テーブルのファイル構成
レコードのカラム情報や、ストレージエンジン情報等
* /var/lib/mysql/[user_name]/[table_name].frm
insertやupdateされたデータ
* /var/lib/mysql/[user_name]/[table_name].iba
InnoDBのパフォーマンス向上のためのmy.cnf設定
OSページキャッシュを使用しない
InnoDBはbuffer_poolにキャッシュする仕組みになっているので、
OSページキャッシュは、オーバーヘッドにしかならないので切る
innodb_flush_method = O_DIRECT
バッファプールのサイズを多めに割り当てる
バッファプール確認
SHOW VARIABLES LIKE "innodb_%_size"
InnoDBはOSページキャッシュを使わず、独自のバッファプールを確保し、テーブルデータをキャッシュするので、
かなり多めに設定する必要がある。
ただし、バッファプール以外にも5〜10%程メモリを使用する為、大体、全体の8割程度に設定しておく。
innodb_buffer_pool_size = 32G
上記に設定したメモリ容量をOSから割り当てられ、キャッシュする
例えば、以下のDBサーバがあるとする
* メモリ容量
* 8GB
- テーブルの容量
- userテーブル : 2GB
- tweetテーブル : 2GB
- user_tweetテーブル : 2GB
innodb_buffer_pool_size = 6G
上記の場合だと、
テーブルの容量が6GB(userテーブル+tweetテーブル+user_tweetテーブル)なので、
全てのテーブルデータがメモリ(バッファプール)に載っかるので、
I/Oが発生せず、高速にデータの更新、参照が可能。
クエリキャッシュについて
- クエリキャッシュは、更新時にグローバルロックを掛ける為、クエリが多い場合切った方がいい
- 更新したクエリに影響が出るキャッシュ情報を全てチェックし、削除するためパフォーマンスが出ない