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.
Now you’re ready to learn things like:
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
| 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 |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+
5.1 but did you turn on performance_schema? - MySQL Fanboy" class="twitter-share-button">Tweet
