You upgraded to > 5.1 but did you turn on performance_schema?

July 26, 2013

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_schemaRoland 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 |
+-------------------------------------------------------------------+------------+--------+-----------+----------+-------------+----------------------------------+

 

MarkGrennanSigniture

 

 

 

 

 

 

posted in Commentary, Tips & Tricks by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

You must be logged in to post a comment.

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.