Linux 上の高負荷時の mysql データベースを徹底的に最適化する

同時に、オンラインのアクセス数も増え続けており、1G のメモリを搭載したサーバーに明らかに負荷がかかると、毎日クラッシュしたり、サーバーが時々フリーズしたりすることがあります。 MySQL は非常にスケーラブルなアルゴリズムを使用しているため、通常は少ないメモリで実行することも、MySQL により多くのメモリを与えてパフォーマンスを向上させることもできます。

mysql をインストールした後、構成ファイルは /usr/local/mysql/share/mysql ディレクトリにあるはずです。my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf などのいくつかの構成ファイルがあります。 , トラフィックの流れが異なるWebサイトや、構成が異なるサーバー環境では、当然、必要な構成ファイルも異なります。

通常の状況では、構成ファイル my-medium.cnf でほとんどのニーズを満たすことができます。通常、構成ファイルを /etc/my.cnf にコピーし、この構成ファイルを変更する必要があるのは、mysqladmin 変数の extend-status –u だけです。 root –p では、現在のパラメータを確認できます。最も重要な設定パラメータは、 key_buffer_size、query_cache_size、table_cacheの 3 つです。

key_buffer_size は MyISAM テーブルに対してのみ機能します。

key_buffer_size はインデックス バッファのサイズを指定します。これにより、インデックス処理の速度、特にインデックスの読み取り速度が決まります。一般に、この値は 16M に設定します。実際、この数値は、少し大きなサイトでは十分ではありません。Key_read_requests と Key_reads のステータス値を確認することで、key_buffer_size の設定が適切かどうかを知ることができます。 key_reads / key_read_requests の比率はできるだけ低くする必要があり、少なくとも 1:100、1:1000 が望ましい (上記のステータス値は、SHOW STATUS LIKE 'key_read%' を使用して取得できます)。 または、phpmyadmin をインストールしている場合は、サーバーの実行ステータスを通じて確認できます。著者は、mysql を管理するために phpmyadmin を使用することを推奨しています。次のステータス値は、phpmyadmin を通じて取得した分析の例です。

このサーバーは 20 日間稼働しています
key_buffer_size – 128M
key_read_requests – 650759289
key_reads - 79112
比率は1:8000に近く、健康状態は非常に良好です。

key_buffer_size を見積もるもう 1 つの方法は、Web サイトのデータベース内の各テーブルのインデックスが占めるスペースを合計することです。このサーバーを例に挙げると、いくつかの大きなテーブルのインデックスを合計すると、この数値はテーブルが大きくなるにつれて増加します。大きくなる。

MySQL 4.0.1 以降、クエリ バッファリング メカニズムが提供されます。クエリ バッファリングを使用すると、MySQL は SELECT ステートメントとクエリ結果をバッファに保存し、今後、同じ SELECT ステートメント (大文字と小文字が区別される) に対して、結果がバッファから直接読み取られるようになります。 MySQL ユーザー マニュアルによると、クエリ バッファリングを使用すると、最大 238% の効率を達成できます。

次のパラメータを調整することで、query_cache_size の設定が適切かどうかを知ることができます。
Qキャッシュ挿入
Qキャッシュのヒット
Qcache lowmem プルーン
Qcache の空きブロック
Qcache の合計ブロック数

Qcache_lowmem_prunes の値が非常に大きい場合は、バッファリングが不十分であることが多いことを示します。同時に、Qcache_hits の値が非常に大きい場合は、クエリ バッファが非常に頻繁に使用されていることを示します。 Qcache_hits の値が小さい場合は、クエリの繰り返し率が非常に低いことを示しているため、クエリ バッファリングを使用しないことを検討してください。さらに、SQL_NO_CACHE を SELECT ステートメントに追加すると、クエリ バッファが使用されていないことを明確に示すことができます。
Qcache_free_blocks、値が非常に大きい場合、バッファ内に多くのフラグメントがあることを示します。 query_cache_type クエリ バッファリングを使用するかどうかを指定します。

私が設定したのは：
クエリキャッシュサイズ = 32M
クエリキャッシュタイプ= 1

次のステータス値を取得します。
キャッシュ 12737 内の Qcache クエリは、現在キャッシュされているアイテムの数を示します
Qcache 挿入 20649006
Qcache ヒット 79060095 クエリの繰り返し率がかなり高いようです。
Qcache lowmem プルーン 617913 キャッシュが低すぎることがよくあります。
Qcache がキャッシュされない 189896
Qcache 空きメモリ 18573912 現在の残りのキャッシュ容量
Qcache の空きブロック数 5328 この数値は少し大きく、断片化しているように見えます
Qcache 合計ブロック数 30953
メモリが 32M を許可する場合は、さらに追加する必要があります。

