Amazon Aurora MySQL データベース設定のベストプラクティス

AWS クラウドで新しい Amazon Aurora MySQL インスタンスを移行または起動した後、以下の質問のうち 1 つ以上を自問したことはありますか?

• 「次のステップは? どうすれば、最適に動作させることができるでしょうか?」
• 「既存のパラメータを変更する方が良いでしょうか?」
• 「どのパラメータを変更すれば良いでしょうか?」

自問したことがあるなら、何をすべきか（そして、何をすべきでないか）について、このブログ記事がガイダンスを提供できることを願っています。

この記事では、MySQL との互換性を持つ Amazon Aurora の設定パラメータについて説明、明確化し、推奨事項を提供します。こうしたデータベースパラメータとその値は、AWS クラウドで新しく作成または移行されたインスタンスの引き継ぎ、チューニング、再設定を行う場合に重要です。また、Amazon RDS for MySQL インスタンスから Aurora インスタンスに引き継がれるパラメータについても説明します。どの値がデフォルト値であり、どの値がインスタンスの安定性や最適なパフォーマンスにとって重要であるかを説明します。

変更を行う前の最も重要な考慮事項は、変更の背後にあるニーズや動機を理解することです。 ほとんどのパラメータ設定はデフォルト値で問題ありませんが、アプリケーションのワークロードの変化によりこうしたパラメータの調整が必要になる場合があります。変更を行う前に、以下の質問を自問してください。

• 再起動やフェイルオーバーなどの安定性の問題がありますか?
• アプリケーションでクエリをより高速に実行できますか?

Aurora パラメータグループの初歩

Aurora MySQL パラメータグループには、DB パラメータグループと DB クラスターパラメータグループの 2 種類があります。バイナリログ形式、タイムゾーン、文字セットのデフォルトなど、一部のパラメータは DB クラスター全体の設定に影響を与えます。他のパラメータは、その範囲が単一の DB インスタンスに限定されます。

このブログ記事では、Aurora クラスターの動作、安定性、機能性に影響を与えるパラメータと、変更するとパフォーマンスに影響を与えるパラメータに分類して説明します。

どちらのタイプのパラメータも出荷時に事前にデフォルト設定されており、一部のパラメータでは変更が可能です。

パラメータグループの変更や操作の基本について、最新の情報をより深く理解するには、Aurora ユーザーガイドの以下のトピックを参照してください。

• DB パラメータグループおよび DB クラスターパラメータグループを使用する
• Aurora MySQL パラメータ

本稼働データベースを変更する前に

パラメータを変更すると、パフォーマンスが低下したりシステムが不安定になったりするなど、予期しない結果が発生する可能性があります。データベース設定パラメータを変更する前に、以下のベストプラクティスに従ってください。

• 本稼働インスタンスのクローンを作成するか、スナップショットを復元することによって、テスト環境に変更を加えます (ドキュメントの説明に従って)。こうすることで、設定は可能な限り本稼働環境に似ているものになります。
• 本稼働ワークロードを模したテストインスタンス用のワークロードを生成します。
• CPU 使用率、データベース接続数、メモリ使用率、キャッシュのヒット率やクエリのスループット、レイテンシーなどの主要なパフォーマンス指標でシステムのパフォーマンスを確認します。変更の前にこれを行ってベースラインの数値を取得し、変更の後に行って結果を観察します。
• 曖昧さを避けるために、一度に変更するパラメータは 1 つだけにします。
• 変更してもテストシステムに測定可能な影響を及ぼさなかった場合は、パラメータをデフォルトに戻すことを検討してください。
• どのパラメーターが期待した良い影響を与えたか、どの主要なパフォーマンス指標が改善を示したかを文書化します。

デフォルトのパラメータ値とその重要性

一部の DB インスタンスパラメータには、値が定数によって決定される変数または式が含まれています。例としては、インスタンスのサイズやメモリフットプリント、インスタンスのネットワークポート、割り当てられたストレージなどがあります。インスタンスの拡大または縮小の操作が実行されるたびに自動的に調整されるため、これらのパラメータは変更しないことをお勧めします。

