Mysql benchmarking: Difference between revisions

From wikinotes
 
(2 intermediate revisions by the same user not shown)
Line 2: Line 2:
= Traps =
= Traps =
<blockquote>
<blockquote>
== The Query Cache ==
== Caches ==
<blockquote>
<blockquote>
Benchmarking is difficult, since MySQL is often configured to use a query cache.<br>
See [[mysql caches]].
This is similar to a key-value store of cacheable-queries, and their result.<br>
This interferes with benchmarking, since repeat queries may be much faster to lookup.


To avoid the query cache:
</blockquote><!-- Caches -->
<syntaxhighlight lang="mysql">
# 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 ...
</syntaxhighlight>
</blockquote><!-- The Query Cache -->
 
== The OS Cache ==
<blockquote>
{{ TODO |
research }}
</blockquote><!-- The OS Cache -->
</blockquote><!-- Traps -->
</blockquote><!-- Traps -->


Latest revision as of 03:11, 7 September 2022

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
Retrieved from "http://willpittman.net:8080/index.php?title=Mysql_benchmarking&oldid=31772"