MySQLのクエリキャッシュの設定値、パフォーマンス

wp-admin

9年前

MySQLのクエリキャッシュの概要、設定値、パフォーマンスについてまとめました。

※目次をクリックすると目次の下部にコンテンツが表示されます。

１．MySQLのクエリ実行の概要
２．クエリキャッシュの概要
３．クエリキャッシュの動作の概要
４．クエリキャッシュの設定
５．クエリキャッシュの保守とステータス確認
６．クエリキャッシュのチューニング

MySQLのクエリ実行の概要

①クライアントがSQL文をサーバーへ送信
　
②サーバーがクエリキャッシュをチェックし、ヒットしたらキャッシュから保存済みの結果をリターンし、ヒットしない場合は、SQL文を次の工程に渡す。
　
・大文字小文字を区別。
　
・ヒットした際、権限をチェックしている。
　
③サーバーがSQL文をパース、前処理、最適化してクエリの実行プラン作成。
　
④クエリ実行エンジンがストレージエンジンに対して実行プランを実行。
　
⑤サーバーが実行結果をクライアントに送信。

クエリキャッシュの概要

・SELECT文のテキストを結果と合わせて格納。あとでまったく同じクエリーを受け取ると、クエリキャッシュから結果を取り出す。
　
・クエリーキャッシュはセッション間で共有されるため、1つのクライアントで生成された結果セットを、別のクライアントから発行された同じクエリーに対して応答として送信できる。
　
・データ変更があると、クエリキャッシュに関連するエントリをすべてフラッシュする。
　
・サーバーの起動時にクエリーキャッシュを無効にするには、query_cache_size システム変数を0に設定する。
　
・クエリーキャッシュのサイズを大きくしすぎると、キャッシュ保守のために必要なオーバーヘッドが増え、キャッシュを有効にする利点が打ち消されることがあるので注意。通常は数十メガバイトのサイズが適切。

クエリキャッシュの動作の概要

１）クエリキャッシュにあるクエリーとの照合
　
・完全に一致しない限り同一とは判断しない。
　
例）大文字小文字を区別
　
下記は異なると判断
SELECT * FROM tbl_name
Select * from tbl_name
　
例）同じクエリーでもキャラクタセットが異なると別のクエリと判断される。
　
２）クエリーキャッシュが使用されない例
　
・クエリーが外部クエリーのサブクエリーである場合。
　
・ストアドファンクション、トリガー、イベントなどのボディ内で実行したクエリー。
　
・ユーザー定義関数(UDF)または格納された関数(stored functions)を指す場合。
　
・ユーザー変数またはローカルに保存されたプログラム変数を指す場合。
　
３）権限
　
・クエリー結果をキャッシュからフェッチする前に、MySQLは関連するすべてのデータベースとテーブルに対してそのユーザーがSELECT権限を持っているかどうかを確認。
　
権限がない場合は、キャッシュ結果は使用しない。
　
４）Qcache_hitsシステム変数
　
・クエリー結果がキャッシュから返る度に、サーバーはQcache_hitsシステム変数の値を増加する。
　
５）テーブルに変更があった場合
　
・そのテーブルからキャッシュしたクエリーのすべてが無効になるため、キャッシュからは削除する。

クエリキャッシュの設定

１）SELECT文のオプション
　
・SQL_CACHE
　
クエリー結果がキャッシュ可能で、query_cache_typeシステム変数がONまたはDEMANDのときは、クエリー結果をキャッシュする。
　
・SQL_NO_CACHE
　
クエリー結果をキャッシュしない
　
例）
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
　
２）クエリキャッシュの設定
　
●have_query_cache
　
・MySQL標準バイナリを使用しているときは、この値は常に、YES。
　
クエリキャッシュを無効化している場合も同様。
　
●query_cache_size
　
・クエリキャッシュのサイズを設定。
　
・値を0にすると、クエリキャッシュを無効化。デフォルトは0。
　
・値を0以外に指定する場合、構造の割り当てにおよそ40KBを必要とするため、クエリーキャッシュのサイズを最低40KBにする。
　
正確なサイズは、システムのアーキテクチャーによる。一般的には32MでOK。
　
サイズが小さすぎると警告がでる。
　
例）

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
　
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936;
         new query cache size is 0

