Mysql benchmarking

Traps

The Query Cache

NOTE:

Mysql-8+ drops support for the query cache.
It is disabled by default starting in MySQL-5.6
https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/

Benchmarking is difficult, since MySQL is often configured to use a query cache.
This is similar to a key-value store of cacheable-queries, and their result.
This interferes with benchmarking, since repeat queries may be much faster to lookup.

To avoid the query cache:

# 1. Add a calculated-function to your selected rows - since they are not cacheable
#    TODO: validate
SELECT ..., NOW() FROM ...

# 2. Use 'SQL_NO_CACHE' in your query
#    (This often has not worked for me)
SELECT SQL_NO_CACHE ... FROM ...

It is implemented as an LRU key-value store for exact (byte-for-byte) query matches.
Each cache entry knows which tables it references for permissions, and cache-invalidation.
Whenever any row is changed in a table, all cache entries using that table are purged.

The OS Cache

TODO:

research

Tools

Status

SHOW STATUS
SHOW SESSION STATUS # low-level info about temp-tables, reads, sorting, etc.

Query Processes

processes in SHOW FULL PROCESSLIST describe a query's current state.
profiling in mysql shows the time spent in each state of a query.

Benchmarker

The benchmark tool repeats a query N times, and gives you the (average?) time to execute.
Beware, this uses the query cache.

SELECT BENCHMARK(100, SELECT * FROM foo LIMIT 10);  # run query 100x times

Profiler

# enable profiling, and query
SET profiling = 1
SELECT SQL_NO_CACHE * FROM footable;

# inspect profile results
SHOW PROFILES;                        # list profiled queries, by ID
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;             # show profile for a specific query-id