MySQL をインストールした後に調整する必要がある 10 のパフォーマンス設定項目

このブログでは、MySQL データベースをインストールした後に調整することが推奨される 10 のパフォーマンス設定について説明します。

通常、MySQL パフォーマンス監査を実行する必要がある場合は、MySQL 構成を確認し、改善のための推奨事項を作成します。ほとんどの場合、何百ものオプションが利用可能であるにもかかわらず、インストール後にいくつかのコア MySQL パフォーマンス チューニング パラメータのみを変更することをお勧めします。この記事の目的は、最も重要なパラメータ設定のリストを示し、それらを調整する方法を説明することです。

調整を始める前に

経験豊富な人でも、大きな問題を引き起こすような間違いを犯す可能性があります。したがって、この記事で推奨されている構成項目を適用する前に、次の点に留意してください。

• 一度に 1 つの設定を変更してください。これはセットアップが機能したことを確認する唯一の方法です。
• ほとんどの構成項目は、SET GLOBAL コマンドを使用して実行時に変更できます。この方法は非常に便利で、変更後に問題が発生した場合でも、すぐに元の設定に戻すことができます。しかし、最終的には、この変更を永続的にするには、構成ファイルに書き込む必要があります。
• MySQL を再起動した後でも、設定ファイル内のパラメータが有効にならない場合があります。この時点で、次の点を考慮する必要があります。正しい構成ファイルを使用していますか?このパラメータを正しい場所に置きましたか? (この記事のすべての設定は [mysqld] セクションに属します)
• 構成を変更した後にデータベースが起動に失敗した場合、正しい単位が使用されているかどうかを確認する必要がありますか?たとえば、innodb_buffer_pool_size はバイト単位ですが、max_connection には単位がありません。
• 設定ファイルでは重複した設定は許可されません。構成の変更を追跡する場合は、バージョン管理を使用します。
• 「新しいサーバーには古いサーバーの 2 倍の RAM があるので、すべての構成オプションを元の値の 2 倍に設定できます」などの単純な計算は行わないでください。

基本設定

ここでは主に、頻繁に目にすることになる 3 つの非常に重要な MySQL パフォーマンス設定項目について説明します。調整しないと、問題が発生する可能性があります。

innodb_buffer_pool_size:

これは、InnoDB ストレージ エンジンを使用する MySQL インストールをインストールした後に最初に確認する必要がある構成です。バッファ プールは、データとインデックスをキャッシュするために使用されます。ほとんどの読み取り操作がディスクではなくメモリから実行されるように、できるだけ多くのメモリを割り当てる必要があります。通常の設定は、5〜6GB（8GB RAM）、20〜25GB（32GB RAM）、100〜120GB（128GB RAM）です。

innodb_log_file_size:

このオプションは、REDO ログのサイズを設定します。 REDO ログは、書き込まれたデータが迅速に書き込まれ、永続化されることを保証するために使用され、クラッシュ回復にも使用できます。 MySQL 5.1 より前では、パフォーマンスを向上させるために redo ログ サイズを増やす一方で、クラッシュからの高速リカバリのために redo ログ サイズを減らす必要があったため、このオプションを調整するのは困難でした。幸いなことに、MySQL 5.5 以降ではクラッシュリカバリのパフォーマンスが大幅に向上し、高速なクラッシュリカバリの要件を満たしながら高速な書き込みパフォーマンスを実現できるようになりました。 MySQL 5.5 までは、REDO ログの合計サイズは 4GB (デフォルトでは 2 つのログ ファイル) に制限されていました。これは MySQL 5.6 で追加されました。

十分な書き込みスペースを確保するために、起動時に innodb_log_file_size = 512M (つまり、1GB の REDO ログ) を設定します。アプリケーションで書き込みが頻繁に行われることが分かっている場合は、さらに増やすことができます。

最大接続数:

「接続数が多すぎます」というエラーが頻繁に発生する場合は、max_connections が小さすぎることが原因です。このエラーは、アプリケーションがデータベースへの接続を適切に閉じていないためによく発生します。接続数をデフォルトの 151 より大きい値に設定する必要があります。 max_connections を高く設定しすぎると (1000 以上など)、1000 以上のトランザクションを実行するときにサーバーが遅くなったり、応答しなくなったりする主な欠点があります。アプリケーション側で接続プールを使用するか、MySQL 側でスレッド プールを使用すると、この問題を解決できます。

InnoDB設定

MySQL 5.5 以降、InnoDB がデフォルトのストレージ エンジンとなり、他のストレージ エンジンよりもはるかに頻繁に使用されるようになりました。そのため、慎重に設定することが重要です。

innodb_file_per_table:

この構成項目は、InnoDB がデータとインデックスを格納するために共有テーブルスペースを使用するか (innodb_file_per_table = OFF)、テーブルごとに個別の ibd ファイルを使用するか (innodb_file_per_table = ON) を決定します。テーブルごとに 1 つのファイルを使用すると、テーブルを削除、切り捨て、または再構築するときにテーブル スペースが再利用されます。圧縮などの一部の高度な機能では、独立した表領域の使用も必要です。ただし、このオプションではパフォーマンスは向上しません。

