Performance schema, as name suggest is a tool for monitoring MySQL performance. It was implemented staring from MySQL 5.5 and improved further in MySQL 5.6. Performance Schema includes set of tables that gives information on how database is performing. It provides a way to inspect internal execution of the server at run time. Performance schema monitors every “event” the server does that takes time and has been instrumented so that timing information can be collected. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database.
Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. Having its own engine allows us to access information about server execution while having minimal impact on server performance. Tables created in performance_schema are persistent in memory. Information from those tables will be wiped out if the database is rebooted. Memory allocation for performance_schema table is all done at server startup, so there is no further memory reallocation or sizing, which greatly streamlines performance.
In this article we will see how different tables are organized and what kind of information each table store. We will also see how we can customize instrumentation in performance schema to gather required performance stats. Performance schema is a very big topic and there have been very exhaustive documentation on the same. I tried to make this article brief and cover major areas of performance schema. For further details readers can check out the document links provided in reference section.
Tables in Performance schema
We can categorize the tables in performance schema in 5 broad categories as described below.
1) Setup tables – These tables contains information about setting up monitoring/instrumentation. We can modify (update) these tables to customize what kind of performance data to be collected. These tables define who, what, how to monitor, and where to save this data.
2) Current Events tables – These tables records performance data about the current events happening in database. This also depends on what kind of instrumentation we have enabled in setup tables. These tables contains most recently collected monitored events.
3) History events table – Performance data collected in current events table move to these history table after a period of time. So structure wise history tables are same as current events table but they contains historical data
4) Summary tables – These tables contacts performance data summarized over an entity. So performance data of events are summarized by various entities like account_id, host_name or by thread. These tables has aggregate value of events grouped by different entities.
5) Object Instances tables – These tables records what instances of objects (files, locks, mutexes, etc) are currently in use. Example if a mutex is currently in use, you will see an entry corresponding t that mutex in mutex_instances table.
Lets try to dig into each category and see what tables we have and what they store.
1) Setup tables
Most of the tables in performance_schema database is read only tables. There are very few tables which we can modify to adjust instrumentation. Setup tables are among those few tables which can be modified to define what kind of performance data we want to collect.
SETUP_ACTORS – This table defines who to monitor. We can insert username@hostname combination in this table that we want to monitor.
By default it has only 1 entry and every user is monitored
mysql> select * from setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| % | % | % |
+------+------+------+
1 row in set (0.00 sec)
SETUP_OBJECTS – This table defines which objects (table) to monitor. By details mysql does not monitor any activity in performance_schema, information_schema and MySQL databases. Everything else is monitored. If you are interested in monitoring only 1 or 2 databases, you can remove the entry of monitoring all databases and insert into databases that you want to monitor.
Default entries looks file following
mysql> select * from setup_objects;
+-------------+--------------------+-------------+---------+-------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+-------------+--------------------+-------------+---------+-------+
| TABLE | mysql | % | NO | NO |
| TABLE | performance_schema | % | NO | NO |
| TABLE | information_schema | % | NO | NO |
| TABLE | % | % | YES | YES |
+-------------+--------------------+-------------+---------+-------+
4 rows in set (0.02 sec)
SETUP_INSTRUMENTS – This table defines the “what” part – what to monitor. This is a major table in performance_schema database. This table has list of all instrumentation that is available in mysql. This table lists classes of instrumented objects for which events can be collected. Each instrument added to the source code provides a row for this table, even when the instrumented code is not executed. You can enable or disable each instrumentation in this table.
This table looks like following
mysql> select * from setup_instruments;
+---------------------------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+---------------------------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool | NO | NO |
| wait/synch/mutex/sql/LOCK_des_key_file | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO |
...
...
...
| statement/com/Set option | YES | YES |
| statement/com/Fetch | YES | YES |
| statement/com/Daemon | YES | YES |
| statement/com/Binlog Dump GTID | YES | YES |
| statement/com/Error | YES | YES |
| statement/abstract/Query | YES | YES |
| statement/abstract/new_packet | YES | YES |
| statement/abstract/relay_log | YES | YES |
| wait/io/socket/sql/server_tcpip_socket | NO | NO |
| wait/io/socket/sql/server_unix_socket | NO | NO |
| wait/io/socket/sql/client_connection | NO | NO |
| idle | YES | YES |
+---------------------------------------------------------------------------------------+---------+-------+
Instrument names have multiple parts and form a hierarchy. An instrument name consists of a sequence of components separated by ‘/’ characters. Example names:
wait/io/file/myisam/log
wait/io/file/mysys/charset
wait/lock/table/sql/handler
wait/synch/cond/mysys/COND_alarm
wait/synch/cond/sql/BINLOG::update_cond
wait/synch/mutex/mysys/BITMAP_mutex
wait/synch/mutex/sql/LOCK_delete
wait/synch/rwlock/sql/Query_cache_query::lock
stage/sql/closing tables
stage/sql/Sorting result
statement/com/Execute
statement/com/Query
statement/sql/create_table
statement/sql/lock_tables
The instrument name space has a tree-like structure. The components of an instrument name from left to right provide a progression from more general to more specific. The number of components a name has depends on the type of instrument.
The interpretation of a given component in a name depends on the components to the left of it. For example, myisam appears in both of the following names, but myisam in the first name is related to file I/O, whereas in the second it is related to a synchronization instrument:
wait/io/file/myisam/log
wait/synch/cond/myisam/MI_SORT_INFO::cond
SETUP_CONSUMERS – This defines type of consumers for which event information is getting collected. In a way this defines is history information should be kept or not or just current event information should be collected and stored.
mysql> select * from setup_consumers;
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+--------------------------------+---------+
12 rows in set (0.00 sec)
If you enable history table for any of the event type, mysql will keep history of performance data collecetd for that event type.
Instrument data will be collected for only those consumers which are enabled in above table.
While we are talking above setup_consumers table, I would like to explain about 3 consumers mentioned in above tables
global_instrumentation – is the highest level consumer. If global_instrumentation is NO, it disables all instrumentation. All other settings are lower level and are not checked; it does not matter what they are set to. If global_instrumentation is YES, the Performance Schema maintains information for global states and also checks the thread_instrumentation consumer.
thread_instrumentation – is checked only if global_instrumentation is YES. Otherwise, if thread_instrumentation is NO, it disables thread-specific instrumentation and all lower-level settings are ignored. No information is maintained per thread and no individual events are collected in the current-events or event-history tables. If thread_instrumentation is YES, the Performance Schema maintains thread-specific information and also checks events_xxx_current consumers.
statement_digest – This consumer requires global_instrumentation to be YES or it is not checked. There is no dependency on the Statement Event consumers. This means you can obtain statistics per digest without having to collect statistics in events_statements_current, which is advantageous in terms of overhead.
Following figure show instrumentation hierarchy:
SETUP_TIMERS – This table defines that is the unit of values for all events that are collecting performance data. For some time of event it could be timing information, in that case it defines that unit of time that is used for those events. If for few events, unit should be number of instances occurring then this table should be updated to reflect the same.
By default it defines following unit of measure for each type of event
mysql> select * from setup_timers;
+-----------+-------------+
| NAME | TIMER_NAME |
+-----------+-------------+
| idle | MICROSECOND |
| wait | CYCLE |
| stage | NANOSECOND |
| statement | NANOSECOND |
+-----------+-------------+
4 rows in set (0.00 sec)
CYCLE refers to the timer that is based on the CPU (processor) cycle counter
2) Current Events tables
Once you update setup tables to set correct monitoring in place, MySQL will start collecting performance data for the events for which instrumentation is enabled.
Following are the current tables
mysql> show tables like '%current%';
+------------------------------------------+
| Tables_in_performance_schema (%current%) |
+------------------------------------------+
| events_stages_current |
| events_statements_current |
| events_waits_current |
+------------------------------------------+
3 rows in set (0.00 sec)
Each current table corresponds to high level instrument type. As we have seen above, instrument name is a tree type hierarchy structure and top of the tree is high level instrument type.
If we want to find out high level instrument name, we can use following SQL
mysql> select distinct substr(name,1,instr(name,'/')) from setup_instruments where name != 'idle';
+--------------------------------+
| substr(name,1,instr(name,'/')) |
+--------------------------------+
| wait/ |
| stage/ |
| statement/ |
+--------------------------------+
3 rows in set (0.08 sec)
Statement – indicates a sql statement (like SELECT) or command (like CONNECT)
Stages – is the stage of statement processing, the , like SORTING RESULT
Wait – is an instrumented wait event, like WAIT I/O/FILE or WAIT/LOCK
Idle – is when a connection is idle, that is, a socket is idle if it is waiting for a request from the client
So corresponding to each of above instrument type, we have current tables.
These tables are populated they are enabled in setup_consumers tables. If you check above the output of setup_consumers table, you will see entries corresponding to these tables.
mysql> select * from setup_consumers where name like '%current%';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_stages_current | NO |
| events_statements_current | YES |
| events_waits_current | NO |
+---------------------------+---------+
3 rows in set (0.01 sec)
So by default, instrumentation for current statements are enabled by default
EVENTS_STAGES_CURRENT
This table contains current stage events, one row per thread showing the current status of the thread’s most recent monitored stage event.
Every statement passes through various stages and we can enable/disable instrumentation for every stage. If you have enabled instrumentation for a stage and if your statement is currently in that stage, we will see a record in this table.
Again, if query taking less time, you will hardly see anything in this table for that thread. Advice is to enable history table.
mysql> select * from setup_instruments where name like 'stage%';
+---------------------------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+---------------------------------------------------------------------------------------+---------+-------+
| stage/sql/After create | NO | NO |
| stage/sql/allocating local table | NO | NO |
| stage/sql/preparing for alter table | NO | NO |
| stage/sql/altering table | NO | NO |
| stage/sql/committing alter table to storage engine | NO | NO |
| stage/sql/Changing master | NO | NO |
...
...
...
EVENTS_STATEMENTS_CURRENT
This table has data about current statement events, one row per thread showing the current status of the thread’s most recent monitored statement event.
Any user runs a statement, performance data about that statement gets recorded in this table.
Please note that this table stores only 1 row per thread, so as soon as user runs another SQL in same connection, previous SQL will be gone.
That’s why its useful to enable history tables in setup_consumers.
This table has many important columns to store performance data like
TIMER_START, TIMER_END, TIMER_WAIT – Which defines how much time the SQL took
LOCK_TIME – defines how much time lock was held
ROWS_AFFECTED, ROWS_SENT, ROWS_EXAMINED – defines how much work was done
CREATED_TMP_DISK_TABLES, CREATED_TMP_TABLES – defines if temp tables where created and if they got spiled over on disk
SELECT_FULL_JOIN, SELECT_FULL_RANGE_JOIN, SELECT_RANGE, SELECT_RANGE_CHECK, SELECT_SCAN – defines access path
mysql> select * from performance_schema.events_statements_current where thread_id = 31 \G
*************************** 1. row ***************************
THREAD_ID: 31
EVENT_ID: 89
END_EVENT_ID: 112
EVENT_NAME: statement/sql/select
SOURCE: mysqld.cc:939
TIMER_START: 351432249673000000
TIMER_END: 351432249845000000
TIMER_WAIT: 172000000
LOCK_TIME: 73000000
SQL_TEXT: select * from event
DIGEST: 4f58e5f352636c00d01edc576732852b
DIGEST_TEXT: SELECT * FROM EVENT
CURRENT_SCHEMA: deo
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 20
ROWS_EXAMINED: 20
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 1
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 1
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
1 row in set (0.00 sec)
EVENTS_WAITS_CURRENT
This table defines wait event information for current wait events, one row per thread showing the current status of the thread’s most recent monitored wait event.
If a thread has passed that wait event, it will remove that record from this table and insert another record about wait event that thread is experiencing right now.
Its bit difficult to use this view unless thread is experience waits currently and for longer time (enough to get hold of data from this table). Else its good idea to enable history tables.
mysql> select * from events_waits_current \G
*************************** 1. row ***************************
THREAD_ID: 30
EVENT_ID: 135
END_EVENT_ID: 135
EVENT_NAME: idle
SOURCE: mysqld.cc:909
TIMER_START: 351281970836000000
TIMER_END: 351286937566000000
TIMER_WAIT: 4966730000000
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
INDEX_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
OPERATION: idle
NUMBER_OF_BYTES: NULL
FLAGS: NULL
1 row in set (0.00 sec)
You might see the same timing columns in this table as in events_statements_current table. But note that these timings column belongs to wait event. They represent how much was the wait time, whereas timings column in events_statements_current table represent how much was the time taken by the entire statement.
3) History events table
These tables as name says, stores historical information of the event that happened. As we have learned above that current table stores only current event that is happening for every thread. If the thread has passed that event, data in current table changes. So history tables are available in MySQL which will store history of passed event happened in a thread.
Corresponding to every current table, we have 2 history table _history and _history_long
mysql> show tables like '%history%';
+------------------------------------------+
| Tables_in_performance_schema (%history%) |
+------------------------------------------+
| events_stages_history |
| events_stages_history_long |
| events_statements_history |
| events_statements_history_long |
| events_waits_history |
| events_waits_history_long |
+------------------------------------------+
6 rows in set (0.00 sec)
_history table contains the most recent 10 records for every thread and _history_long table contains most recent 10,000 records for every thread.
Example if a thread is executing 20 SQL statements, events_statements_history will records latest 10 SQL statements executed by that thread, where as events_statements_history_long table will record all 20 sql statements. As new events are added to the table, older events are discarded if the table is full. Events are not added to the table until they have ended.
Structure wise they are same as current table. They just store more data per thread.
You can enable history tables and history_long table from setup_consumers table
mysql> select * from setup_consumers where name like '%history%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_history | NO |
| events_statements_history_long | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
+--------------------------------+---------+
6 rows in set (0.00 sec)
If you think that storing 10 records in history table and 10000 records in history_long table is less, you can increase how many records you want to store in history and history long for each of the instrument type table.
Following system level variables are available which can be set at start of instance which defines how many records to store in these tables
mysql> show variables like 'performance_schema_%history%';
+--------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------+-------+
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_stages_history_size | 10 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_statements_history_size | 10 |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
+--------------------------------------------------------+-------+
6 rows in set (0.00 sec)
Note that changing these values will need mysql reboot to take affect and also increasing these values means that you will need more memory to store these values. If you increase number of records setting in system variables above, but there is no sufficient memory available to store those many records, MySQL will not be able to load history table.
You can check if this situation is happening by tracking some status variables in MySQL. The Performance Schema implements several status variables that provide information about instrumentation that could not be loaded or created due to memory constraints:
mysql> show status like 'performance%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
+-----------------------------------------------+-------+
23 rows in set (0.04 sec)
Each of the above status variables will get updated if corresponding performance schema table is not able to store records. We will see details about above variables in later section of this article.
4) Summary tables
Summary tables provide aggregated information for terminated events over time. The tables in this group summarize event data in different ways.
There are bunch of summary tables available in performance schema
mysql> show tables like '%summary%';
+----------------------------------------------------+
| Tables_in_performance_schema (%summary%) |
+----------------------------------------------------+
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
| events_stages_summary_by_thread_by_event_name |
| events_stages_summary_by_user_by_event_name |
| events_stages_summary_global_by_event_name |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |
| file_summary_by_event_name |
| file_summary_by_instance |
| objects_summary_global_by_type |
| socket_summary_by_event_name |
| socket_summary_by_instance |
| table_io_waits_summary_by_index_usage |
| table_io_waits_summary_by_table |
| table_lock_waits_summary_by_table |
+----------------------------------------------------+
25 rows in set (0.00 sec)
Each summary table has grouping columns that determine how to group the data to be aggregated, and summary columns that contain the aggregated values
I will give 1 liner for few tables to understand how data is stored in these tables.
Event Wait Summaries:
- events_waits_summary_global_by_event_name: Wait events summarized per event name
- events_waits_summary_by_instance: Wait events summarized per instance
- events_waits_summary_by_thread_by_event_name: Wait events summarized per thread and event name
Stage Summaries:
- events_stages_summary_by_thread_by_event_name: Stage waits summarized per thread and event name
- events_stages_summary_global_by_event_name: Stage waits summarized per event name
Statement Summaries:
- events_statements_summary_by_thread_by_event_name: Statement events summarized per thread and event name
- events_statements_summary_global_by_event_name: Statement events summarized per event name
4.1 Digest summary Table
In above summary table, you see a table – events_statements_summary_by_digest
So what exactly is digest table
As of MySQL 5.6.5, the Performance Schema maintains statement digest information. Digesting converts a SQL statement to normalized form and computes a hash value for the result.
Normalization permits statements that are similar to be grouped and summarized to expose information about the types of statements the server is executing and how often they occur.
We can enable digest summary by enabling the same in setup_consumers table
The statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) have DIGEST and DIGEST_TEXT columns that contain digest MD5 values and the corresponding normalized statement text strings.
Normalizing a statement transforms the statement text to a more standardized string representation that preserves the general statement structure while removing information not essential to the structure.Object identifiers such as database and table names are preserved. Values and comments are removed, and whitespace is adjusted.
Consider these statements:
SELECT * FROM orders WHERE customer_id=10 AND quantity>20
SELECT * FROM orders WHERE customer_id = 20 AND quantity > 100
To normalize these statements, the Performance Schema replaces data values by ? and adjusts whitespace. Both statements yield the same normalized form and thus are considered “the same”:
SELECT * FROM orders WHERE customer_id = ? AND quantity > ?
The normalized statement contains less information but is still representative of the original statement. Other similar statements that have different comparison values have the same normalized form.
5) Object Instances tables
Instance tables records synchronization object instances. There are three types of synchronization objects: cond, mutex, and rwlock.
cond – represents condition. 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 work
mutex – mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be “protected” by the mutex.
rwlock – synchronization mechanism used in the code to enforce that threads at a given time can have access to some common resource following certain rules. The resource is said to be “protected” by the rwlock. The access is either shared (many threads can have a read lock at the same time) or exclusive (only one thread can have a write lock at a given time).
Corresponding to this we have following 5 instances table
mysql> select TABLE_NAME from information_schema.tables where TABLE_NAME like '%instance%' and table_name not like '%summary%';
+------------------+
| TABLE_NAME |
+------------------+
| cond_instances |
| file_instances |
| mutex_instances |
| rwlock_instances |
| socket_instances |
+------------------+
5 rows in set (0.03 sec)
cond_instances – table lists all the conditions seen by the Performance Schema while the server executes
file_instances – table lists all the files seen by the Performance Schema when executing file I/O instrumentation. If a file on disk has never been opened, it will not be in file_instances. When a file is deleted from the disk, it is also removed from the file_instances table.
mutex_instances – table lists all the mutexes seen by the Performance Schema while the server executes.
rwlock_instances – table lists all the rwlock instances (read write locks) seen by the Performance Schema while the server executes.
socket_instances – table provides a real-time snapshot of the active connections to the MySQL server.
You can enable this instrumentation using setup_instruments table. Every instance tables mentioned above will get an entry if those events are enabled and executed.
setup_instruments table has many instruments as explained above. Enabling these instruments will populate specific instances table
+------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/PAGE::lock | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_sync | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_active | NO | NO |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_pool | NO | NO |
| wait/synch/mutex/sql/LOCK_des_key_file | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO |
...
| wait/synch/rwlock/sql/Binlog_transmit_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/Binlog_relay_IO_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/LOCK_grant | NO | NO |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger | NO | NO |
| wait/synch/rwlock/sql/LOCK_sys_init_connect | NO | NO |
| wait/synch/rwlock/sql/LOCK_sys_init_slave | NO | NO |
...
| wait/synch/cond/sql/TC_LOG_MMAP::COND_active | NO | NO |
| wait/synch/cond/sql/TC_LOG_MMAP::COND_pool | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::prep_xids_cond | NO | NO |
| wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_done | NO | NO |
...
| wait/io/file/sql/map | YES | YES |
| wait/io/file/sql/binlog | YES | YES |
| wait/io/file/sql/binlog_index | YES | YES |
| wait/io/file/sql/relaylog | YES | YES |
| wait/io/file/sql/relaylog_index | YES | YES |
| wait/io/file/sql/casetest | YES | YES |
...
| wait/io/socket/sql/server_tcpip_socket | NO | NO |
| wait/io/socket/sql/server_unix_socket | NO | NO |
| wait/io/socket/sql/client_connection | NO | NO |
+------------------------------------------------------------+---------+-------+
Lets take a example of mutex_instance table and understand how it works and how these tables play a role.
The mutex_instances table lists all the mutexes seen by the Performance Schema while the server executes. A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to some common resource. The resource is said to be “protected” by the mutex.
When two threads executing in the server (for example, two user sessions executing a query simultaneously) do need to access the same resource (a file, a buffer, or some piece of data), these two threads will compete against each other, so that the first query to obtain a lock on the mutex will cause the other query to wait until the first is done and unlocks the mutex.
For every mutex instrumented in the code, the Performance Schema provides the following information.
- The setup_instruments table lists the name of the instrumentation point, with the prefix wait/synch/mutex/.
- When some code creates a mutex, a row is added to the mutex_instances table. The OBJECT_INSTANCE_BEGIN column is a property that uniquely identifies the mutex.
- When a thread attempts to lock a mutex, the events_waits_current table shows a row for that thread, indicating that it is waiting on a mutex (in the EVENT_NAME column), and indicating which mutex is waited on (in the OBJECT_INSTANCE_BEGIN column).
- When a thread succeeds in locking a mutex:
- events_waits_current shows that the wait on the mutex is completed (in the TIMER_END and TIMER_WAIT columns)
- The completed wait event is added to the events_waits_history and events_waits_history_long tables
- mutex_instances shows that the mutex is now owned by the thread (in the THREAD_ID column).
- When a thread unlocks a mutex, mutex_instances shows that the mutex now has no owner (the THREAD_ID column is NULL).
- When a mutex object is destroyed, the corresponding row is removed from mutex_instances.
By performing queries on both of the following tables, a monitoring application or a DBA can detect bottlenecks or deadlocks between threads that involve mutexes:
- events_waits_current, to see what mutex a thread is waiting for
- mutex_instances, to see which other thread currently owns a mutex
6) Other remaining tables
Apart from above 5 categories there are few other tables which records account level and thread level information.
account table – Connection statistics per client account. “Account” here means username + hostname combination
mysql> select * from accounts;
+------+-----------+---------------------+-------------------+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+-----------+---------------------+-------------------+
| NULL | NULL | 20 | 26 |
| root | localhost | 1 | 7 |
+------+-----------+---------------------+-------------------+
2 rows in set (0.01 sec)
users table – Connection statistics per client user name
mysql> select * from users;
+------+---------------------+-------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| NULL | 20 | 26 |
| root | 1 | 7 |
+------+---------------------+-------------------+
2 rows in set (0.01 sec)
hosts table – Connection statistics per client host name
mysql> select * from hosts;
+-----------+---------------------+-------------------+
| HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+-----------+---------------------+-------------------+
| NULL | 20 | 26 |
| localhost | 1 | 7 |
+-----------+---------------------+-------------------+
2 rows in set (0.00 sec)
host_cache – Information from the internal host cache.
performance_timers – Shows which event timers are available
This table shows available times in your mysql server. If a timer is not listed in this table it cannot be used. value of timers for various event types set in setup_timers should be from performance_timers table
mysql> select * from performance_timers;
+-------------+-----------------+------------------+----------------+
| TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+-------------+-----------------+------------------+----------------+
| CYCLE | 2932537313 | 1 | 22 |
| NANOSECOND | 1000000000 | 1000 | 264 |
| MICROSECOND | 1000000 | 1 | 116 |
| MILLISECOND | 1037 | 1 | 135 |
| TICK | 101 | 1 | 254 |
+-------------+-----------------+------------------+----------------+
5 rows in set (0.00 sec)
threads – table contains a row for each server thread. Each row contains information about a thread and indicates whether monitoring is enabled for it
Performance Schema status variables
We have following performance_schema status variables
mysql> show status like '%performance%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
+-----------------------------------------------+-------+
23 rows in set (0.00 sec)
If an instrumentation could not be loaded because of memory constraints, corresponding status variable will get updated.
Non-zero values of these variables does not mean there is any issue with end user operation. Queries and DMLs will get executed as expected. Non-Zero values of these variables only mean corresponding instrumentation is not getting recorded because of less memory.
Names for these variables have several forms:
Performance_schema_accounts_lost – The number of times a row could not be added to the accounts table because it was full.
Performance_schema_xxx_classes_lost – How many instruments of type xxx could not be loaded.
Performance_schema_hosts_lost – The number of times a row could not be added to the hosts table because it was full.
Performance_schema_xxx_instances_lost – How many instances of object type xxx could not be created.
Performance_schema_xxx_handles_lost – How many instances of object type xxx could not be opened.
Performance_schema_locker_lost – How many events are “lost” or not recorded for various reason.
Performance_schema_session_connect_attrs_lost – The number of times a connection attribute string was larger than the reserved storage.
Performance_schema_users_lost – The number of times a row could not be added to the users table because it was full.
References:
http://manuales.guebs.com/mysql-5.6/performance-schema.html
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-quick-start.html
A Visual Guide to the MySQL Performance Schema
http://www.databasejournal.com/features/mysql/an-overview-of-the-mysql-performance-schema.html
Persistent Storage Solutions 