MySQL Performance-Schema (1) configuration table, performanceschema

MySQL Performance-Schema (1) configuration table, performanceschema

Performance-schema was first introduced in MYSQL 5.5, but now more metrics are added to performance-Schema in MYSQL 5.6 and 5.7, and more statistics are available, more and more ORACLE-AWR statistics to catch up, really DBA kids shoes Performance Diagnosis Analysis of the gospel. This article focuses on the configuration table in Performance-Schema. You can use the configuration table to get a general picture of performance-schema and prepare for future use and in-depth understanding.

Configuration table

There are five configuration tables in Performance-Schema:

Root @ performance_schema 06:03:09> show tables like '% setup % ';
+ ---------------------------------------- +
| Tables_in_performance_schema (% setup %) |
+ ---------------------------------------- +
| Setup_actors |
| Setup_consumers |
| Setup_instruments |
| Setup_objects |
| Setup_timers |
+ ---------------------------------------- +

1. setup_actors is used to configure user-dimension monitoring. By default, all user threads are monitored.
Root @ performance_schema 05:47:27> select * from setup_actors;
+ ------ +
| HOST | USER | ROLE |
+ ------ +
| % |
+ ------ +

2. The setup_consumers table is used to configure the consumer type of the event, that is, the collected events are finally written into which statistical tables.
Root @ cece_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 |
+ -------------------------------- + --------- +
We can see that there are 12 consumers. If you don't want to pay attention to some consumers, you can set ENABLED to NO, for example, events_statements_history_long to NO,
The collection events are not written to the events_statements_history_long table. The 12 consumers are not hierarchical and have multi-level hierarchies. The specific table is as follows:
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-level consumer follows a basic principle. Only YES at the previous level will check whether this layer is YES or NO. Global_instrumentation is the highest level of consumer. If it is set to NO, all consumers will be ignored. If you only enable global_instrumentation, and disable all other sub-consumer (set to NO), only collect statistics of global dimensions, such as xxx_instance table, instead of collecting user dimensions, statement dimension information. The second level is thread_instrumentation. Statistical information of the user thread dimension, such as the xxx_by_thread table and the other is statements_digest, which is used for global statistics of SQL-digest. The third level is the statement dimension, including events_waits_current, events_stages_current and events_statements_current, which are used to calculate wait, stages, and statement information respectively. The fourth level is the historical table information, including xxx_history and xxx_history_long.

3. The setup_instruments table is used to configure specific instruments, including four categories: idle, stage/xxx, statement/xxx, wait/xxx.
Root @ performance_schema 06:25:50> select name, count (*) from setup_instruments group by LEFT (name, 5 );
+ --------------------------------- + ---------- +
| Name | count (*) |
+ --------------------------------- + ---------- +
| Idle | 1 |
| Stage, SQL, and After create | 111 |
| Statement/SQL/select | 170 |
| Wait/synch/mutex/SQL/PAGE: lock | 296 |
+ --------------------------------- + ---------- +
Idle indicates the idle time of the socket, the stage class indicates the statistics of each execution phase of the statement, and the statement class indicates the dimension information of the statement. The wait class counts various waiting events, such as IO, mutux, and spin_lock, condition. From the statistical results in the preceding table, we can see the number of instruments in each category. The number of stages is 111, the number of statement is 170, and the number of wait instances is 296.

4. The setup_objects table is used to configure monitoring objects. By default, all tables in mysql, cece_schema, and information_schema are not monitored. All tables in other databases are monitored.

Root @ performance_schema 06:25:55> select * from setup_objects;
+ ------------- + -------------------- + ------------- + --------- + ------- +
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED |
+ ------------- + -------------------- + ------------- + --------- + ------- +
| TABLE | mysql | % | NO |
| TABLE | performance_schema | % | NO |
| TABLE | information_schema | % | NO |
| TABLE | % | YES |
+ ------------- + -------------------- + ------------- + --------- + ------- +

5. The setup_timers table is used to configure the statistical time units for each type of commands. MICROSECOND indicates that the statistical unit is subtle, CYCLE indicates that the statistical unit is the clock CYCLE, and the time measurement is related to the CPU clock speed. NANOSECOND indicates that the statistical unit is a NANOSECOND, see the performance_timer table. Because the wait class contains wait events, a single SQL call is frequently performed. Therefore, cycle is the smallest unit of measurement. However, no matter which measurement unit is used, the time of the final statistics in the statistical table is changed to the second of skin.

Root @ performance_schema 06:29:50> select * from setup_timers;
+ ----------- + ------------- +
| NAME | TIMER_NAME |
+ ----------- + ------------- +
| Idle | MICROSECOND |
| Wait | CYCLE |
| Stage | NANOSECOND |
| Statement | NANOSECOND |
+ ----------- + ------------- +

Configuration method

By default, the setup_instruments table only opens the statement and wait/io commands. Many consumers in the setup_consumer table are not opened. To enable the required options, you can use the update statement to directly modify the configuration table, which takes effect immediately after modification. However, this method must be modified after the server is started and cannot be persisted. After restart, you have to reset it again. The configuration method of my. cnf is provided from 5.6.4 in the following format:

1. Set the collected instruments
Performance_schema_instrument = 'instrument _ name = value'
(1) Open wait commands
Performance_schema_instrument = 'wait/%'
(2) Open all commands
Performance_schema_instrument = '% = On'

2. Set consumer
Performance_schema_consumer_xxx = value
(1) Enable events_waits_history consumer

Performance_schema_consumer_events_waits_current = on

Performance_schema_consumer_events_waits_history = on

Pay attention to the level of consumer. events_waits_history is in layer 4th. Therefore, when setting it, make sure that the ENABLED statuses of events_statements_current, thread_instrumentation, and global_instrumentation are YES. Because the default thread_instrumentation and global_instrumentation are both YES, you only need to set events_waits_current and events_waits_current.

3. Set the statistics table size
All performance_schema tables use the PERFORMANCE_SCHEMA storage engine. All data in the table only exists in memory, and the table size has been
Fixed, so the occupied memory is certain. You can configure the number of records for each table.
Performance_schema_events_waits_history_size = 20
Performance_schema_events_waits_history_long_size = 15000