Mysql benchmarking: Difference between revisions
From wikinotes
(→Traps) |
|||
(6 intermediate revisions by the same user not shown) | |||
Line 2: | Line 2: | ||
= Traps = | = Traps = | ||
<blockquote> | <blockquote> | ||
== | == Caches == | ||
<blockquote> | <blockquote> | ||
See [[mysql caches]]. | |||
</blockquote><!-- Caches --> | |||
</blockquote><!-- | |||
</blockquote><!-- Traps --> | </blockquote><!-- Traps --> | ||
Line 42: | Line 24: | ||
profiling in mysql shows the time spent in each state of a query. | profiling in mysql shows the time spent in each state of a query. | ||
</blockquote><!-- Query Processes --> | </blockquote><!-- Query Processes --> | ||
== Benchmarker == | |||
<blockquote> | |||
The benchmark tool repeats a query N times, and gives you the (average?) time to execute.<br> | |||
Beware, this uses the query cache. | |||
<syntaxhighlight lang="mysql"> | |||
SELECT BENCHMARK(100, SELECT * FROM foo LIMIT 10); # run query 100x times | |||
</syntaxhighlight> | |||
</blockquote><!-- Benchmarker --> | |||
== Profiler == | == Profiler == | ||
<blockquote> | <blockquote> | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
# enable profiling | # enable profiling, and query | ||
SET profiling = 1 | SET profiling = 1 | ||
SELECT SQL_NO_CACHE * FROM footable; | SELECT SQL_NO_CACHE * FROM footable; | ||
SHOW PROFILES; | |||
# inspect profile results | |||
SHOW PROFILES; # list profiled queries, by ID | |||
SHOW PROFILE; | SHOW PROFILE; | ||
SHOW PROFILE FOR QUERY 1; | SHOW PROFILE FOR QUERY 1; # show profile for a specific query-id | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Profiler --> | </blockquote><!-- Profiler --> | ||
</blockquote><!-- Tools --> | </blockquote><!-- Tools --> |
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 currentstate
.
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 timesProfiler
# 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