Amazon Web Services ブログ

Amazon RDS for MySQL のパラメータ設定 パート 1: パフォーマンス関連のパラメータ

Amazon RDS for MySQL を使用すると、コスト効率が高く、サイズ変更が可能なハードウェア容量を持ったうえで、スケーラブルな MySQL サーバーをわずか数分でデプロイできます。Amazon RDS が、バックアップ、ソフトウェアのパッチ適用、モニタリング、スケーリング、レプリケーションなど、時間を要するデータベース管理タスクを処理することで、お客様はアプリケーション開発に集中できます。

Amazon RDS for MySQL の大多数のユーザーは、400 を超えるサーバー設定パラメータの値を変更する必要がありません。ただし、Amazon RDS のデフォルト設定をカスタマイズしたい場合は、AWS マネジメントコンソール、または AWS CLI から、カスタムパラメータグループを作成して、お使いのデータベースインスタンスに適用できます。

パラメータ設定の基本例

パラメータグループを使用することで、パラメータをグローバルに設定できます。または、SET コマンドを使用して、特定のセッションに対して設定できます。パラメータグループからは、パラメータはすべてのセッションを対象にしてグローバルに設定されます。その一方、SET コマンドを使用すると、パラメータ値の設定対象は特定のセッションにのみに設定できます。その場合は、SESSION、@@session、@@ のいずれかの変数名が優先されます。SET sort_buffer_size=10000; や SET @@local.sort_buffer_size=10000; が、そのような SET ステートメントの一例です。

修飾子が存在しない場合は、SET がセッション変数を変更します。お使いのインスタンスにデフォルトのパラメータグループを使用する場合は、まずカスタムパラメータグループを作成して、インスタンスにアタッチする必要があります。カスタムパラメータグループの使用の詳細については、AWS サポートの記事『Amazon RDS DB パラメータグループの値の変更方法を教えてください』を参照してください。

パラメータには、静的パラメータか動的パラメータが考えられます。静的パラメータの変更はインスタンスを再起動して有効にする必要があります。動的パラメータの変更は再起動の必要はなくオンラインで変更が有効になります。

動的パラメータの設定には、「セッションスコープ」と「グローバルスコープ」があります。変数のグローバルスコープとは、そのインパクトがサーバー全体、なおかつすべてのセッションに対しておよぶことを意味します。これに対して、変数のセッションスコープは、有効になるのが設定対象のセッションに限られます。グローバルスコープとセッションスコープの両方を持つ変数も一部にあります。この場合は、グローバルの値がセッションスコープのデフォルトとなります。セッションスコープを合わせ持つパラメータに対するグローバルな変更のインパクトがおよぶのは新規セッションに限られます。接続の切断と再接続、データベースの再起動、現在の接続に対するセッション設定の変更のいずれかが必要となります。

変数の現在の値をクエリするには、show variable コマンドを使用します。以下に例を示します。

show variables like "max_connections";

このコマンドは以下のような結果セットを返し、現在の最大接続数の設定を示します。

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

上で示した show コマンドのようにキーワード global を指定してしない場合は、表示される結果はセッションレベルの値となります。たとえば、セッションレベルの値を表示するには、show session variables like "max_connections"; を使用します。グローバルな値を表示するには、show global variables like 'max_connections'; を使用します。

皆さまがお使いの際に、Amazon RDS for MySQL パラメータの設定ミスに起因するオペレーション上の問題が生じることがあります。本ブログシリーズではもっとも一般的なデータベースパラメータの変更例をご紹介しますので、データベースインスタンスの信頼性と安定性を向上させるガイダンスとしてご活用ください。

パフォーマンス関連のパラメータ設定のベストプラクティス

今から、パフォーマンス関連の各パラメータを設定するベストプラクティスをご紹介します。

innodb_buffer_pool_size

このパラメータは buffer pool、つまり InnoDB がテーブルおよびインデックスデータをキャッシュするメモリ領域のサイズ (バイト単位) を決定します。このパラメータはお使いの MySQL インスタンスでは最重要設定の 1 つで、通常はメモリの 80 パーセント以上がここに割り当てられます。

RDS のこのパラメータのデフォルト値は、{DBInstanceClassMemory*3/4} です。ただし、ワークロードによって、この値の調整を要するケースが多いです。

たとえば、100 ギガバイトのデータがあるのに、アプリケーションは通常 1 ギガバイト分しかアクセスしないとします。この場合は、バッファプールには数ギガバイトあれば十分ということになります。一方、10 ギガバイトのデータがあって、アプリケーションが絶えずそのすべてにアクセスしている場合、そのデータおよびインデックスの規模に足りるだけの大容量のバッファプールが必要でしょう。このように、このパラメータ値は皆さまがお使いのアプリケーション要件やワークロードのパターンによって決定することを推奨します。

