[opencms-dev] Better living with the MySql query cache - decreasing DB load and improving response times
christian_steinert at web.de
Thu Mar 26 11:40:10 CET 2009
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:
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
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
Generally though I would expect almost anyone to see a noticable
decrease in DB load (unless you use the accellerator module of Alkacon).
More information about the opencms-dev