An Overview of the MySQL Performance Schema

Not long after writing my Understanding the MySQL Information Schema Database article I realized that the title is just a little misleading.   In MySQL, a schema and database are synonymous, so the phrasing of both “Schema” and “Database” is redundant.  Lesson learned! In today’s follow-up article, we’ll be exploring the information Schema’s counterpart, the Performance Schema. 

Origins and Uses

As the name suggests, the Performance Schema is a tool for monitoring MySQL Server execution at a low level.  It was initially implemented in MySQL 5.5 and further improved for MySQL 5.6.  One interesting feature about the Performance Schema is that it’s also the name of the storage engine. Having its own engine allows us to access information about server execution while having minimal impact on server performance.  Moreover, it uses views or temporary tables so as to minimize persistent disk storage.  Finally, memory allocation is all done at server startup, so there is no further memory reallocation or sizing, which greatly streamlines performance.

The Performance Schema is enabled by default as of MySQL 5.6.6. Before that version, it was disabled by default. To enable or disable it explicitly, you have to start the server with the performance_schema variable set to “on”. 

You can quickly check your MySQL version by calling the version() function:

mysql> select version();
 +---------------------+
 | version()           |
 +---------------------+
 | 5.6.20              |
 +---------------------+

Place the  performance_schema  switch in your my.cnf file if required:

[mysqld]
 performance_schema=on

To verify successful initialization, use this statement:

mysql> SHOW VARIABLES LIKE 'performance_schema';
 +--------------------+-------+
 | Variable_name      | Value |
 +--------------------+-------+
 | performance_schema | ON    |
 +--------------------+-------+

A value of ON is what you are looking for; that means that the Performance Schema is initialized and ready to go.

You can also use the SHOW ENGINES command and scan the result set for the PERFORMANCE_SCHEMA engine.  If you like, you can use the following query to remove all unsupported engines from the result list:

mysql>SELECT * FROM information_schema.ENGINES WHERE support = 'YES';
 +---------------------------------------------------------------------------------------------+
 | Engine            | Support | Comment                      | Transactions | XA | Savepoints |
 +---------------------------------------------------------------------------------------------+
 |MRG_MYISAM         | YES     | Collection of identical...   | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |MyISAM             | YES     | MyISAM storage engine        | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |BLACKHOLE          | YES     | /dev/null storage engine     | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |CSV                | YES     | CSV storage engine           | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |MEMORY             | YES     | Hash based,stored in memory  | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |ARCHIVE            | YES     | Archive storage engine       | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+
 |InnoDB             | DEFAULT | Supports transactions        | YES          | YES| YES        |
 +---------------------------------------------------------------------------------------------+
 |PERFORMANCE_SCHEMA | YES     | Performance Schema           | NO           | NO | NO         |
 +---------------------------------------------------------------------------------------------+

Performance Schema Tables

The Performance Schema, called “performance_schema”, contains 52 tables in total.   That’s a few too many to list here, but suffice to say that they are a mix of configuration tables and data tables. These can be further broken down into:

  • Configuration Tables
  • Object Tables
  • Current Tables
  • History Tables
  • Summary Tables
  • Other Tables

Configuration Tables

Configuration tables mainly store logistical information on monitored entities.  Hence, these tables can be utilized to set what is being reported on.  For performance schema purposes, the word “instrument” is often used in place of “monitor”.  Think of schema components as instruments that need to be tuned in order to perform at their best.  In that context, entities are instrumented, and the act of performance tuning may be referred to as instrumentation.

  • setup_instruments: stores which monitoring/instrumentation points are enabled
  • setup_consumers: stores which aggregation tables are maintained
  • setup_actors: defines which users will be monitored/instrumented
  • setup_objects: which objects need to be monitored/instrumented
  • threads: define which threads are monitored/instrumented

Instance Tables

