Query cacheのメモリ使用とチューニング

mysqlのquery cacheサイズ設定(query_cache_size)最小で40 kしか設定できません.bytes数は1024の倍数に設定するのが望ましいです.query_cache_typeが0の場合、mysqlもquery cacheにquery_cache_sizeサイズのメモリを割り当てます.これはあなたが望んでいない可能性があります.mysqlのquery cacheはメモリに完全に格納されています.mysqlが再起動すると、cacheの内容は完全に失われます.mysqlでは初期化を開始すると、連続するブロックが割り当てられます(query_cache_サイズ)メモリ.システムの実行中に、データをキャッシュする際に、このメモリから最小query_cache_min_res_unitサイズが割り当てられ、このパラメータの整数倍サイズのメモリブロックが割り当てられるたびに、キャッシュデータを格納するblockのほかに、hashテーブルが2つあり、hashテーブルにはhashが格納されたsql文が1つ、もう1つはhashが格納されているCacheが参照したこれらの文のデータベース#データベース#テーブルは、データベースにテーブルが変更された場合、このhashテーブルをチェックして、query cacheに関連するキャッシュを失効させる必要があるかどうかを確認する必要があります.query cacheに関連するシステム変数については、以下を参照してください.(関連書籍ドキュメントから直接コピーしたものは翻訳されていません):Query Cache System Variables:query_cache_limit:Result sets larger than this are not cached.The default is 1 Mb.query_alloc_block_size:Specifies the size of memory blocks allocated by thequery cache during query processing.The default is 8 Kb.query_cache_min_res_unit:Specifies theminimum size for blocks in the querycache. The default value is 4 Kb.query_cache_size:   The total memory allocated by mysqld for the querycache. The default is 0, which disables the query cache.query_cache_type:   Determines the mode of operation of the query   cache. Options are OFF,ON,  and DEMAND. The  default is ON.query_cache_wlock_invalidate: If set to TRUE, queries referencing MyISAM tables are invalidatedwhen a write lock for that table is obtained, even if none of the datais  changed   when       the write lock is released. The default is FALSE.query_cache_prealloc_size:   Specifies the size of the buffer used for query parsingby the cache. Defaults to 8 Kb.状態情報の変数もいくつかあります(Status Variable):Query Cache Status Variables:Qcache_free_blocks:      Number of memory blocks free in the query cacheQcache_free_memory  :  Total bytes of memory free in the query cacheQcache_hits:             Number of times a query matched the query cacheQcache_inserts:        Number of times a query and result set were inserted into the query cacheQcache_lowmem_prunes:   Number of times a query was removed due to query pruningQcache_not_cached:     Number of queries that could not be cachedQcache_queries_in_cache:    Number of queries currently stored in the query cacheQcache_total_blocks:Total number of memory blocks in cache一般query cache tuningは、これらのstatus variablesに対してシステムvariablesを調整することです.これらの変数と状態情報はSHOW GLOBAL VariablesまたはSHOW GLOBAL STATUSで表示できます.INFORMATION_SCHEMAでも見ることができます.次に、上記のステータス情報を使用して、いくつかの指標を見てみましょう(バージョン5.0.45のシステムの実際のデータを列挙します).1:キャッシュヒット率(query cache hit ratio).この指標の計算式は、キャッシュヒット回数をヒット回数とヒット回数(Com_selectは、cacheからキャッシュが見つかりませんでした)の合計Qcache_hits/(Qcache_hits+Com_select)223254326/(223254326+38402673)*100%=85.32%2:キャッシュの挿入率で割ったものです(insert ratio)Qcache_inserts/Com_select 37878146/38402673*100%=98.63%という割合が高いのは、ほとんどのselect文がquery cacheに入っていることを示しています.Qcache_lowmem_prunesという数値の大きさは70013で、状況は良いようですが、98.63%の文がquery cacheに入っている場合、pruning回数は少ないようですのです.3:空のキャッシュが占める割合.バイトとブロックで計算できます.Qcache_free_memory/query_cache_size * 100%Qcache_free_blocks/Qcache_total_blocks*100%空のキャッシュレートが高い場合はquery_を減らすべきですcache_size、またはcacheより多くのクエリー.この値が低い場合、Qcache_queries_in_Cacheも比較的低いので、以下のような状況になる可能性があります.1)query_cache_sizeは、より多くのクエリーをキャッシュするために増大する必要があります.2)query_cache_limitは、データ結果セットを打つ文を減らすために減らす必要があります.3)query cacheには多くの破片が現れており,クリーンアップが必要である.次に、より多くのクエリーをcacheする方法を見て、破片の問題について議論します.あなたのquery cacheのmemoryの利用率が足りないと思ったら、以下のいくつかの方法でcacheをより多く検索することができます.1)SELECT SQLを極力利用するNO_CACHE(query_cache_typeがONなら)2)SELECT SQL_CACHEを最大限利用(query_cache_type DEMANDの場合)3)Qcache_not_cachedはキャッシュできないクエリであり、この値が大きい場合は、より大きな結果セットを持つクエリをキャッシュに入れるためにquery_cache_limitの値を大きくします.4)Qcache_lowmem_prunesとQcache_free_memoryの値が高い場合は、破片が発生する可能性が高いので、query cachをクリーンアップする必要がありますe了.mysqlでは主に2つの側面が破片の出現を招いた:1)前に述べたように、query cacheは使用時にblockに従って割り当てられているので、結果セットのサイズが適切であるはずがない.query cacheの管理プログラムは、最初は結果セットのサイズを知らなかった.データがrow by rowであることを受け入れたため、すべての結果セットがキャッシュに格納されると、管理プログラムは最初から割り当てられたメモリを削減して結果セットのサイズと一致させる.2)次に、query cacheの失効(invalidation)が原因です.キャッシュが失効すると、キャッシュされたデータがquery cacheから削除され、query cacheの空洞(hole)が現れます.query cacheの断片化を避けるにはどうすればいいのでしょうか.1つの方法は、query cacheのblockサイズを調整し、すべての結果セットサイズの平均値に設定することです.(query_cache_size - Qcache_free_memory)/Qcache_queries_in_Cacheもちろんこれは一般的な方法ですが、時には使うかどうか、例えば結果セットがたくさんあっても小さいものもたくさんあります.もう1つの方法はquery cacheのprune回数とinvalidation回数を減らすことである.どのように減らすかは複雑な問題です.UPDATE、INSERT、DELETE文の頻度を減らすことができない場合は、大きなテーブルを小さなテーブルに分けたり、merge tableやpartitioningを使ったりすることができます.破片を避けるのが難しい場合はquery_cache_typeはDEMANDに設定され、SQL_でCACHEは、キャッシュしたいクエリーをキャッシュします.フラグメントが発生した後、FLUSH QUERY CACHE文でquery cacheを整理することができます.この文はquery cacheのデータを失うことはありません.メモリを整理して破片を取り除くだけです(defragment)ですが、query_cache_sizeが大きく設定されていると、この時間は長くなりますが、この時間内にほとんどのクエリー文や他のDML、DDL文がdefragmentの完了を待っているので、この操作をするときはbe careful!最後に、query cacheの内容をクリアするにはRESET QUERY CACが使えますHE .