mysqlを最適化するにはどうすればよいですか? mysql関連の最適化-mysql チュートリアル-php.cn

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

1.1、CPU 関連

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

1. CPU のパフォーマンスを最大化するための Performance Per Watt Optimized (DAPC) モード。通常、大量の計算を必要とする DB などのサービスを実行する場合は、省電力を考慮しません。

2. C1E や C などのオプションをオフにします。同じ目的のステートです。

3. メモリ周波数の最大パフォーマンスを選択します。 4. ディスク I/O の問題を回避するために、ノードインターリーブを有効にします。 1.2。 IOPS パフォーマンス向上の順序でディスク I/O を最適化できるいくつかの対策は次のとおりです。

1. SSD または PCIe SSD デバイスを使用して、IOPS を少なくとも数百倍、場合によっては 1 万倍向上させます ;
2. CACHE モジュールと BBU モジュールの両方を搭載したアレイカードを購入すると、IOPS (主に SSD または PCIe SSD を除くメカニカルディスクを指します。) を大幅に向上させることができます。同時に、CACHE モジュールと BBU モジュールの健全性を定期的にチェックする必要があります。 BBU モジュールは、事故でデータが失われないことを保証する条件です);

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

4. できるだけ RAID-5 の代わりに RAID-10 を使用します

5. メカニカルディスクを使用する場合は、次のことを選択します。可能な限りハイエンドのディスクを使用します。たとえば、回転速度が 7.2KRPM のディスクではなく 15KRPM のディスクを選択すると、数ドルの価値はありません

。

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

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

ファイルシステム層では、次の対策により IOPS パフォーマンスを大幅に向上させることができます:

1. Deadline/noop I/O スケジューリングサーバーを使用し、決して使用しないcfq を使用します (DB サービスの実行には適していません)

2. xfs ファイルシステムを使用し、ext3 は使用しないでください。ext4 はかろうじて利用可能ですが、ビジネス量が大きい場合は xfs を使用する必要があります。 3. ファイルシステムのマウントパラメータに noatime、nodiratime、nobarrier オプションを追加しました (nobarrier は xfs ファイルシステムに固有です) 2.2. 他のカーネルパラメータの最適化

適切なカーネルを設定する目的パラメータの値は、スワップの傾向を減らし、メモリとディスク I/O の大きな変動を防ぎ、瞬間的なピーク負荷を引き起こすことを目的としています:

1. vm.swappiness を約 5 ～ 10 に設定します。 SWAP が使用される可能性を減らすために、これを 0 に設定します (RHEL 7 以降では、OOM kill の発生を許可しない限り、0 に設定するように注意してください)。 2. vm.dirty_background_ratio を 5 ～ 10 に設定します。 vm .dirty_ratio は、瞬間的な I/O 書き込みや深刻な待機を回避するために、ダーティデータをディスクに継続的にフラッシュできるように、その約 2 倍に設定されています (MySQL の innodb_max_dirty_pages_pct と同様)

3. net.ipv4 を変更します。 tcp_tw_recycle と net.ipv4.tcp_tw_reuse は両方とも 1 に設定され、TIME_WAIT を削減し、TCP 効率を向上させます

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

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

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

これについては、ほとんどの人がこれを選択すると思います。

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

もう 1 つの重要なブランチバージョンは MariaDB です。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 である場合は、innodb_buffer_pool_size のサイズを調整します。エンジンテーブルの場合は、物理メモリの 50% ～ 70% に設定することを検討してください。

4. 実際のニーズに応じて、innodb_flush_log_at_trx_commit と sync_binlog の値を設定します。データが失われない場合は、両方を 1 に設定します。多少のデータ損失が許容される場合は、それぞれ 2 と 10 に設定できます。また、データが失われたかどうかを気にする必要がない場合 (たとえば、スレーブではいずれにせよやり直される)、すべてを 0 に設定できます。これら 3 つの設定値はデータベースのパフォーマンスに影響します。高、中、低です。つまり、最初の値はデータベースを最も遅くし、最後の値はその逆になります。 innodb_file_per_table = 1 を設定し、使用します。独立したテーブルスペース、共有テーブルスペースを使用する利点は本当に思いつきません

6. 同時トランザクションが多い場合は、デフォルトの 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 ミリ秒) に設定することをお勧めします。 (最大接続数)、max_connection_error (エラーの最大数は 100,000 以上に設定することをお勧めします。パラメータ open_files_limit、innodb_open_files、table_open_cache、table_defining_cache は max_connection の約 10 倍のサイズに設定できます。

10. よくある誤解は、tmp_table_size と max_heap_table_size の設定が比較的大きいことです。これら 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 テーブルは自動インクリメントなしで設計されています。ビジネス目的の列が主キーとして使用される場合、これはほとんどのシナリオに当てはまります。この場合、TokuDB を使用する方がコスト効率が高くなります。
2. フィールドの長さはニーズを満たす前提として、可能な限り短い長さを選択します。さらに、フィールド属性に NOT NULL 制約を追加するようにしてください。これにより、パフォーマンスがある程度向上します

3. 必要に応じて、TEXT/BLOB 型を使用しないようにしてください。それらをメインテーブルと一緒に配置しないことで、SELECT * を使用するときの読み取りパフォーマンスの低下を回避できます。

4. データを読み取るときは、特に一部の TEXT/BLOB 列を読み取る場合、深刻なランダム読み取りの問題を避けるために、必要な列のみを選択しないでください。 VARCHAR(N) 列では、通常、その長さの約 50% (またはそれよりも小さい) を使用してプレフィックスインデックスを作成するだけで、クエリのニーズの 80% 以上を満たすことができます。完全長のインデックスを作成する必要はありません。列全体;

6、通常の状況では、サブクエリのパフォーマンスは比較的低いため、JOIN 書き込みに変更することをお勧めします。型は可能な限り一貫している必要があり、インデックスが必要です。

8. マルチテーブルクエリに接続するときは、小さな結果セットを含むテーブルを使用します (これは必ずしもフィルターされた結果セットを指すわけではないことに注意してください)テーブル全体の少量のデータ) を駆動テーブルとして使用します。

9. 複数のテーブルが結合されて並べ替えられる場合、並べ替えフィールドはドライバーテーブル内に存在する必要があります。そうでない場合、並べ替え列はインデックスを使用できません。
10. 特に一部のカーディナリティが小さすぎる場合 (たとえば、列の一意の値の合計数が 255 未満である列には独立インデックスを作成しない)、複合インデックスを多く使用し、複数の独立インデックスの使用を減らします。

11. ページング関数と同様の SQL の場合、最初に主キーの関連付けを使用してから結果セットを返すことをお勧めします。これにより、はるかに効率的になります。

3.4. その他の提案

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

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

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

4. pt-duplicate-key-checker を定期的に使用して、重複したインデックスをチェックして削除します。 pt-index-usage ツールを定期的に使用して、使用頻度が非常に低いインデックスを確認して削除します。 5. 低速クエリのログを定期的に収集し、pt-query-digest ツールを使用して分析します。風速計と組み合わせることができます。分析のための低速クエリ管理のためのシステム。

6. pt-kill を使用して、長期 SQL リクエストを強制終了することもできます。この機能を実現するための innodb_kill_idle_transaction オプションもあります。 ;

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

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

最後に: この最適化リファレンスでは、ほとんどの場合に適用できるシナリオを紹介しましたが、アプリケーションのシナリオがこの記事で説明されているものと異なる場合は、次のようにすることをお勧めします。機械的にコピーするのではなく、実際の状況に応じて調整します。質問や提案は歓迎しますが、頭を経由しない習慣的な抵抗は拒否されます。