Qcache_free_blocksの肥大化を防ぐためcronで定期的にFLUSH QUERY CACHEを実行していたのですが、定期的にmysqlcheckやmysqldumpを実行しているこのサーバ設定では実行する意味がないことに気付きました。
この設定変更による実害はありませんでしがmysqlを理解していればもっと早く気付いたはず。戒めとしてまとめてみます。
Qcache_free_blocksとFLUSH QUERY CACHE
まずクエリキャッシュの状況を確認するために、mysqlでSHOW STATUS LIKE ‘Qcache%’を実行します。
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 173 | | Qcache_free_memory | 23911320 | | Qcache_hits | 88534 | | Qcache_inserts | 72307 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12306 | | Qcache_queries_in_cache | 417 | | Qcache_total_blocks | 1037 | +-------------------------+----------+ 8 rows in set (0.00 sec)
Qcache_free_blocksはMySQLのクエリキャッシュのフリーなブロック数で数が大きな場合はキャッシュの断片化を意味しています。クエリキャッシュのメモリ不足など悪影響が出ている場合は、キャッシュから削除されたクエリの件数（Qcache_lowmem_prunes）がカウントされます。
Qcache_lowmem_prunes=0であれば、query_cache_sizeの設定値やQcache_free_blocksとQcache_free_memoryの値は問題ないとも言えます。
クエリキャッシュの断片化はFLUSH QUERY CACHEでクリアできます。
mysql> FLUSH QUERY CACHE; Query OK, 0 rows affected (0.02 sec) mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 23914528 | | Qcache_hits | 88536 | | Qcache_inserts | 72313 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12307 | | Qcache_queries_in_cache | 417 | | Qcache_total_blocks | 865 | +-------------------------+----------+ 8 rows in set (0.00 sec)
このコマンドは気が向いた時に手動でも良いらしいのですが、定期的にメンテした方が効率的ではないか？と考え、以前作成したDBのテーブル最適化スクリプトmysqlopti.shに追加することにしました。
mysqlopti2.sh#!/bin/bash
## 変数に値を設定
HOSTNAME=********
SQLUSER=YYYYYYY
SQLPASSWD=ZZZZZZ
MYSQLDB=DBname
#mysql オーバーヘッド最適化
/usr/bin/mysqlcheck -o -u${SQLUSER} -p${SQLPASSWD} ${MYSQLDB}
#mysql FLUSH QUERY CACHE
mysql -u${SQLUSER} -p${SQLPASSWD} -e "FLUSH QUERY CACHE;"
exit
実はFLUSH QUERY CACHEは不要だった件について
mysqlopti2.shに改良を加えてから、帰宅後に毎日Qcache_free_blocksの平時の値を確認してみたのですが、値はどれも200前後の間が記録されていて改良前と変わりありませんでした。
ノーメンテであればこの値は右肩上がりで増えるはず。それが変わらないということは既に毎日FLUSH QUERY CACHEかそれに代わるコマンドが実行されていることになります。毎日実行しているmysql系のコマンドといえばDBのテーブル最適化mysqlcheckコマンドとDBバックアップ用のmysqldumpコマンドの二つです。
結論 mysqlcheck/mysqldumpはクエリキャッシュがクリアされる
結論からいうと、mysqlcheckとmysqldumpどちらもQcache_free_blocks=1になりました。
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 25148288 | | Qcache_hits | 88561 | | Qcache_inserts | 72370 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 12315 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ 8 rows in set (0.00 sec)
ただし正確にはFLUSH QUERY CACHEではなくQcache_total_blocks=1となっているので、クエリキャッシュをクリアするRSET QUERY CACHEと同等の処理が行われたようです。
mysqlcheck/mysqldumpどちらのコマンドもクエリを一旦完了させ静止した状態を確保してから処理するはず。ということはクエリキャッシュもクリアしないと処理の邪魔になるんだろうな、ということはなんとなく推測できます。
結果 元に戻しました
ということでmysqlopti2.shは一週間で廃棄となり、元のmysqlopti.shに戻しました。
この記事は元はmysqlopti2.shの効果を書く予定でスタートしたので実質意味のないものになってしまいました。「とりあえずやってみる」のは良いこともありますが、時間の無駄遣いにならないよう注意が必要ですね。
