MySQLのquery cacheがうまく利用されていない気がします。

(MySQL 5.0.67-modified-logを利用しています)

疑問点は、query cacheに32MB割り当てましたが、ステータスを見てみるとQcache_free_memoryが17MBあるのに対して、Qcache_free_blocksが1しかありません。
私の認識では、以下の式が成り立つと考えていました。

Qcache_free_memory = Qcache_free_blocks * key cache block size

しかしながら実際の数値を代入すると以下となってしまい、認識が間違っていたようです。どこの認識が間違っているか教えていただきたいです。
17865784 = 1 * 1024


mysql> show status like 'QCa%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 17865784 |
| Qcache_hits | 27314861 |
| Qcache_inserts | 17590833 |
| Qcache_lowmem_prunes | 14252112 |
| Qcache_not_cached | 5444078 |
| Qcache_queries_in_cache | 3484 |
| Qcache_total_blocks | 7232 |
+-------------------------+----------+
8 rows in set (0.00 sec)

回答の条件
  • 1人1回まで
  • 登録:2009/10/27 00:04:25
  • 終了:2009/10/31 11:39:20

ベストアンサー

id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012009/10/27 05:05:26

ポイント69pt

キャッシュを有効に使ってくれるかどうかは、

どのような構造のデータベースにどのようなSQLを投げているかに大きく左右されます。


まずは

  Qcache_free_blocks = 1個 

  Qcache_free_memory = 17MB

断片化を起こすほど酷い状態ではなく、一見すると余裕があると見えなくもないが、

これらをもってして有効利用していないとまでは、まだ断言できない。


続いて、

  Qcache_hits = 2731万回

  Qcache_inserts = 1759万回

  Qcache_not_cached = 544万回

利用数 = 2731 + 1759 + 544 = 5034

キャッシュの利用率 = 2731 / 5034 = 54%

2回に1回しかキャッシュから読み込んでいないという事になり、

あまり有効利用はされてないように見受けられるが、この原因がSQLにあるのか、

データ構造にまで及ぶのかは実データやSQLを見てみないことには、まだ判らない。


さらに、

  Qcache_queries_in_cache = 3484個

  Qcache_total_blocks = 7232個

  Qcache_lowmem_prunes = 1425万回

空きメモリが17MBあったということから、メモリ量が十分とも誤解しやすいが、

それにしては、キャッシュに残っているクエリの数は決して多くない。

32-17=15MB使っていて3484個なので1個あたり4KBという小さなものなのに、

頻繁に消されるのは何故かを考えていかないといけないだろう。


対応としては、メモリ不足でキャッシュが頻繁に消されているという点から、

キャッシュ容量を増やすというのも一つの手。ただし、そもそもの原因が、

例えば、「時折、1回のSQLで得る結果が大きなものがある」だと仮定すると、

根元であるSQLのほうを何とかしないと、全体のパフォーマンスは上がらない。


結論的には、冒頭の繰り返しになるのだけど、

まずは「どのような構造のデータベースにどのようなSQLを投げているか」について、

見直さないといけないという話に戻ります。


なお、どのようなSQLを投げれば効率良くキャッシュされるかについては、

詳細な設計情報やソースそのものがないと絞り込めず、

あれやこれやの弁論大会になりそうなので、ひとまず置かせてください。

id:matsubobo

とても丁寧な回答ありがとうございます!

おっしゃること理解できました。

1点、Qcache_free_blocksの意味は以下の定義であっているのでしょうか?

> Qcache_free_memory=未だblockが割り当てられていないmemory

理由は、FLUSH QUERY CACHE するとQcache_free_memoryが1になり、Qcache_free_memoryが増加するので。

2009/10/27 09:05:37

その他の回答(1件)

id:goog20090901 No.1

goog20090901回答回数637ベストアンサー獲得回数172009/10/27 00:12:06

ポイント1pt

ブロックサイズは可変です。

この例だと1つのフリーブロックが17Mあるということです。

断片化とかしてたら、フリーブロックサイズが3とか4とかになるはずです。

id:matsubobo

質問中にある単語を使って解説お願いします。どの変数を指しているのかクリアにしたいので。

ご回答から察するに、以下の説明で正しいでしょうか?

------------------------------------------------------------------------------------------------------------------------------

free memoryから、キャッシュを保存するときにblockが割り当てられる。もし、free blockがあれば、それを優先的に使う。