table_cache はテーブル キャッシュのサイズを指定します。 MySQL がテーブルにアクセスするたびに、テーブル バッファにスペースがある場合は、テーブルが開かれてそこに配置され、テーブルの内容に高速にアクセスできるようになります。ピーク時のステータス値 Open_tables と Opened_tables を確認することで、table_cache の値を増やす必要があるかどうかを判断できます。 open_tables が table_cache と等しく、open_tables が増加していることがわかった場合は、table_cache の値を増やす必要があります (上記のステータス値は、SHOW STATUS LIKE 'Open%tables' を使用して取得できます)。 table_cache をむやみに大きな値に設定することはできないことに注意してください。設定値が高すぎると、ファイル記述子が不足し、パフォーマンスが不安定になったり、接続が失敗したりする可能性があります。

1G のメモリを搭載したマシンの場合、推奨値は 128 ～ 256 です。

作者設定
テーブルキャッシュ = 256

次のステータスを取得します。
オープンテーブル 256
オープンテーブル 9046
open_tables はすでに table_cache と等しくなりますが、サーバーの実行時間と比較すると、20 日間実行されており、open_tables の値も非常に低くなります。したがって、table_cache の値を増やしてもあまり意味がありません。 6 時間実行した後に上記の値が表示された場合は、table_cache を増やすことを検討する必要があります。

バイナリ ログを記録する必要がない場合は、この機能をオフにすると、問題が発生する前のデータを復元できなくなることに注意してください。バイナリ ログには、データを更新するすべてのステートメントが含まれています。目的は、データベースを可能な限り最終状態に復元するために使用します。さらに、同期レプリケーション (レプリケーション) を行う場合は、バイナリ ログを使用して変更を転送する必要もあります。

log_bin はログ ファイルを指定します。ファイル名が指定されていない場合は、MySQL がデフォルトのファイル名を自動的に生成します。 MySQL は、ファイル名の後に数値インデックスを自動的に追加し、サービスが開始されるたびに新しいバイナリ ファイルを再生成します。さらに、log-bin-index を使用してインデックス ファイルを指定し、binlog-do-db を使用して記録するデータベースを指定し、binlog-ignore-db を使用して記録しないデータベースを指定します。注: binlog-do-db および binlog-ignore-db では、一度に 1 つのデータベースのみを指定します。複数のデータベースを指定するには、複数のステートメントが必要です。さらに、MySQL はすべてのデータベース名を小文字に変更します。データベースを指定するときはすべて小文字を使用する必要があります。そうしないと機能しません。

この機能をオフにするには、その前に # 記号を追加します。
#ログビン

スロークエリログをオンにする (スロークエリログ)

スロー クエリ ログは、問題のあるクエリを追跡するのに役立ちます。これは、long_query_time をチェックしたすべてのクエリを記録し、必要に応じてインデックスを使用せずに記録します。遅いクエリ ログの例を次に示します。

スロー クエリ ログを有効にするには、パラメータ log_slow_queries、long_query_times、および log-queries-not-using-indexes を設定する必要があります。

log_slow_queries はログ ファイルを指定します。ファイル名が指定されていない場合は、MySQL が自動的にデフォルトのファイル名を生成します。 long_query_times は低速クエリのしきい値を指定します。デフォルトは 10 秒です。 log-queries-not-using-indexes は 4.1.0 以降に導入されたパラメータで、インデックスを使用しないクエリがログに記録されることを示します。作成者はlong_query_time=10を設定します

作成者の設定:

ソートバッファサイズ = 1M
max_connections=120
待機タイムアウト =120
back_log=100
読み取りバッファサイズ = 1M
スレッドキャッシュ=32
インタラクティブ_タイムアウト=120
thread_concurrency = 4

パラメータの説明:

やり残し
MySQL に必要な接続の数。これは、MySQL のメイン スレッドが短期間に多くの接続リクエストを受信し、メイン スレッドが接続を確認して新しいスレッドを開始するのに (短時間ではありますが) 時間がかかる場合に機能します。 back_log 値は、MySQL が一時的に新しいリクエストへの応答を停止するまでに、短期間にスタックに保存できるリクエストの数を示します。短期間に多くの接続が予想される場合にのみ、この値を増やす必要があります。つまり、この値は、受信 TCP/IP 接続のリスニング キューのサイズです。オペレーティング システムには、このキュー サイズに対する独自の制限があります。 Unix listen(2) システムコールのマニュアルページに詳細が記載されています。この変数の最大値を確認するには、OS のドキュメントを確認してください。 back_log をオペレーティング システムの制限よりも高く設定しようとしても、効果はありません。
最大接続数
最大同時接続数は120です。この値を超えた場合は自動的に回復し、問題は自動的に解決されます。

スレッドキャッシュ
具体的な手順は見つかりませんでしたが、32に設定したところ、以前は1日で数千スレッドが作成されていたのに対し、20日で400以上のスレッドが作成されたため、まだ役に立ちます。

スレッド同時実行性
#CPU の数 x2 に設定します。たとえば、CPU が 1 つしかない場合は、thread_concurrency=2
#CPU が 2 つある場合、thread_concurrency=4

スキップ-innodb
#innodb サポートを削除