MySQLでInnoDB、最初にしておけばよかった7つのこと

ご無沙汰してます。kouです。
最近、MySQLのInnoDBを使ったシステム開発・運用でいろいろ痛い目に遭いましたので、忘れないようにまとめてみました。今思えば「最初にしておけば良かったなー」という項目を7点ほどピックアップ!
方針としては性能を確保しつつ運用しやすく安全志向で設定してます。データ量も更新量も少ない場合はここで書いたことは特に気にしなくてもいいかもしれません。
0. 自分に問いかけてみる
本当にInnoDBでいいですか?MyISAMじゃダメですか?他のストレージエンジンはどうですか?
自分の中では

  • トランザクションが必要
  • データ更新処理を伴う同時接続数が多い

のどちらかが求められたときにInnoDBを選択するようにしてます。
1. innodb_buffer_pool_size
InnoDBのストレージエンジンがキャッシュに利用するメモリサイズです。やはりメモリ内でデータ処理ができないようでは性能は出せません。サーバがMySQLデータベース専用でストレージエンジンがInnoDBに限られるなら物理メモリの7〜8割程度確保してしまいましょう。
2. innodb_log_file_size
更新ログのファイルサイズ。テーブルに対して更新された情報はいったん更新ログファイル(ib_logfile0とかib_logfile1)に格納されます。この更新ログファイルがいっぱいになるとやっとテーブルスペースに更新が反映されます。つまりログファイルサイズを大きくするとチェックポイントのフラッシュ回数を減らす(=ディスクI/O削減)ことが出来ます。ただし、下記のルールを守る必要があります。

innodb_log_files_in_group×innodb_log_file_size < innodb_buffer_pool_size

3. innodb_flush_method
O_DIRECTにする。OSでのキャッシュとInnoDBでのキャッシュの両方を利用するのは非効率なのでどちらかに統一。ただ、正直そんなにデフォルトのfdatasyncとの違いは無い気がします。
4. innodb_file_per_table
デフォルトの設定ではibdata1というテーブルスペースファイルに全てのInnoDBテーブルのデータが格納されます。データ量が増えるとこのファイルが巨大化していきます。一度大きくなるとデータ量を減らしてもファイルサイズを小さくすることはできません。 このオプションを設定するとテーブル単位でテーブルスペースファイルが作成されます。ディスクI/Oを分散させるためにテーブルスペースを別の物理ディスクに移動したり、(工夫すれば)テーブル単位でのバックアップやリストアでき、運用しやすくなります。
5. innodb_thread_concurrency
経験的に

CPU数×コア数〜(CPU数×コア数)×2

で設定。同時接続数や発行されるSQLによって性能がぶれるみたいなので、適正値は試行錯誤する必要があります。マニュアルでは

CPU数+ディスク数

が推奨されています。
6. innodb_status_file
起動オプションに"–innodb_status_file=1"を追加。このオプションで起動するとinnodb_status.というファイルにSHOW ENGINE INNODB STATUSの結果を定期的に出力してくれます。何らかの理由でMySQLがクラッシュしてしまった時に原因究明の参考になる情報があるかもしれません。
これ以外にも変更するパラーメータがありますがInnoDB固有のパラーメータではないので、ここでは割愛です。

データベースは本当に奥深く、未だに模索中です。



Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 16271409 bytes) in /home/yumeco/www/prod/wp-includes/wp-db.php on line 1171