Mysql benchmarking: Difference between revisions
From wikinotes
(Created page with " = Traps = <blockquote> == The Query Cache == <blockquote> Benchmarking is difficult, since MySQL is often configured to use a query cache.<br> 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: <syntaxhighlight lang="yaml"> - Add a function to your selected rows - since they are not cacheable ex. 'SELECT ..., NOW() FROM ...'...") |
|||
Line 9: | Line 9: | ||
To avoid the query cache: | To avoid the query cache: | ||
<syntaxhighlight lang=" | <syntaxhighlight lang="mysql"> | ||
# 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 ... | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- The Query Cache --> | </blockquote><!-- The Query Cache --> |
Revision as of 20:30, 4 September 2022
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 currentstate
.
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