言葉の定義は以下。

Qcache_free_memory=未だblockが割り当てられていないmemory

Qcache_free_blocks=blockが割り当てられているけど空のblock

2009/10/27 00:35:59
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012009/10/27 05:05:26ここでベストアンサー

ポイント69pt

キャッシュを有効に使ってくれるかどうかは、

どのような構造のデータベースにどのようなSQLを投げているかに大きく左右されます。


まずは

  Qcache_free_blocks = 1個 

  Qcache_free_memory = 17MB

断片化を起こすほど酷い状態ではなく、一見すると余裕があると見えなくもないが、

これらをもってして有効利用していないとまでは、まだ断言できない。


続いて、

  Qcache_hits = 2731万回

  Qcache_inserts = 1759万回

  Qcache_not_cached = 544万回

利用数 = 2731 + 1759 + 544 = 5034

キャッシュの利用率 = 2731 / 5034 = 54%

2回に1回しかキャッシュから読み込んでいないという事になり、

あまり有効利用はされてないように見受けられるが、この原因がSQLにあるのか、

データ構造にまで及ぶのかは実データやSQLを見てみないことには、まだ判らない。


さらに、

  Qcache_queries_in_cache = 3484個

  Qcache_total_blocks = 7232個

  Qcache_lowmem_prunes = 1425万回

空きメモリが17MBあったということから、メモリ量が十分とも誤解しやすいが、

それにしては、キャッシュに残っているクエリの数は決して多くない。

32-17=15MB使っていて3484個なので1個あたり4KBという小さなものなのに、

頻繁に消されるのは何故かを考えていかないといけないだろう。


対応としては、メモリ不足でキャッシュが頻繁に消されているという点から、

キャッシュ容量を増やすというのも一つの手。ただし、そもそもの原因が、

例えば、「時折、1回のSQLで得る結果が大きなものがある」だと仮定すると、

根元であるSQLのほうを何とかしないと、全体のパフォーマンスは上がらない。


結論的には、冒頭の繰り返しになるのだけど、

まずは「どのような構造のデータベースにどのようなSQLを投げているか」について、

見直さないといけないという話に戻ります。


なお、どのようなSQLを投げれば効率良くキャッシュされるかについては、

詳細な設計情報やソースそのものがないと絞り込めず、

あれやこれやの弁論大会になりそうなので、ひとまず置かせてください。

id:matsubobo

とても丁寧な回答ありがとうございます!

おっしゃること理解できました。

1点、Qcache_free_blocksの意味は以下の定義であっているのでしょうか?

> Qcache_free_memory=未だblockが割り当てられていないmemory

理由は、FLUSH QUERY CACHE するとQcache_free_memoryが1になり、Qcache_free_memoryが増加するので。

2009/10/27 09:05:37
  • id:goog20090901
    ブロックサイズは可変です。
    この例だと1つのフリーブロックが17Mあるということです。

    断片化とかしてたら、フリーブロックが3とか4とかになるはずです。
  • id:kn1967
    id:matsuboboさんへ

    回答1の返信欄に書いておられる項目について、
    ポイント付きメッセージでお送りしましたので、
    私の回答を開く前にご確認ください。

    Qcache_free_memoryだけで検索して適当に回答しているのでなければ、
    id:goog20090901 氏ご当人からフォローコメントがあるはずですから、
    それと照らし合わせていただいても結構です。
  • id:matsubobo
    このMySQLは、他社が作ったCMSやプログラム、自分で作ったアプリケーションが動いているため原因特定が難しいです。。。。
    自分で作成したアプリケーションのクエリは最適化しているので、それ以外に原因がありそうです。
  • id:kn1967
    >FLUSH QUERY CACHE すると
    >Qcache_free_memoryが増える


    ブロックの再利用タイミングなどの詳細までは判らないので、
    具体的な所は私には判りません。
    (ソースを追えば判るのでしょうが、ご勘弁ください。)


    >他社が作ったCMSやプログラム


    スロークエリログを取ってみると判ってくるかもしれません。
    http://dev.mysql.com/doc/refman/5.1/ja/log-files.html
  • id:matsubobo
    そうですね。slow queryを追っていくのが一番近道だと思います。
    しかるべくして出力されているクエリーとそうではないクエリーを選別するのが結構大変ですが、仕方ないですね。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません