If you’ve upgraded from MySQL 5.1 to a newer version (5.5? 5.6? 5.7?) Mark Leith shows you why you should turn on performance_schema. Roland Bouman gave us Common_Schema to help improve the SQL user experience. Mark now lets us look inward at the IO latency to improve the system.
Mark has developed a great set of views and procedures to work with performance_schema that can give you things like:
A list all statements who’s average runtime, in microseconds, are the top 5% slowest
statements that use have done a full table scans
Find indexes that have had no events against them (and hence, no usage)
Show the top disk IO consumers by bytes usage by file
You’ll need to start mysql with the command line –performance_schema option or add performance_schema=on to your my.cnf and restart the server. Then download the lattest 5.5 or 5.6 code from Mark’s page and import it.
mysql < ps_helper_56.sql
Now you’re ready to learn things like:
mysql> SELECT * FROM statements_with_errors_or_warnings;
+——————————————————————-+————+——–+———–+———-+————-+———————————-+
| query | exec_count | errors | error_pct | warnings | warning_pct | digest |
+——————————————————————-+————+——–+———–+———-+————-+———————————-+
| CREATE PROCEDURE currently_ena … w_instruments BOOLEAN DEFAULT | 2 | 2 | 100.0000 | 0 | 0.0000 | ad6024cfc2db562ae268b25e65ef27c0 |
| CREATE PROCEDURE currently_ena … ents WHERE enabled = ? ; END | 2 | 1 | 50.0000 | 0 | 0.0000 | 4aac3ab9521a432ff03313a69cfcc58f |
| CREATE PROCEDURE currently_enabled ( BOOLEAN show_instruments | 1 | 1 | 100.0000 | 0 | 0.0000 | c6df6711da3d1a26bc136dc8b354f6eb |
| CREATE PROCEDURE disable_backg … d = ? WHERE TYPE = ? ; END IF | 1 | 1 | 100.0000 | 0 | 0.0000 | 12e0392402780424c736c9555bcc9703 |
| DROP PROCEDURE IF EXISTS currently_enabled | 12 | 0 | 0.0000 | 6 | 50.0000 | 44cc7e655d08f430e0dd8f3110ed816c |
| DROP PROCEDURE IF EXISTS disable_background_threads | 3 | 0 | 0.0000 | 2 | 66.6667 | 0153b7158dae80672bda6181c73f172c |
| CREATE SCHEMA IF NOT EXISTS ps_helper | 2 | 0 | 0.0000 | 1 | 50.0000 | a12cabd32d1507c758c71478075f5290 |
+——————————————————————-+————+——–+———–+———-+————-+———————————-+
Tweet
via Planet MySQL You upgraded to > 5.1 but did you turn on performance_schema?