MySQL パフォーマンスの包括的な最適化方法リファレンス、CPU、ファイルシステムの選択から mysql.cnf パラメータの最適化まで

この記事では、一般的な MySQL 最適化方法をいくつかまとめて簡単に紹介します。これは、フルタイムの MySQL DBA を持たない企業が基本的な最適化作業を行うのに役立つことを目的としています。特定の SQL 最適化については、適切なインデックスを追加することでほとんどを実現できます。より複雑なものには、特定の分析が必要です。このサイトでいくつかの最適化事例を参照するか、お問い合わせください。

1. ハードウェア層関連の最適化

1.1、CPU関連

サーバーの BIOS 設定では、次の構成を調整して、CPU パフォーマンスを最大化したり、従来の NUMA の問題を回避したりできます。

1. CPU パフォーマンスを最大化するには、ワットあたりのパフォーマンス最適化 (DAPC) モードを選択します。通常、高い計算能力を必要とする DB サービスを実行する場合は、省電力を考慮しないでください。

2. CPU 効率を向上させるために、C1E や C ステートなどのオプションをオフにします。

3. メモリ周波数に最大パフォーマンスを選択します。

4. メモリ設定メニューで、NUMA の問題を回避するためにノードインターリーブを有効にします。

1.2 ディスクI/O関連

以下は、ディスク I/O を最適化するために使用できるいくつかの対策であり、IOPS パフォーマンスの向上の程度によって並べられています。

1. SSD または PCIe SSD デバイスを使用して、IOPS を少なくとも数百倍、さらには数万倍に増加します。

2. CACHE モジュールと BBU モジュールを搭載したアレイ カードを購入すると、IOPS が大幅に向上します (主に機械式ディスクで、SSD または PCIe SSD は除きます。同時に、事故が発生した場合にデータが失われないように、CACHE モジュールと BBU モジュールの健全性を定期的にチェックする必要があります)。

3. アレイ カードがある場合は、アレイ書き込みポリシーを WB に設定するか、FORCE WB に設定します (デュアル電源保護がある場合、またはデータ セキュリティ要件が特に高くない場合)。WT ポリシーの使用は固く禁じられています。そして、クローズド配列の事前読み取り戦略は基本的に役に立ちません。

4. 可能な場合は RAID-5 ではなく RAID-10 を選択します。

5. 機械式ディスクを使用する場合は、7.2KRPM ディスクではなく、15KRPM ディスクなどの高速ディスクを選択するようにしてください。数ドル節約できます。

2. システムレベルの最適化

2.1. ファイルシステム層の最適化

ファイル システム レベルでは、次の対策により IOPS パフォーマンスを大幅に向上できます。

1. 期限/noop I/O スケジューラを使用し、cfq は使用しないでください (DB サービスの実行には適していません)。

2. xfs ファイル システムを使用し、ext3 は使用しないでください。ext4 はほとんど使用できませんが、ビジネス ボリュームが大きい場合は xfs を使用する必要があります。

3. ファイル システムのマウント パラメータに次のオプションを追加しました: noatime、nodiratime、nobarrier (nobarrier は xfs ファイル システムに固有です)。

2.2. その他のカーネルパラメータの最適化

主要なカーネルパラメータに適切な値を設定する目的は、スワッピングの傾向を減らし、瞬間的なピーク負荷につながるメモリとディスク I/O の大きな変動を防ぐことです。

1. SWAP が使用される可能性を減らすために、vm.swappiness を 5 ～ 10 程度、または 0 に設定します (RHEL 7 以降では、OOM キルを許可しない限り、0 に設定するように注意してください)。

2. vm.dirty_background_ratio を 5 ～ 10 に設定し、vm.dirty_ratio をその値の約 2 倍に設定して、ダーティ データが継続的にディスクにフラッシュされ、長時間の待機を引き起こす瞬間的な I/O 書き込みを回避できるようにします (MySQL の innodb_max_dirty_pages_pct と同様)。

3. TIME_WAIT を減らして TCP 効率を向上させるには、net.ipv4.tcp_tw_recycle と net.ipv4.tcp_tw_reuse を 1 に設定します。

4. オンラインで流通している 2 つのパラメータ read_ahead_kb と nr_requests については、テストの結果、読み取りと書き込みが混在する OLTP 環境にはほとんど影響がないことがわかりました (読み取りに敏感なシナリオではより効果的であるはずです)。ただし、私のテスト方法に問題がある可能性があります。独自の判断で調整するかどうかを検討してください。

3. MySQLレイヤー関連の最適化

