Mysql benchmarking

Traps

Caches

See mysql caches.

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