Object instance tables record what instances of objects (files, locks, mutexes, etc.) are instrumented.  In case you’re wondering, mutexes are mutually exclusive locks that are designed to prevent concurrent access to resources.

  • cond_instances: identifies Conditions.  A condition is a synchronization mechanism used in the code to signal that a specific event has happened, so that a thread waiting for this condition can resume its operation.
  • file_instances: identifies files
  • mutex_instances: identifies mutexes
  • rwlock_instances: Identifies read/write locks
  • socket_instances: identifies sockets/active connections
  • threads: the threads table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it.

Event Tables

Events can be broken down into two parts: Current Events and Event Histories.  Current Events tables contain the most recently collected monitored event while Event Histories tables contain a  history of what has occurred over time.  

Current Events tables include:

  • events_stages_current
  • events_statements_current
  • events_waits_current

Event Histories tables consist of two tables for each event type, such as:

  • events_waits_history and events_waits_history_long

Summary Tables

Summary tables contain information for completed events.  Tables include:

  • events_stages_summary_by_thread_by_event_name: stage waits summarized per thread and event name
  • events_waits_summary_by_thread_by_event_name: stage waits summarized per event name only
  • file_summary_by_instance: file events summarized per file instance
  • table_io_waits_summary_by_index_usage: table I/O waits per index
  • table_io_waits_summary_by_table: table I/O waits per table
  • table_lock_waits_summary_by_table:  table lock waits per table

Other Tables

Other tables that don’t fall into the above categories include:

  • performance_timers: shows which event timers are available
  • host_cache: exposes contents of the host cache, which contains client host name and IP address information and is used to avoid DNS lookups
  • session_connect_attrs: contains connection attributes for all sessions
  • session_account_connect_attrs: contains connection attributes for sessions open for your own account

Querying Performance Schema Tables

Something would be terribly amiss if we didn’t spend some time on queries that can be run against the Performance Schema.

List Unused Indexes

Some indexes aren’t helping as much as you think.  Here’s how to find the lemons:

SELECT object_schema, object_name, index_name
 FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE index_name IS NOT NULL
 AND count_star = 0
 ORDER BY object_schema, object_name;

To limit the results to a specific schema, just include the following statement to the WHERE clause:

AND OBJECT_SCHEMA = 'test'

Here are some sample results:

 +----------------------+-------------+------------+
 | object_schema        | object_name | index_name |
 +----------------------+-------------+------------+
 | test                 | datetest    | PRIMARY    |
 +----------------------+-------------+------------+
 | test                 | employees   | PK         |
 +----------------------+-------------+------------+

Show the Full Process List

This is a great way to find a long-running query that is hogging resources.

SELECT PROCESSLIST_ID AS id, 
        PROCESSLIST_USER AS user, 
        PROCESSLIST_HOST AS host, 
        PROCESSLIST_DB AS db, 
        PROCESSLIST_COMMAND AS command, 
        PROCESSLIST_TIME AS time, 
        PROCESSLIST_STATE AS state, 
        LEFT(PROCESSLIST_INFO, 80) AS info
 FROM performance_schema.threads
 WHERE PROCESSLIST_ID IS NOT NULL
 AND PROCESSLIST_COMMAND NOT IN ('Sleep', 'Binlog Dump')
 ORDER BY PROCESSLIST_TIME ASC;
 
 +----+------+-----------+------+---------+------+---------------------+----------------------+
 | id | user | host      | db   | command | time | state               | info                 |
 +----+------+-----------+------+---------+------+---------------------+----------------------+
 | 2  | root | localhost | test | Query   | 5944 | Creating sort index | SELECT PROCESSLIST...|
 +----+------+-----------+------+---------+------+---------------------+----------------------+
 
 

Conclusion

The Performance Schema provides a way to troubleshoot a lot of common performance issues without having to rely on specialized third-party solutions.  The fact that it uses regular SQL is also a big plus!

See all articles by Rob Gravelle

Robert Gravelle
Robert Gravelle
Rob Gravelle resides in Ottawa, Canada, and has been an IT guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles