[opencms-dev] Better living with the MySql query cache - decreasing DB load and improving response times

[Christian Steinert]

Dear all,

Those of you who use MySql as DBMS might get noticable speedups by using 
the MySql query cache.
The query cache can cache speed up the response time for repetitive 
database queries a lot. Even if your templates do their best to avoid 
requesting the same content twice from opencms within the same request 
and even if you use the flex cache intensely, you may still get 
significant load reduction on your database.

The cache can be activated by adding a few settings to the [mysql] 
section of my.conf:

query_cache_size=32M
query_cache_limit=4000
query_cache_wlock_invalidate=1

The following settings have fared me well and seem to be close to a 
sweet spot for our installation. These settings are biased to favor 
small queries (in fact, query results above 4k are not even cached). 
With these settings I get cache hit rates >60% even for workloads where 
I would have expected cache hit rates to be fairly low. As a result, the 
speed of dynamically generating pages has almost doubled for us, even 
though we already had some performance-improving provisions in our 
template code.

Caveat: we don't use structured xml content, only normal html pages and 
a few custom file system properties. If you use structured XML content, 
then you might need to allow mysql to cache larger query results. This 
means that you possibly need to increase query_cache_limit to a higher 
value to allow caching of larger result sets. Also, you can try to tweak 
query_cache_size (increase from 32M to 128M did not offer improvements 
for our site, but your mileage may vary). Don't make the cache too big 
though, since it also increases MySql-internal overhead). Also you can 
try to play with query_cache_min_res_unit. Settings this to small values 
(512 is the minimum) will decrease fragmentation when caching small 
queries but decreases performance if most results are larger than this. 
The default for this setting is actually 4096 but reducing it to 512 
gave a small but noticable improvement for our installation. Again, your 
mileage may vary.

As always with performance tuning YOU need to *experiement* and 
*measure* the results. there are no magic numbers, so you need to tune 
this for your workload. For example, the cache is very simple-minded and 
will invalidate all results for a table as soon as any record of that 
table is changed. Also, the cache of course generates some overhead 
itself. So if you have very update-heavy workloads (which should be 
rather uncommon for opencms) then the query cache might hurt more than 
it helps.

Generally though I would expect almost anyone to see a noticable 
decrease in DB load (unless you use the accellerator module of Alkacon).

Best Regards
Christian