Today I would like to discuss on cache hit ratio of on memory query cache. Query cache is not a free lunch: to create a cache entry, extra processing is required. Also, when a table related to the cache entry gets updated, all cache entries related to the table are removed. In summary, to enjoy the benefit of query cache, the cache hit ratio should be high enough. The cache hit ratio can be obtained by show pool_cache query.
test=# \x \x Expanded display is on. test=# show pool_cache;
show pool_cache; -[ RECORD 1 ]---------------+--------- num_cache_hits | 891703 num_selects | 99995 cache_hit_ratio | 0.90 num_hash_entries | 131072 used_hash_entries | 99992 num_cache_entries | 99992 used_cache_enrties_size | 12482600 free_cache_entries_size | 54626264 fragment_cache_entries_size | 0
As you can see the cache hit ratio is 90% here. Question is 90% is high enough? Do we need more number? Or less number is enough? So I decided to do some benchmarking to find the right number. I used pgbench -S to generate read query. I started the system from "cold" state, which means cache hit ration = 0, then run read query 1,000 times and see cache hit rate by using show pool_cache command. I repeated this cycle 100 times. Configuration parameters of query cache is all default. The system used is my latop, which has CORE i5 2.6 GHz dual core, 8GB of memory and SSD storage, running Linux kernel 2.6.35.
I also did same pgbench run against no query cache configuration. I got 5979.27TPS in average. My intention is, finding a TPS number exceeding the number, then look into the cache ration, which is the number I am looking for.
Shared memory case
In the first test case , shared memory is used for the cache storage. Here is the graph.
Memcached case
In the next test case cache storage is on memcached (memcached is running on the same laptop).
Conclusion
Shared memory query cache exceeds non cache case performance at the cache hit ratio 52% or higher, while memcached query cache exceeds at 62% or higher. Of course this number will significantly vary depending on the work load. In the benchmark I used pgbench -S, which is just an index lookup and returning 1 row thus one of the most light weight query in rthe eal world. If query is heavier, the cache hit ratio will become lower because the performance of cache hit case will be relatively higher.
In our manual we stat "If the cache_hit_ratio is lower than 70%, you might want to disable on memory cache". 70% is a little bit too conservative, but I think it's not too far from the number I got in the benchmark.
