MySQL Cache メモ #MySQL

メモと言う名のコピペ

ちょっと古い、5.1 だけど日本語ドキュメントがそこしか無かったのと、英語読む気力ないけど、とりあえずめも

動作とかごにょごにょ

クエリキャッシュは、SELECT SQL_CALC_FOUND_ROWS ... のクエリで動作し、後続する SELECT FOUND_ROWS() クエリで返る値を格納します。FOUND_ROWS() は前のクエリがキャッシュからフェッチしていても、正確な値を返します。これは、検索したレコードの数をキャッシュで保管しているためです。SELECT FOUND_ROWS() クエリ自体はキャッシュの対象ではありません。

クエリをキャッシュする設定である場合、その結果 (クライアントに送信したデータ) を、結果の読み出し中に、クエリキャッシュに格納します。そのため、データの扱いは、ひとまとめではありません。つまり、クエリキャッシュで、データをブロックに分割するため、1 つのブロックが埋まれば、次のブロックを埋めることになります。これは、メモリの割り当てに時間がかかるため、クエリキャッシュではブロックにします。そのときのブロックのサイズを決定するのが、 query_cache_min_res_unit 変数です。

キャッシュするクエリには、少なくとも 2 つのブロックを必要とします。1 つはクエリテキスト用で、もう 1 つはクエリ結果用です。さらに、もう 1 つ、テーブルのクエリ要求用にもブロックを必要とします。ただし、複数のクエリで同じテーブルを使用している場合は、1 ブロックの割り当てで済みます。

Query の大文字小文字に注意

クエリは、バイト同士など、完全に一致しない限り、同一とは判断しません。

解析前のクエリには、解釈が始まる前にクエリキャッシュにあるクエリとの照合を行います。そのため、次の 2 つのクエリは、クエリキャッシュで異なるものである、とみなします。

SELECT * FROM tbl_name
Select * from tbl_name

キャッシュを使う所

InnoDB テーブルを使用するトランザクションでもクエリキャッシュを使用します
MySQL 5.1 では、ビューで生成したクエリもキャッシュします。

Select Statement

Select文でキャッシュを使うか使わないかを選択できる

SQL_CACHE
query_cache_type 環境変数の値が、DEMAND または ON のときは、クエリ結果をキャッシュします。
SQL_NO_CACHE
クエリ結果をキャッシュしません。

例

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

キャッシュしない条件

クエリキャッシュは、解釈が始まる前にクエリ同士を照合することから、次のような種類のクエリはキャッシュの対象になりません。
1. 準備されたステートメント (準備文)
2. クエリが外部クエリのサブクエリである場合
3. Stored プロシージャ、Stored 関数、トリガ、イベントなどのボディ内で実行したクエリ
クエリ結果をキャッシュからフェッチする前に、MySQL で、そのユーザがすてべのデータベースと関連するテーブルにおいて、 SELECT 権限があるかどうかを調べます。権限がない場合は、キャッシュ結果は使用しません。
テーブルに変更があった場合、そのテーブルからキャッシュしたクエリのすべてが無効になるため、キャッシュからは削除します。変更があったクエリのマップである MERGE テーブルを使用するクエリも削除の対象になります。INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE、DROP DATABASE など、様々なステートメントでテーブルは変化します。
一部関数を含むクエリはキャッシュしない
BENCHMARK()
CONNECTION_ID()
CURDATE()
CURRENT_DATE()
CURRENT_TIME()
CURRENT_TIMESTAMP()
CURTIME()
DATABASE()
ENCRYPT() (パラメータなし)
FOUND_ROWS()
GET_LOCK()
LAST_INSERT_ID()
LOAD_FILE()
MASTER_POS_WAIT()
NOW()
RAND()
RELEASE_LOCK()
SYSDATE()
UNIX_TIMESTAMP() (パラメータなし)
USER()
ユーザ定義関数 (UDF) または格納された関数 (stored functions) を指す場合
ユーザ変数を指す場合（よくわからん、指すって？使う場合じゃなくて？）
mysql システムデータベースのテーブルを指す場合
次のようなSQL
SELECT ... IN SHARE MODE
SELECT ... FOR UPDATE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL
準備されたステートメントとして発行した場合。プレースホルダを採用していない場合も同様。例として、次のようなクエリはキャッシュにならない。(よくわからん)

char *my_sql_stmt = "SELECT a, b FROM table_c";
/* ... */
mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));

