MySQLサーバーのチューニングと最適化

この記事では、主にパフォーマンスに影響するさまざまなMySQL設定について説明します。便宜上、すべての変数はセクション（基本設定、制限、ストリーム設定、クエリキャッシュ、タイミング、バッファー、InnoDB）に分割されています。まず、MySQLのバージョン4で変更されたいくつかの変数の名前を明確にします。ネットワークは、名前の古い変種と新しい変種に引き続き対応しているため、疑問が生じます。

そのため、バージョン4では、多くの変数の末尾に_size

ます。これは、 thread_cache_size

変数とBuffersセクションの変数に適用されます。また、バージョン4以前の変数read_buffer_size

と呼ばれていrecord_buffer

。また、バージョン4以前の基本設定セクションのskip_external_locking

変数はskip_external_locking

と呼ばれていskip_locking

。

変数は、値を持つ変数と変数フラグの2つの主要なカテゴリに分類されます。値を持つ変数は、 variable = value

として構成ファイルに書き込まれ、フラグ変数は単純に指定されます。また、変数名に「 -

」と「 _

」が使用されている場合があることにお気づきでしょう。ハイフン付きの変数はサーバーの開始オプションであり、サーバーの実行中は変更できません（SETを使用）。下線付きの変数はサーバーオプションであり、その場で変更できます。「状態変数」について話している場合、または名前がVariable_Name

の形式で記述されている変数の値を確認することをお勧めする場合は、 SHOW STATUS LIKE "Variable_Name"

リクエストを実行してこの変数の値を取得するか、追加のコメントがあるphpMyAdminのステータスタブを確認する必要がありますこの変数の値によって。

ここで、変数とその可能な値を見てみましょう。

基本設定

low-priority-updates

-このオプションは、SELECTと比較してINSERT / UPDATE操作の優先度を下げます。実際には、データが高速で書き込むよりも速く読み取ることが重要である場合。
skip-external-locking

オプションはバージョン4以降、デフォルトでインストールされます。データベースを操作するときに外部ロックを使用しないようにMySQLサーバーに指示します。外部ロックは、複数のサーバーが同じデータファイルを操作する状況で必要です。実際には使用されない同じdatadir

持ちます。
skip-name-resolve

接続しているクライアントのIPアドレスのドメイン名を定義しません。同時に、ホストではなくIPアドレス（localhostを除く）でユーザー権限を構成する必要があります。ローカルマシンからのみサーバーに接続する場合、それは実際には問題ではありません。外部接続の場合、接続セットアップを高速化します。
skip-networking

使用しない、つまり TCP / IP接続をまったく処理しません。サーバーとの通信は、ソケットを介して排他的に行われます。 TCP / IPのみを使用してサーバーと通信するソフトウェアがない場合に推奨されます。

制限事項

bind-address

サーバーがリッスンするインターフェース。セキュリティ上の理由から、サーバーへの外部接続を使用していない場合は、ここで127.0.0.1を設定することをお勧めします。
max_allowed_packet

-1つのリクエストで転送できるデータの最大サイズ。「パケットが大きすぎます」エラーが発生した場合は、増やす必要があります。
max_connections

サーバーへの同時接続の最大数。「接続が多すぎる」問題が発生した場合は、値を増やします。
max_join_size

指定された行数を超える、または指定された数のディスク検索を超えると解析されるはずのSELECTステートメントを禁止します。数百万行を読み取ろうとする不正なクエリから保護するために使用されます。デフォルト値は40億を超えているため、大幅に減らすことをお勧めします。
max_sort_length

ソート時に使用するBLOBまたはTEXTタイプのフィールドの先頭からのバイト数を示します。デフォルト値は1024です。誤って設計されたテーブルまたはクエリを恐れている場合は、減らす必要があります。

ストリーム設定

thread_cache_size

キャッシュされたスレッドの数を示します。リクエストを処理した後、サーバーはストリームを終了しませんが、キャッシュで見つかったスレッドの数が指定された値より少ない場合、キャッシュに配置します。デフォルト値は0で、8に増やすか、すぐに16にThreads_Created

ますThreads_Created

状態変数の値がThreads_Created

場合、 thread_cache_size

も増やす必要があります。
thread_concurrency

-Solaris / SunOSにのみ関連します。ネットワーク上での記述とは逆です。関数thr_setconcurrencyを呼び出して、システムが同時に開始するスレッド数を「ヒント」します。推奨値は、プロセッサコアの数が2倍または3倍です。

キャッシュをリクエスト

query_cache_limit

キャッシュされたリクエストの最大サイズ。
query_cache_min_res_unit

キャッシュに保存されるブロックの最小サイズ。
query_cache_size

キャッシュサイズ。 0はキャッシュの使用を無効にします。最適な値を選択するには、 Qcache_lowmem_prunes

状態Qcache_lowmem_prunes

を監視し、その値Qcache_lowmem_prunes

大幅に増加Qcache_lowmem_prunes

ないことを確認する必要があります。また、キャッシュが大きすぎると不必要な負荷が発生することを覚えておく必要があります。
query_cache_type

（OFF、DEMAND、ON）。 OFFはキャッシュを無効にし、 DEMAND-クエリにSQL_CACHEディレクティブが存在する場合にのみキャッシュが実行され、 ONはキャッシュを有効にします。
query_cache_wlock_invalidate

られているテーブルが読み取り用にロックされている場合、キャッシュからデータを取得するかどうかを決定します。

クエリキャッシュは、キーがクエリであり、値がクエリの結果であるハッシュ配列と考えることができます。結果に加えて、MySQLはテーブルのリストをキャッシュに保存し、その選択はキャッシュされます。キャッシュ内に選択があるテーブルのいずれかで変更が発生した場合、MySQLはそのようなサンプルをキャッシュから削除します。また、MySQLは結果が変わる可能性のあるクエリをキャッシュしません。

起動時に、MySQLはquery_cache_size

のサイズのメモリブロックを割り当てます。クエリを実行すると、結果の最初の行が受信されるとすぐに、サーバーはそれらのキャッシュを開始します。キャッシュにquery_cache_min_res_unit

と等しいメモリブロックを割り当て、選択結果を書き込みます。すべての選択がブロックに収まらない場合、サーバーは次のブロックを選択します。記録の開始時に、MySQLは結果のサンプルのサイズを認識しないため、キャッシュに記録されたサンプルサイズがquery_cache_limit

より大きい場合、記録が停止し、占有スペースが解放されます。したがって、選択の結果が大きいことが事前にわかっている場合は、ディレクティブで実行する必要がありますSQL_NO_CACHE

タイミング

interactive_timeout

サーバーが、対話接続を閉じる前に（ CLIENT_INTERACTIVEフラグを使用して）対話接続の側からのアクティビティを待機する時間（秒単位）。
log_slow_queries

長い（「遅い」）リクエスト（ long_query_time

よりも長く実行される）をログに記録するようにサーバーに指示します。値は完全修飾ファイル名です（たとえば、 /var/log/slow_queries

）。
long_query_time

クエリが指定された時間（秒単位）より長く実行された場合、「遅い」と見なされます。
net_read_timeout

接続が中断される前にサーバーがデータの受信を待機する秒単位の時間。サーバーが非常に遅いまたは不安定なチャネルを持つクライアントにサービスを提供しない場合、15秒で十分です。
net_write_timeout

接続が中断される前にサーバーがデータの受信を待機する秒単位の時間。サーバーが非常に遅いまたは不安定なチャネルを持つクライアントにサービスを提供しない場合、15秒で十分です。
wait_timeout

サーバーが接続アクティビティを中断する前に待機する秒単位の時間。一般に、30秒で十分です。

バッファ

すべてのバッファには共通点が1つあります。バッファサイズが大きいためにデータがページファイルに送られる場合、バッファは害を及ぼすよりも害が大きくなります。したがって、常に使用可能な物理RAMの量に注目してください。

key_buffer_size

インデックスに割り当てられ、すべてのスレッドで使用可能なバッファーのサイズ。パフォーマンスに影響する非常に重要な設定。デフォルト値は8 MBですが、間違いなく増やす価値があります。合計RAMの15〜30％が推奨されますが、すべての.MYIファイルの合計サイズを超えてインストールすることは意味がありません。 Key_reads

およびKey_read_requests

状態変数をKey_read_requests

ますKey_reads/Key_read_requests

の比率はKey_reads/Key_read_requests

必要があります（<0.01）。この比率が大きい場合、バッファサイズを増やす必要があります。
max_heap_table_size

（タイプMEMORYの）メモリに格納されたテーブルの最大許容サイズ。 MEMORYテーブルを使用しない場合、デフォルト値は16 MBで、この値をtmp_table_size

設定します。
myisam_sort_buffer_size

- REPAIR TABLEでインデックスをソートするため、またはCREATE INDEX、ALTER TABLEでインデックスを作成するためにMyISAMによって割り当てられたバッファのサイズ。デフォルト値は8 MBです。RAMの最大30〜40％に増やす必要があります。それぞれパフォーマンスの向上は、上記のリクエストを実行する場合にのみ発生します。
net_buffer_length

ストリームごとに接続バッファーと結果バッファーに割り当てられたメモリーの量。接続バッファーは指定されたサイズになり、結果バッファーは同じサイズになります。ダブルストリームnet_buffer_length

が各スレッドに割り当てられます。指定された値は初期値であり、必要に応じて、バッファーはmax_allowed_packet

まで増加します。デフォルトのサイズは16 KBです。メモリが限られている場合、または小さなクエリのみを使用している場合は、値を減らすことができます。大規模なクエリと十分なメモリを常に使用する場合は、値をリクエストの推定平均サイズまで増やす必要があります。
read_buffer_size

順次テーブルスキャン中の各スレッドは、各テーブルに指定された量のメモリを割り当てます。テストが示すように、この値は特に増やすべきではありません。デフォルトのサイズは128 KBです。256KBに増やしてから512 KBに増やし、 SELECT COUNT(*) FROM table WHERE expr LIKE "a%";

