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は、サーバー操作パラメーターに応じてさまざまな変数を調整することについてアドバイスします。
記事の準備では、公式文書と自分の頭に加えて、次の資料が使用されました。

• http://blogfreebsd.com/freebsd/optimizaciya-mysql.html
• http://habrahabr.ru/blogs/mysql/41166/
• http://www.mysqlperformanceblog.com/
• http://mysyslog.ru/posts/223

興味を持って聞き 、コメント、発言、追加を読みます。
この記事の詳細については、 こちらをご覧ください 。