MySQL パフォーマンス チューニングについて知っておくべき 15 個の重要な変数 (要約)

序文：

MYSQL は最も人気のある WEB バックエンド データベースです。最近、NOSQL がますます話題になっていますが、ほとんどのアーキテクトは依然としてデータ ストレージに MYSQL を選択すると思います。この記事の著者は、MySQL のパフォーマンス チューニングに重要な 15 個の変数を要約して整理しています。補足すべき不足点があれば、ご指摘ください。

1.デフォルトのストレージエンジン

すでに MySQL 5.6 または 5.7 を使用しており、テーブルがすべて InnoDB である場合は、すでに設定されています。そうでない場合は、テーブルを InnoDB に変換し、default_storage_engine を InnoDB に設定してください。

なぜ？つまり、InnoDB は MySQL (Percona Server および MariaDB を含む) に最適なストレージ エンジンであり、トランザクション、高い同時実行性をサポートし、非常に優れたパフォーマンスを発揮します (正しく構成されている場合)。その理由を詳しく説明したバージョンはこちら

2. INNODB_BUFFER_POOL_SIZE

これは InnoDB の最も重要な変数です。実際、主なストレージ エンジンが InnoDB である場合、この変数は MySQL にとって最も重要です。

基本的に、innodb_buffer_pool_size は、キャッシュされたデータ、セカンダリ インデックス、ダーティ データ (変更されたがディスクにフラッシュされていないデータ)、およびアダプティブ ハッシュ インデックスなどのさまざまな内部構造を格納するために使用される InnoDB バッファー プールに MySQL が割り当てるメモリの量を指定します。

経験則として、スタンドアロンの MySQL サーバーでは、マシン上の合計メモリの 80% を MySQL に割り当てる必要があります。 MySQL が共有サーバー上で実行されている場合、または InnoDB バッファ プールのサイズが正しく設定されているかどうかを確認したい場合は、詳細についてはここを参照してください。

3. INNODB_LOG_FILE_SIZE

InnoDB の再実行ログ ファイル設定は、MySQL コミュニティではトランザクション ログとも呼ばれます。 MySQL 5.6.8 までは、トランザクション ログのデフォルト値 innodb_log_file_size=5M が InnoDB のパフォーマンスを最も低下させる要因でした。 MySQL 5.6.8 以降では、デフォルト値が 48M に引き上げられましたが、多くのやや忙しいシステムでは、まだ低すぎます。

経験則として、サーバーがビジー状態のときに 1 ～ 2 時間分の書き込みを保存できるようにログ サイズを設定する必要があります。面倒なことをしたくない場合は、サイズを 1 ～ 2G に設定すると、良好なパフォーマンスが得られます。この変数も非常に重要です。詳細な紹介については、こちらをご覧ください。

次の変数に進む前に、innodb_log_buffer_size について簡単に説明しましょう。 「クイックメンション」は、理解が不十分な場合が多く、焦点が当てられすぎている場合が多いためです。実際、ほとんどの場合、トランザクションがコミットされてディスクに書き込まれる前に、小さなトランザクションの変更を保持するのに十分なだけの小さなバッファを使用するだけで済みます。

もちろん、大規模なトランザクション変更が多数ある場合は、InnoDB ログ バッファ サイズをデフォルトよりも大きい値に変更すると、パフォーマンスがある程度向上しますが、自動コミットを使用している場合、またはトランザクション変更が数 KB 未満の場合は、デフォルト値を維持する方が適切です。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

デフォルトでは、innodb_flush_log_at_trx_commit は 1 に設定されており、これは、各トランザクションがコミットされた直後に InnoDB が同期データをディスクにフラッシュすることを意味します。自動コミットを使用する場合、INSERT、UPDATE、または DELETE ステートメントはそれぞれトランザクション コミットになります。

同期は、ハードディスクへの実際の同期物理書き込みを伴うため、コストのかかる操作です (特にライトバック キャッシュがない場合)。したがって、可能な場合はデフォルト値を使用することはお勧めしません。

可能な値は 0 と 2 の 2 つです。

* 0 はハードディスクにフラッシュしますが、同期されません (トランザクションをコミットするときに実際の IO 操作はありません)

* 2 はリフレッシュも同期も行われないことを意味します (実際の IO 操作はありません)

したがって、0 または 2 に設定すると、同期操作は 1 秒ごとに 1 回実行されます。したがって、明らかな欠点は、最後の 1 秒間に送信されたデータが失われる可能性があることです。具体的には、トランザクションが送信されたが、サーバーの電源がすぐに切れた場合、送信は実行されなかったのと同じになります。

結局のところ、銀行などの金融機関にとって、これは耐え難いことです。ただし、ほとんどの Web サイトでは、innodb_flush_log_at_trx_commit=0|2 を設定でき、サーバーがクラッシュしても大きな問題は発生しません。結局のところ、ほんの数年前まで多くのサイトが MyISAM を使用しており、クラッシュすると 30 個のデータが失われていました (回復プロセスがひどく遅いことは言うまでもありません)。

では、0 と 2 の実際の違いは何でしょうか? OS キャッシュへのフラッシュは非常に高速であるため、パフォーマンスの顕著な違いはごくわずかです。したがって、MySQL がクラッシュした場合 (マシン全体ではない)、データはすでに OS キャッシュにあり、最終的にはディスクに同期されるため、データが失われないように、明らかに 0 に設定する必要があります。

5.SYNC_BINLOG

sync_binlog と innodb_flush_log_at_trx_commit との関係についてはすでに多くのドキュメントが存在するため、簡単に紹介しましょう。

a) サーバーがスレーブとして設定されておらず、バックアップも行わない場合は、sync_binlog=0 に設定するとパフォーマンスが向上します。

b) スレーブ サーバーがあり、バックアップを実行しているが、マスターがクラッシュした場合にバイナリ ログ内の一部のイベントが失われてもかまわない場合は、パフォーマンスを向上させるために sync_binlog=0 を設定します。

c) スレーブとバックアップがあり、スレーブの一貫性と特定の時点へのリカバリ能力 (最新の一貫性のあるバックアップとバイナリ ログを使用してデータベースを特定の時点に復元する能力) を重視している場合は、innodb_flush_log_at_trx_commit=1 を設定し、sync_binlog=1 の使用を真剣に検討する必要があります。

問題は、sync_binlog=1 がコストがかかることです。つまり、各トランザクションをディスクに同期する必要が生じるのです。なぜ 2 つの同期を 1 つにまとめないのかとお考えかもしれません。その通りです。MySQL の新しいバージョン (5.6 および 5.7、MariaDB および Percona Server) にはすでにコミットをマージする機能があるため、この場合、sync_binlog=1 操作はそれほどコストがかかりませんが、古いバージョンの MySQL ではパフォーマンスに依然として大きな影響を及ぼします。

6. INNODB_FLUSH_METHOD

ダブルバッファリングを回避するには、innodb_flush_method を O_DIRECT に設定します。O_DIRECT を使用すべきでないのは、オペレーティング システムがそれをサポートしていない場合のみです。ただし、Linux を実行している場合は、O_DIRECT を使用して直接 IO を有効にします。

直接 IO がない場合、すべてのデータベースの変更が最初に OS キャッシュに書き込まれ、次にディスクに同期されるため、二重バッファリングが発生します。そのため、InnoDB バッファ プールと OS キャッシュには、同じデータのコピーが同時に保持されます。特に、バッファ プールが合計メモリの 50% に制限されている場合、書き込み集中型の環境ではメモリの最大 50% が無駄になる可能性があります。 50% に制限されていない場合、OS キャッシュへの負荷が高くなり、サーバーがスワップを使用する可能性があります。

簡単に言えば、innodb_flush_method=O_DIRECT を設定します。

7. INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5 では、内部ロックの競合を減らし、MySQL のスループットを向上させる手段として、バッファリングされたインスタンスが導入されました。

MySQL 5.5 では、これはスループットの向上にほとんど役立ちませんが、MySQL 5.6 ではこの向上が非常に大きいため、MySQL 5.5 では控えめに innodb_buffer_pool_instances=4 に設定し、MySQL 5.6 および 5.7 では 8 ～ 16 のバッファー プール インスタンスに設定できます。

設定するとパフォーマンスが大幅に向上するわけではありませんが、ほとんどの高負荷状況で適切なパフォーマンスが得られるはずです。

ちなみに、この設定によって個々のクエリの応答時間が短縮されるとは思わないでください。この違いは、同時負荷が高いサーバーでのみ確認できます。たとえば、複数のスレッドが同時に多くのことを実行します。

8. INNODB_THREAD_CONCURRENCY

innodb_thread_concurrency=0 に設定して忘れたほうがよいとよく耳にするかもしれません。ただし、これは低負荷のサーバーで使用する場合にのみ当てはまります。ただし、サーバーの CPU または IO の使用量が飽和状態 (特に時折ピークが発生する) にあり、システムが過負荷時にクエリを正常に処理できるようにする必要がある場合は、innodb_thread_concurrency に注意することを強くお勧めします。

InnoDB には、並行して実行されるスレッドの数を制御する方法があり、これを同時実行制御メカニズムと呼びます。これらのほとんどは、innodb_thread_concurrency 値によって制御されます。 0 に設定すると同時実行制御がオフになり、InnoDB はすべての受信リクエストを (可能な限り) 即座に処理します。

CPU コアが 32 個あり、リクエストが 4 つしかない場合はこれで問題ありません。しかし、CPU コアが 4 つしかなく、リクエストが 32 個しかない場合を想像してください。32 個のリクエストを同時に処理すると、問題が発生します。これら 32 件のリクエストには 4 つの CPU コアしかないため、明らかに通常よりも少なくとも 8 倍 (実際には 8 倍以上) 遅くなりますが、これらのリクエストにはそれぞれ独自の外部ロックと内部ロックがあり、リクエストがスタックする可能性が高くなります。

この変数を変更する方法は、MySQL コマンド プロンプトで次のように実行されます。

グローバル innodb_thread_concurrency=X を設定します。

ほとんどのワークロードとサーバーでは、設定値 8 が適切な開始点であり、サーバーがこの制限に達してリソースの使用率が十分に活用されなくなったら、徐々に値を増やすことができます。現在のクエリ処理ステータスは、show engine innodb status\G で確認でき、次のような行を探します。

InnoDB 内に 22 クエリ、キュー内に 104 クエリ

9.SKIP_NAME_RESOLVE

これについては、まだ追加していない人がたくさんいるので、言及する必要があります。接続時に DNS 解決を回避するには、skip_name_resolve を追加する必要があります。

ほとんどの場合、DNS サーバーの解決は非常に高速であるため、これを変更しても何も気付かないでしょう。しかし、DNS サーバーに障害が発生すると、サーバー上で「認証されていない接続」として表示され、すべてのリクエストが突然遅くなり始めます。

したがって、このような事態が起こるまで変更を待たないでください。ここでこの変数を追加し、ホスト名ベースの認証を回避します。

10. INNODB_IO_CAPACITY、INNODB_IO_CAPACITY_MAX

* innodb_io_capacity: ダーティ データを更新するときに MySQL によって 1 秒あたりに実行される書き込み IO の量を制御するために使用されます。

* innodb_io_capacity_max: 負荷がかかっている場合、MySQL がダーティデータをフラッシュするときに 1 秒あたりに実行する書き込み IO の量を制御します。

まず、これは読み取り、つまり SELECT クエリが行うこととは何の関係もありません。読み取り操作の場合、MySQL は最善を尽くして処理し、結果を返します。書き込み操作に関しては、MySQL はバックグラウンドでループして更新します。各ループでは、更新する必要があるデータの量をチェックし、更新操作に innodb_io_capacity で指定された数を超えるデータを使用しません。これには、変更バッファのマージも含まれます (変更バッファは、ダーティ ページがディスクにフラッシュされる前の二次的な保存の鍵となります)。

次に、「プレッシャーがかかっている」とはどういう意味かを説明する必要があります。MySQL では、これは「緊急」と呼ばれ、MySQL がバックグラウンドでフラッシュしているときに、新しい書き込み操作を許可するために一部のデータをフラッシュする必要がある場合です。すると、MySQL は innodb_io_capacity_max を使用します。

では、なぜ innodb_io_capacity と innodb_io_capacity_max を設定する必要があるのでしょうか?

最善のアプローチは、ストレージ セットアップのランダム書き込みスループットを測定し、innodb_io_capacity_max をデバイスが達成できる最大 IOPS に設定することです。特にシステムが主に書き込み中心である場合は、innodb_io_capacity を 50 ～ 75% に設定する必要があります。

通常、システムの IOPS がどうなるかを予測できます。たとえば、8 台の 15k ハードディスクで構成される RAID10 では、1 秒あたり約 1000 回のランダム書き込み操作を実行できるため、innodb_io_capacity=600 および innodb_io_capacity_max=1000 を設定できます。多くの安価なエンタープライズ SSD は 4,000 ～ 10,000 IOPS などを実現できます。

この値が完璧に設定されていなくても、大きな問題にはなりません。ただし、デフォルトの 200 と 400 では書き込みスループットが制限されるため、フラッシュ プロセスがキャッチされる場合があることに注意してください。このような場合は、ハード ディスクの書き込み IO スループットに達したか、値が小さすぎてスループットが制限されている可能性があります。

11. INNODB_STATS_ON_METADATA

MySQL 5.6 または 5.7 を実行している場合は、innodb_stats_on_metadata のデフォルト値はすでに正しく設定されているため、変更する必要はありません。

ただし、MySQL 5.5 または 5.1 では、この変数をオフにすることを強くお勧めします。オンにすると、show table status などのコマンドは、実行前に数秒待つのではなく、すぐに INFORMATION_SCHEMA を照会し、追加の IO 操作を使用します。

バージョン 5.1.32 以降では、これは動的変数であるため、これをオフにするために MySQL サーバーを再起動する必要はありません。

12. INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN と INNODB_BUFFER_POOL_LOAD_AT_STARTUP

変数 innodb_buffer_pool_dump_at_shutdown と innodb_buffer_pool_load_at_startup はパフォーマンスとは関係ありませんが、MySQL サーバーを時々再起動する場合 (有効にするためなど) は関係します。両方を有効にすると、MySQL を停止したときに、MySQL バッファ プールの内容 (具体的には、キャッシュされたページ) がファイルに保存されます。次回 MySQL を起動すると、バッファ プールの内容をロードするスレッドがバックグラウンドで開始され、ウォームアップ速度が 3 ～ 5 倍向上します。

2つのこと:

まず、閉じるときにバッファ プールの内容をファイルに実際にコピーするのではなく、テーブルスペース ID とページ ID のみをコピーします。これは、ディスク上のページを見つけるのに十分な情報です。そうすれば、何千もの小さなランダム読み取りを必要とするのではなく、大量の連続読み取りでそれらのページを非常に速く読み込むことができます。

2 番目に、MySQL はバッファ プールのコンテンツがロードされるまで待機せずにリクエストの受け入れを開始するため、コンテンツは起動時にバックグラウンドでロードされます (したがって、影響はないと思われます)。

MySQL 5.7.7 では、デフォルトでは、mysql がシャットダウンされたときにバッファー プール ページの 25% のみがファイルにダンプされますが、この値は制御できます。innodb_buffer_pool_dump_pct を使用します。75 ～ 100 が推奨されます。

この機能は MySQL 5.6 以降でのみサポートされています。

13. INNODB_ADAPTIVE_HASH_INDEX_PARTS

多数の SELECT クエリを持つ MySQL サーバーを実行している場合 (そして可能な限り最適化している場合)、アダプティブ ハッシュ インデックスが次のボトルネックになります。アダプティブ ハッシュ インデックスは、InnoDB によって内部的に維持される動的インデックスであり、最も一般的に使用されるクエリ パターンのパフォーマンスを向上させます。この機能はサーバーを再起動することで無効にできますが、MySQL のすべてのバージョンではデフォルトで有効になっています。

この手法は非常に複雑ですが、ほとんどの場合、ほとんどの種類のクエリで大幅な高速化が実現します。ただし、データベースに送信されるクエリが多すぎると、ある時点で AHI ロックとラッチを待機するのに時間がかかりすぎてしまいます。

MySQL 5.7 を使用している場合、この問題は発生しません。innodb_adaptive_hash_index_parts はデフォルトで 8 に設定されており、グローバル ミューテックスがないため、アダプティブ ハッシュ インデックスは 8 つのパーティションに分割されます。

ただし、MySQL 5.7 より前のバージョンでは、AHI パーティションの数を制御することはできません。つまり、AHI を保護するためのグローバル ミューテックスがあり、これにより選択クエリが頻繁に行き詰まる可能性があります。

したがって、5.1 または 5.6 を実行していて、選択クエリが多数ある場合、最も簡単な解決策は、同じバージョンの Percona Server に切り替えて、AHI パーティションをアクティブ化することです。

14.クエリキャッシュタイプ

クエリ キャッシュがうまく機能すると思われる場合は、必ずそれを使用する必要があります。まあ、うまくいくこともあります。ただし、これは負荷が低い場合、特に読み取りがほとんどで書き込みがほとんどまたはまったくない場合にのみ役立ちます。

その場合は、query_cache_type=ON と query_cache_size=256M を設定するだけです。ただし、256M より大きい値を設定しないでください。そうしないと、クエリ キャッシュの障害により深刻なサーバー停止が発生します。

MySQL サーバーの負荷が高い場合は、query_cache_size=0 および query_cache_type=OFF に設定し、サーバーを再起動して有効にすることをお勧めします。こうすることで、MySQL はすべてのクエリに対してクエリ キャッシュ ミューテックスの使用を停止します。

15.テーブルオープンキャッシュインスタンス

MySQL 5.6.6 以降では、テーブル キャッシュを複数のパーティションに分割できます。

テーブル キャッシュは現在開いているテーブルのリストを格納するために使用され、暗黙的な一時テーブルであっても、開いているテーブルまたは閉じているテーブルごとにミューテックスがロックされます。複数のパーティションを使用すると、潜在的な競合が確実に減少します。

MySQL 5.7.8 以降では、table_open_cache_instances=16 がデフォルト設定になります。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。