~Linux、MySQLからAJAXまでMySQLのクエリキャッシュの設定値、パフォーマンスMySQLのクエリキャッシュの概要、設定値、パフォーマンスについてまとめました。※目次をクリックすると目次の下部にコンテンツが表示されます。1.MySQLのクエリ実行の概要2.クエリキャッシュの概要3.クエリキャッシュの動作の概要4.クエリキャッシュの設定5.クエリキャッシュの保守とステータス確認6.クエリキャッシュのチューニングMySQLのクエリ実行の概要1クライアントがSQL文をサーバーへ送信2サーバーがクエリキャッシュをチェックし、ヒットしたらキャッシュから保存済みの結果をリターンし、ヒットしない場合は、SQL文を次の工程に渡す。・大文字小文字を区別。・ヒットした際、権限をチェックしている。3サーバーがSQL文をパース、前処理、最適化してクエリの実行プラン作成。4クエリ実行エンジンがストレージエンジンに対して実行プランを実行。5サーバーが実行結果をクライアントに送信。・SELECT文のテキストを結果と合わせて格納。あとでまったく同じクエリーを受け取ると、クエリキャッシュから結果を取り出す。・クエリーキャッシュはセッション間で共有されるため、1つのクライアントで生成された結果セットを、別のクライアントから発行された同じクエリーに対して応答として送信できる。・データ変更があると、クエリキャッシュに関連するエントリをすべてフラッシュする。システム変数を0に設定する。・クエリーキャッシュのサイズを大きくしすぎると、キャッシュ保守のために必要なオーバーヘッドが増え、キャッシュを有効にする利点が打ち消されることがあるので注意。通常は数十メガバイトのサイズが適切。・完全に一致しない限り同一とは判断しない。tbl_name例)同じクエリーでもキャラクタセットが異なると別のクエリと判断される。・クエリーが外部クエリーのサブクエリーである場合。・ストアドファンクション、トリガー、イベントなどのボディ内で実行したクエリー。functions)を指す場合。・ユーザー変数またはローカルに保存されたプログラム変数を指す場合。・クエリー結果をキャッシュからフェッチする前に、MySQLは関連するすべてのデータベースとテーブルに対してそのユーザーがSELECT権限を持っているかどうかを確認。権限がない場合は、キャッシュ結果は使用しない。・クエリー結果がキャッシュから返る度に、サーバーはQcache_hitsシステム変数の値を増加する。・そのテーブルからキャッシュしたクエリーのすべてが無効になるため、キャッシュからは削除する。クエリキャッシュの設定1)SELECT文のオプション・SQL_CACHEクエリー結果がキャッシュ可能で、query_cache_typeシステム変数がONまたはDEMANDのときは、クエリー結果をキャッシュする。●have_query_cache・MySQL標準バイナリを使用しているときは、この値は常に、YES。クエリキャッシュを無効化している場合も同様。●query_cache_size・クエリキャッシュのサイズを設定。・値を0にすると、クエリキャッシュを無効化。・値を0以外に指定する場合、構造の割り当てにおよそ40KBを必要とするため、クエリーキャッシュのサイズを最低40KBにする。正確なサイズは、システムのアーキテクチャーによる。一般的には32MでOK。サイズが小さすぎると警告がでる。0・query_cache_sizeの値は、もっとも近い1024バイトの倍数に揃えられる。そのため、報告される値は、設定したものとは異なる可能性がある。●query_cache_typeクエリキャッシュのサイズが0より大きい場合、query_cache_type変数がその機能に影響する。・0またはOFFキャッシュを行わない、またはキャッシュした結果の読み出しを行わない。クエリーキャッシュバッファの割当て自体はquery_cache_sizeで行っていることに注意。始まるステートメント以外のキャッシュになる。始まるステートメントだけのキャッシュになる。・最悪のケースではパフォーマンスのオーバーヘッドが約15%・SELECTの比率が高いサーバで有効。●query_cache_limit・キャッシュされるクエリーの最大サイズを設定。この設定値より大きなサイズの結果はキャッシュされない。・デフォルトは1MB。極端に大きなサイズのクエリによってキャッシュを占めてしまうのは望ましくないが、この設定より少し大きいクエリが多数実行されてパフォーマンスが低下している場合などはこの設定値を増加させる。●query_cache_wlock_invalidate・通常は、MyISAMテーブルで書き込みロックされている場合、クエリの結果がクエリキャッシュ内に格納されている場合はアクセスはブロックされない。この設定を有効にすると書き込みロックされている場合は、アクセスがブロックされて待機状態となる。●query_cache_min_res_unit・クエリキャッシュに格納するデータはブロックに分割される。そのブロックのサイズを設定する。デフォルト値は、4KB。・結果が小さいクエリーが多い場合は、フリーのブロックが多く存在することになり、デフォルトのブロックサイズではメモリーの断片化になりる。断片化するとメモリー不足を解消するために、キャッシュからクエリーを取り除く(削除)動作が強制的に行われる。そのため、query_cache_min_res_unitの値を減らす必要が出てくる。フリーブロックの数はQcache_free_blocksを、そして、この動作で強制的に削除の対象になったクエリーはQcache_lowmem_prunesのステータス変数で確認できる。・クエリーの大部分が大きな結果である場合は、query_cache_min_res_unitで値を増やして、パフォーマンスを改善できる。(Qcache_total_blocks、Qcache_queries_in_cacheステータス変数で確認できる)。値を増やすとメモリー不足の状態になる恐れがあるので注意が必要。クエリキャッシュの保守とステータス確認1)クエリーキャッシュをデフラグ・クエリキャッシュでは、変数長さのブロックを使用するため、クエリキャッシュのメモリー断片化が起こる。CACHEステートメントでデフラグが出来る。このステートメント実行でキャッシュからクエリーが消えることはない。TABLESステートメントを使用する。●Qcache_total_blocks、Qcache_free_blocks・クエリキャッシュのメモリー断片化を確認。後には、フリーのブロックが1つになる。●Qcache_lowmem_prunes・クエリーのキャッシュサイズを調節するときに役立つ。・新しいクエリーのキャッシュを入れるために取り除かれたクエリーの数をカウントしている。クエリキャッシュでは、古い順番にクエリーをキャッシュから削除(LRU)する。クエリキャッシュのチューニング1)キャッシュサイズ・Qcache_free_memoryを見てキャッシュがFullになっていたらquery_cache_sizeの値を増やす。・Qcache_free_memoryの値が小さかったらクライアントコードがキャッシュできないようになっていないかチェックし、特に問題なかったらキャッシュサイズを減らす事を検討する。2)キャッシュヒットレート・下記式でキャッシュヒットレートを求める。100※com_select:キャッシュを利用せず実際にSelectクエリが実行された数。90%以上であれば良好と考えられる。3)query_cache_min_res_unitのチューニング1query_cache_min_res_unitの設定値とフラグメンテーション・クエリキャッシュに格納するデータはブロックに分割されるが、query_cache_min_res_unitの設定値でそのブロックのサイズを設定する。・query_cache_min_res_unitの値を増やすとメモリの割当てプロセスを高速にできるが、結果が小さいクエリーが多い場合は、フリーの領域が多く存在することになり、メモリーのフラグメンテーションを招く。断片化するとメモリー不足を解消するために、キャッシュからクエリーを取り除く(削除)動作が強制的に行われる。2query_cache_min_res_unitのチューニング・キャッシュ内の各クエリの平均サイズを下記式で見積もる。Qcache_queries_in_cacheこの計算値に合わせてquery_cache_min_res_unitを設定するのが一つの方法。・上記方法は各クエリの平均のサイズである事を考慮する。大半のクエリのサイズがquery_cache_min_res_unitと同程度で一部のクエリのサイズが極端に大きい場合は、query_cache_min_res_unitの設定値は変更せず、query_cache_limit(キャッシュされるクエリーの最大サイズを設定)の設定で大きいサイズのクエリはキャッシュしないようにする方法もある。コメントをキャンセルメールアドレスが公開されることはありません。コメントデータの処理方法の詳細はこちらをご覧ください。