What is the MySQL Performance Schema and Why is It Needed?

What is the MySQL Performance Schema and Why is It Needed?:
When you have a non-trivial database installation, you will inevitably sooner or later encounter performance related issues ranging from a query not executing as fast as desirable to complete meltdowns where the database does not respond at all.
Until MySQL 5.5 the tools available to investigate what is going on inside MySQL were somewhat limited. Some of the tools were:

• The slow and general query logs


• The status counters available through SHOW [SESSION|GLOBAL] STATUS


• Storage engine status, e.g. SHOW ENGINE INNODB STATUS


• The EXPLAIN command to investigate the query plan of a SELECT statement


• SHOW PROFILE to profile one or more queries


• The MySQL error log

All of these tools are very useful, but also have their limitations, for example the SHOW STATUS mainly consists of counters that does not provide much insight into what is happening specifically.
In MySQL 5.5 a new tool was introduced, the Performance Schema (often abbreviated P_S). The Performance Schema consist of instrumentation points directly in the source code which allow inspection of the internals of the server at runtime. Some of the advantages for the Performance Schema implementation are:

• The Performance Schema data is available through the PERFORMANCE SCHEMA storage engine in the performance_schema database, so it is possible to query the data using standard SQL statements.


• The Performance Schema is available irrespectively of the platform, so while the exact data collected will differ between platforms, the way it works from a DBAs perspective it is the same. This for example means it is possible to create tools that can work across all the MySQL instances. A great example of this is the ps_helper collection of views and stored procedures written by Mark Leith.


• It is possible to configure the Performance Schema dynamically as long as the plugin has been enabled (this is the defaults as of MySQL 5.6.6).


• It is easy to add new instrumentation points including adding instrumentation to third party plugins.


• Enabling the Performance Schema is transparent to normal operations (although obviously there will be a small performance impact – MySQL 5.6 is much better in this respect than MySQL 5.5 though).

For the full list of implementation details, see the documentation of the Performance Schema in the MySQL Reference Manual.
In MySQL 5.5 the amount of information in the Performance Schema is relatively limited. If you take a look into the performance_schema database, it becomes immediately obvious that much has happened between MySQL 5.5 and 5.6.6: the former has 17 views, the latter 52 views. The information available in MySQL 5.5 is primarily I/O oriented whereas MySQL 5.6 also – among other – includes:

• Statement information (similar to the slow query log)


• Information about index usage, e.g. to identify unused indexes


• The possibility to create a stack trace for a given thread


• etc.

The Performance Schema is the subject of both a talk and a hands-on lab at the upcoming MySQL Connect 2012 which takes place on September 29th and 30th in San Francisco. These two sessions will go into greater depth about the above features. You can also read more about the sessions at:

• Mark Leith: Profiling with the Performance Schema (CON9309)


• Jesper Krogh: Improving Performance with the MySQL Performance Schema (HOL10472)

PlanetMySQL Voting:
Vote UP /
Vote DOWN

DIGITAL JUICE