3.1. バージョン選択について

公式バージョンを ORACLE MySQL と呼びます。これについては特に言うことはありませんが、ほとんどの人がこれを選択すると思います。

個人的には、パフォーマンス、信頼性、管理性において多くの改善が行われた、比較的成熟した優れた MySQL ブランチ バージョンである Percona ブランチ バージョンを選択することを強くお勧めします。基本的にORACLE MySQL公式版と完全互換で、性能も20%以上向上しているので真っ先にお勧めし、私も2008年から愛用しています。

もう 1 つの重要なブランチ バージョンは MariaDB です。MariaDB は ORACLE MySQL を置き換えることを目標としているため、実際にはブランチ バージョンであると言うのは適切ではありません。これは主に、元の MySQL Server レイヤーのソース コード レベルの多くの改善が行われており、非常に信頼性が高く、優れたブランチ バージョンでもあります。しかし、これにより、GTID などの新機能が公式バージョンと互換性がなくなりました (MySQL 5.7 以降では、GTID モードの動的なオンライン有効化または無効化もサポートされています)。ほとんどの人が引き続き公式バージョンに従うことを考慮すると、MariaDB は優先的に推奨されません。

3.2. 最も重要なパラメータオプションに関する提案

パフォーマンスを向上させるには、次の主要なパラメータを調整することをお勧めします (このサイトが提供する my.cnf ジェネレータを使用して、構成ファイル テンプレートを生成できます)。

1. Percona または MariaDB バージョンを選択する場合は、同時実行性が高い場合にパフォーマンスが大幅に低下しないように、スレッド プール機能を有効にすることを強くお勧めします。さらに、extra_port 関数は非常に実用的で、危機的な瞬間に命を救うことができます。もう 1 つの重要な機能は QUERY_RESPONSE_TIME 関数です。これにより、全体的な SQL 応答時間の分布を直感的に理解できます。

2. default-storage-engine=InnoDB を設定します。これは、InnoDB エンジンがデフォルトで使用されることを意味します。MyISAM エンジンを使用しないことを強くお勧めします。InnoDB エンジンは、間違いなく 99% 以上のビジネス シナリオに対応できます。

3. innodb_buffer_pool_size を調整します。インスタンスが 1 つだけで、テーブルのほとんどが InnoDB エンジン テーブルである場合は、物理メモリの約 50% ～ 70% に設定することを検討してください。

4. 実際のニーズに応じて、innodb_flush_log_at_trx_commit と sync_binlog の値を設定します。データ損失が必要な場合は、両方とも 1 に設定されます。多少のデータ損失が許容できる場合は、それぞれ 2 と 10 に設定できます。データの損失をまったく気にしない場合は (たとえば、スレーブでは最悪の場合、データがやり直されることになるでしょう)、すべてを 0 に設定できます。 3 つの設定は、高、中、低の順にデータベースのパフォーマンスに影響します。最初の設定ではデータベースが最も遅くなり、最後の設定ではデータベースが最も遅くなります。

5. innodb_file_per_table = 1 を設定し、別のテーブルスペースを使用します。共有テーブルスペースを使用する利点はまったく思いつきません。

6. innodb_data_file_path = ibdata1:1G:autoextend を設定します。デフォルトの 10M は使用しないでください。同時トランザクションが多い場合に大きな影響が出ます。

7. innodb_log_file_size=256M、innodb_log_files_in_group=2 を設定します。これにより、基本的に 90% 以上のシナリオを満たすことができます。

8. long_query_time = 1 に設定します。バージョン 5.5 以上では、1 未満に設定できます。後続の分析とトラブルシューティングのために実行速度が遅い SQL ステートメントを記録するには、0.05 (50 ミリ秒) に設定することをお勧めします。

9. 実際のビジネスニーズに応じて、max_connection (最大接続数) と max_connection_error (最大エラー数) を調整します。100,000 以上に設定することをお勧めします。また、パラメータ open_files_limit、innodb_open_files、table_open_cache、および table_definition_cache は、max_connection の約 10 倍のサイズに設定できます。

10. よくある間違いは、tmp_table_size と max_heap_table_size を比較的大きな値に設定することです。1G の設定を見たことがあります。これら 2 つのオプションは各接続セッションに割り当てられるため、あまり大きく設定しないでください。そうしないと、簡単に OOM が発生します。sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size などの他の接続セッション レベルのオプションも、あまり大きく設定しないでください。

11. MyISAM エンジンを使用しないことが推奨されているため、key_buffer_size を約 32M に設定し、クエリ キャッシュ機能をオフにすることを強くお勧めします。

3.3. スキーマ設計仕様とSQL使用の提案

MySQL の効率を向上させるのに役立つ一般的なスキーマ設計仕様と SQL 使用の提案を次に示します。

1. すべての InnoDB テーブルは、ビジネス用途以外の自動増分列を主キーとして設計されています。これはほとんどのシナリオに当てはまります。InnoDB テーブルが本当に読み取り専用であるケースは多くありません。その場合は、TokuDB を使用する方がコスト効率が高くなります。

2. フィールドの長さが要件を満たしている場合は、可能な限り短い長さを選択します。さらに、フィールド属性に NOT NULL 制約を追加して、パフォーマンスをある程度向上させてみましょう。

3. TEXT/BLOB 型の使用はできる限り避けてください。必要に応じて、SELECT * 使用時の読み取りパフォーマンスの低下を避けるために、サブテーブルに分割し、メインテーブルと一緒に配置しないことをお勧めします。

4. データを読み取るときは、必要な列のみを選択し、特に一部の TEXT/BLOB 列を読み取るときに深刻なランダム読み取りの問題を回避するために、毎回 SELECT * を使用しないでください。

5. VARCHAR(N) 列のインデックスを作成する場合、通常は、クエリ要件の 80% 以上を満たすには、その長さの約 50% (またはそれ以下) のプレフィックス インデックスを作成すれば十分です。列全体に対してフル レングスのインデックスを作成する必要はありません。

6. 一般的に、サブクエリのパフォーマンスは比較的低いため、JOIN 式に変換することをお勧めします。

7. 複数のテーブルをクエリする場合、関連するフィールドのタイプは可能な限り一貫している必要があり、すべてにインデックスが必要です。

8. 複数のテーブルをクエリする場合は、結果セットが最も小さいテーブル (これはフィルタリングされた結果セットを指し、必ずしもデータ量が最も小さいテーブルではないことに注意してください) を駆動テーブルとして使用します。

9. 複数のテーブルを結合してソートする場合、ソートフィールドは駆動テーブル内に存在する必要があります。そうでない場合、ソート列はインデックスを使用できません。

10. 複合インデックスを多く使用し、独立インデックスを少なくします。特に、カーディナリティの小さい列（たとえば、列内の一意の値の合計数が 255 未満）には独立インデックスを作成しないでください。

11. ページング機能を持つ SQL の場合、最初に主キーに関連付けてから結果セットを返すことをお勧めします。これにより、効率が大幅に向上します。

3.4 その他の提案

MySQL の管理と保守に関するその他の提案は次のとおりです。

1. 一般的に、単一テーブルの物理サイズは 10 GB を超えず、単一テーブル内の行数は 1 億を超えず、行の平均長は 8 KB を超えません。マシンのパフォーマンスが十分であれば、MySQL はこの量のデータを完全に処理できるため、パフォーマンスの問題を心配する必要はありません。この推奨事項は主に ONLINE DDL のコストが高いためです。

2. OOM キルが発生しず、大量の SWAP が使用されない限り、mysqld プロセスがメモリを大量に消費することについてあまり心配する必要はありません。

3. これまで、1 台のマシンで複数のインスタンスを実行する目的は、コンピューティング リソースの使用を最大化することでした。1 つのインスタンスですでにコンピューティング リソースのほとんどを使い切ってしまう場合は、複数のインスタンスを実行する必要はありませんでした。

4. pt-duplicate-key-checker を定期的に使用して、重複するインデックスをチェックして削除します。 pt-index-usage ツールを使用して、あまり使用されないインデックスを定期的にチェックし、削除します。

5. スロークエリのログを定期的に収集し、pt-query-digest ツールを使用して分析します。スロークエリを管理する Anemometer システムを組み合わせて、スロークエリを分析し、その後の最適化作業を実行できます。

6. 時間がかかりすぎる SQL リクエストを強制終了するには、pt-kill を使用できます。Percona バージョンには、この機能も実現できる innodb_kill_idle_transaction オプションがあります。

7. pt-online-schema-change を使用して、大規模なテーブルの ONLINE DDL 要件を完了します。

8. pt-table-checksum と pt-table-sync を定期的に使用して、MySQL マスター スレーブ レプリケーション間のデータの違いをチェックし、修復します。

この最適化リファレンスでは、ほとんどの場合に適用可能なシナリオを紹介しています。アプリケーションシナリオがこの記事で説明したものと異なる場合は、盲目的にコピーするのではなく、実際の状況に基づいて調整することをお勧めします。