より包括的な MySQL 最適化リファレンス

この記事は、MySQL の一般的な最適化方法をまとめ、共有するための簡単な概要を作成し、専任の MySQL DBA を持たない企業が基本的な最適化作業を行えるようにすることを目的としています。具体的な SQL の最適化については、ほとんどの企業が実行できます。効果を得るには、より複雑なインデックスを追加する必要があります。

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

1.1. CPU 関連

サーバーの BIOS 設定では、CPU のパフォーマンスを最大化するか、古典的な NUMA の問題を回避するために、次の構成を調整できます。 . CPU のパフォーマンスを最大化するには、Performance Per Watt Optimized (DAPC) モードを選択します。通常、大量の計算を必要とする DB などのサービスを実行する場合は、省電力を考慮しません。同じ目的の C ステート CPU 効率を向上させるため; 3. メモリ周波数の最大パフォーマンスを選択します。 4. ディスク I/O の問題を回避するために、ノード インターリーブを有効にします。 IOPS パフォーマンス向上の順序でディスク I/O を最適化できるいくつかの対策:

1. SSD または PCIe SSD デバイスを使用して、少なくとも数百倍、さらには 1 万倍の IOPS 向上を実現します

2. 搭載されたアレイ カードを購入します。 CACHE モジュールと BBU モジュールの両方を使用すると、IOPS (主に SSD または PCIe SSD を除くメカニカル ディスクを指します) が大幅に向上する可能性があります。同時に、CACHE モジュールと BBU モジュールの正常性状態を定期的にチェックして、データが破損していないことを確認する必要があります。

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

4. できるだけ RAID-5 ではなく RAID-10 を選択してください

5. メカニカルディスクを使用する場合は、高レベルのディスクを選択するようにしてください。 -speed one, such as 7.2KRPM ディスクの代わりに 15KRPM ディスクを選択してください。数ドルの価値はありません

2. システム層関連の最適化

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

IOPS パフォーマンスを大幅に改善できる対策:

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

2. xfs ファイル システムを使用し、ext3 を決して使用しないでください。 ; ext4 はかろうじて使用可能ですが、ビジネス量が大きい場合は、最適化を使用する必要があります

主要なカーネル パラメーターに適切な値を設定すると、スワップの傾向が軽減され、メモリとディスク I/O の大きな変動が防止されます。瞬間的なピーク負荷:

1. vm.swappiness を約 5 ～ 10 に設定するだけで十分ですが、0 に設定しても (OOM 強制終了を許可しない限り、RHEL 7 以降では 0 に設定するように注意してください)、 SWAP を使用する可能性

2. vm.dirty_background_ratio を 5 ～ 10 に設定し、ダーティ データを継続的にディスクにフラッシュして、瞬間的な I/O 書き込みと重大な待機を回避できるようにします (同様のMySQL の innodb_max_dirty_pages_pct に);

3. TIME_WAIT を削減し、TCP 効率を向上させるために、net.ipv4 .tcp_tw_recycle と net.ipv4.tcp_tw_reuse を両方とも 1 に設定します

4. 送信される read_ahead_kb と nr_requests の 2 つのパラメーターについては、このネットワークは、テストの結果、主に読み取りと書き込みを組み合わせて使用​​する OLTP に適していることがわかりました。環境への影響は大きくありません (読み取りに敏感なシナリオではより効果的であるはずです)。ただし、何か問題がある可能性があります。私のテスト方法は、調整するかどうかを決めることができます

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

バージョンの選択について

これについては、ほとんどの人が言うことはないと思います。それを選択します。

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

