MySQL最適化パラメータリファレンス!

はじめに

毎日の MySQL の運用とメンテナンスの調整に関しては、MySQL 構成ファイル my.cnf を無視することはできません。 MySQL のデフォルトのパラメータでは日常のオンライン ビジネスのニーズを満たすことができないため、パラメータの最適化も不可欠です。 my.cnf 設定に含まれる項目の数と各項目の意味については、公式ドキュメントに記載されていますので記載しません。以下では、日常の作業で注意すべきいくつかのパラメータのみを説明します。

いくつかのパラメータについては以下で説明します。もちろん、負荷やハードウェアに応じて、他の設定が影響する可能性があります。低速なメモリと高速なディスク、高い同時実行性、および書き込み集中型のワークロードがある状況では、特別な調整が必要になります。ただし、ここでの目標は、重要でない MySQL 設定を調整したり、ドキュメントを読んでどの設定が重要であるかを調べることにあまり時間を費やすことなく、堅牢な MySQL 構成をすぐに取得できるようにすることです。

InnoDB 構成

MySQL バージョン 5.5 以降、InnoDB がデフォルトのストレージ エンジンとなり、他のストレージ エンジンよりもはるかに多く使用されます。そのため、慎重に構成する必要があります。

innodb_file_per_table

テーブルのデータとインデックスは、共有テーブルスペースまたは別のテーブルスペースに保存されます。この作業シナリオのインストールでは、デフォルトで innodb_file_per_table = ON に設定されます。これにより、作業中の個別のテーブルスペースの移行も容易になります。 MySQL 5.6 では、このプロパティのデフォルト値は ON です。

_flush_log_at_trx_commit

デフォルト値は 1 で、InnoDB が ACID 機能を完全にサポートすることを意味します。この値は、マスター ノードなどのデータ セキュリティが主な関心事である場合に最も適切です。ただし、ディスク (読み取りおよび書き込み) 速度が遅いシステムの場合、REDO ログへの変更をフラッシュするたびに追加の fsync が必要になるため、大きなオーバーヘッドが発生します。

値を 2 に設定すると、動作が信頼できなくなります。コミットされたトランザクションは 1 秒に 1 回だけ REDO ログにフラッシュされるため、たとえば、この値はプライマリ ノードのバックアップ ノードに許容されます。値 0 は高速ですが、システムクラッシュが発生した場合にデータが失われる可能性があります。バックアップ ノードにのみ適用されます。このパラメータについて話すとき、別の sync_binlog が必ず頭に浮かびます。

innodb_flush_method

この構成は、データとログがハードディスクに書き込まれる方法を決定します。デフォルトでは O_DIRECT を使用します。 O_DIRECT モード: データ ファイルの書き込み操作は、オペレーティング システム バッファを経由せずに、mysql innodb バッファからディスクに直接行われます。実際の完了はフラッシュ ステップで行われ、ログは引き続き OS バッファを経由する必要があります。

innodb_log_buffer_size

この設定は、まだ実行されていないトランザクションに割り当てられるキャッシュを決定します。通常はデフォルト値 (1MB) で十分ですが、トランザクションに大きなバイナリ オブジェクトまたは大きなテキスト フィールドが含まれている場合、このキャッシュはすぐにいっぱいになり、追加の I/O 操作がトリガーされます。 Innodb_log_waits ステータス変数を確認し、0 でない場合は、innodb_log_buffer_size を増やします。

innodb_buffer_pool_size

このパラメータは運用・保守時に注意が必要なパラメータです。バッファ プールは、データとインデックスがキャッシュされる場所です。通常の状況では、このパラメータは物理メモリの 60% ～ 70% に設定されます。 (ただし、弊社のインスタンスは基本的に複数インスタンスの混合デプロイなので、この値はビジネス規模に応じて分析する必要があります。)

innodb_log_file_size

これはREDOログのサイズです。 REDO ログは、書き込み操作が高速かつ信頼性が高く、クラッシュから回復できることを保証するために使用されます。アプリケーションで頻繁にデータを書き込む必要があり、MySQL 5.6 を使用している場合は、最初から 4G に設定できます。 (具体的なサイズは自社のビジネスに応じて適切に調整する必要があります)

innodb_support_xa

innodb_support_xa は、InnoDB の XA 2 段階トランザクション送信を切り替えることができます。デフォルトでは、innodb_support_xa=true は XA の 2 段階のトランザクション送信をサポートします。 XA の 2 段階のトランザクション送信ではフラッシュやその他の操作が冗長になるため、パフォーマンスへの影響は 10% に達します。そのため、一部の DBA はパフォーマンスを向上させるために innodb_support_xa=false を設定します。この場合、redolog と binlog は同期されず、トランザクションがメイン データベースに送信されても​​ binlog に記録されない状況が発生する可能性があります。これにより、トランザクション データが失われる可能性もあります。

innodb_Additional_mem_pool_size

このパラメータは、データフィールド情報およびその他の内部データ構造を保存するために使用されます。テーブルが多いほど、ここでより多くのメモリを割り当てる必要があります。 InnoDB がこのプールのメモリを使い果たすと、InnoDB はオペレーティング システムからメモリの割り当てを開始し、警告メッセージを MySQL エラー ログに書き込みます。デフォルトは 8MB です。一般的な設定は 16MB です。

max_connections

MySQL サーバーのデフォルトの接続数は比較的小さく、通常は約 100 です。最大値を大きく設定することをお勧めします。一般に、500 ～ 1000 に設定すると、各リンクが一定量のメモリを占有するため、パラメータは大きいほど良いです。接続が多すぎる場合、このパラメータのサイズを大きくする人もいますが、実際には、業務ボリュームやプログラム ロジックに問題がある場合、または SQL が適切に記述されていない場合は、このパラメータを大きくしても役に立ちません。エラーが再び発生するのは時間の問題です。アプリケーションで接続プーリングを使用するか、MySQL でプロセス プーリングを使用すると、この問題の解決に役立ちます。

• セッションレベルのメモリ割り当て

リーリー

• グローバルレベルのメモリ割り当て

リーリー パラメータ最適化の最終的な目標は、セッション メモリの割り当てを減らすために合理的な CPU 使用率を制御することで、MySQL がリソースをより効率的に利用できるようにすることです。

サーバーID

スキーマをコピーするときは、サーバー ID が異なることを確認してください。通常、マスター ID はスレーブ ID よりも小さいです。

log_bin

データベースサーバーをマスターノードのバックアップノードとして機能させたい場合は、バイナリログを有効にすることが必須です。これを行う場合は、server_id を一意の値に設定することを忘れないでください。サーバーが 1 台だけの場合でも、これ (バイナリ ログを有効にする) は、ポイントインタイム データのリカバリを実行する場合に役立ちます。最新のバックアップ (完全バックアップ) から復元し、バイナリ ログの変更を適用します (増分バックアップ)。 。

バイナリログは作成されると永久に保存されます。したがって、ディスク容量を使い果たしたくない場合は、PURGE BINARY LOGS を使用して古いファイルを削除するか、expire_logs_days を設定してログが自動的に削除されるまでの日数を指定できます。バイナリ ログにはオーバーヘッドがないわけではないため、プライマリ ノードではないレプリカ ノードで必要ない場合は、このオプションをオフにすることをお勧めします。

skip_name_resolve

クライアントがデータベースサーバーに接続すると、サーバーはホスト名解決を実行しますが、DNS が遅いと、接続の確立も遅くなります。したがって、DNS ルックアップを行わずにサーバーを起動する場合は、skip_name_resolve オプションをオフにすることをお勧めします。唯一の制限は、後で GRANT ステートメントで使用できるのは IP アドレスのみであるため、この設定を既存のシステムに追加する場合は注意が必要です。

sync_binlog

sync_binlog のデフォルト値は 0 です。他のファイルを更新するオペレーティング システムのメカニズムと同様、MySQL はディスクと同期しませんが、オペレーティング システムに依存してバイナリ ログを更新します。

sync_binlog =N (N>0) の場合、MySQL はバイナリ ログを N 回書き込むたびに fdatasync() 関数を使用して、書き込まれたバイナリ ログをディスクに同期します。 innodb_flush_log_at_trx_commit と sync_binlog の両方が 1 の場合が最も安全です。mysqld サービス クラッシュまたはサーバー ホスト クラッシュの場合、バイナリ ログでは最大 1 つのステートメントまたはトランザクションが失われる可能性があります。ただし、ケーキを持って食べることはできません。Double 1 では IO 操作が頻繁に発生するため、このモードは最も遅い方法でもあります。ビジネス上の考慮事項から、ビジネス上のプレッシャーが許せば、デフォルトはダブル 1 構成になります。

log_slave_update

ビジネスでカスケード アーキテクチャを使用する必要がある場合、パラメータ log_slave_update = 1 をオンにする必要があります。オンにしないと、第 3 レベルが第 1 レベルによって生成されたバイナリログを受信できない可能性があり、そのためデータ同期を実行できません。

tmpdir

メモリ一時テーブルが制限を超えると、MySQL はそれをディスクベースの MyISAM テーブルに自動的に変換し、指定された tmpdir ディレクトリに保存します。そのため、tmpdir をできるだけパフォーマンスが良く、高速なストレージ デバイスに設定します。

スローログ関連

slow_query_log = 1 #スローログを開く

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5 #クエリがスローログに入力される秒数を設定します

その他の質問

パラメータに対するSSDの影響

科学技術の発展に伴い、ますます多くのストレージデバイスが従来の機械コンポーネントから電子コンポーネントで構成される永久ストレージに移行し始めており、価格も企業にとって受け入れられるものになりつつあります。ストレージ コンポーネントの速度が向上した後は、従来の機械コンポーネントの DB 構成を使用するのは無駄であると思われるため、さまざまなストレージ テクノロジに応じて MySQL 構成を調整する必要があります。たとえば、innodb_io_capacity を増やしたり、ログ ファイルを作成したりする必要があります。 SSD へのアトミック書き込みは、二重書き込みバッファ、InnoDB 圧縮、単一マシンの複数インスタンス + cgroup などを必要としません。 I/O 状況を分析し、innodb_io_capacity と innodb_max_dirty_pages_pct を動的に調整して、innodb_adaptive_flushing を調整して効果を確認します。

スレッドプール設定

innodb_write_io_threads と innodb_read_io_threads の調整はまだ行っていませんが、8 または 16 に調整することでシステム I/O パフォーマンスが向上すると思います。また、次の点に注意する必要があります。調整はデータのサポートと厳密な分析に基づいていなければなりません。そうでないと、この種の調整は非常に意味があり、実際に価値をもたらす可能性があるため、さらに努力する必要があります。そしてなぜそのような調整を行う必要があるのか​​を可能な限り理解してください。

CPU関連

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• テーブル定義キャッシュ=2000

IO関連

• Innodb_flush_method O_DIRECT の使用をお勧めします
• Innodb_io_capacity は、ディスクでサポートされている最大 IOPS に設定されます
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• Innodb の事前読み取りに関して、メインまたは一意のインデックス システムに基づいている場合は、事前読み取りを無効にすることをお勧めします
• Innodb_random_read_ahead = オフ

以上がMySQL最適化パラメータリファレンス!の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。