Mysql benchmarking

Traps

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 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 ...

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.

Profiler

# enable profiling in current session
SET profiling = 1
SELECT SQL_NO_CACHE * FROM footable;
SHOW PROFILES;  # list profiled queries, by ID
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;  # show profile for a specific query-id