mysql のパフォーマンスを最適化する 5 つの方法

(1) 接続

接続は通常、Web サーバーから行われます。接続に関連するいくつかのパラメーターとその設定方法を以下に示します。

1. max_connections

これは、Web サーバーで許可される接続の最大数です。各接続でセッション メモリが使用されることに注意してください (セッション メモリについては後ほど説明します)。

2. max_packet_allowed

最大パケット サイズは、通常、1 つの大きなブロックで返す必要がある最大のデータ セットのサイズと同じです。リモート mysqldump を使用している場合は、その値を大きくする必要があります。

3. aborted_connects

システム ステータス カウンタをチェックして、数値が増加していないことを確認します。それは、クライアントで接続時にエラーが発生したことを意味します。

4. thread_cache_size

MySQL での接続の開閉は、Oracle などの他のデータベースほど多くの継続的な接続を持たないため、MySQL で新しいスレッドが作成されます。時間の節約にはならないため、MySQL スレッド キャッシュが必要です。

増加している場合は、スレッド キャッシュを大きくするために作成されたスレッドに細心の注意を払ってください。thread_cache_size が 2550 または 100 の場合、メモリ使用量はそれほど多くありません。

(2) クエリ キャッシュ

MySQL のキャッシュされたクエリには、2 つの解析クエリ プランと返されるデータ セットが含まれており、基になるテーブル データまたは構造が変更されると、クエリ キャッシュ内の項目は無効になります。

1. query_cache_min_res_unit

MySQLパラメータのquery_cache_min_res_unitのクエリキャッシュ内のブロックは、次の計算式を使用してクエリキャッシュの平均サイズを計算し、計算結果に従って設定します。 MySQL はクエリをより効率的に使用し、より多くのクエリをキャッシュし、メモリの無駄を削減します。

2. query_cache_size

このパラメータは、クエリ キャッシュの合計サイズを設定します。

3. query_cache_limit

このパラメータは、このサイズを超えるクエリを破棄するように MySQL に指示します。たとえば、大規模なレポートの統計を実行するためにバッチ プロセスを実行するなど、大規模なクエリは比較的まれです。そのため、これらの大きな結果セットがいっぱいになることはありません。クエリキャッシュ。

qcache ヒット率 = qcache_hits / (qcache_hits + com_select)

これらの変数を見つけるには、

SQL> show status like 'qcache%';

SQL> を使用します。

平均クエリ サイズ = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache

SQL> 'query%' のような変数を表示します。

qcache_* で取得できるステータス変数:

SQL> のようなステータスを表示します。 ' ;

query_cache_size の値を取得します。

(3) 一時テーブル

メモリの速度は非常に速いので、すべての並べ替え操作がメモリ内で実行されるようにクエリを調整して結果セットを小さくしてメモリの並べ替えを実現したり、変数を設定したりすることができます。より大きい。 tmp_table_size

max_heap_table_size

MySQL で一時テーブルが作成されるたびに、これら 2 つの変数の最小値が重要な値として使用され、ディスク上に一時テーブルを構築するだけでなく、多くのセッションも作成されます。リソースは限られています。同時に、BLOB または TEXT フィールド タイプを持つテーブルはディスクに直接書き込まれることに注意してください。 MySQL 双方向レプリケーション技術の徹底解説

(4) セッション メモリ

MySQL の各セッションには独自のメモリがあり、このメモリは SQL クエリに割り当てられるメモリなので、できるだけ大きくする必要があります。可能な限りニーズに応えます。ただし、同時にデータベース内の一貫したセッションの数のバランスを取る必要があります。ここでのちょっとした黒魔術は、MySQL がオンデマンドでキャッシュを割り当てるため、単純にキャッシュを追加してセッション数を乗算することはできず、MySQL の通常の使用量よりもはるかに大きな見積もりが得られることです。ベスト プラクティスは、MySQL を起動し、すべてのセッションに接続し、引き続き最上位セッションの VIRT 列に注目することです。通常、mysqld 行の数は、実際のすべての静的 MySQL メモリ領域を差し引いた値になります。すべてのセッションの実際のメモリを取得し、セッション数で割って平均を取得します。 1. read_buffer_size

このキャッシュは、MyISAM テーブルだけでなく、ストレージ エンジン全体にキャッシュされます。

2. sort_buffer_size

実行ソートバッファのサイズ。1M ～ 2M に設定し、特定のクエリに対してより高い値を設定するにはセッション内で設定するのが最適です。

3. join_buffer_size

結合クエリの実行に割り当てられるバッファのサイズ。1M ～ 2M のサイズに設定し、各セッションで必要に応じて個別に設定します。

4. read_rnd_buffer_size

は、操作による並べ替えと並べ替えに使用されます。これを 1M に設定し、セッション内でセッション変数としてより大きな値に設定するのが最適です。

(5) スロークエリログ

スロークエリログは、MySQL の非常に便利な機能です。 1.log_slow_queries

my.cnf ファイルの MySQL パラメータに log_slow_queries パラメータを設定し、それを on に設定します。デフォルトでは、MySQL はファイルをデータ ディレクトリに配置し、ファイルには「hostname-slow.log」の形式で名前が付けられます。 " ですが、このオプションを設定するときに名前を指定することもできます。

2. long_query_time

デフォルト値は 10 秒で、値の範囲は 1 からオンに設定できます。データベースが起動すると、ログはデフォルトでオフになります。 5.1.21 および Google パッチ適用バージョンでは、このオプションはマイクロ秒単位で設定できます。これは素晴らしい機能です。1 秒以上かかるクエリをすべて削除すると、調整は非常に成功し、問題のある SQL を事前に削除するのに役立ちます。それは大きな問題になります。

3. log_queries_not_using_indexes

このオプションをオンにすることをお勧めします。これにより、すべての行を返すクエリが実際に記録されます。

概要

MySQL パラメータ設定の 5 つの主要なカテゴリを紹介しましたが、これらのパラメータは、データベースのパフォーマンス チューニングや障害診断を実行するときに依然として非常に役立ちます。