TEMPORARY テーブルを使用している場合
テーブルを全く使用しない場合
関連テーブルのすべてに対して、ユーザがカラムレベルの権限を持つ場合

メンテとか

クエリ結果がキャッシュから返る度に、サーバは Qcache_hits システム変数の値を増加します。Com_select ではありません

設定

変数の設定は別途参照。クエリキャッシュのシステム変数名はすべて、query_cache_ という文字で始まります。

have_query_cache

have_query_cache というサーバのシステム変数は、クエリキャッシュの利用可能にします。（通常時は YES）

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

query_cache_size

クエリキャッシュのサイズを設定するには、query_cache_size システム変数を使用します。値を 0 にすると、クエリキャッシュは無効化します。デフォルトは 0 で設定しています。
query_cache_size の値がゼロではない場合は、ストラクチャのアロケートにおよそ 40 KB を必要とするため、クエリキャッシュのサイズを最低 40 KB でセットしてください。正確なサイズは、システムのアーキテクチャによります。サイズが小さすぎると、警告がでます。

query_cache_size は、1024 バイトに近い値のブロックで位置合わせしています。そのため、報告の値は、設定したものとは異なる可能性があります。

query_cache_type

0 または OFF という値で、キャッシュを行わない、またはキャッシュした結果の読み出しを行わない、という効果になります。
1 または ON という値で、SELECT SQL_NO_CACHE で始まるステートメント以外のキャッシュになります。
2 または DEMAND という値で、SELECT SQL_CACHE で始まるステートメントだけのキャッシュになります。

GLOBAL query_cache_type と query_cache_limit

変更後に接続するクライアントに対するクエリキャッシュの動作を指定できます。SESSION query_cache_type の値を設定すると、それぞれのクライアントで接続時のキャッシュ動作を制御できます。

キャッシュしたそれぞれのクエリ結果の最大サイズの制御は、query_cache_limit 変数で行います。デフォルトは 1 MB です。

query_cache_min_res_unit と Qcache_free_blocks と Qcache_lowmem_prunes

クエリ実行毎に、ブロックはサイズでトリムすることになるので、メモリを節約できます。

query_cache_min_res_unit のデフォルト値は、4 KB です。大抵の場合、これで十分です。
結果が小さいクエリが多い場合は、フリーのブロックが多く存在することになり、デフォルトのブロックサイズはメモリのフラグメントになります。フラグメントは、メモリ不足を解消するために、キャッシュからクエリを取り除く (削除) 動作を強制的に行います。そのため、query_cache_min_res_unit の値を減らす必要が出てきます。フリーブロックの数は Qcache_free_blocks を、そして、この動作で強制的に削除の対象になったクエリは Qcache_lowmem_prunes を、それぞれのステータス変数で確認してください。
クエリの大部分が大きな結果である場合は、query_cache_min_res_unit で値を増やして、パフォーマンスを改善できます。(Qcache_total_blocks および Qcache_queries_in_cache で、ステータス変数を確認してください。) しかし、値を増やすと、前述のようにメモリ不足の状態になるので、注意が必要です。

保守

クエリキャッシュが有効になっているか確認する

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

RESET QUERY CACHE

RESET QUERY CACHEステートメントは、クエリキャッシュからクエリ結果を削除します。FLUSH TABLES ステートメントでも同様のことができます。

メモリ不足対策で、クエリキャッシュのフラグには、FLUSH QUERY CACHE ステートメントを使用します。このステートメントでは、キャッシュからクエリが消えることはありません

パフォーマンスを監視

SHOW STATUS を使用して、キャッシュのステータス変数をみます。

sql> 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    |
+-------------------------+--------+

SELECT クエリの合計数は、次の計算式で求めます。

  Com_select
+ Qcache_hits
+ queries with errors found by parser

Com_select 値は次の計算式で求めます。

  Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during the column-privileges check

クエリキャッシュでは、変数長さのブロックを使用するため、クエリキャッシュのメモリフラグメンテーションは、Qcache_total_blocks および Qcache_free_blocks で確認できます。FLUSH QUERY CACHE 後には、フリーのブロックが 1 つになります。

Qcache_lowmem_prunes システム変数

Qcache_lowmem_prunes システム変数の情報は、クエリのキャッシュサイズを調節するときに役立ちます。この変数は、新しいクエリのキャッシュを入れるために取り除かれたクエリの数をカウントしています。クエリキャッシュでは、古い順番にクエリをキャッシュから削除 (LRU) します。