Wednesday, November 4, 2015
High Performance MySQL: CH3 Profile Server Performance
1- the author has mentioned Percona Toolkit’s pt-query-digest tools a lot and he recommends to use it for profiling and analyzing as it gives a lot of good profiling information. other mentioned tool is strace.
2- the author advised that profiling should be enabled all the time, it doesnt have to be enable for all requests you can write something like
$profiling_enabled = rand(0, 100) > 99;
which means record profiling information for 1% of the requests.
3- the author also mentioned New Relic as a tool for profiling.
Profiling MySQL Queries
1- MySQL comes with a tool for query profiling, It’s the so-called slow query log, the overhead of it is negligible .
2- the author also talked about Percona Server which logs significantly more details to the slow query log than MySQL
3- also sometimes you dont have access to the server that you need to profile, Percona Server is gonna help you in this situation, they have couple of scripts to profile the server even if you dont have access to it (this is done by running SHOW FULL PROCESSLIST repeatedly and get information or capturing TCP network traffic)
4- the author gave an example about using pt-query-digest
Profiling a Single Query
1-MySQL provides us with 4 ways to profile a query, SHOW PROFILE, SHOW STATUS, EXPLAIN, Performance Schema
2- in SHOW PROFILE: Every time you issue a query to the server, it records the profiling information in a temporary table and assigns the statement an integer identifier.
you can run a statement like
mysql> SHOW PROFILE FOR QUERY 1;
to get exact execution plan for the query.
3- in SHOW STATUS: They tell you how often various activities took place (e.g. temp table has been created for 3 times in this query)
4- EXPLAIN: it shows an estimate of what the server thinks it will do, it is not useful ass SHOW STATUS and SHOW PROFILE
4- PERFORMANCE SCHEMA: MySQL 5.5 comes with a PERFORMANCE schema which gives you some performance information. example"
mysql> SELECT event_name, count_star, sum_timer_wait
-> FROM events_waits_summary_global_by_event_name
-> ORDER BY sum_timer_wait DESC LIMIT 5;
Profiling Server Wide Problem:
in the previous section we were talking about profiling a single query, however sometimes the issue is global and you need to profile the whole server.
to do this you can use:
1- SHOW GLOBAL STATUS, it will give you some information like number of running threads, number of connected threads
2- SHOW PROCESSLIST: also gives you information about the threads.
Capturing Diagnostic Data
he is talking about how you can capture the diagnostic data, the idea is to wait for a trigger (like a peek in the number of running threads) then when the trigger happens start gathering data.
he mentioned alot of tools that can be used to help you in doing that, all the tools are part of Percona toolkit.
there is a case study as well.