などのクエリの実行速度を観察してSELECT COUNT(*) FROM table WHERE expr LIKE "a%";

大きなテーブル。
read_rnd_buffer_size

「 ORDER BY 」、つまり結果をソートする必要があり、インデックスを持つテーブルを参照するクエリの場合。デフォルト値は256 KBですが、メモリが許す場合は1 MB以上に増やします。示されたメモリ値も各スレッドに割り当てられることに注意してください。
sort_buffer_size

ソート（ ORDER BY ）またはグループ化（ GROUP BY ）を実行する各スレッドは、指定されたサイズのバッファーを割り当てます。デフォルト値は2 MBです。指定されたタイプのクエリを使用し、メモリが許可する場合、値を増やす必要があります。状態変数Sort_merge_passes

の大きな値は、 Sort_merge_passes

を増やす必要があることを示しています。また、大きなテーブルでSELECT * FROM table ORDER BY name DESC

の形式のクエリを実行する速度をチェックする価値がありSELECT * FROM table ORDER BY name DESC

バッファを増やすと速度が低下する可能性があります（一部のテストではこれが当てはまります）。
table_cache

（バージョン5.1.3以降のtable_open_cache

）-すべてのスレッドのキャッシュされたオープンテーブルの数。テーブルファイルを開くことは、リソースを大量に消費する操作になる可能性があるため、開いているテーブルをキャッシュに保持することをお勧めします。このキャッシュの各エントリはシステム記述子を使用するため、記述子の数（ ulimit ）を増やす必要があることに注意してください。デフォルト値は64で、テーブルの数が許容限度内であれば、テーブルの合計数まで増やすのが最適です。 Opened_tables

状態Opened_tables

使用すると、キャッシュをバイパスして開かれたテーブルの数を監視できます。その値はできるだけ低いことが望ましいです。
tmp_table_size

内部ニーズのためにMySQLによって作成された一時テーブルに割り当てられるメモリの最大サイズ。この値は変数max_heap_table_size

によっても制限されるため、結果として、 max_heap_table_size

とtmp_table_size

から最小値が選択され、残りの一時テーブルがディスク上に作成されます。デフォルト値はシステムによって異なりますCreated_tmp_disk_tables

設定して、 Created_tmp_disk_tables

ステータス変数を確認してCreated_tmp_disk_tables

。値はできるだけ小さくする必要があります。

構成ファイルの値はバイト単位で指定され、それぞれキロバイトとメガバイトをバイトに変換する必要があります。

Innodb

innodb_additional_mem_pool_size

さまざまな内部構造を格納するためにInnoDBによって割り当てられたメモリのサイズ。 InnoDBにこのメモリが不足している場合、OSからメモリが要求され、MySQLエラーログに警告が書き込まれます。
innodb_buffer_pool_size

は、インデックスとデータの両方を保存するためにInnoDBによって割り当てられたメモリのサイズです。意味-より良い。すべてのInnoDBテーブルの合計サイズまたはRAMの最大80％のいずれか小さい方まで増やすことができます。
innodb_flush_log_at_trx_commit

-3つの有効な値があります：0、1、2。値が0の場合、進行中のトランザクションに関係なく、ログは1秒に1回ディスクにフラッシュされます。値が1の場合、ログは各トランザクションでディスクにフラッシュされます。値が2の場合、ログはトランザクションごとに書き込まれますが、ディスクにフラッシュされることはなく、OSの良心に任されます。デフォルトは1です。これは最も信頼できる設定ですが、最速ではありません。一般に、2を安全に使用できます。データは、OSクラッシュが発生した場合にのみ、OSの設定に応じて数秒で失われます。 0が最速モードですが、OSのクラッシュ中とMySQLサーバー自体のクラッシュ中の両方でデータが失われる可能性があります（ただし、データは1〜2秒のみです）。
innodb_log_buffer_size

ログバッファーのサイズ。デフォルト値は1 MBです。多数のInnoDBトランザクションがあることがわかっている場合、またはInnodb_log_waits

状態Innodb_log_waits

増加している場合は、 Innodb_log_waits

価値がありInnodb_log_waits

。 8 MB以上に増やす必要はほとんどありません。
innodb_log_file_size

-1つのログファイルの最大サイズ。このサイズに達すると、InnoDBは新しいファイルを作成します。デフォルト値は5 MBです。サイズを大きくするとパフォーマンスは向上しますが、データ回復時間が長くなります。サーバーのサイズに応じて、この値を32 MB〜512 MBの範囲で設定します（主観的に判断します）。

phpMyAdminを使用してサーバーの動作を監視することも便利です; [ ステータス]タブと[ 変数]タブが重要です。さらに、phpMyAdminは、サーバー操作パラメーターに応じてさまざまな変数を調整することについてアドバイスします。

記事の準備では、公式文書と自分の頭に加えて、次の資料が使用されました。

興味を持って聞き、コメント、発言、追加を読みます。

この記事の詳細については、こちらをご覧ください。

All Articles