サーバーのバッファプールの使用状況は、show engine コマンドの innodb status \G を使用してチェックできます。ワークロードのピーク時であっても、経時的にフリーバッファの大部分があるということがアウトプットから一貫して分かる場合、割り当てられたバッファプールのサイズが大きすぎることが考えられます。

show engine innodb status \G の同じアウトプットでは、evicted without access X.XX/s のようなメッセージが表示されることがあります。evicted without access の値がゼロでない場合には、これはデータがバッファプールに読み込まれて、アクセスされる前に再び外に出されたことを意味します (バッファプールチャーンとも呼ばれる)。この値を経時的に追跡していて、上昇が見られた場合は、innodb_buffer_pool_size の値を増やす必要があるでしょう。ただし、innodb_buffer_pool_size の設定値があまりにも大きすぎるのは推奨しません。大きすぎると、オペレーティングシステムでページングが発生し、パフォーマンス低下の影響を及ぼすことが考えられます。innodb_buffer_pool_size の値をさらに増やす必要がある場合は、よりサイズの大きいインスタンスにスケールアップして、合計メモリ容量を増やしてから innodb_buffer_pool_size を割り当てることを検討してください。

InnoDB のパフォーマンスはキャッシュ内のデータの有無に左右されます。キャッシュミスがあると、ディスクに対してランダムな I/O リクエストを引き起こし、その結果オペレーション速度が大きく低下します。

InnoDB バッファプールのミス率を取得するには、一定の時間枠で以下の SQL ステートメントを 2 回実行します。

show global status where variable_name in ('Innodb_buffer_pool_reads','Uptime');

1 秒あたりのミス率は以下の数式で算出できます。

Miss_rate = delta(Innodb_buffer_pool_reads)/delta(Uptime)

innodb_buffer_pool_size を増やしたあとも、ステータス変数 innodb_buffer_pool_reads の増加率が下降し、ステータス変数 innodb_buffer_pool_pages_free が前期と比較して上昇しないことがあります。これらの場合は、innodb_buffer_pool_size を増やすことで得られる InnoDB 全体のパフォーマンスの向上に目を向ける必要があります。

innodb_log_file_size

このパラメータは MySQL の redo ログの固定サイズを決定します。この値を調整すると、クラッシュリカバリ時間やシステム全体のパフォーマンスに影響を及ぼします。デフォルト値は、134,217,728 (約 128 MB) です。

innodb_log_file_size のデフォルト値はユーザーのワークロードには小さいかもしれません。変動率の高いワークロードではこの値を増やすことを推奨します。使用頻度の高い挿入、アップデート、削除のアクションでは以下の初期設定を推奨します。

innodb_log_file_size = 600M
innodb_log_files_in_group = 2

innodb_log_files_in_group パラメータは、ロググループ内のログファイル数を定義します。innodb_log_files_in_group で 2 よりも大きい値では、大きなメリットはありません。innodb_log_file_size * innodb_log_files_in_group の最大許容値は MySQL バージョン 5.6 以降からは 512 ギガバイトです。そのため、innodb_log_files_in_group を 2 よりも大きい値にする場合は、innodb_log_file_size を制限値以内に抑える必要があります。

innodb_log_file_size は、MySQL 5.6 以降ではデフォルトの 128 MB を増やすことを推奨します。これを増やすことにより、ログフラッシュの頻度が下がります。そうしなければ、データインポート中はフラッシュは、たとえば 5 分間に複数回というふうに頻繁に発生することになります。

ただし、innodb_log_file_size (ファイルサイズ) の値が大きくなればなるほど、クラッシュリカバリ時間は長くなります。そのため、I/O 数を削減するようサイズを最適化し、それでいて大幅にリカバリ時間に影響をおよぼさないようにする必要があります。

このパラメータのサイズを最適化するには、サーバー使用のピーク時に以下のクエリを実行します。

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb statusG select sleep(60); show engine innodb statusG
Log sequence number 82 3836410821
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334625
1 row in set (0.05 sec)

以上のクエリから、トランザクションログに書き込まれた総バイト数が得られます。この結果、1 分間にログに書き込まれたメガバイト数がわかります。

mysql> select (3838334625 - 3836410821) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83468246| 
+------------+

注意: これまでの経験則として、1 時間程度は保持できるサイズの redo ログが作成可能です。また、innodb_log_file_size は最低でも、データベースで最大 BLOB のサイズの 10 倍である必要があります。

innodb_change_buffering

change buffer は、バッファプールに存在しないセカンダリインデックスページの変更をキャッシュする用途で使用する特別なデータストラクチャです。innodb_change_buffering パラメータは、データ操作言語 (DML) のオペレーション後にセカンダリインデックスを最新の状態に保つために使用する I/O 操作の相当量を軽減することに有用です。このパラメータは、変更バッファリング操作の範囲を管理します。

下表では、このパラメータに使用可能な値を示します。

