MySQL Performance-schema (a) configuration table

Performance-schema first appeared in MySQL 5.5, and now 5.6,5.7 Performance-schema added more monitoring items, more statistical information, more and more oracle-awr statistical information Bright, is the DBA children's shoes for performance diagnostic analysis of the gospel. This article mainly discusses the configuration table in Performance-schema , through the configuration table can understand the overall picture of Performance-schema, for the subsequent use and deep understanding to prepare.

Configuration table

There are 5 main configuration tables in Performance-schema, specific as follows:

[Email protected]_schema 06:03:09>show tables like '%setup% ';
+----------------------------------------+
| Tables_in_performance_schema (%setup%) |
+----------------------------------------+
| setup_actors |
| Setup_consumers |
| setup_instruments |
| setup_objects |
| Setup_timers |
+----------------------------------------+

The 1.setup_actors is used to configure the monitoring of the user dimension, which by default monitors all users threads.
[Email Protected]_schema 05:47:27>select * from Setup_actors;
+------+------+------+
| HOST | USER | ROLE |
+------+------+------+
| % | % | % |
+------+------+------+

The 2.setup_consumers table is used to configure the consumer type of the event, that is, which statistics are eventually written to the collected events.
[Email Protected]_schema 05:48:16>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 |
+--------------------------------+---------+
you can see that there are 12 consumer, if you do not want to focus on some consumer, you can set enabled to no, such as Events_statements_history_long set to No,
The collection event is not written to the corresponding table Events_statements_history_long. 12 consumer are not lateral, there are multilevel hierarchical relationships. Specifically, the following table:
global_instrumentation
|–thread_instrumentation
|–events_waits_current
|–events_waits_history
|–events_waits_history_long
|–events_stages_current
|–events_stages_history
|–events_stages_history_long
|–events_statements_current
|–events_statements_history
|–events_statements_history_long
|–statements_digest

Multi-layered consumer follow a basic principle, and only the previous level is yes to continue checking that this layer is yes or NO. Global_instrumentation is the highest level of consumer, and if it is set to No, then all consumer will be ignored. If only global_instrumentation is turned on and all other sub-consumer are turned off (set to No), only the statistics of the global dimension, such as the Xxx_instance table, are collected, rather than the information of the user dimension, statement dimension. The second level is thread_instrumentation, user line Starway weijing statistics, such as Xxx_by_thread table, the other is statements_digest, this is used to Global statistics sql-digest information. The third level is the statement dimension, which includes Events_waits_current,events_stages_current and events_statements_current, respectively, for counting Wait, Stages and statement information, the fourth level is the historical table information, mainly including Xxx_history and Xxx_history_long.

the 3.setup_instruments table is used to configure a specific instrument, consisting of 4 major categories: Idle,stage/xxx,statement/xxx,wait/xxx.
[Email Protected]_schema 06:25:50>select name,count (*) from Setup_instruments GROUP by Left (name,5);
+---------------------------------+----------+
| name | count (*) |
+---------------------------------+----------+
| idle | 1 |
| Stage/sql/after Create | 111 |
| Statement/sql/select |
| wait/synch/mutex/sql/page::lock | 296 |
+---------------------------------+----------+
Idle represents the time the socket is idle, the stage class represents the statistics for each execution phase of the statement, the information for the statement class Statistic statement dimension, and the wait class counts various waitsevents, such as Io,mutux,spin_lock,condition. From the above table statistics, you can basically see the number of instrument per class, stage contains 111, statement contains 170, wait contains 296.

The 4.setup_objects table is used to configure monitoring objects, which are not monitored by default for all tables in Mysql,performance_schema and INFORMATION_SCHEMA. All tables in the other db are monitored.

[email protected]_schema 06:25:55>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 |
+-------------+--------------------+-------------+---------+-------+

because the wait class contains wait events, a single SQL call is more numerous, so select the unit of measure cycle with the least cost.

[Email Protected]_schema 06:29:50>select * from Setup_timers;
+-----------+-------------+
| NAME | Timer_name |
+-----------+-------------+
| Idle | microsecond |
| Wait | CYCLE |
| stage | nanosecond |
| Statement | nanosecond |
+-----------+-------------+

How to configure

by default, the Setup_instruments table only opens the instructions for the statement and Wait/io sections, and many consumer in the Setup_consumer table are not open. in order to open the required options, you can directly modify the configuration table through the UPDATE statement, and the modification can take effect immediately, but this method must be started before the server can be modified, and cannot be persisted, after restarting, you have to reset again. The configuration of MY.CNF is provided starting from 5.6.4, in the following format:

1. Set the instrument of the collection
Performance_schema_instrument= ' Instrument_name=value '
(1) Open the wait type directive
Performance_schema_instrument= ' wait/% '
(2) Open all instructions
Performance_schema_instrument= '%=on '

2. Set Consumer
Performance_schema_consumer_xxx=value
(1) Open events_waits_history Consumer

Performance_schema_consumer_events_waits_current=on

Performance_schema_consumer_events_waits_history=on

It is important to note the hierarchical relationship of consumer, Events_waits_history is on the 4th level, so when setting it, make sure Events_statements_current,thread_instrumentation and global The enabled state of the _instrumentation is yes to take effect. Because both the default thread_instrumentation and global_instrumentation are yes, you only need to display the settings events_waits_current and events_waits_current.

3. Set the statistical table size
All Performance_schema tables use the Performance_schema storage engine, and all the data in the table has only memory, and the size of the table is at System initialization
Fixed, so the memory occupied is certain. The number of records per table can be customized by configuration.
Performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

MySQL Performance-schema (a) configuration table