MySQL 5.6 以降のバージョンでは、この構成項目はデフォルトで有効になっているため、ほとんどの場合、何もする必要はありません。以前のバージョンの MySQL では、新しく作成されたテーブルにのみ影響するため、起動する前にこれを ON に設定する必要があります。

innodb_flush_log_at_trx_commit:

デフォルト値は 1 で、InnoDB が ACID 機能を完全にサポートすることを意味します。たとえば、データのセキュリティを主に重視するマスターノードでは、これが最適な設定です。ただし、変更が REDO ログにフラッシュされるたびに追加の fsync 操作が必要になるため、低速ディスク システムでは大きなオーバーヘッドが発生する可能性があります。 2 に設定すると、コミットされたトランザクションは 1 秒に 1 回しか REDO ログにフラッシュされないため、信頼性は低くなりますが、マスターの場合はそれでも許容できる場合があり、レプリケーション関係のスレーブの場合は適切な値です。 0 に設定すると高速になりますが、クラッシュが発生した場合に一部のデータが失われる可能性があります。これはスレーブにのみ適した設定です。

innodb_flush_method:

この設定は、データとログをディスクにフラッシュする方法を決定します。サーバー ハードウェアに RAID コントローラー、電源オフ保護、およびライトバック キャッシュ メカニズムがある場合、最もよく使用される値は O_DIRECT です。その他のほとんどのシナリオでは、デフォルト値の fdatasync が使用されます。 Sysbench は、こ​​れら 2 つの値を選択するのに役立つ優れたツールです。

innodb_log_buffer_size:

この設定は、まだコミットされていないトランザクションをキャッシュするために使用されるバッファのサイズを設定するために使用されます。通常はデフォルト値 (1MB) で十分ですが、トランザクションに大きな BLOB/テキスト フィールドが含まれるようになると、このバッファーはすぐにいっぱいになり、追加の I/O 負荷が発生します。 innodb_log_waits ステータス変数の値を確認します。0 でない場合は、innodb_log_buffer_size を増やす必要があります。

その他の設定

クエリキャッシュサイズ:

同時実行性が高くない場合でも、クエリ キャッシュがボトルネックになることは誰もが知っています。最適な設定は、使用初日にクエリ キャッシュを無効にする (query_cache_size = 0) ことです。このオプションは、MySQL 5.6 以降ではデフォルトで無効になっています。クエリ速度は、適切なインデックスを設計したり、読み取りと書き込みの分離を増やしたり、追加のキャッシュ (たとえば、memcache や redis) を使用したりすることで改善できます。クエリ キャッシュを有効にした MySQL を使用していて、問題が発生したことがない場合は、クエリ キャッシュはおそらく有益であり、無効にする場合は慎重に進める必要があります。

ログビン:

レプリケーション関係においてノードをプライマリ ノードにする場合は、バイナリ ログを有効にする必要があります。同時に、グローバルに一意の server_id を設定する必要があります。単一インスタンス データベースの場合、データを以前の時点に復元する (最新のバックアップを使用して復元し、binlog を使用して回復する) には、バイナリ ログが必要です。バイナリ ログは作成されると永続的に保存されるため、ディスク領域を使いたくない場合は、PURGE BINARY LOGS を使用して古いバイナリ ログ ファイルをクリーンアップするか、expire_logs_days オプションを設定して、期限切れのバイナリ ログを自動的にクリーンアップするのにかかる日数を指定する必要があります。

バイナリ ファイルのログ記録はリソースを消費するため、マスター/スレーブ レプリケーション環境では、スタンバイ データベースに Binlog が必要ない場合は無効にすることができます。

スキップ名解決:

クライアントが接続すると、サーバーはホスト名の解決を実行しますが、DNS が遅いと接続の確立に時間がかかります。したがって、起動時に skip-name-resolve を設定して DNS ルックアップを無効にすることをお勧めします。唯一の制限は、GRANT ステートメントは IP アドレスでのみ使用できることです。そのため、このオプションを既存のシステムに追加する場合は細心の注意が必要です。

結論は

もちろん、負荷やハードウェアの状態に応じて、チューニングに使用できる他の設定もあります。たとえば、メモリが小さい、ディスクが高速、同時実行性が高い、書き込みが集中する負荷の場合は、特定のチューニングが必要です。ただし、この記事の目的は、MySQL パフォーマンス チューニング構成項目をいくつか提供し、公式ドキュメントを読むのに多くの時間を費やすことなく、適切な MySQL 構成ファイルをすばやく構成し、どのパラメータが重要であるかを理解できるようにすることです。

以上がMySQLをインストールした後に調整する必要がある10のパフォーマンス設定項目の詳細です。MySQLのパフォーマンス設定項目の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。