MySQLで検索キャッシュサイズを設定してみる
MySQLで検索キャッシュサイズを設定してみる
０．改訂履歴
- 2005.08.30 新規作成
- 2005.08.31 経過報告の追加
１．はじめに
このドキュメントでは，MySQLの検索結果を格納するquery_cache_sizeパラメータについて使用してみた感想を説明する．
query_cache_sizeは，MySQL4.0以降で利用できるようになったよう（※）で，これを利用する事でDBサーバの負荷を削減する事ができた． デフォルトで有効になっていないのには何か理由があるのかもしれませんが，このパラメータは動的に変更できるのでサーバを落とさずに設定する事が可能なので，気軽に？効果を見てみる事ができる． 今回用いたのはMySQL 4.1.12である．
※そこかのサイトにそう書いてあって，真相については詳しく調べてはいない
２．現在の設定を確認し，キャッシュを設定してみる
mysql> show status like "Qcache%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
mysql>
|
- キャッシュヒット率がゼロである．
- キャッシュの設定がどうなっているか確認する．
mysql> show variables like "query%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)
mysql>
|
- query_cache_typeがONになっているので，キャッシュを使うように設定されている．
- しかし，query_cache_sizeが0でメモリ上にキャッシュ格納領域が取られてないので，キャッシュヒットする訳が無い．
- キャッシュを設定する．
mysql> set global query_cache_size=32M;
ERROR 1232 (42000): Incorrect argument type to variable 'query_cache_size'
mysql>
|
- メガ単位だと設定できないようなので，バイト単位でパラメータを指定して実行する．
mysql> set global query_cache_size=33554432;
Query OK, 0 rows affected (0.01 sec)
mysql>
|
mysql> show variables like "query%";
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+----------+
7 rows in set (0.00 sec)
mysql>
|
３．効果測定
mysql> select sysdate();show status like "Qcache%";
+---------------------+
| sysdate() |
+---------------------+
| 2005-08-30 20:09:45 |
+---------------------+
1 row in set (0.00 sec)
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 33172976 |
| Qcache_hits | 312 |
| Qcache_inserts | 365 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 5 |
| Qcache_queries_in_cache | 329 |
| Qcache_total_blocks | 689 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql>
|
- 既に312ヒット，フリーメモリも380KB程度減って消費されている事が分かる．
- 20分近く経過したので再度確認してみる．
mysql> select sysdate();show status like "Qcache%";
+---------------------+
| sysdate() |
+---------------------+
| 2005-08-30 20:27:57 |
+---------------------+
1 row in set (0.00 sec)
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 48 |
| Qcache_free_memory | 32043144 |
| Qcache_hits | 4307 |
| Qcache_inserts | 2560 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 33 |
| Qcache_queries_in_cache | 1320 |
| Qcache_total_blocks | 2740 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql>
|
- クエリ数の合計は，Qcache_hits(4307) ＋Qcache_inserts(2560) ＋ Qcache_not_cached(33)なので，6900クエリとなる．
- Qcache_hits(4307)÷トータルクエリ(6900)≒0.62420
- キャッシュヒット率は約62%となる．
- その後の経過を見てみた．
- 約１５時間後．
mysql> select sysdate();show status like "Qcache%";
+---------------------+
| sysdate() |
+---------------------+
| 2005-08-31 11:32:30 |
+---------------------+
1 row in set (0.00 sec)
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 2827 |
| Qcache_free_memory | 24994944 |
| Qcache_hits | 235943 |
| Qcache_inserts | 90525 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 932 |
| Qcache_queries_in_cache | 7135 |
| Qcache_total_blocks | 17177 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql>
|
- Qcache_hits(235,943) ＋Qcache_inserts(90,525) ＋ Qcache_not_cached(932)なので，327,400クエリとなる．
- Qcache_hits(235,943)÷トータルクエリ(327,400)≒0.7206
- キャッシュヒット率は72%となる．
４．ポイント
- Qcache_lowmem_prunesは，メモリ不足のためにキャッシュから削除されたクエリ数のため，余裕があればquery_cacheを増やす事で改善するかもしれない．
５．恒久的に設定する
- 動的パラメータの場合，MySQLサーバを再起動すると内容が消えてしまうので，恒久的にするには設定ファイルに記述する．
- 設定ファイルは，/etc/my.cnfファイル．
- [mysqld]セクションにキャッシュサイズを設定しておく．
[mysqld]
query_cache_size=32M
|