もう 1 つの重要なブランチ バージョンは MariaDB です。その目的は ORACLE MySQL を置き換えることであるため、MariaDB がブランチ バージョンであると言うのは実際には不適切です。主にオリジナルの 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% に設定します。 About;

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 未満に設定できます。0.05 (50 ミリ秒) に設定することをお勧めします。その後の分析とトラブルシューティングのために、実行が遅い SQL を記録します。 9. 実際のビジネス ニーズに応じて、max_connection (最大値) を適切に調整します。接続数) と max_connection_error (エラーの最大数。10 に設定することをお勧めします。10,000 を超え、パラメータ open_files_limit、innodb_open_files、table_open_cache、および table_defining_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 使用法に関する提案について

以下にリストします 一般的なスキーマ設計仕様と SQL 使用法に関する提案をいくつか示します。これは MySQL の効率を向上させるのに役立ちます:

1. すべての InnoDB テーブルは、ビジネス目的を持たない主キーとして自動インクリメント列を使用して設計されています。これは、ほとんどのシナリオに当てはまります。この場合のみ、TokuDB を使用する方がコスト効率が高くなります。

2. フィールド長がニーズを満たすという前提で、可能な限り短い長さを選択します。さらに、フィールド属性に NOT NULL 制約を追加するようにしてください。これにより、パフォーマンスがある程度向上します

3. 必要に応じて、TEXT/BLOB 型を使用せずにサブテーブルに分割することをお勧めします。 SELECT * 時の読み取りパフォーマンスの低下を避けるために、それらをメインテーブルと組み合わせてください。

4. データを読み取るときは、特に一部の TEXT/BLOB 列を読み取る場合、深刻なランダム読み取りの問題を避けるために、毎回 SELECT * を選択しないでください。通常、プレフィックス インデックスの作成に使用されるインデックスの長さは 80% 以上で十分です。列全体に対して完全長のインデックスを作成する必要はありません。

6. 通常、サブクエリのパフォーマンスは比較的悪いため、JOIN 記述方法に変更することをお勧めします。

7. 複数のテーブル結合を使用してクエリを実行する場合、関連フィールドの型は可能な限り一貫している必要があります。 、そしてそれらはインデックスを持っている必要があります

8. 複数のテーブル結合でクエリを実行する場合、小さな結果セットを含むテーブルを次のように配置します (これは、フィルターされた結果セットを指し、必ずしもテーブル全体の少量のデータを指すわけではないことに注意してください)。ドライバーテーブル;

9. 複数のテーブルを結合してソートする場合、ソートフィールドはドライバーテーブル内になければなりません、そうしないとソート列のインデックスは使用できません

10. 複合インデックスの使用を増やし、複数の独立したインデックスの使用を減らします。カーディナリティが小さすぎる列 (たとえば、列の一意の値の合計数が 255 未満) については、独立したインデックスを作成しないでください

11. 同様のページング機能を備えた SQL の場合は、使用することをお勧めします。

3.4. その他の提案

MySQL の管理とメンテナンスに関するその他の提案は次のとおりです:

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

2. mysqld プロセスが大量のメモリを消費することについては、あまり心配しないでください。OOM キルが発生せず、大量の SWAP が使用される限り、問題はありません。単一のマシン上で複数のインスタンスを実行することは、コンピューティング リソースの使用を最大化するためでした。単一のインスタンスがすでにコンピューティング リソースのほとんどを消費できる場合、複数のインスタンスを実行する必要はありません。

4. pt-duplicate-key を定期的に使用します。 -checker 重複したインデックスをチェックして削除します。 pt-index-usage ツールを定期的に使用して、使用頻度が非常に低いインデックスを確認して削除します。

5. スロークエリログを定期的に収集し、pt-query-digest ツールで分析します。遅いクエリを分析し、その後の最適化作業を実行します。

6. pt-kill を使用して、この機能を実現できるオプション innodb_kill_idle_transaction があります。

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

8. pt-table-checksum と pt-table-sync を定期的に使用して、mysql マスターとスレーブのデータの違いを確認して修復します。レプリケーション;

最後に記載: この最適化リファレンス、大 場合によっては、適用可能なシナリオを紹介しましたが、この記事で説明されているものと異なる場合は、代わりに実際の状況に応じて調整することをお勧めします。それを機械的にコピーすることです。質問や提案は歓迎しますが、頭を経由しない習慣的な抵抗は拒否されます。

上記は比較的包括的な MySQL 最適化リファレンスです。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。