値 説明
none いかなる操作もバッファリングしない。
inserts 挿入操作をバッファリングする。
deletes 削除マーキング操作、厳密にはパージ操作中にその後の削除用にインデックス記録をマーキングする書き込みをバッファリングする。
changes 挿入および削除マーキング操作をバッファリングする。
purges  バックグラウンドで発生する物理的な削除操作ををバッファリングする。
all デフォルト値。挿入、削除マーキング、パージ操作をバッファリングする。

デフォルト値は、all です。変更バッファリングでは値を none にして無効にすることを推奨します。これは、all ではアップグレード時のシャットダウン時間がきわめて長くなるためです。この機能はディスク速度の遅い時代には有用でしたが、現在では意味がありません。

innodb_io_capacity

このパラメータは、InnoDB が InnoDB バックグラウンドタスクで実行する 1 秒あたりの最大 I/O 操作数を制御します。このタスクの例としては、MySQL ドキュメントに記述されているように、バッファプールからのページのフラッシングや、変更バッファからのデータのマージが挙げられます。

このパラメータのデフォルト値は 200 で、許容される値の範囲は 100～18,446,744,073,709,551,615 となっています。I/O 集中型のシステムでは、この値は通常 1,000 が使用されます。低い値では十分ではないことが明らかな場合を除き、20,000 のような極端な値は推奨しません。このような値は、フラッシングが遅すぎて、なおかつ IOPS に余裕がある場合には使用が考えられます。

フラッシングが遅いかどうかを確認するには、以下の方法でダーティページの割合をチェックします。ダーティページの割合が非常に高い場合は、フラッシングの発生が想定よりも速くないことを意味します。バッファプール内のダーティデータ量を見るには、Innodb_buffer_pool_bytes_dirty メトリックを SHOW GLOBAL STATUS アウトプットと合わせて使用します。例を以下に示します。

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
...
| Innodb_buffer_pool_pages_data         | 3271        |
| Innodb_buffer_pool_bytes_data         | 38192680    |
| Innodb_buffer_pool_pages_dirty        | 1024        |
| Innodb_buffer_pool_bytes_dirty        | 16760878    |
...
| Innodb_buffer_pool_pages_total        | 5395        |
...
+---------------------------------------+-------------+
10 rows in set (0.01 sec)

上の出力の場合、変数は以下の通りです。

  • Innodb_buffer_pool_pages_data と Innodb_buffer_pool_bytes_data: バッファプール内のバッファ量。
  • Innodb_buffer_pool_pages_dirty と Innodb_buffer_pool_bytes_dirty: バッファプール内のダーティバッファの量。
  • Innodb_buffer_pool_pages_total: バッファプール内の合計ページ数。

ダーティページの現在の割合を計算するには次の数式を使用します。

                            Innodb_buffer_pool_pages_dirty
Dirty page percentage = ------------------------------------- x 100%
                            Innodb_buffer_pool_pages_total

また、information_schema を使用している場合、次のクエリを使用できます。

