またまたまた次の投稿の続きです。今回はクエリキャシュに挑戦です♪
メモリに格納した SELECT の結果を取り出すようにしたら、一体どれくらい早くなるでしょうか、楽しみです♪
ポイント
# サーバシステム変数とその値の中からクエリキャシュの設定を表示 mysqladmin -u root -p variables | grep query_cache # サーバステータス変数とその値の中からクエリキャシュに関わる設定を表示 mysqladmin -u root -p extended-status | grep Qcache
現状調査！
クエリキャシュ関係の項目に何が設定されているか調べました。
まずはサーバシステム変数の設定値を見てみますと、クエリキャシュは有効になっています（have_query_cache が YES）し、普通の SQL （SELECT SQL_NO_CACHE で始まらないステートメント）が発行された時にキャッシュします（query_cache_type が ON）ようになっております。しかしながら、キャッシュするサイズが 0 です（query_cache_size）。
これでは、いけませんね。
[root@oki2a24 ~]$ # サーバシステム変数とその値の中からクエリキャシュの設定を表示 [root@oki2a24 ~]$ mysqladmin -u root -p variables | grep query_cache Enter password: | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | [root@oki2a24 ~]$
続いて、サーバステータス変数を見てみます。字面だけ見ますとサーバシステム変数となにが違うのか全くわかりません。何度か試してみたところ、先ほどのサーバシステム変数が設定値で、サーバステータス変数が現在の状況を表示、という感覚です。
さて、全くキャッシュしておりません状況ですから、もちろん、サーバシステム変数はオールゼロです。
[root@oki2a24 ~]$ # サーバステータス変数とその値の中からクエリキャシュに関わる設定を表示 [root@oki2a24 ~]$ mysqladmin -u root -p extended-status | grep Qcache Enter password: | 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 | [root@oki2a24 ~]$
調査の最後に、MySQL ではなくサーバー自体のメモリ状況を確認しておきます。もし全然余裕がなければ、そもそもクエリキャシュに設定できるメモリもありませんし。
結果は、800 M 程あまりがございました。余裕ですね。
[root@oki2a24 ~]$ free -mt
total used free shared buffers cached
Mem: 1024 204 819 0 0 0
-/+ buffers/cache: 204 819
Swap: 0 0 0
Total: 1024 204 819
[root@oki2a24 ~]$
設定編集♪反映♪
[mysqld] ディレクティブの最後に次を追加いたしました。
query_cache_size = 64M
設定した値を反映するために、MySQL を再起動しました。結果を見るのが楽しみですね♪
[root@oki2a24 ~]# /etc/init.d/mysqld restart mysqld を停止中: [ OK ] mysqld を起動中: [ OK ] [root@oki2a24 ~]#
クエリキャシュの威力を確認！
MySQL の再起動後、しばらく本ブログをテキトーに巡回し、クエリキャシュがされたであろうと見計らって確認しました。
まずは、サーバシステム変数です。クエリキャシュ（query_cache_size）が
67108864 と、なんとなく 64 MB っぽい値です。少なくとも 0 ではないですね、よしよし。
[root@oki2a24 ~]# mysqladmin -u root -p variables | grep query_cache Enter password: | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | [root@oki2a24 ~]#
続いてサーバステータス変数です。こちらは設定前とぜんぜん違います。量はぜんぜんキャッシュされていませんけれども、確実に動いていそうな感じです。
[root@oki2a24 ~]# mysqladmin -u root -p extended-status | grep Qcache Enter password: | Qcache_free_blocks | 30 | | Qcache_free_memory | 66106320 | | Qcache_hits | 1594 | | Qcache_inserts | 686 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 22 | | Qcache_queries_in_cache | 219 | | Qcache_total_blocks | 478 | [root@oki2a24 ~]#
次に CentOS のメモリを確認しました。なんとピッタリ 64 MB のメモリが追加で使用されています！サーバステータス変数ではキャッシュされていない容量が表示されていたことから、MySQL はクエリキャッシュ分のメモリを OS からぶんどっておいて、あとからゆっくり消費していくスタイルなのですね。贅沢です。
[root@oki2a24 ~]# free -mt
total used free shared buffers cached
Mem: 1024 268 755 0 0 0
-/+ buffers/cache: 268 755
Swap: 0 0 0
Total: 1024 268 755
[root@oki2a24 ~]#
さて、実際に本ブログにアクセスしてみましたが、ぜんぜん早くなったように感じられません。ですので Apache Bench で確認してみます。
まずはクエリキャシュの設定前でございます。これは「Requests per second: 0.85 [#/sec] (mean)」でした。すごく小さいですね。
続いて、クエリキャシュの設定後です。「Requests per second: 0.97 [#/sec] (mean)」とちょっとだけ向上しております。ただ、メモリを 64 MB も使用しましてほんのわずかの向上ですから、あまりよいとは思えません。。。＞＜
[root@localhost ~]# ab -n 100 -c 10 https://oki2a24.com/
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/
Benchmarking oki2a24.com (be patient).....done
Server Software: Apache
Server Hostname: oki2a24.com
Server Port: 80
Document Path: /
Document Length: 125970 bytes
Concurrency Level: 10
Time taken for tests: 103.572661 seconds
Complete requests: 100
Failed requests: 5
(Connect: 0, Length: 5, Exceptions: 0)
Write errors: 0
Total transferred: 12618698 bytes
HTML transferred: 12596998 bytes
Requests per second: 0.97 [#/sec] (mean)
Time per request: 10357.266 [ms] (mean)
Time per request: 1035.727 [ms] (mean, across all concurrent requests)
Transfer rate: 118.97 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 23 25 2.4 26 37
Processing: 2915 10065 10364.6 7701 71049
Waiting: 590 4804 4013.0 3636 21100
Total: 2939 10091 10364.5 7729 71076
Percentage of the requests served within a certain time (ms)
50% 7729
66% 8964
75% 9652
80% 10083
90% 11321
95% 35307
98% 66600
99% 71076
100% 71076 (longest request)
[root@localhost ~]#
これはあれでしょうか、ほかのチューニング項目と組み合わせることで威力を発揮するものなのでしょうか。
チューニング後の MySQLTuner 診断を確かめてみますと、、、
チューニング前は、次のように 4 つのパラメーターを調整するべし！とおっしゃっていましたのに今や、あ、あと 1 コでいいですから、、、と 3 つも解消しました。
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
実際の表示は次のようになりました。tmp_table_size と max_heap_table_size は設定しなくともよいのでしょうか？おそらくそんなはずはありません。もしかしたら、これら 2 つを設定してから、今回調節した query_cache_size を設定するべきだったのかもしれません。
[root@oki2a24 ~]# /tmp/MySQLTuner-perl-master/mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 10M (Tables: 12)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[OK] Total fragmented tables: 0
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 23h 6m 15s (591K q [3.490 qps], 8K conn, TX: 2B, RX: 84M)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 88.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 493.8M (48% of installed RAM)
[OK] Slow queries: 0% (0/591K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/4.3M
[OK] Key buffer hit rate: 99.1% (723K cached / 6K reads)
[OK] Query cache efficiency: 88.3% (475K cached / 538K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
[OK] Temporary tables created on disk: 12% (2K on disk / 24K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 64% (51 open / 79 opened)
[OK] Open file limit used: 6% (68/1K)
[OK] Table locks acquired immediately: 99% (65K immediate / 65K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Variables to adjust:
thread_cache_size (start at 4)
[root@oki2a24 ~]#
今回はこれでよしとします。残りのパラメーター、thread_cache_size をまず調節し、MySQLTuner の診断で問題なし、を目指すことを優先したいと思います。
おわりに
次の本家ページが参考になりました。ありがとうございます！
また、クエリキャッシュとはなんぞや？にも次の本家ページが答えていただけます。ありがとうございます！
以上です。