Azure Database for MySQL でのサーバー パラメーター

適用対象: Azure Database for MySQL - 単一サーバー

この記事では、Azure Database for MySQL でサーバー パラメーターを構成するための考慮事項とガイドラインを示します。

サーバー パラメーターとは

MySQL エンジンには、エンジンの動作を構成および調整するために使用する、さまざまなサーバー変数やパラメーターが用意されています。 実行時に動的に設定できるパラメーターもあれば、適用するためにサーバーを再起動する必要がある静的なものもあります。

Azure Database for MySQL には、Azure portal、Azure CLI、PowerShell を使用して、MySQL サーバーのさまざまなパラメーターの値をワークロードのニーズに合わせて変更できる機能が用意されています。

構成可能なサーバー パラメーター

サポートされるサーバー パラメーターの一覧は、拡大を続けています。 Azure portal の [サーバー パラメーター] タブを使用して全一覧を表示し、サーバー パラメーターの値を構成できます。

よく更新されるいくつかのサーバー パラメーターの制限について詳しくは、次のセクションを参照してください。 制限は、サーバーの価格レベルと仮想コアによって決まります。

スレッド プール

MySQL では、従来からすべてのクライアント接続にスレッドが割り当てられます。 同時実行ユーザーの数が増えると、それに応じてパフォーマンスが低下します。 アクティブなスレッドが多いと、コンテキスト切り替えの増加、スレッドの競合、CPU キャッシュの不適切な局所性によって、パフォーマンスが大幅に低下する可能性があります。

サーバー側の機能であり、接続プールとは異なる "スレッド プール" により、ワーカー スレッドの動的プールが導入され、パフォーマンスが最大化されます。 この機能を使用して、サーバーで実行されるアクティブ スレッドの数を制限し、スレッド チャーンを最小化します。 これにより、接続の急増が原因で、サーバーでリソースやメモリが不足するのを防ぐことができます。 スレッド プールは、短いクエリと CPU 集中型のワークロード (OLTP ワークロードなど) に対して最も効率的です。

詳しくは、Azure Database for MySQL でのスレッド プールの導入に関する記事を参照してください。

スレッド プールを構成する

スレッド プールを有効にするには、thread_handling サーバー パラメーターを pool-of-threads に更新します。 既定では、このパラメーターは one-thread-per-connection に設定されています。したがって、MySQL で新しい接続ごとに新しいスレッドが作成されます。 これは静的パラメーターであるため、適用するにはサーバーを再起動する必要があります。

次のサーバー パラメーターを設定して、プール内のスレッドの最大数と最小数を構成することもできます。

• thread_pool_max_threads: この値は、プール内のスレッドの数を制限します。
• thread_pool_min_threads: この値は、接続が閉じられた後も予約されるスレッドの数を設定します。

スレッド プールでの短いクエリのパフォーマンス問題を改善するために、"バッチ実行" を有効にできます。 クエリの実行直後にスレッドがスレッド プールに戻されるのではなく、この接続での次のクエリを待機するために、スレッドが短い時間アクティブのままとなります。 その後、スレッドによりクエリが迅速に実行されます。これが完了すると、スレッドは次のクエリを待機します。 このプロセスは、全体の所要時間がしきい値を超えるまで継続されます。

次のサーバー パラメーターを使用して、バッチ実行の動作を決定します。

• thread_pool_batch_wait_timeout:この値は、スレッドで別のクエリの処理を待機する時間を指定します。
• thread_pool_batch_max_time: この値は、スレッドでクエリ実行のサイクルを繰り返して次のクエリを待機する最大時間を決定します。

log_bin_trust_function_creators

Azure Database for MySQL では、バイナリ ログが常に有効になっています (log_bin パラメーターが ON に設定されています)。 トリガーを使用したい場合、"SUPER 特権を持っておらず、バイナリ ログが有効になっています (より安全度の低い log_bin_trust_function_creators 変数を使用することもできます)" のようなエラーが表示されます。

バイナリ ログ形式は常に行であり、サーバーへのすべての接続では "常に" 行ベースのバイナリ ログが使用されます。 行ベースのバイナリ ログは、セキュリティを維持するのに役立ちます。またバイナリ ログは中断されないため、log_bin_trust_function_creators を TRUE に問題なく設定できます。

innodb_buffer_pool_size

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

汎用ストレージ v1 (最大 4 TB をサポート) 上のサーバー

汎用ストレージ v2 (最大 16 TB をサポート) 上のサーバー

innodb_file_per_table

MySQL では、テーブルの作成時に指定した構成に基づいて、InnoDB テーブルが異なるテーブルスペースに保存されます。 システム テーブルスペースは、InnoDB データ辞書のストレージ領域です。 file-per-table テーブルスペースは、1 つの InnoDB テーブルのデータとインデックスを含み、ファイル システム内の専用のデータ ファイルに保存されます。

この動作は、innodb_file_per_table サーバー パラメーターを使用して制御します。 innodb_file_per_table を OFF に設定すると、InnoDB でシステム テーブルスペースにテーブルが作成されます。 それ以外の場合、InnoDB で file-per-table テーブルスペースにテーブルが作成されます。

Azure Database for MySQL では、汎用ストレージ v2 の 1 つのデータ ファイルで、最大 4 TB までサポートされます。 データベースのサイズが 4 TB を超える場合は、innodb_file_per_table テーブルスペースにテーブルを作成する必要があります。 1 つのテーブル サイズが 4 TB を超える場合は、パーティション テーブルを使用する必要があります。

join_buffer_size

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

max_connections

接続数が上限を超えると、エラーが発生することがあります。

max_heap_table_size

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

query_cache_size

クエリ キャッシュは、既定では無効になっています。 クエリ キャッシュを有効にするには、query_cache_type パラメーターを構成します。

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

lower_case_table_names

lower_case_table_name パラメーターは既定で 1 に設定されます。MySQL 5.6 と MySQL 5.7 では、このパラメーターを更新できます。

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

innodb_strict_mode

Row size too large (> 8126) のようなエラーが表示される場合は、innodb_strict_mode パラメーターをオフにすることを検討してください。 innodb_strict_mode は、サーバー レベルでグローバルには変更できません。 行データのサイズが 8K を超える場合、エラー通知なしでデータが切り捨てられ、それによりデータが損失する可能性があります。 ページ サイズ制限に合うようにスキーマを変更することをお勧めします。

このパラメーターは、init_connect を使用してセッション レベルで設定できます。 セッション レベルで innodb_strict_mode を設定するには、一覧にないパラメーターの設定に関する記事を参照してください。

sort_buffer_size

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

tmp_table_size

このパラメーターの詳細については、MySQL のドキュメントを確認してください。

InnoDB バッファー プールのウォームアップ

Azure Database for MySQL を再起動した後、テーブルに対してクエリを実行すると、ディスクに存在するデータ ページが読み込まれます。 これにより、クエリを最初に実行したときの待機時間が長くなり、パフォーマンスが低下します。 待機時間の影響を受けやすいワークロードの場合、このパフォーマンスの低下を許容できない場合があります。

InnoDB バッファー プールのウォームアップを使用して、ウォームアップ期間を短縮できます。 このプロセスでは、DML または SELECT 操作による対応する行へのアクセスを待たずに、再起動の "前" に、バッファー プールに存在するディスク ページが再読み込みされます。 詳しくは、InnoDB バッファー プール サーバーのパラメーターに関する記事を参照してください。

ただし、パフォーマンスの向上は、サーバーの起動時間が長くなるという代償を伴います。 このパラメーターを有効にすると、サーバーにプロビジョニングされている IOPS に応じて、サーバーの起動と再起動にかかる時間が長くなることが予想されます。 その間はサーバーを利用できないため、再起動時間をテストおよび監視して、起動または再起動のパフォーマンスが許容可能なものであることを確認することをお勧めします。 プロビジョニングされた IOPS の数が 1000 未満の場合 (つまり、プロビジョニングされたストレージが 335 GB 未満の場合) は、このパラメーターは使用しないでください。

サーバーのシャットダウン時にバッファー プールの状態を保存するには、サーバー パラメーター innodb_buffer_pool_dump_at_shutdown を ON に設定します。 同様に、サーバー起動時にバッファー プールの状態を復元するには、サーバー パラメーター innodb_buffer_pool_load_at_startup を ON に設定します。 サーバー パラメーター innodb_buffer_pool_dump_pct の値を下げて微調整すると、起動または再起動時間への影響を制御できます。 既定では、このパラメーターは 25 に設定されます。

time_zone