・query_cache_sizeの値は、もっとも近い1024バイトの倍数に揃えられる。そのため、報告される値は、設定したものとは異なる可能性がある。
　
●query_cache_type
　
クエリキャッシュのサイズが0より大きい場合、query_cache_type変数がその機能に影響する。
　
・0またはOFF
キャッシュを行わない、またはキャッシュした結果の読み出しを行わない。
クエリーキャッシュバッファの割当て自体はquery_cache_sizeで行っていることに注意。
　
・1またはON
SELECT SQL_NO_CACHE で始まるステートメント以外のキャッシュになる。
　
・2またはDEMAND
SELECT SQL_CACHE で始まるステートメントだけのキャッシュになる。
　
・最悪のケースではパフォーマンスのオーバーヘッドが約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ステータス変数で確認できる)。
　
しかし、値を増やすとメモリー不足の状態になる恐れがあるので注意が必要。

クエリキャッシュの保守とステータス確認

１）クエリーキャッシュをデフラグ
　
・クエリキャッシュでは、変数長さのブロックを使用するため、クエリキャッシュのメモリー断片化が起こる。
　
・FLUSH QUERY CACHEステートメントでデフラグが出来る。
　
このステートメント実行でキャッシュからクエリーが消えることはない。
　
２）クエリキャッシュからクエリー結果を削除
　
・RESET QUERY CACHE、または、FLUSH TABLESステートメントを使用する。
　
３）クエリキャッシュのパフォーマンスを監視
　

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+

・Qcache_free_blocks
キャッシュ内のフリーメモリーブロックの数
・Qcache_free_memory
フリーメモリの量
・Qcache_hits
キャッシュ上でヒットした数
・Qcache_inserts
キャッシュに挿入されたクエリの数
・Qcache_lowmem_prunes
メモリ不足でキャッシュから削除されたエントリの数
・Qcache_not_cached
メモリ不足などでキャッシュされなかったクエリの数
・Qcache_queries_in_cache
キャッシュ内のクエリの数
・Qcache_total_blocks
キャッシュ内のトータルのメモリブロックの数
　
●Qcache_total_blocks、Qcache_free_blocks
　
・クエリキャッシュのメモリー断片化を確認。
　
・FLUSH QUERY CACHE 後には、フリーのブロックが1つになる。
　
●Qcache_lowmem_prunes
　
・クエリーのキャッシュサイズを調節するときに役立つ。
　
・新しいクエリーのキャッシュを入れるために取り除かれたクエリーの数をカウントしている。
　
クエリキャッシュでは、古い順番にクエリーをキャッシュから削除(LRU)する。

クエリキャッシュのチューニング

１）キャッシュサイズ
　
・Qcache_free_memoryを見てキャッシュがFullになっていたらquery_cache_sizeの値を増やす。

・Qcache_free_memoryの値が小さかったらクライアントコードがキャッシュできないようになっていないかチェックし、特に問題なかったらキャッシュサイズを減らす事を検討する。
　
２）キャッシュヒットレート
　
・下記式でキャッシュヒットレートを求める。
(Qcache_hits /(Qcache_hits + com_select)) * 100
　
※com_select：キャッシュを利用せず実際にSelectクエリが実行された数。
　
90%以上であれば良好と考えられる。
　
３）query_cache_min_res_unitのチューニング
　
①query_cache_min_res_unitの設定値とフラグメンテーション
　
・クエリキャッシュに格納するデータはブロックに分割されるが、query_cache_min_res_unitの設定値でそのブロックのサイズを設定する。
　
・query_cache_min_res_unitの値を増やすとメモリの割当てプロセスを高速にできるが、結果が小さいクエリーが多い場合は、フリーの領域が多く存在することになり、メモリーのフラグメンテーションを招く。
　
断片化するとメモリー不足を解消するために、キャッシュからクエリーを取り除く(削除)動作が強制的に行われる。
　
②query_cache_min_res_unitのチューニング
　
・キャッシュ内の各クエリの平均サイズを下記式で見積もる。
(query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
　
この計算値に合わせてquery_cache_min_res_unitを設定するのが一つの方法。
　
・上記方法は各クエリの平均のサイズである事を考慮する。大半のクエリのサイズがquery_cache_min_res_unitと同程度で一部のクエリのサイズが極端に大きい場合は、query_cache_min_res_unitの設定値は変更せず、query_cache_limit（キャッシュされるクエリーの最大サイズを設定）の設定で大きいサイズのクエリはキャッシュしないようにする方法もある。