mysql> SELECT dirty.Value AS 'Dirty Pages', total.Value AS 'Total Pages', ROUND(100*dirty.Value/total.Value, 2) AS 'Dirty Pct' FROM (SELECT VARIABLE_VALUE AS Value FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total') AS total INNER JOIN (SELECT VARIABLE_VALUE AS Value FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS dirty;
+-------------+-------------+-----------+
| Dirty Pages | Total Pages | Dirty Pct |
+-------------+-------------+-----------+
| 200        | 1000        |     20.00 |
+-------------+-------------+-----------+
1 row in set (0.00 sec)

ダーティページの割合は、次の数式を使って show engine innodb status から取得することも可能です。

                                   Modified DB pages
Dirty page percentage = ------------------------------------- x 100%
                            Database pages + Free buffers

次に対応する情報スキーマクエリを示します。

mysql> SELECT MODIFIED_DATABASE_PAGES, DATABASE_PAGES, FREE_BUFFERS, ROUND(100*MODIFIED_DATABASE_PAGES/(DATABASE_PAGES+FREE_BUFFERS), 2) AS 'Dirty Pct' FROM information_schema.INNODB_BUFFER_POOL_STATS;
+-------------------------+----------------+--------------+-----------+
| MODIFIED_DATABASE_PAGES | DATABASE_PAGES | FREE_BUFFERS | Dirty Pct |
+-------------------------+----------------+--------------+-----------+
|                     207 |           4333 |          599 |     4.19  |
+-------------------------+----------------+--------------+-----------+
1 row in set (0.00 sec)

ダーティページまたは変更済みページの割合を希望の値まで増やすことができない場合は、innodb_io_capacity を増やすか、または減らすことが推奨されます。このパラメーターは、フラッシングレートと関連するディスク I/O を制御します。ただし、このパラメーターまたは innodb_io_capacity_max を高く設定しすぎると、パフォーマンスに深刻なダメージを与える可能性があります。また、フラッシングが早すぎると、ディスクの IOPS を浪費する可能性があります。

このパラメーターの最適値は、ワークロードとデータアクセスのパターンに応じて決まります。DML に負荷がかかるワークロードの場合、パラメーターの設定値が低すぎると、ダーティページの割合が非常に高くそのためにメモリの使用量が過剰になっているときに、フラッシングが遅延する可能性があります。同様に、パラメーターの設定値が高すぎると、フラッシングおよび IOPS / 処理容量の飽和により、ディスク書き込みが大幅に増加し、フラッシングが過度に早く発生する場合があります。

innodb_io_capacity_max

このパラメーターは、フラッシングが遅延している場合に、innodb_io_capacity の設定を拡張するために、InnoDB に許容される最大値を定義します。値を指定しない場合、デフォルト値は innodb_io_capacity の 2 倍の値となります。最小値は 2,000 です。デフォルト値は 2,000、許容値は 2,000–18,446,744,073,709,547,520 です。

ベストプラクティスとして、書き込み負荷の低いシステムには小さい値を、書き込みのアクティビティが高いシステムには大きい値を使用することが推奨されます。この値は innodb_io_capacity より低くすることはできません。ほとんどの場合、innodb_io_capacity の 2 倍の値が最適な選択とされます。adaptive flushing の場合、この値は、フラッシングレートを実際に制限している変数となります。

innodb_purge_threads

このパラメーターは、MySQL が InnoDB のパージ操作で使用するバックグラウンドスレッドの数を定義します。最小値は 1 ですので、パージ操作は常に 1 つのバックグランドスレッドによって実行されます。マスタースレッドの一部として実行されることはありません。1 つの個別のスレッドを使って InnoDB のパージ操作を実行すると、InnoDB との内部の競合が減り、スケーラビリティも向上します。RDS MySQL でのデフォルト値は 1、最大値は 32 です。

複数のパージスレッドを使うと、DML 操作が複数のテーブルに及んでいる場合に、DML に負荷がかかるワークロードの undo の記録を効率的に削除できます。DML 操作が単一のテーブルかごく少数のテーブルに集中している場合は、パージスレッド間の競合を避けるために、この値をできるだけ低く維持します。複数のテーブルで実行中の DML のパージ操作を高速化することについての明確な理由がない限り、値は 1 に設定することが推奨されます。それ以上高い値に設定すると、MySQL bug で確認したように、パージスレッドの競合が生じる要因となります。

innodb_stats_persistent

このパラメーターは、ANALYZE TABLE コマンドが生成した InnoDB テーブルとインデックスの統計を、ディスクに保存するか否かを指定します。これらの統計は mysql.innodb_table_stats と mysql.innodb_index_stats の各テーブルに保存されます。統計を保存しないと、再起動のたびに再計算をする必要が生じ、オーバーヘッドの原因となります。

利用可能な値は 1 (ON) または 0 (OFF) です。デフォルトでは、このパラメーターは ON に設定されています。このパラメーターを有効にすると、インスタンスの再起動後も永続するオプティマイザ統計が利用できます。これにより、プランの安定性が高まり、クエリのパフォーマンスの一貫性もさらに向上します。

このパラメーターは、グローバルなレベルで永続的統計を有効にします。ただし、この永続性は、テーブルの作成中に STATS_PERSISTENT 句を使用することで、テーブルレベルで有効化または無効化することも可能です。

このパラメーターを OFF にするとオプティマイザ統計は非永続的になり、次回テーブルにアクセスする際に、各インスタンスの再起動後に再計算をする必要が生じます。この状態では、統計を再計算したときに異なる推定値が生成され、異なる実行プランが作成される可能性があります。したがって、永続的で安定した、最適化された実行プランのために、このパラメーターを ON に設定しておくことが推奨されます。

永続的統計を使用する場合、ANALYZE TABLE コマンドを実行することにより統計を定期的に更新する必要があります。データが安定しているか、またはその変化が緩やかであるテーブルの場合は、分析を行う頻度は週毎または月毎が推奨されます。データが小さいか、またはその変化が激しい場合は、上記よりも多い頻度で ANALYZE TABLE を実行することが推奨されます。永続的統計を設定する唯一のデメリットは、ANALYZE TABLE を時折実行して、統計データをディスクに書き込まなければならないことです。しかしこのオーバーヘッドはごくわずかなものであって、インスタンスの再起動後にテーブルにアクセスがあるたびに再計算を行うような、コストのかかる統計は回避できます。

innodb_thread_concurrency

このパラメータを使用すると、アクティブスレッドの数が vCPU の数を大きく上回る場合に起きる問題を解決するのに役立ちます。

インスタンスのサイズが小さい場合は、ほとんどのセッションがアイドル状態であれば大量のセッションを処理することができます。しかし、アクティブスレッドの数は vCPU の数を大幅に上回ることがあります。もしこのようなことが起きた場合、オペレーティングシステムはすべての使用可能な vCPU を使用可能なスレッドに分割しようとします。これは、コンテキストスイッチと呼ばれる処理によって実行されるもので、CPU 使用率に大きなオーバーヘッドを追加します。このオーバーヘッドは、拡張モニタリングコンソールのシステムカテゴリで確認できます。小さいインスタンスクラスの場合、この問題は大きいインスタンスにスケールアップすることで解決できますが、大きいインスタンスではこのオプションを使うことができません。

オペレーティングシステムレベルでは、Linux はスレッドごとに実行された内容を認識しません。しかし、InnoDB ではそれを認識しており、適切にスレッドに優先順位を付けることができます。スレッドの優先順位付けは innodb_thread_concurrency パラメータを使用して行われ、これにより同時スレッド数が制限されます。このパラメータのデフォルト値は 0 で、無制限の同時実行数（同時実行チェックなし）として解釈されます。

実行スレッドの数がこの制限に達すると、追加スレッドはキューに入る前に数マイクロ秒ほどスリープします。スレッドがスリープするマイクロ秒数は、構成パラメータ innodb_thread_sleep_delay を使用して設定します。innodb_thread_concurrency パラメータは、大きいインスタンスサイズのコンテキストスイッチングを回避するのに役立ちます。

このパラメータの調整を行うもう 1 つのユースケースは、すべてが書き込みを必要とする場合の高同時挿入です。そういった場合には、より多くのスレッドの同時並列性が役立ちます。パラメータの最適な値は、所定のワークロードに対するテストを実施して決定します。ほとんどの場合、デフォルト値が最適な値です。

innodb_sync_array_size

このパラメータを使用して、スレッドの連動に使用される内部データ構造を分割することができます。これは、多数のスレッドが待機している場合の高並列実行ワークロードに特に有効です。強化された監視メトリクス loadAverageMinute は、待機中のスレッドを示す優れた指標です。平均負荷が高いということは、システムが過負荷になっており、多くのスレッドがCPU時間を待っている状態です。負荷の平均値が高い（vCPUの数を超える）場合は、vCPU の需要が高いことを意味します。

多くのスレッドが頻繁に待機中になるような高並列実行ワークロードの場合は、値を増やすことをお勧めします。innodb_sync_array_size のデフォルト値は 1 です。このような低い値は、並列実行ワークロードが大きい場合に多くの競合を発生させる原因となります。ただし、このパラメータの最適値は予想される並列接続数により異なるため、負荷テストを十分に実施して決定する必要があります。多くの場合、innodb_sync_array_size=16 で良い結果が得られます。

innodb_flush_log_at_trx_commit

トランザクションの耐久性を維持するには、ログバッファを堅牢なストレージにフラッシュする必要があります。ただし、ディスクへの書き込みはパフォーマンスに影響します。システムの耐久性よりパフォーマンスを優先する場合は、innodb_flush_log_at_trx_commit パラメータを調整してログバッファをディスクにフラッシュする頻度を制御することができます。

以下の設定が可能です。

0 – この設定では、トランザクションコミット時には何も実行しませんが、ログバッファをログファイルに書き込み、1 秒ごとにログファイルをフラッシュします。OS はログをフラッシュしようとしますが、フラッシュは保証されません。そのため、クラッシュが起きた場合は、最新のコミット済みのトランザクションの一部を失うリスクを受け入れる必要があります。トランザクションの損失だけでなく、データの損失も起こりえます。したがって、この設定はデータ破損の原因となる場合があります。

1 – この設定では、トランザクションがコミットされるたびにログバッファをログファイルに書き込み、ログファイルを堅牢なストレージにフラッシュします。この設定がデフォルトであり、最も安全な設定です。ディスクまたはオペレーティングシステムが「フェイク」でフラッシュ操作を行わない限り、コミット済みのトランザクションを失うことはありません

2 – この設定では、コミットが実施されるたびにログバッファをファイルに書き込みますが、バッファのフラッシュは行いません。1 秒に 1 回だけデータをディスクにフラッシュします。OS はログをフラッシュしようとしますが、フラッシュは保証されません。

この設定と 0 の設定の最も重要な違い (そして 2 に設定するべき理由) は、2 に設定すると MySQL プロセスがクラッシュしてもトランザクションが失われないことです。ただし、サーバー全体がクラッシュしたり、電源の損失があったりした場合は、トランザクションが失われることがあります。これは、OS が原因でクラッシュが起きた場合と同様のデータ損失です。0 値と同じように、この設定は破損の原因となる可能性があり、データ損失はトランザクションの損失だけにとどまりません。

値を 1 に設定しない限り、InnoDB は ACID プロパティを保証しません。クラッシュが発生すると、最大 1 秒分の最新のトランザクションが失われる可能性があります。そのため、このパラメータを使用することで、クラッシュ発生時にリードレプリカの操作が中断されたり、スナップショットのデータが損失したりする場合があります。クラッシュした際にこれらの問題を回避するため、レプリケーションのシナリオではマスターでこのパラメータを設定しないでください。RDS のベストプラクティスとして、レプリケーションマスター用とそのレプリカ用に、別々のパラメータグループを用意することをお勧めします。

注意: バッファをログに書き込むと、データは単純に InnoDB のメモリバッファからオペレーティングシステムのキャッシュに移されますが、これもメモリ内です。実際にデータを堅牢なストレージに書き込んでいるわけではありません。ログを堅牢なストレージにフラッシュするということは、InnoDB がオペレーティングシステムに対して、実際にデータをキャッシュからフラッシュし、データが確実にディスクに書き込まれるように要求することを意味します。

tmp_table_size and max_heap_table_size

MySQL のドキュメントには、｢内部一時テーブルが最初にインメモリーテーブルとして作成されたが、これが大きくなりすぎた場合、MySQL はこれを自動的にディスク上のテーブルに変換します。インメモリー一時テーブルの最大サイズは、tmp_table_size と max_heap_table_size のどちらかの最小値です｣とあります。 ディスク上のテーブルが関係するオペレーションは、インメモリ一時テーブルの場合に比べ非常に時間がかかります。MySQL 5.7 のドキュメントにも類似の記述が確認できます。これらのパラメータのデフォルト値はどちらも 16,777,216 バイトです。

しばしば問題に出くわすのは、長い時間 (通常 5～15 分) がかかる複雑なクエリを実行するユーザーが show full processlist から copying to tmp table on disk を確認するときです。この種の問題はしばしば大きな書き込み IOPS やディスクキューの深さ (100 超) といった兆候を伴います。メモリ上およびディスク上の一時テーブルの作成を確認するには、比較のために上述のクエリの実行終了の前と最中で下記の status like 'Created_tmp%' を表示してみます。

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 
+-------------------------+--------+ 
| Variable_name           | Value  | 
+-------------------------+--------+ 
| Created_tmp_disk_tables | 582    | 
| Created_tmp_files       | 5      | 
| Created_tmp_tables      | 188654 | 
+-------------------------+--------+ 
3 rows in set (0.01 sec)

このようなケースでは、tmp_table_size および max_heap_table_size 両方の変数を 64 MB、128 MB、あるいは 512 MB にまで増加させれば問題を解決できます。この変更の前後でパフォーマンスを比較することを推奨します。tmp_table_size の値は、インメモリ内部一時テーブルの最大サイズを決定します。しかし、インメモリ一時テーブルの実際の最大サイズを決定するのは、max_heap_table_size と tmp_table_size の最小値です。しばしば tmp_table_size のみを設定しがちですが、実際には両方を増やす必要があります。

例として、以下の条件の場合を考えてみます。

tmp_table_size      = 64 MB
max_heap_table_size = 32 MB

この場合、MySQL は tmp_table_size に 32 MB という値、max_heap_table_size に 32 MB という値を割り当てます。

次に、以下の条件の場合を考えてみます。

tmp_table_size      = 32 MB
max_heap_table_size = 64 MB

この場合にも、MySQL は tmp_table_size に 32 MB という値、max_heap_table_size に 32 MB という値を割り当てます。

このコードは sql_select.cc ファイル内で確認できます。

if (thd->variables.tmp_table_size == ~ (ulonglong) 0)        // No limit
    table->s->max_rows= ~(ha_rows) 0;
  else
    table->s->max_rows= (ha_rows) (((table->s->db_type == DB_TYPE_HEAP) ?
                                    min(thd->variables.tmp_table_size,
                                        thd->variables.max_heap_table_size) :
                                    thd->variables.tmp_table_size)/
                                   table->s->reclength);

以下では MySQL から使用された min() 関数を確認できます。

min(thd->variables.tmp_table_size,thd->variables.max_heap_table_size)

手動状態の MySQL は件の 2 つの変数の値のうち小さいほうを採用し tmp_table_size に適した値として使用します。したがって、tmp_table_size と max_heap_table_size は同じ値にすることを推奨します。

しかし、これらの値を増加させる前に、データベースが適切にインデックスを付けられているか、特に結合およびカラムによるグループ化に関してチェックしてください。適切なインデックスは一時テーブルの作成を減少させます。そうしないと、これらのパラメータの値を単に増加させただけでは、インデックスなしで効果のないクエリを実行することになり、必要以上の一時テーブルを作成することになりかねません。

あるいくつかの条件でインメモリ一時テーブルの使用を回避できます。その場合、サーバーは代わりにディスク上のテーブルを使用し、その結果、書き込み I/O が増加します。その条件を以下に挙げます。

  • テーブルに BLOB 型または TEXT 型のカラムが存在する。
  • バイナリ文字列なら 512 バイト超、または非バイナリ文字列なら 512 文字超の GROUP BY 句または DISTINCT 句に文字列カラムが存在する。(MySQL 5.6.15 より前のバージョンでは、文字列の種類にかかわらず制限は 512 バイト)。
  • UNION または UNION ALL を使用の場合、バイナリ文字列なら 512 バイト超、または非バイナリ文字列なら 512 文字超の最大長を持つ文字列カラムが SELECT リスト内に存在する。
  • SHOW COLUMNS 構文および DESCRIBE 構文がいくつかのカラムのタイプとして BLOB を使用し、そのため結果に使用される一時テーブルがディスク上のテーブルである。

foreign_key_checks

データインポートプロセスは、外部キーのチェックが有効の場合、しばしば時間がかかります。foreign_key_checks パラメータはデフォルトで有効化されており、このパラメータのデフォルト値は 1 (ON) です。しかし、これはパラメータグループでは利用できません。データの整合性よりもパフォーマンスを重視する場合、インポート SQL 構文を実行する前にこのパラメータを 0 に設定して無効化し、インポートのパフォーマンスを向上させることが可能です。

例を以下に示します。

SET foreign_key_checks=0;
...SQL import statements ...
SET foreign_key_checks=1;

インポートファイルの末尾に SET FOREIGN_KEY_CHECKS=1; を追加することを忘れないでください。

大きなテーブルでこれを行うと、大量のディスク I/O を削減できます。発生しうる問題は、インポート中に外部キーのチェックを失敗させたおそれのあるデータの不整合が、外部キーが有効に戻った後であってもデータベースに残存することです。

私たちは、グローバル設定を動的に有効化および無効化するラップ済みストアドプロシージャをいくつか提供しています。プロシージャの名前は、mysql.rds_set_fk_checks_on と mysql.rds_set_fk_checks_off です。これらのプロシージャは RDS MySQL 5.6 の 5.6.29 以上のバージョン、RDS MySQL 5.7 の 5.7.17 以上のバージョンで利用可能です。

以下は、これらのプロシージャの使用例です。

CALL mysql.rds_set_fk_checks_off();
CALL mysql.rds_set_fk_checks_on();

UNIQUE_CHECKS

データに対する INSERT の実行前に SET UNIQUE_CHECKS=0 を実行して UNIQUE_CHECKS を無効化すれば、大きなテーブルにおいて挿入のパフォーマンスを大きく向上させることもできます。それから、挿入の完了時に SET UNIQUE_CHECKS=1 を実行します。また、UNIQUE_CHECKS はデフォルトで有効化されており、このパラメータのデフォルト値は 1 (ON) です。しかし、これはパラメータグループでは利用できません。

例として、これを行うためにファイルのトップに以下の行を追加します。

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

末尾には以下を追加します。

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

こうすることにより、大量のディスクアクセスを回避できます。関係するデータに重複キーが含まれていないことを確認してください。

query_cache_size

query_cache_size のデフォルト値は 1,048,576 (バイト) です。たいていの場合、クエリキャッシュの設定によってパフォーマンスが低下します。クエリキャッシュは問題をはらんでいるため、MySQL 5.7.20 で非推奨となり MySQL 8.0 では削除されました。そのため、使用は推奨しません。

クエリキャッシュが有効かどうかの検証には、2 つの変数を利用できます。

  • query_cache_size: この値はクエリキャッシュに割り当てられたメモリ容量です。query_cache_size が 0 の場合、クエリキャッシュは実質的に無効化されます。しかし、query_cache_type が OFF に設定されないかぎり、いくらかのオーバーヘッドが残留します。
  • query_cache_type: query_cache_type の設定には有効な値が 3 つあり、クエリキャッシュを OFF にするには 0 にする必要があります。
    • 0 または OFF: クエリキャッシュを完全に無効化します。しかし、バッファの割り当ても解除するには、query_cache_size を 0 に設定します。
    • 1 または ON: デフォルトでクエリキャッシュを使用します。あるクエリに対してクエリキャッシュを無効化するには、SQL_NO_CACHE ヒントを使用します。
    • 2 または DEMAND: この場合、クエリキャッシュは SQL_CACHE ヒントが指定されたときにのみ使用されます。

以下に例を示します。

mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1448576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

上記の出力によってクエリキャッシュは無効化されます。query_cache_size が 0 で query_cache_type が OFF であることが確認できます。

optimizer_switch

MySQL のドキュメントに記されているように、オプティマイザはクエリに対して DBMS が取るべき実行パスを決定する一連のルーチンです。オプティマイザは、結果の取得のための最も効果的なクエリプランの選択を担います。このパラメータはオプティマイザの動作の制御に使用され、一連のフラグで構成されます。これらのフラグを ON/OFF することでオプティマイザの動作を制御できます。

以下に例を示します。

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,
                    index_merge_intersection=on,
                    engine_condition_pushdown=on,
                    index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,
                    block_nested_loop=on,batched_key_access=off,
                    materialization=on,semijoin=on,loosescan=on,
                    firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on

The default values are the following: index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on

通常、デフォルト値が推奨されます。オプティマイザによるクエリプランが不適切な場合、または推定が誤っている場合は、これらのフラグを調整する必要があるかもしれません。たとえば、optimizer_switch フラグの condition_fanout_filter および derived_merge を無効化することで、5.6 から 5.7 へのアップグレード後の、クエリにおけるパフォーマンス低下を緩和できます。フラグはパフォーマンスに大きな影響を与える可能性があるため、これらのフラグを有効化する前にワークロードを入念にテストしてください。

Innodb_read_io_threads と Innodb_write_io_threads

これらのパラメータは InnoDB における読み込みおよび書き込み操作それぞれの I/O スレッドの数です。両方ともデフォルト値は 4 です。これらの値を増加させると、特定の InnoDB オペレーションのスレッドが増加します。大量の OLTP ワークロード向けにこれらを調整し innodb_read_io_threads = 16、 innodb_write_io_threads = 4 と設定すると、インスタンスの稼働を維持するのに役立ちます。

これらはほとんどのワークロードに影響しませんが、本番実装の前に適切なテストを実行してください。ときおり、フォアグラウンドのスレッドとこれらのスレッドとの間でディスクリソースの競合が発生することがあります。

innodb_status_output_locks

このパラメータは InnoDB ロックモニターを有効化または無効化します。1 に設定するとモニターを有効化し、0 に設定すると無効化します。デフォルト値は 0 です。有効化時、このパラメータは SHOW ENGINE INNODB STATUS 出力において追加のロック情報を表示し、MySQL エラーログに定期的な出力の表示も行います。このパラメータは MySQL 5.7 に導入されました。これはロックの特定に役立ち、特に他のセッションがロックされた行にアクセスしておらず、そのために衝突が発生していない場合に効果的です。

table_open_cache

このパラメータは定義キャッシュに保存できるテーブル定義の数を定義します。デフォルト値は 2,000 です。

経験則では、このパラメータはテーブルの大多数が常にオープンで使用される状態を維持するのに充分な値に設定します。テーブルのオープンとクローズはオペレーションを大幅に遅滞させます。まず max_connections の値またはテーブルの総数を倍にし、そこから調整していくとよいかもしれません。

システムが数時間稼働して温まったら、以下のコマンドを使用して割り当てが充分かどうかチェックします。

mysql> show global status like 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 200   |
+---------------+-------+
1 row in set (0.04 sec)

mysql> show global status like 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 5000 |
+---------------+-------+
1 row in set (0.00 sec)

Opened_tables の増加から、いくつのテーブル定義を追加すべきかを推定できます。オープンのテーブル数が 1 秒あたり 2、3 を切るまでチェックを繰り返すことを推奨します。

Opened_tables の数が table_open_cache_size の値に達しておらず、サーバーがしばらくの間稼働中の場合、値を減らすことを検討します。通常、オープンのテーブル数が 1 秒あたり 1 を下回るのが好ましいです。

table_open_cache の値を増加させて使用できるかどうか判断するには、サーバー稼働時間と併せてオープンのテーブル数を確認します。

thread_cache_size

これは、いくつの接続処理スレッドがリリースおよび再割り当てされるかわりに再使用に向けてキャッシュされるべきかを定義する構成変数です。スレッドの作成および終了は接続と切断のたびに起こり、費用がかかる場合があります。

Threads_Created の値を使用して 1 秒あたりに作成されるスレッド数を把握することで、スレッドキャッシュの効率を推定できます。それから、ある既知の期間にわたってこれをサンプリングできます。thread_cache_size のデフォルト値は 14 です。通常、これは最低でも 16 に設定すべきです。アプリケーションが同時接続数を大幅に増加させ Threads_Created が急増していく場合、この変数はより高い値に増やす必要があります。通常のオペレーションにおいてスレッドを作成させないようにすることが目的です。

以下に例を示します。

mysql> show global status like 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 20    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

max_seeks_for_key

この値は、キーに基づいて行を検索する際に推定されるシークの最大数の限度です。デフォルト値は 18,446,744,073,709,551,615 です。この値は通常、クエリが (インデックススキャンは高コスト効率であるにもかかわらず) インデックスを使用しておらず、フルスキャンを実行するときに使用されます。このような場合、この値は低め (例: 100) に設定し、テーブルスキャンの代わりにインデックスを使用するよう MySQL に指示することができます。この値はすべてのクエリに適用されるため、これをグローバルレベルで変更すると他のクエリに予期せぬ副次的影響をもたらすおそれがあります。そのため、入念にテストしてください。

結論

以上、RDS MySQL インスタンスからベストパフォーマンスを引き出すために留意すべき最重要のパラメータを扱いました。これらを調整して、ワークロードに適合させ、ここで述べたベストプラクティスにも合致させることができます。

このブログシリーズの次のパートでは、最もよく使われるパラメータによる RDS MySQL レプリケーションの最適化および安定化と、そのパラメータ調整のベストプラクティスについて考察します。

 

著者について

Saikat Banjeree は、アマゾン ウェブ サービスのクラウドサポートエンジニアです。