初期デプロイの時点で、Azure Database for MySQL を実行するサーバーには、タイム ゾーン情報のシステム テーブルが含まれていますが、これらのテーブルには値が設定されていません。 MySQL コマンド ラインや MySQL Workbench などのツールから mysql.az_load_timezone ストアド プロシージャを呼び出すことによって、テーブルに値を設定できます。 ストアド プロシージャを呼び出して、グローバルまたはセッション レベルのタイム ゾーンを設定する方法について詳しくは、タイム ゾーン パラメーターの操作 (Azure portal) またはタイム ゾーン パラメーターの操作 (Azure CLI) に関する記事を参照してください。

binlog_expire_logs_seconds

Azure Database for MySQL では、このパラメーターは、サービスがバイナリ ログ ファイルを消去するまでの待機の秒数を指定します。

"バイナリ ログ" には、テーブルの作成操作やテーブル データの変更など、データベースの変更を示すイベントが含まれます。 また、変更を行った可能性のあるステートメントのイベントも含まれます。 バイナリ ログは、主にレプリケーション操作とデータの復旧操作の 2 つの目的で使用されます。

通常、バイナリ ログは、ハンドルがサービス、バックアップ、またはレプリカ セットから解放されるとただちに消去されます。 レプリカが複数ある場合、バイナリ ログは、最も遅いレプリカが変更を読み取るまで待機してから消去されます。 バイナリ ログを長く保持する場合は、パラメーター binlog_expire_logs_seconds を構成できます。 binlog_expire_logs_seconds が既定値の 0 に設定されている場合、バイナリ ログは、そのバイナリ ログのハンドルが解放されるとただちに消去されます。 binlog_expire_logs_seconds を 0 より大きい値に設定すると、バイナリ ログは、その期間の後でのみ消去されます。

Azure Database for MySQL では、バックアップおよび読み取りレプリカでのバイナリ ファイルの消去などのマネージド機能は内部で処理されます。 Azure Database for MySQL サービスからデータ出力をレプリケートする場合、レプリカによってプライマリから変更が読み取られる前にバイナリ ログが消去されるのを回避するために、プライマリでこのパラメーターを設定する必要があります。 binlog_expire_logs_seconds を大きい値に設定すると、バイナリ ログはすぐには消去されなくなります。 これにより、ストレージの課金が増加する場合があります。

event_scheduler

Azure Database for MySQL では、event_schedule サーバー パラメーターは、イベント (つまり、スケジュールに従って実行されるタスク) の作成、スケジュール設定、および実行を管理します。これらは、特別なイベント スケジューラ スレッドによって実行されます。 event_scheduler パラメーターが ON に設定されている場合、イベント スケジューラ スレッドは SHOW PROCESSLIST の出力にデーモン プロセスとして一覧表示されます。 次の SQL 構文を使用して、イベントを作成およびスケジュールできます。

CREATE EVENT <event name>
ON SCHEDULE EVERY _ MINUTE / HOUR / DAY
STARTS TIMESTAMP / CURRENT_TIMESTAMP
ENDS TIMESTAMP / CURRENT_TIMESTAMP + INTERVAL 1 MINUTE / HOUR / DAY
COMMENT ‘<comment>’
DO
<your statement>;

event_scheduler サーバー パラメーターの構成

次のシナリオは、Azure Database for MySQL で event_scheduler パラメーターを使用する 1 つの方法を示しています。 シナリオを説明するために、次の単純なテーブルの例を考えてみましょう。

mysql> describe tab1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| CreatedAt | timestamp   | YES  |     | NULL    |                |
| CreatedBy | varchar(16) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.23 sec)

Azure Database for MySQL で event_scheduler サーバー パラメーターを構成するには、次の手順に従います。

1. Azure portal でサーバーに移動し、[設定] で [サーバー パラメーター] を選択します。

2. [サーバー パラメーター] ブレードで、[値] ドロップダウン リストで event_scheduler を検索し、[オン] を選択し、[保存] を選択します。

   Note

   動的なサーバー パラメーター構成の変更は、再起動なしでデプロイされます。

3. 次に、イベントを作成するには、MySQL サーバーに接続し、次の SQL コマンドを実行します。

   CREATE EVENT test_event_01
   ON SCHEDULE EVERY 1 MINUTE
   STARTS CURRENT_TIMESTAMP
   ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   COMMENT ‘Inserting record into the table tab1 with current timestamp’
   DO
   INSERT INTO tab1(id,createdAt,createdBy)
   VALUES('',NOW(),CURRENT_USER());
   