たとえば、Aurora DB のパラメータ innodb_buffer_pool_size のデフォルト値は次のとおりです。

{DBInstanceClassMemory*3/4}

DBInstanceClassMemory は、GiB 単位でのインスタンスのメモリサイズに設定される変数です。

例: 30.5 GiB のメモリを持つ db.r4.xlarge インスタンスの場合、この値は 20,090,716,160 バイトまたは 18.71 GiB です。

このパラメータを固定値、たとえば 18,000,000,000 バイトに設定すると決めた後、半分のメモリ (15.2 GiB) がある db.r4.large への縮小操作を実行するとします。この場合、データベースエンジンを変更した後にデータベースエンジンでメモリ不足の状態が発生し、インスタンスが正しく起動できなくなる可能性があります。

どのパラメータがシステム変数によって自動的に計算されるかを一目で確認するには、パラメータグループ定義内でこれらのパラメータを検索します。これを行うには、波括弧文字「{」を検索します。

インスタンスによって使用されている実際の値を照会したい場合は、これをコマンドラインで実行する 2 つの方法があります。SHOW GLOBAL VARIABLES または SELECT ステートメントを使用します。

mysql> SHOW GLOBAL VARIABLES where Variable_Name='innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 8001683456 |
+-------------------------+------------+
1 row in set (0.01 sec)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                8001683456 |
+---------------------------+
1 row in set (0.00 sec)

誤って設定されたパラメーター値による徴候と診断

特定のパラメータの設定が誤っていると、MySQL のエラーログにメモリ不足の状態が記録されることで表面化する可能性があります。この場合、インスタンスはローリング再起動状態に入り、どのパラメータ値を調整する必要があるかについての推奨事項を含む、次のようなイベントログを生成します。

2018-12-29 19:05:16 UTC  [-]MySQL has been crashing due to incompatible parameters.Please check your memory parameters, in particular the max_connections, innodb_buffer_pool_size, key_buffer_size, query_cache_size, tmp_table_size, innodb_additional_mem_pool_size and innodb_log_buffer_size.Modify the memory parameter and reboot the instance.

パラメータの分類

このブログ記事の範囲では、Aurora MySQL のパラメータを 2 つの主なグループに分類できます。

1. データベースの動作や機能を制御しますが、リソース使用率やインスタンスの安定性には影響を与えないパラメータ
2. キャッシュや内部メモリベースのバッファなどのリソースがインスタンス内でどのように割り当てられるかを管理することによってパフォーマンスに影響を与える可能性があるパラメータ

これらのパラメータのいくつか、それらのデフォルト値、および変更されたときにインスタンスの動作またはパフォーマンスにどのように影響するかを見てみましょう。次の表は、パラメーターグループで見つかるパラメータ名、Aurora および MySQLのデフォルト値、このパラメータを変更すると影響を受ける機能の概要を示しています。

推奨事項および影響

以下は、これらの重要なパラメータのそれぞれがデータベースにどのように影響するかについての簡単な説明と、それらをチューニングする方法に関するいくつかのユースケースです。

autocommit

推奨される設定: デフォルト値 (1 またはオン) を使用して、ユーザーによって開始されたトランザクションの一部でないかぎり、それぞれの SQL ステートメントが実行時に自動的にコミットされるようにします。

影響: 値をオフにすると、トランザクションが必要以上に長く開いている、閉じていない、またはまったくコミットされていないなどの誤った使用パターンを容認することがあります。これはデータベースのパフォーマンスと安定性に影響を与える可能性があります。

max_connections

推奨される設定: デフォルト (変数値)。カスタム値を使用する場合は、アプリケーションが作業の実行に積極的に使用するのと同じ数の接続だけを設定します。

影響: 多すぎる接続制限を設定すると、接続がアクティブに使用されていない場合でも、メモリ使用量が増加する可能性があります。また、データベース接続の急上昇を引き起こし、データベースのパフォーマンスと安定性に影響を与える可能性があります。

この変数パラメータは、次の式を使用してインスタンスのメモリ割り当てとサイズに基づいて自動的に設定されます。したがって、最初はデフォルト値を使用してください。

GREATEST({log(DBInstanceClassMemory/805306368,2)*45},{log(DBInstanceClassMemory/8187281408,2)*1000})

たとえば、15.25 GiB のメモリを持つ Aurora MySQL db.r4.large インスタンスの場合、1,000 に設定されます。

DBInstanceClassMemory = 16374562816 バイト
max_connections = GREATEST({log(16374562816/805306368,2)*45},{log(16374562816/8187281408,2)*1000})
max_connections = GREATEST(195.56,1000) = 1000

接続エラーが発生してエラーログ内に Too many connections が多すぎる場合は、このパラメータを変数設定ではなく固定値に設定することができます。

アプリケーションでより多くの接続が必要な場合に max_connections を固定値に設定することを検討している場合は、アプリケーションとデータベースの間に接続プールまたはプロキシを使用することを検討します。接続を確実に予測または制御できない場合も、これを実行できます。

推奨される接続数を超える値をこのパラメータに手動で設定すると、DB 接続の Amazon CloudWatch メトリクスは閾値を超えたところに赤い線を表示します。以下は、CloudWatch が使用する式です。

max_connections の閾値 = {DBInstanceClassMemory/12582880}

たとえば、メモリサイズが 15.25 GiB ( 15.25 x 1,024 x 1,024 x 1,024 = 16,374,562,816 バイト) の db.r4.large インスタンスの場合、警告閾値は約 1,300 接続です。インスタンスに十分なリソースがある場合は、設定された最大接続数を使用することができます。

max_allowed_packet

推奨される設定: デフォルト (4,194,304 バイト)。データベースのワークロードで必要な場合にだけ、カスタム値を使用します。長い文字列や BLOB など、大きな要素を返すクエリを扱う場合は、このパラメータをチューニングします。

影響: ここで大きな値を設定しても、メッセージバッファの初期サイズには影響しません。代わりに、クエリで要求された場合は、定義されたサイズまで拡張できます。多数の同時適格照会と大きなパラメータ値が組み合わされると、メモリ不足状態になるリスクを高める可能性があります。

このパラメータを小さくしすぎると、次の例のようなエラーが表示されます。

 ERROR 1153 (08S01) at line 3: Got a packet bigger than 'max_allowed_packet' bytes

group_concat_max_len

推奨される設定: デフォルト (1,024 バイト)。ワークロードに必要な場合にだけ、カスタム値を使用します。このパラメータをチューニングする必要があるのは、GROUP_CONCAT() ステートメントの戻り値を変更して、エンジンがより長い列値を返すようにする場合だけです。この値は、応答の最大サイズを決定するため、max_allowed_packet と並行して使用する必要があります。

影響: このパラメータを高く設定しすぎると、メモリ使用量が多くなり、メモリ不足の状態になることがあります。低く設定しすぎると、クエリが失敗します。

innodb_ft_result_cache_limit

推奨される設定: デフォルト (2,000,000,000 バイト)。ワークロードに応じてカスタム値を使用します。

影響: この値はすでに 1.9 GiB に近いため、デフォルトを超えて値を増やすと、メモリ不足の状態になる可能性があります。

max_heap_table_size

推奨される設定: デフォルト (16,777,216 バイト)。ユーザーによって定義されるメモリ内に作成されるテーブルの最大サイズを制限します。 この値を変更しても、新しく作成されたテーブルにだけ影響があり、既存のテーブルには影響しません。

影響: このパラメータを高く設定しすぎると、メモリ内のテーブルが大きくなった場合に、メモリ使用率が高くなったり、メモリ不足の状態になります。

performance_schema

推奨される設定: メモリ使用率が高いため、t2 インスタンスを無効にします。

影響: Aurora MySQL 5.6 では、パフォーマンススキーマメモリは試行錯誤的に事前に割り当てられます。この事前割り当ては、max_connections、table_open_cache、table_definition_cache などの他の設定パラメータに基づいています。Aurora MySQL 5.7 では、パフォーマンススキーマメモリはオンデマンドで割り当てられます。パフォーマンススキーマは、インスタンスクラス、ワークロード、データベース設定に応じて、通常 1〜3 GB のメモリを消費します。DB インスタンスのメモリが不足している場合、パフォーマンススキーマを有効にするとメモリ不足の状態になる可能性があります。

binlog_cache_size

推奨される設定: デフォルト (32,768 バイト)。このパラメータは、バイナリログキャッシュが使用できるメモリの量を制御します。このパラメータを大きくすると、バッファを使用して過度のディスク書き込みを回避することで、大規模トランザクションのあるシステムでパフォーマンスを向上させることができます。このキャッシュは、接続ごとに割り当てられます。

影響: DB 接続数が多い環境では、この値を制限してメモリ不足状態を引き起こさないようにします。

bulk_insert_buffer_size

推奨される設定: Aurora MySQL には適用されないため、そのままにします。

innodb_buffer_pool_size

推奨される設定: デフォルト (変数値)、Aurora ではインスタンスメモリサイズの 75% に事前設定されているため。SHOW ENGINE INNODB STATUS の出力にバッファプールの使用状況が表示されます。

影響: バッファプールを大きくすると、同じテーブルデータに繰り返しアクセスするときのディスク I/O が少なくなるため、全体的なパフォーマンスが向上します。InnoDB エンジンのオーバーヘッドのため、実際に割り当てられたメモリ量が実際に設定された値よりわずかに多い場合があります。

innodb_sort_buffer_size

推奨される設定: デフォルト (1,048,576 バイト)。

影響: デフォルト値より高い値は、多数の同時クエリがあるシステムで全体的なメモリ負荷を増加させる可能性があります。

join_buffer_size

推奨される設定: デフォルト (262,144 バイト)。この値はさまざまな種類の操作 (結合など) に対して事前に割り当てられているため、単一のクエリでこのバッファの複数のインスタンスを割り当てることができます。結合のパフォーマンスを向上させたい場合は、そのようなテーブルにインデックスを追加することをお勧めします。

影響: このパラメータを変更すると、多数の同時クエリがある環境では、メモリに大きな負荷がかかる可能性があります。この値を大きくしても、インデックスを追加した場合に、JOIN クエリのパフォーマンスが速くなるわけではありません。

key_buffer_size

推奨される設定: これは Aurora には関係がなく、MyISAM テーブルのパフォーマンスにだけ影響するため、デフォルト値 (16,777,216 バイト) のままにします。

影響: Aurora のパフォーマンスには影響しません。

myisam_sort_buffer_size

推奨される設定: デフォルト値 (8,388,608 バイト) のままにします。InnoDB には影響がないため、Aurora には適用されません。

影響: Aurora のパフォーマンスには影響しません。

query_cache_size

推奨される設定: デフォルト (変数値)。このパラメータは Aurora であらかじめチューニングされており、値は MySQL のデフォルト値よりはるかに大きいです。Aurora のクエリキャッシュは、スケーラビリティの問題に悩まされることはありません (MySQL のクエリキャッシュとは異なります)。高スループットで要求の厳しいワークロードに合わせて変更することは容認できる方法です。

影響: このキャッシュを介してクエリにアクセスすると、クエリのパフォーマンスが影響を受けます。クエリキャッシュの使用状況は、「QCache」セクションの下の SHOW STATUS コマンドの出力で確認できます。

query_cache_type

推奨される設定: 有効。デフォルトで、クエリキャッシュは Auroraで有効になっており、パフォーマンスの向上とオーバーヘッドの低減のために有効にしておくことをお勧めします。ただし、ワークロードにメリットがないことがわかっている場合は、クエリキャッシュを無効にしても問題ありません。例としては、読み取りクエリがない、または読み取りクエリが限られている、書き込みが中心のワークロードがあります。

影響: 繰り返すことができる SQL ステートメントのようにワークロードがクエリを再利用する場合、Aurora でクエリキャッシュを無効にするとデータベースのパフォーマンスに影響を与える可能性があります。クエリキャッシュの使用状況は、「Qcache」セクションの下の SHOW STATUS コマンドの出力で確認できます。

read_buffer_size

推奨される設定: デフォルト (262,144 バイト)。

影響: 大きな値を設定すると、全体的なメモリ負荷が高まり、メモリ不足の問題が発生します。大きな値を設定しても安定性を犠牲にすることなくパフォーマンスに役立つことを確認できない限り、設定を上げないでください。

read_rnd_buffer_size

推奨される設定: デフォルト (524,288 バイト)。基盤となるストレージクラスターのパフォーマンス特性により、Aurora の設定を増やす必要はありません。

影響: 大きな値を設定すると、メモリ不足の問題が発生する可能性があります。

table_open_cache

推奨される設定: ワークロードで非常に多数のテーブルに同時にアクセスする必要がない限り、そのままにします。テーブルキャッシュは主要なメモリコンシューマーであり、Aurora のデフォルト値は MySQL のデフォルトよりもかなり高いです。このパラメータは、インスタンスのサイズに基づいて自動的に調整されます。

影響: すべてのテーブルがメモリに収まるわけではないため、多数のテーブル (数十万) を持つデータベースではより大きな設定が必要です。この値を高く設定しすぎると、メモリ不足の状態になる可能性があります。また、パフォーマンススキーマが有効な場合、この設定はパフォーマンススキーマのメモリ使用量にも間接的に影響します。

table_definition_cache

推奨される設定: デフォルト。この設定は、Aurora では MySQL よりも大幅に大きくなるように事前にチューニングされており、インスタンスのサイズとクラスに基づいて自動的に調整されます。ワークロードがそれを必要とし、データベースが非常に多数のテーブルを同時にオープンすることを必要とする場合、この値を増やすことでテーブルオープンの操作をスピードアップできる可能性があります。このパラメータは、table_open_cache と組み合わせて使用されます。

影響: また、パフォーマンススキーマが有効な場合、この設定はパフォーマンススキーマのメモリ使用量にも間接的に影響します。メモリ不足の問題を引き起こす可能性があるので、デフォルトより高い設定には注意してください。

tmp_table_size

推奨される設定: デフォルト (16,777,216 バイト)。max_heap_table_size とともに、このパラメータはクエリ処理に使用されるメモリ内テーブルのサイズを制限します。一時テーブルサイズの制限を超えると、テーブルはディスクにスワップされます。

影響: 非常に大きな値 (数百メガバイト以上) を設定すると、メモリの問題やメモリ不足エラーを引き起こすので要注意です。このパラメータは、MEMORY エンジンで作成されたテーブルには影響しません。

結論および要点

新しい Aurora MySQL インスタンスをデプロイするときは多くのパラメータが既に最適化されており、パラメータを変更する前に適切なベースラインとして機能します。それぞれのパラメータの値の正確な組み合わせは、個々のシステム、アプリケーションのワークロード、必要なスループット特性に大きく依存します。さらに、変化率、成長、データ取り込み、動的なワークロードが高いデータベースシステムでは、こうしたパラメータの継続的な監視と評価も必要です。データベースをアプリケーションやビジネスのニーズに合わせて調整するため、数ヶ月ごと、おそらく数週間ごとにこの監視と評価を行うことをお勧めします。

測定可能なパフォーマンスの向上につながるパラメータのチューニングを成功させるには、変更した後に実験し、ベースラインを確立し、結果を比較するのが良い方法です。変更が実際の本稼働システムにコミットされる前に、これを行うことをお勧めします。

特定のパラメータに関する詳細情報が必要な場合は、AWS サポートまたは担当の AWS テクニカルアカウントチームにお問い合わせください。

著者について

Fabio Higa は、RDS Aurora / MySQL エンジンを専門とする AWS のデータベーススペシャリストテクニカルアカウントマネージャーです。彼は、3 年以上にわたって世界中の企業顧客と仕事をしてきました。余暇には、彼は自分の車をいじり、地元の競走場へ乗っていくのが好きです。