The SQL query cache #

This page is for database administrators who want to know whether the SQL query cache helps their site, and what it means for writes they make themselves. The cache keeps the rows a SELECT returned and answers the same statement again until a write to one of its tables makes the result stale. It is off by default. It works with the MySQL, MariaDB, PostgreSQL, SQLite and Oracle drivers; the MongoDB driver does not use it. Source: The SQL query cache and settings/querycache.ini.

Contents #

1. The settings
2. What is never cached
3. How writes invalidate
4. Writes outside Exponential
5. Switching it on
6. What is normal, what to look at
7. Checklist

The settings #

settings/querycache.ini [QueryCacheSettings], overridden in settings/override/querycache.ini.append.php:

[DebugSettings] Report=enabled adds the cache's counters to the debug output.

What is never cached #

• reads inside a transaction;
• statements whose tables cannot all be read from the text: a subquery, hint or PARTITION in FROM, a parenthesised join, a comment, a quote left unpaired or inside a quoted name, Oracle's q'' and PostgreSQL's E'' and dollar-quoted strings, anything that is not a plain SELECT. Each SELECT of UNION, MINUS, INTERSECT and EXCEPT is read. Where a backslash escapes depends on the driver: in MySQL's literals it does, in PostgreSQL's (with standard_conforming_strings on, the default; the driver reads the setting from the connection), SQLite's and Oracle's it does not;
• statements whose result is not a function of their tables: NOW(), RAND(), UUID(), LAST_INSERT_ID(), locks (FOR UPDATE), variables, SQL_NO_CACHE; PostgreSQL's clock_timestamp(), txid_current(), pg_*() functions and FOR NO KEY UPDATE; SQLite's changes(), last_insert_rowid() and datetime('now');
• reads of temporary tables;
• results with more than MaxRows rows, and reads of ExcludeTables.

On Oracle also statements that read a sequence, the clock, SYS_GUID, SYS_CONTEXT, DBMS_RANDOM or the catalog.

How writes invalidate #

INSERT, REPLACE, UPDATE, DELETE, TRUNCATE, CREATE/ALTER/DROP TABLE, index changes and RENAME TABLE mark the tables they name as written. A write whose tables cannot be read marks everything stale. Inside a transaction the tables are marked at the outermost COMMIT; on ROLLBACK nothing is marked. Other processes (scripts, the admin host, Velocity) see the change through a shared state file in var/<site>/cache/querycache/. The file is written under a lock on state.lock, which must be writable by every user the site runs as (FPM, Velocity, cron); on NFS it needs NFSv4 or lockd. A write that cannot take the lock makes every result stale, and Setup > System information says so in red. PostgreSQL's TRUNCATE ... CASCADE and DROP ... CASCADE reach tables the statement does not name, so they make everything stale too. Deleting the cache directory is safe: results kept under the state file that was deleted are never answered again.

Writes outside Exponential #

The cache cannot see a write made with a SQL client, a mysql < dump.sql, a restore or another program. Results stay valid for up to MaxAge seconds. After every such write, and after every restore, clear it:

php bin/php/ezcache.php --clear-id=querycache --allow-root-user

or Setup > Caches. --clear-all and the tags content and ini include it.

Switching it on #

1. Check APCu: Setup > System information, PHP section, or php -m | grep apcu.
2. Set Mode=request in settings/override/querycache.ini.append.php; clear the INI cache, reload PHP-FPM, restart Velocity. Browse for a day.
3. Set Mode=shared the same way. Watch the hit rate in Setup > System information, box Database queries.

To switch it off, set Mode=off and do the same; nothing needs deleting. The buttons in Setup > Caches and Setup > System information (clear the cache, reset its counters) need the setup/managecache policy.

What is normal, what to look at #

• A hit rate of 30 to 40 percent right after a clear or a restart, rising as pages are visited.
• The SQL profiler counts only statements that reached the database: a warm shared-cache page shows 0 (Finding slow queries).

When in doubt, set Mode=off and compare.

Checklist #

• To do: Every runbook that writes to the database outside Exponential ends with a query cache clear.
• To do: cache_cleanup scheduled when Storage is file or auto without APCu.
• To do: Hit rate and APCu memory watched after switching to shared.

Related pages #

• Performance and scaling
• Caches
• Caching layers (for hosting companies)
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.