4. イベント スケジューラの詳細を表示するには、次の SQL ステートメントを実行します。

   SHOW EVENTS;
   

   次のような出力が表示されます。

   mysql> show events;
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   | Db  | Name          | Definer     | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends                | Status  | Originator | character_set_client | collation_connection | Database Collation |
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   | db1 | test_event_01 | azureuser@% | SYSTEM    | RECURRING | NULL       | 1              | MINUTE         | 2023-04-05 14:47:04 | 2023-04-05 15:47:04 | ENABLED | 3221153808 | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
   +-----+---------------+-------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+
   1 row in set (0.23 sec)
   

5. 数分たったら、テーブルの行に対してクエリを実行し、構成した event_scheduler パラメーターに従って、1 分ごとに挿入される行の表示を開始します。

   mysql> select * from tab1;
   +----+---------------------+-------------+
   | id | CreatedAt           | CreatedBy   |
   +----+---------------------+-------------+
   |  1 | 2023-04-05 14:47:04 | azureuser@% |
   |  2 | 2023-04-05 14:48:04 | azureuser@% |
   |  3 | 2023-04-05 14:49:04 | azureuser@% |
   |  4 | 2023-04-05 14:50:04 | azureuser@% |
   +----+---------------------+-------------+
   4 rows in set (0.23 sec)
   

6. 1 時間たったら、テーブルで Select ステートメントを実行し、この例での event_scheduler の構成どおり、1 時間の間 1 分ごとにテーブルに挿入された値のすべての結果を表示します。

   mysql> select * from tab1;
   +----+---------------------+-------------+
   | id | CreatedAt           | CreatedBy   |
   +----+---------------------+-------------+
   |  1 | 2023-04-05 14:47:04 | azureuser@% |
   |  2 | 2023-04-05 14:48:04 | azureuser@% |
   |  3 | 2023-04-05 14:49:04 | azureuser@% |
   |  4 | 2023-04-05 14:50:04 | azureuser@% |
   |  5 | 2023-04-05 14:51:04 | azureuser@% |
   |  6 | 2023-04-05 14:52:04 | azureuser@% |
   ..< 50 lines trimmed to compact output >..
   | 56 | 2023-04-05 15:42:04 | azureuser@% |
   | 57 | 2023-04-05 15:43:04 | azureuser@% |
   | 58 | 2023-04-05 15:44:04 | azureuser@% |
   | 59 | 2023-04-05 15:45:04 | azureuser@% |
   | 60 | 2023-04-05 15:46:04 | azureuser@% |
   | 61 | 2023-04-05 15:47:04 | azureuser@% |
   +----+---------------------+-------------+
   61 rows in set (0.23 sec)
   

その他のシナリオ

イベントは、ご自身の具体的なシナリオの要件に基づいて設定できます。 以下に、SQL ステートメントを異なる時間間隔で実行するようにスケジュールする、いくつかの類似する例を示します。

SQL ステートメントを今すぐ実行し、1 日に 1 回繰り返し、終了時間は設定しない

CREATE EVENT <event name>
ON SCHEDULE
EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
COMMENT 'Comment'
DO
<your statement>;

1 時間ごとに SQL ステートメントを実行し、終了時間は設定しない

CREATE EVENT <event name>
ON SCHEDULE
EVERY 1 HOUR
COMMENT 'Comment'
DO
<your statement>;

1 日ごとに SQL ステートメントを実行し、終了時間は設定しない

CREATE EVENT <event name>
ON SCHEDULE 
EVERY 1 DAY
STARTS str_to_date( date_format(now(), '%Y%m%d 0200'), '%Y%m%d %H%i' ) + INTERVAL 1 DAY
COMMENT 'Comment'
DO
<your statement>;

構成不可能なサーバー パラメーター

次のサーバー パラメーターは、このサービスでは構成できません。

ここにリストされていないその他の変数は、MySQL の既定値に設定されています。 バージョン 8.0、5.7、5.6 の MySQL ドキュメントを参照してください。

次のステップ

• Azure portal を使用してサーバー パラメーターを構成する方法を学習する
• Azure CLI を使用してサーバー パラメーターを構成する方法を学習する
• PowerShell を使用してサーバー パラメーターを構成する方法を学習する