同時にテーブルを開いておく設定

MySQLサーバ全体で同時に開いておくことができる数はtable_open_cacheで定義可能です。SHOW GLOBAL STATUS文のOpened_tablesが増加していく場合は，このtable_open_cacheの値を大きくすることでテーブルを繰り返し開くことによるオーバーヘッドを削減できます。

• table_open_cache

テーブル数が多い場合やパーティショニングが行われているテーブルが多い場合には，table_open_cacheの値およびInnoDBの表領域ファイル.ibdを同時に開くことができる数の上限innodb_open_filesの値を大きくしておきます。テーブルの定義ファイル.frmをキャッシュしておく数はtable_definition_cacheで設定可能ですが，MySQL 5.6ではデフォルト値-1に設定しておくと下記の数式で自動的に設定されます。

400 + (table_open_cache / 2)

InnoDBのパラメタ

MySQL 5.6からデフォルトのストレージエンジンとなったInnoDBのチューニングポイントを解説します。最も重要なパラメタはInnoDBのデータブロックをキャッシュするメモリ領域バッファプールのサイズを指定するinnodb_buffer_pool_sizeです。他のパラメタに必要なメモリを割り当てた後の空き領域の80％程度を設定するのが一般的です。あえて空き領域の80％程度とするのはスワップなどが発生しないようにするためです。

• innodb_buffer_pool_size

バッファプールのサイズが1GBを超えると，innodb_buffer_pool_instancesで指定した数のインスタンスに分割して同時実行性能を高めることが期待できます。各インスタンスが1GB以上になるようにバッファプールの数とインスタンス数を設定することが効率を高めるために有効とされています。

InnoDBのトランザクションログ

トランザクションコミット時にトランザクション内容を記録するのがInnoDBログです。ログファイルのサイズはinnodb_log_file_sizeで指定し，innodb_log_files_in_groupの数だけInnoDBログのファイルが作成されます。InnoDBログのサイズの最大値は，ログ全体（innodb_log_file_size × innodb_log_files_in_group）で512GBです。InnoDBログを大きくするとログからデータファイルへのチェックポイント処理が削減できるためスループットの向上が期待できますが，シャットダウンや障害から復旧後のクラッシュリカバリ処理の時間が長くなります。ただこのクラッシュリカバリ処理の時間はMySQL 5.5で劇的に改善されたため，innodb_log_file_sizeを2GB以上のサイズに設定することも一般的です。多数の行を挿入や更新するトランザクションが多数ある場合には，ログバッファをinnodb_log_buffer_sizeの値で大きくしておきます（一般的には32MB程度⁠）⁠。

バックグラウンドでのIOの上限設定

InnoDBのデフォルトの設定では，バッファプールからディスクへの書き出し処理などのIO性能の上限を，ハードディスク2本がストライピングされた程度のIOPS（秒間IO処理数）を想定した値200に設定されています。ハードディスクよりも高いIOPSを持つSSDや高性能な外部ストレージの場合は，innodb_io_capacityの値を1000程度に大きくしておきます。ストレージの潜在性能のIOPSよりも遙かに大きすぎる値にすると，書き込み処理の競合などが起こってしまい逆に性能低下を招きかねません。

ストレージのディスクが多数ある場合などは，データの読み書きをするスレッド数の値innodb_read_io_threadsとinnodb_write_io_threadsを増やすことを検討してください。

InnoDBのスレッド数の設定

デフォルトではInnoDBのトランザクションの処理スレッドは，接続クライアントからトランザクションが実行された数だけ無制限に生成されます。同時実行数が多い環境のMySQL 5.6ではinnodb_thread_concurrencyを128程度に設定して，設定値を上下に変更してベンチマークを行って最適値を探すことがあります。CPUのスレッド数（OS上で見えるコア数）が32以下の場合は，スレッド数にあわせておくことが一般的な指針です。MySQL Enterprise Editionのスレッドプール機能を使っている場合は，innodb_thread_concurrencyをデフォルト値の0（無制限）のままで問題ありません。

リスクを伴うパラメタチューニング

コミット時にInnoDBログにトランザクションを記録する際にディスクへのフラッシュをどのタイミングで行うかを設定するパラメタがinnodb_flush_log_at_trx_commitです。このパラメタはデフォルトで確実にログをディスクに書き出す最も安全な設定になっているため，変更することは推奨できません。ただリスクを理解し，他のノードからデータを復元できる環境などでは値を0に変更することで，ディスクへの同期を行わなくなるためレイテンシの短縮が見込めます。

また，データのディスクへの書き込みの不整合を防ぐために，InnoDBはダブルライトという仕組みを持っています。実際のデータファイルへの書き込みの前に，ダブルライトバッファという領域にデータを確実に書き込めてからデータファイルの該当のポジションに書き込みます。ファイルシステムでアトミックな操作が可能であれば，--skip-innodb_doublewriteオプションを有効にしてIO量を削減することも可能です。

まとめ

いずれのパラメタのチューニングでも，必ずテストをしてから本番環境に反映することを心がけてください。また，テストの際には，複数のパラメタを一度に変えるのではなく，基本的に一度に変更するパラメタは1つだけにして変更の影響を見極めていきます。

次回は

次回はMySQLのスケールアップおよびスケールアウトの構成案と，それぞれの構成上の注意点をご紹介いたします。