第6回はMySQLのパラメタチューニング基礎として、MySQLのカテゴリ別のチューニングパラメタを解説します。

アーキテクチャ再確認

第1回で、

1. 接続からSQL実行までの共通部分
2. トランザクション管理やデータ永続化、インデックス管理などストレージエンジン固有部分

に大別されると説明しました。この共通部分もパラメタチューニングの際には2つに分けることができます。

• 接続したクライアントからのSQL文を処理するサーバスレッド（接続スレッド）が利用するメモリなどのリソース
• MySQLサーバ全体で利用されるメモリなどのリソース

設定可能なパラメタについてはリファレンスマニュアルの下記を参照して下さい。

• MySQL Server Version Reference :: 1 mysqld Option/Variable Reference

このページでは、設定可能なシステム変数はSrvr列にYesの表示がされています。Dyn列がYesのシステム変数はサーバを再起動せずにSETコマンドで動的に変更なことを表しています。またそれぞれのパラメタがどのバージョンで利用かも併せて表示されており、各バージョンに示されたYesからデフォルト値や最小値／最大値などそれぞれのバージョンでの具体的な情報にリンクされています。

接続スレッド関連のパラメタ

MySQLではクライアントから新しい接続が行われると、新たにスレッドを生成してSQL文の処理を行います。SQL文処理のために利用されるメモリの領域はこのスレッド毎に確保されます。

最大接続数

同時に接続できるクライアントの数の最大値は、生成されるスレッド数の最大値にもなります。MySQLサーバの最大接続数はグローバル変数max_connectionsで設定します。

• max_connections

最大接続数は通常システムの要件から算出して行きます。システムとして想定される同時接続数を見積もり、そのうちデータベースまで到達するリクエストの比率、それぞれのデータベースでの処理時間を待ち行列理論に照らして考慮します。データベースに到達する同時リクエスト数が同じだとしても、処理時間が違う場合はデータベース内で同時に実行されている処理の件数も違ってくるため、同時接続数も違ってきます。注意すべき点は、システム開発の早い段階では見積もりの精度が高くないため、ある程度余裕を持たせた設計で始め、かつ段階的に見積もりの精度を高めていくことが求められることです。

接続が切断されるとMySQLサーバ内の接続スレッドも破棄されます。コネクションプーリング機能を使用していない環境では、接続による接続スレッドの生成と切断による切断スレッドの破棄が繰り返され、オーバーヘッドとして無視できなくなります。このような状況はSHOW GLOBAL STATUS文のConnectionsとThread_の値を監視することで確認できます。下記の例ではSHOW GLOBAL STATUS文の代わりにInformation_schemaのGLOBAL_STATUSテーブルを利用しています。

mysql> SELECT * FROM GLOBAL_STATUS
     >   WHERE VARIABLE_NAME = 'Connections'
     >      OR VARIABLE_NAME LIKE 'Threads%';
+-------------------+----------------+
| VARIABLE_NAME     | VARIABLE_VALUE |
+-------------------+----------------+
| CONNECTIONS       | 2910337        |
| THREADS_CACHED    | 0              |
| THREADS_CONNECTED | 122            |
| THREADS_CREATED   | 2910096        |
| THREADS_RUNNING   | 45             |
+-------------------+----------------+
5 rows in set (0.02 sec)

上記の例ではこれまでの接続回数CONNECTIONSの値と生成されたスレッド数THREADS_CREATEDが近い値となっており、接続スレッドの生成と破棄が繰り返されたことがわかります。このような環境ではスレッドを破棄せずにキャッシュして再利用するためにthread_cache_sizeの値を設定します。

• thread_cache_size

スレッド毎にメモリを確保するパラメタは、接続数だけ個別に確保されるので同時接続数を大きすぎる値に設定してスワップ発生させてはいけません。さらに同時処理件数が大きくなると処理の競合などによってスループットが低下する傾向があります。同時接続数が大きくなることが見込まれている場合は、複数台のサーバに分割するか、スレッドのグループ化と内部的なキューイングの仕組みを持ったMySQL Enterprise Editionのスレッドプールプラグインの利用を検討してください。

• MySQL Enterprise Scalability

接続スレッド毎に確保されるメモリ

このカテゴリの代表的なパラメタはデータのソート時に使うメモリの領域、ソートバッファの最大値を設定するsort_buffer_sizeです。このパラメタのスコープはGlobalおよびSessionとなっており、サーバ全体に値を設定するだけではなく、現在接続中のセッションでの設定をSET文で一時的に変えることも可能です。

• sort_buffer_size

ファイルを利用したマージソートが行われるとパフォーマンスの面では不利になります。ファイルを利用したソートが行われている回数はSHOW GLOBAL STATUS文のSort_merge_passesに出力されます。また、実行計画を確認するEXPLAIN文の出力では、Extra列にUsing filesortと表示されます。

MySQLのファイルを使ったソートのアルゴリズムには2種類あります。BLOB型またはTEXT型が含まれたレコードをソートする場合には、ソートのキーとなる列と行のIDをソートバッファに格納して入りきらなかった場合にはファイルを使ったソートを行う、MySQLが以前から持っていたアルゴリズムでソートします。これらのデータ型が含まれておらず、かつソートキーの列とSELECT文で取得する列のサイズがmax_length_for_sort_dataを超えていない場合は、行のIDではなく行レコードそのものをソート対象レコードとするため、行を複数回のスキャンする必要がなくなり前者のアルゴリズムよりも高速化できます。

他に接続スレッド毎に設定可能なパラメタは以下の通りです。多くの場合はデフォルトで問題ありませんが、パーティショニングを行っているテーブルが多数ある場合や複雑なSQL文がある場合などにはthread_stackが小さすぎるというエラーが出ることがあります。

次項で紹介する一時表以外で最大メモリを利用する可能性があるサイズは下記の式で算出できます。

max_used_connections * ( 
    read_buffer_size + 
    read_rnd_buffer_size +
    join_buffer_size + 
    sort_buffer_size + 
    binlog_cache_size +
    thread_stack +
    2 * net_buffer_length
 )

一時表作成時に確保されるメモリ

MySQLではユーザがCREATE TEMPORARY TABLE文で明示的に一時表を作成できます。この一時表がMEMORYストレージエンジンを使ってメモリ上に作成されるサイズの上限がmax_heap_table_sizeです。この値を超えるサイズのテーブルはMyISAMストレージエンジンを使ってディスク上に作成されます。この値はテーブル毎のサイズの上限値であり、複数のメモリ上の一時表をそれぞれの接続で作成していくとMySQLサーバ全体では大量のメモリを使うことになりかねない点に注意が必要です。

• max_heap_table_size

UNION句を用いたクエリやTEMPTABLEアルゴリズムを用いたビュー、GROUP BYとORDER BYの列が異なる場合などは自動的に内部一時表が作成されます。この内部一時表がメモリ上に作成されるのは、格納されるデータがmax_heap_table_sizeまたはtmp_table_sizeのいずれかの最小値までとなります。

• 8.4.4 How MySQL Uses Internal Temporary Tables

サーバ全体のパラメタ

MySQLにはSELECT文の結果をキャッシュするクエリキャッシュ機能がありますが、アクセスがシングルスレッドで実行されているため複数のクライアントからアクセスされている環境ではボトルネックになります。またフラグメントが起きやすく、処理効率が低いため、MySQL 5.6ではデフォルトでは無効になっているほか、利用することも推奨できません。

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

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のスケールアップおよびスケールアウトの構成案と、それぞれの構成上の注意点をご紹介いたします。