Re: Having query cache in core

On 07.05.2018 08:23, Laurenz Albe wrote:
> Having been bitten by the feature on MySQL, I think it's not a good thing.
>
> Essentially, it's a band-aid for badly written applications, but it will
> only help in certain cases and hurts in many others.

The MySQL query cache helped quite a bit in the early web days, before
session handling became a thing (e.g. before PHP 4.0), before massive
caching on other layers, and when most machines were still having a
single CPU core only.

With multiple cores the cost of query cache maintenance, and especially
result purging, quickly outweighed the gain though. I'd assume that this
would be even more of a burden in a multi-process model as it is in
MySQLs one-process-multiple-threads model already.

And so for about a decade now there's a simple rule:

Q: What is the ptimal size for my query cache?
A: zero

(There's even a tuning wizzard web site for this, created by a former
team member of mine: <https://dom.as/tech/query-cache-tuner/> )

All of the above is probably true for all query result caches that
try to invalidate cache entries as soon as the underlying tables
change.

Caches with a simple "time-to-live" logic will not suffer from cache
maintenance contention, but at the same time they don't really need
any information available on the server side only. So having such
kind of cache in a separate proxy process, like pqc, or on the
MySQL/MariaDB side e.g. in the cache modules for MaxScale and
ProxySQL, looks like the better approach for me.

PS:

I tried PQC for one of my PostGIS based OpenStreetMap projects,
there i suffer from "badly written application", or actually
from too many layers of abstraction. For rendering maps in
different file formats (SVG, PDF, PNG) I need to run the full
Mapnik rendering queue multiple times, even though the map
bounding box, and so the underlying queries from the Mapnik
style sheet, actually stay the same.

Even with that setup, a set of moderately complex queries
on a rather large database being run three times in a row,
adding PQC didn't provide that much of an improvement though,
and in the end I didn't bother to have to take care of yet
another service component in the setup.

--
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
MariaDB Corporation | http://www.mariadb.com/