1, Performance_ Introduction to schema
performance: performance
schema: a diagram (table) that represents a plan or theory in the form of an outline or model.
MySQL's performance schema is used to monitor the resource consumption and resource waiting of MySQL server during a low-level operation.
characteristic
1. It provides a method to check the internal execution of the server in real time when the database is running. performance_ Tables in the schema database use performance_schema storage engine. The database mainly focuses on the performance related data during database operation_ Different from schema, information_schema mainly focuses on metadata information during server operation
2,performance_schema monitors the internal operation of the server by monitoring the events of the server. Events are anything done in the internal activities of the server and the corresponding time consumption. Use this information to judge where the relevant resources in the server are consumed? Generally speaking, an event can be a function call, an operating system wait, a stage of SQL statement execution (such as the parsing or sorting stage during SQL statement execution), or an entire SQL statement and a collection of SQL statements. Event collection can easily provide synchronous call information of disk files, table I/O, table locks and other resources by relevant storage engines in the server.
3,performance_ The events in the schema are different from the events written to the binary log (events describing data modification) and the events of the event plan scheduler (this is a stored program). performance_ The events in the schema record the consumption and time-consuming of some resources by the server when executing some activities, and the number of times these activities are executed.
4,performance_ Events in the schema are only recorded in the performance of the local server_ In the schema, when the data in these tables under it changes, it will not be written to the binlog, nor will it be copied to other servers through the replication mechanism.
5. Information recorded in tables related to current active events, historical events and event summary. It can provide the execution times and usage time of an event. In turn, it can be used to analyze the activities associated with a specific thread and a specific object (such as mutex or file).
6,PERFORMANCE_ The schema storage engine uses the "checkpoint" in the server source code to collect event data. For performance_ The code of the schema implementation mechanism itself does not have a separate thread to detect, which is different from other functions, such as replication or event scheduler
7. The collected event data is stored in performance_ In the table of the schema database. These tables can be queried using a SELECT statement or performance can be updated using an SQL statement_ Table records in the schema database (such as dynamically modifying several configuration tables at the beginning of setup * of performance_schema, but note that the changes to the configuration table will take effect immediately, which will affect data collection)
8,performance_ The data in the table of the schema will not be persisted and stored in disk, but stored in memory. Once the server is restarted, these data will be lost (all data under the whole performance_schema, including the configuration table)
9. The event monitoring function is available in all platforms supported by MySQL, but the types of timers used to count event time overhead may vary in different platforms.
2, Getting started with performance schema
In mysql version 5.7, the performance mode is enabled by default. If you want to explicitly close it, you need to modify the configuration file. You cannot modify it directly, and an error Variable 'performance' will be reported_ schema’ is a read only variable.
– view performance_ Properties of schema
mysql> SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.01 sec)
– modify performance in the configuration file_ The attribute value of schema. On means on and off means off
[mysqld]
performance_schema=ON
1. Switch database
use performance_schema;
2. View the information stored in the following table of the current database
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema |
+------------------------------------------------------+
| accounts |
| cond_instances |
| events_stages_current |
| events_stages_history |
| events_stages_history_long |
| events_stages_summary_by_account_by_event_name |
| events_stages_summary_by_host_by_event_name |
......ellipsis......
| status_by_thread |
| status_by_user |
| table_handles |
| table_io_waits_summary_by_index_usage |
| table_io_waits_summary_by_table |
| table_lock_waits_summary_by_table |
| threads |
| user_variables_by_thread |
| users |
| variables_by_thread |
+------------------------------------------------------+
87 rows in set (0.00 sec)3. Use show create table tablename to view the table structure when creating a table
mysql> show create table setup_consumers;
+-----------------+---------------------------------------------------------------------+
| Table | Create Table
+-----------------+---------------------------------------------------------------------+
| setup_consumers | CREATE TABLE `setup_consumers` (
`NAME` varchar(64) NOT NULL,
`ENABLED` enum('YES','NO') NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)4. Two basic concepts
instruments: producer, which is used to collect event information generated by various operations in mysql. The corresponding configuration items in the configuration table can be called monitoring collection configuration items.
Consumers: consumers. The corresponding consumer table is used to store the data collected from instruments. The configuration items in the corresponding configuration table can be called consumption storage configuration items.
3, Performance_ Classification of schema tables
performance_ The tables under the schema library can be grouped according to different monitoring latitudes.
1. Statement event record table
– statement event record tables, which record statement event information, and the current statement event table events_statements_current, historical statement and event table events_statements_history and long statement history events table events_statements_history_long, and the aggregated summary table. The summary table can also be subdivided according to account, host, program, thread, user and global
mysql> show tables like '%statement%'; +----------------------------------------------------+ | Tables_in_performance_schema (%statement%) | +----------------------------------------------------+ | events_statements_current | | events_statements_history | | events_statements_history_long | | 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_program | | events_statements_summary_by_thread_by_event_name | | events_statements_summary_by_user_by_event_name | | events_statements_summary_global_by_event_name | | prepared_statements_instances | +----------------------------------------------------+ 11 rows in set (0.00 sec)
2. Waiting event log
– wait event log table, similar to the related log table of statement event type:
mysql> show tables like '%wait%'; +-----------------------------------------------+ | Tables_in_performance_schema (%wait%) | +-----------------------------------------------+ | events_waits_current | | events_waits_history | | events_waits_history_long | | 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 | | table_io_waits_summary_by_index_usage | | table_io_waits_summary_by_table | | table_lock_waits_summary_by_table | +-----------------------------------------------+ 12 rows in set (0.00 sec)
3. Stage event record
– stage event record table, which records the stage events of statement execution
mysql> show tables like '%stage%'; +------------------------------------------------+ | Tables_in_performance_schema (%stage%) | +------------------------------------------------+ | events_stages_current | | events_stages_history | | events_stages_history_long | | 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 | +------------------------------------------------+ 8 rows in set (0.00 sec)
4. Transaction event log
– transaction event record table, a table that records events related to transactions
mysql> show tables like '%transaction%'; +------------------------------------------------------+ | Tables_in_performance_schema (%transaction%) | +------------------------------------------------------+ | events_transactions_current | | events_transactions_history | | events_transactions_history_long | | events_transactions_summary_by_account_by_event_name | | events_transactions_summary_by_host_by_event_name | | events_transactions_summary_by_thread_by_event_name | | events_transactions_summary_by_user_by_event_name | | events_transactions_summary_global_by_event_name | +------------------------------------------------------+ 8 rows in set (0.00 sec)
5. Table for monitoring file system layer calls
– tables that monitor file system layer calls
mysql> show tables like '%file%'; +---------------------------------------+ | Tables_in_performance_schema (%file%) | +---------------------------------------+ | file_instances | | file_summary_by_event_name | | file_summary_by_instance | +---------------------------------------+ 3 rows in set (0.00 sec)
6. Table for monitoring memory usage
--Table for monitoring memory usage mysql> show tables like '%memory%'; +-----------------------------------------+ | Tables_in_performance_schema (%memory%) | +-----------------------------------------+ | memory_summary_by_account_by_event_name | | memory_summary_by_host_by_event_name | | memory_summary_by_thread_by_event_name | | memory_summary_by_user_by_event_name | | memory_summary_global_by_event_name | +-----------------------------------------+ 5 rows in set (0.00 sec)
7. Configuration table
– dynamic performance_ Configuration table for schema configuration
mysql> show tables like '%setup%'; +----------------------------------------+ | Tables_in_performance_schema (%setup%) | +----------------------------------------+ | setup_actors | | setup_consumers | | setup_instruments | | setup_objects | | setup_timers | +----------------------------------------+ 5 rows in set (0.00 sec)
4, Performance_ Simple configuration and use of schema
When the database is just initialized and started, not all instruments (the word means instrument, which is understood here as event collector. Each item in the collector's configuration table has a switch field, either YES or NO) and consumers (similar to the collector, it is understood here as a saver, which also has a corresponding event type saving table configuration item. If YES, it means that the corresponding table saves performance data, and if NO, it means that the corresponding table does not save performance data.) Both are enabled, so all events will not be collected by default. The events you need to detect may not be opened and need to be set. You can use the following two statements to open the corresponding instruments and consumers (the row count may vary depending on the MySQL version).
1. Event collector configuration item switch
– turn on the collector configuration item switch waiting for events, and you need to modify the corresponding collector configuration item in the setup_instruments configuration table
UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'where name like 'wait%';
– turn on the save table configuration switch for waiting events and modify the corresponding configuration items in the setup_consumers configuration table
UPDATE setup_consumers SET ENABLED = 'YES'where name like '%wait%';
– after the configuration is completed, you can view what the current server is doing by querying events_waits_current table. Each thread in the table contains only one row of data, which is used to display the latest monitoring events of each thread
select * from events_waits_current\G
*************************** 1. row ***************************
THREAD_ID: 11
EVENT_ID: 570
END_EVENT_ID: 570
EVENT_NAME: wait/synch/mutex/innodb/buf_dblwr_mutex
SOURCE:
TIMER_START: 4508505105239280
TIMER_END: 4508505105270160
TIMER_WAIT: 30880
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
INDEX_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 67918392
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: NULL Attribute description
This information indicates that the thread with thread id 11 is waiting for buf_dblwr_mutex lock, waiting event is 30880
id: which thread does the event come from and what is the event number
event_name: indicates the specific content detected
Source: indicates the source file and line number of the detection code
timer_start: indicates the start time of the event
timer_end: indicates the end time of the event
timer_wait: indicates the total time spent on this event
be careful
Only one record is reserved for each thread in the current table. Once the thread completes its work, the event information of the thread will not be recorded in the table
2. history table: records the event information that each thread should complete
mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21; +-----------+----------+-----------------------------------------------+------------+ | thread_id | event_id | event_name | timer_wait | +-----------+----------+-----------------------------------------------+------------+ | 10 | 5571 | wait/synch/mutex/innodb/buf_pool_mutex | 146784 | | 10 | 5572 | wait/synch/mutex/innodb/buf_pool_mutex | 18904 | | 10 | 5573 | wait/synch/mutex/innodb/flush_list_mutex | 91740 | | 10 | 5574 | wait/synch/mutex/innodb/buf_pool_mutex | 20016 | | 10 | 5575 | wait/synch/mutex/innodb/buf_dblwr_mutex | 30580 | | 10 | 5566 | wait/synch/mutex/innodb/buf_pool_mutex | 125656 | | 10 | 5567 | wait/synch/mutex/innodb/buf_pool_mutex | 19460 | | 10 | 5568 | wait/synch/mutex/innodb/flush_list_mutex | 76728 | | 10 | 5569 | wait/synch/mutex/innodb/buf_pool_mutex | 19460 | | 10 | 5570 | wait/synch/mutex/innodb/buf_dblwr_mutex | 38364 | | 15 | 10030 | wait/synch/mutex/innodb/log_sys_mutex | 20572 | | 15 | 10029 | wait/synch/mutex/innodb/dict_sys_mutex | 22796 | | 15 | 10028 | wait/synch/sxlock/innodb/dict_operation_lock | 1043056 | | 15 | 10027 | wait/synch/mutex/innodb/row_drop_list_mutex | 135664 | | 15 | 10026 | wait/synch/mutex/innodb/flush_list_mutex | 212392 | | 15 | 10035 | wait/synch/mutex/innodb/flush_list_mutex | 110644 | | 15 | 10034 | wait/synch/mutex/innodb/log_flush_order_mutex | 24464 | | 15 | 10033 | wait/synch/mutex/innodb/log_sys_mutex | 21128 | | 15 | 10032 | wait/synch/mutex/innodb/fil_system_mutex | 78952 | | 15 | 10031 | wait/synch/mutex/innodb/log_sys_write_mutex | 20016 | | 21 | 3361 | wait/synch/mutex/innodb/log_sys_mutex | 131772 | +-----------+----------+-----------------------------------------------+------------+ 21 rows in set (0.00 sec)
The history table records the event information that each thread should complete execution, but only 10 event information of each thread will be recorded, and any more will be overwritten_ The event information of all threads is recorded in the long table, but the total number of records is 10000. If it exceeds 10000, it will be overwritten
3. Summary: summary information of all events
mysql> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10; +-----------------------------------------------+------------+ | EVENT_NAME | COUNT_STAR | +-----------------------------------------------+------------+ | wait/io/file/sql/FRM | 2944 | | wait/synch/mutex/innodb/buf_pool_mutex | 2808 | | wait/synch/mutex/innodb/log_sys_mutex | 2805 | | wait/synch/mutex/innodb/flush_list_mutex | 1871 | | wait/synch/mutex/innodb/sync_array_mutex | 1870 | | wait/io/file/innodb/innodb_data_file | 1619 | | wait/synch/mutex/innodb/buf_dblwr_mutex | 936 | | wait/synch/mutex/innodb/dict_sys_mutex | 935 | | wait/synch/mutex/innodb/log_flush_order_mutex | 935 | | wait/synch/mutex/innodb/fil_system_mutex | 935 | +-----------------------------------------------+------------+ 10 rows in set (0.08 sec)
The summary table provides summary information of all events. The tables in this group summarize event data in different ways (e.g. by user, by host, by thread, etc.). For example, to see which instruments take up the most time, you can check events_ waits_ summary_ global_ by_ event_ COUNT of name table_ Star or SUM_ TIMER_ Query the wait column (these two columns are calculated by COUNT (*) on the number of event records and SUM (TIMER_WAIT) on the TIMER_WAIT column of event records)
4. instance table: records which types of objects will be detected
mysql> select * from file_instances limit 20; +------------------------------------------+--------------------------------------+------------+ | FILE_NAME | EVENT_NAME | OPEN_COUNT | +------------------------------------------+--------------------------------------+------------+ | /usr/share/mysql/english/errmsg.sys | wait/io/file/sql/ERRMSG | 0 | | /usr/share/mysql/charsets/Index.xml | wait/io/file/mysys/charset | 0 | | /var/lib/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 | | /var/lib/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 | | /var/lib/mysql/mysql/engine_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file | 1 | | /var/lib/mysql/mysql/help_keyword.ibd | wait/io/file/innodb/innodb_data_file | 1 | | /var/lib/mysql/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file | 1 | | /var/lib/mysql/mysql/help_topic.ibd | wait/io/file/innodb/innodb_data_file | 1 | | /valib/mysql/mysql/innodb_index_stats.ibd| wait/io/file/innodb/innodb_data_file | 3 | | /va/mysql/mysql/innodb_table_stats.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/plugin.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/server_cost.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /var/lib/mysql/mysql/servers.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /va/mysql/mysql/slave_master_info.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /va/mysql/mysql/slave_relay_log_info.ibd | wait/io/file/innodb/innodb_data_file | 3 | | lib/mysql/mysql/slave_worker_info.ibd | wait/io/file/innodb/innodb_data_file | 3 | | /ib/mysql/mysql/time_zone.ibd | wait/io/file/innodb/innodb_data_file | 3 | +------------------------------------------+--------------------------------------+------------+ 20 rows in set (0.00 sec)
The instance table records which types of objects will be detected. When these objects are used by the server, an event record will be generated in the table, for example, file_ The instances table lists file I/O operations and their associated file names
5, Parameter description of common configuration items
1. Startup options
performance_schema_consumer_events_statements_current=TRUE
Whether to start events when mysql server starts_ statements_ The recording function of the current table (which records the current statement event information) can also be recorded in setup after startup_ Use the UPDATE statement in the consumers table to dynamically UPDATE the setup_ Events in the consumers configuration table_ statements_ Current configuration item. The default value is TRUE
performance_schema_consumer_events_statements_history=TRUE
And performance_ schema_ consumer_ events_ statements_ The current option is similar, but it is used to configure whether to record short history information of statement events. The default is TRUE
performance_schema_consumer_events_stages_history_long=FALSE
And performance_ schema_ consumer_ events_ statements_ The current option is similar, but it is used to configure whether to record long history information of statement events. The default is FALSE
In addition to statement events, it also supports wait events, state events and transaction events. Like statement events, they all have three startup items configured separately, but these waiting events are not enabled by default. If they need to be started together when MySQL Server is started, they usually need to be written into my.cnf configuration file
performance_schema_consumer_global_instrumentation=TRUE
Whether to enable the recording function of global tables (such as mutex_instances, rwlock_instances, cond_instances, file_instances, users, hostaccounts, socket_summary_by_event_name, file_summary_by_instance and other global object count statistics and event summary statistics) when MySQL Server is started. After startup, you can also use setup_ Use the UPDATE statement in the consumers table to dynamically UPDATE global configuration items
The default value is TRUE
performance_schema_consumer_statements_digest=TRUE
Whether to start events when MySQL Server starts_ statements_ summary_ by_ The record function of digest table can also be recorded in setup after startup_ Use the UPDATE statement in the consumers table to dynamically UPDATE the digest configuration item
The default value is TRUE
performance_schema_consumer_thread_instrumentation=TRUE
Is it enabled when MySQL Server starts
events_ xxx_ summary_ by_ yyy_ by_ event_ The record function of name table can also be in setup after startup_ Use the UPDATE statement to dynamically UPDATE thread configuration items in the consumers table
The default value is TRUE
performance_schema_instrument[=name]
Whether to enable some collectors when MySQL Server is started. Since there are thousands of instruments configuration items, the configuration item supports key value mode and universal configuration with% number, as follows:
[= name] can be specified as a specific instrument name (but if there are multiple instruments to be specified, this option needs to be used multiple times). Wildcards can also be used. You can specify the same prefix + wildcard of instruments, or% can be used to represent all instruments
Specifies to open a single instrument
--performance-schema-instrument= 'instrument_name=value'
Use wildcards to specify that multiple instruments are opened
--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'
Switch all instruments
--performance-schema-instrument= '%=ON' --performance-schema-instrument= '%=OFF'
be careful
The prerequisite for these startup options to take effect is to set performance_schema=ON. In addition, although these startup options cannot be viewed with the show variables statement, we can use setup_instruments and setup_ The consumers Table queries the values specified by these options.
2. System variable
show variables like '%performance_schema%'; --Important attribute interpretation performance_schema=ON /* Control performance_ The switch of schema function should use MySQL performance_schema, which needs to be enabled when mysqld is started to enable the event collection function This parameter supports performance before 5.7.x_ In the schema version, it is turned off by default and turned on by default from version 5.7.x Note: if mysqld is initializing performance_ If it is found that no related internal buffer can be allocated during schema, performance_schema will be automatically disabled and performance will be_ Schema is set to OFF */ performance_schema_digests_size=10000 /* Control events_ statements_ summary_ by_ The maximum number of rows in the digest table. If the generated statement summary information exceeds this maximum value, it cannot be stored in the table. At this time, performance_schema adds state variables */ performance_schema_events_statements_history_long_size=10000 /* Control events_ statements_ history_ The maximum number of rows in the long table. This parameter controls all sessions in events_ statements_ history_ The total number of event records that can be stored in the long table. If this limit is exceeded, the oldest record will be overwritten Global variable, read-only variable, integer value, introduced in version 5.6.3 * 5.6.x, the default value of version 5.6.5 and before is 10000, and the default value of version 5.6.6 and after is - 1. Generally, the automatically calculated value is 10000 * in version 5.7. X, the default value is - 1. Generally, the automatically calculated value is 10000 */ performance_schema_events_statements_history_size=10 /* Control events_ statements_ The maximum number of rows of a single thread (session) in the history table. This parameter controls the number of events in a single session_ statements_ The number of event records that can be stored in the history table. If this limit is exceeded, the earliest record of a single session will be overwritten Global variable, read-only variable, integer value, introduced in version 5.6.3 * 5.6.x, the default value of version 5.6.5 and before is 10, and the default value of version 5.6.6 and after is - 1. Generally, the automatically calculated value is 10 * 5.7.x, the default value is - 1. Generally, the automatically calculated value is 10 In addition to statement events, wait events, state events and transaction events, like statement events, have three parameters for storage restriction configuration respectively. Interested students study them by themselves and will not repeat them here */ performance_schema_max_digest_length=1024 /* Used to control the storage of SQL statement text in standardized form in performance_ The limit length of schema. This variable is the same as max_ digest_ Length variable (please refer to relevant data for the meaning of max_digest_length variable) Global variable, read-only variable, default value 1024 bytes, integer value, value range 0 ~ 1048576 */ performance_schema_max_sql_text_length=1024 /* Control stored in events_statements_current，events_statements_history and events_statements_history_long statementsql in event table_ The maximum SQL length bytes of the text column. System variable performance exceeded_ schema_ max_ SQL_TEXT_ The length part will be discarded and will not be recorded. Generally, this parameter does not need to be adjusted unless the truncated part is very different from other SQL Global variable, read-only variable, integer value, default value is 1024 bytes, value range is 0 ~ 1048576, introduced in version 5.7.6 Reduce system variable performance_ schema_ max_ sql_ text_ The length value can reduce memory usage, but if the truncated part of the summarized SQL is significantly different, there will be no way to distinguish these SQL with significant differences. Increasing the value of this system variable will increase memory usage, but it can more accurately distinguish different parts for summary SQL. */
6, Relevant description of important configuration table
Configuration tables are related to each other. According to the order of configuration impact, they can be added as:
1,performance_timers table
Which event timers are available in the server are recorded in
mysql> select * from performance_timers; +-------------+-----------------+------------------+----------------+ | TIMER_NAME | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD | +-------------+-----------------+------------------+----------------+ | CYCLE | 3599393034 | 1 | 22 | | NANOSECOND | 1000000000 | 1 | 70 | | MICROSECOND | 1000000 | 1 | 76 | | MILLISECOND | 1037 | 1 | 76 | | TICK | 103 | 1 | 1800 | +-------------+-----------------+------------------+----------------+ 5 rows in set (0.00 sec)
Field interpretation
timer_name: indicates the name of available timers. CYCLE is a timer based on CPU CYCLE counter
timer_frequency: indicates the number of timer units corresponding to each second. The conversion value of CYCLE timer is related to the frequency of CPU
timer_resolution: timer precision value, indicating the additional value added when each timer is called
timer_overhead: indicates the minimum cycle value of the overhead when using the timer to get events
2,setup_timers
Record the currently used event timer information in the table
mysql> select * from setup_timers; +-------------+-------------+ | NAME | TIMER_NAME | +-------------+-------------+ | idle | MICROSECOND | | wait | CYCLE | | stage | NANOSECOND | | statement | NANOSECOND | | transaction | NANOSECOND | +-------------+-------------+ 5 rows in set, 1 warning (0.00 sec)
Field interpretation
name: timer type, corresponding to an event category
timer_name: timer type name
3,setup_consumers Table
Lists the configurable list items for consumers
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 | YES | | events_statements_history_long | NO | | events_transactions_current | NO | | events_transactions_history | NO | | events_transactions_history_long | NO | | events_waits_current | YES | | events_waits_history | YES | | events_waits_history_long | YES | | global_instrumentation | YES | | thread_instrumentation | YES | | statements_digest | YES | +----------------------------------+---------+ 15 rows in set (0.00 sec)
Field interpretation
NAME: consumers configuration NAME
ENABLED: whether consumers is ENABLED. The valid values are YES or NO. this column can be modified with the UPDATE statement.
4,setup_instruments table
Lists the instruments list configuration items, which represent which events are supported to be collected:
SELECT * FROM setup_instruments; 1023 rows in set (0.00 sec)
Field interpretation
NAME: instrument NAME, which may have multiple parts and form a hierarchy
ENABLED: whether instrumentns is ENABLED. The valid values are YES or NO. this column can be modified with the UPDATE statement. If it is set to NO, the instruments will not be executed and NO event information will be generated
TIMED: whether the instruments collect time information. The valid values are YES or NO. this column can be modified with the UPDATE statement. If it is set to NO, the instruments will not collect time information
5,setup_actors table
The initial content is to match any user and host, so for all foreground threads, monitoring and historical event collection are enabled by default
mysql> SELECT * FROM setup_actors; +------+------+------+---------+---------+ | HOST | USER | ROLE | ENABLED | HISTORY | +------+------+------+---------+---------+ | % | % | % | YES | YES | +------+------+------+---------+---------+ 1 row in set (0.00 sec)
Field interpretation
HOST: HOST name similar to grant statement, a specific string name, or "%" for "any HOST"
USER: a specific string name, or "%" for "any USER"
ROLE: not used at present. ROLE function is enabled in MySQL 8.0
ENABLED: whether to enable the monitoring function of foreground threads matching HOST, USER and ROLE. The valid values are: YES or NO
HISTORY: whether to enable the historical event recording function of foreground threads matching HOST, USER and ROLE. The valid values are: YES or NO
6,setup_objects table
Control performance_ Whether the schema monitors specific objects. By default, the maximum number of rows in this table is 100.
mysql> SELECT * FROM setup_objects; +-------------+--------------------+-------------+---------+-------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | ENABLED | TIMED | +-------------+--------------------+-------------+---------+-------+ | EVENT | mysql | % | NO | NO | | EVENT | performance_schema | % | NO | NO | | EVENT | information_schema | % | NO | NO | | EVENT | % | % | YES | YES | | FUNCTION | mysql | % | NO | NO | | FUNCTION | performance_schema | % | NO | NO | | FUNCTION | information_schema | % | NO | NO | | FUNCTION | % | % | YES | YES | | PROCEDURE | mysql | % | NO | NO | | PROCEDURE | performance_schema | % | NO | NO | | PROCEDURE | information_schema | % | NO | NO | | PROCEDURE | % | % | YES | YES | | TABLE | mysql | % | NO | NO | | TABLE | performance_schema | % | NO | NO | | TABLE | information_schema | % | NO | NO | | TABLE | % | % | YES | YES | | TRIGGER | mysql | % | NO | NO | | TRIGGER | performance_schema | % | NO | NO | | TRIGGER | information_schema | % | NO | NO | | TRIGGER | % | % | YES | YES | +-------------+--------------------+-------------+---------+-------+ 20 rows in set (0.00 sec)
Field interpretation
OBJECT_TYPE: instruments type. Valid values are: "EVENT" (EVENT scheduler EVENT), "FUNCTION" (stored FUNCTION), "PROCEDURE" (stored PROCEDURE), "TABLE" (base TABLE), "TRIGGER" (TRIGGER). The configuration of TABLE object type will affect TABLE I/O events (wait/io/table/sql/handler instrument) and TABLE lock events (wait/lock/table/sql/handler instrument) collection
OBJECT_SCHEMA: the name of the database covered by a monitoring type object, a string name, or "%" (for "any database")
OBJECT_NAME: the name of the table covered by an object of a monitoring type, a string name, or "%" (representing "any object in the database")
ENABLED: whether to enable the monitoring function of a certain type of object. The valid values are: YES or NO. this column can be modified
TIMED: whether to enable the time collection function for a certain type of object. The valid values are: YES or NO. this column can be modified
7. threads table
For each server thread, a row is generated containing thread related information
select * from threads; 26 rows in set (0.00 sec)
Field interpretation
THREAD_ID: unique identifier (ID) of the thread
NAME: the NAME associated with the thread detection code in the server (note that this is not the NAME of instruments)
TYPE: thread TYPE. Valid values are: foreround and backgroup. It represents FOREGROUND thread and BACKGROUND thread respectively
PROCESSLIST_ID: corresponds to the ID column in the INFORMATION_SCHEMA.PROCESSLIST table.
PROCESSLIST_USER: the user name associated with the foreground thread, which is NULL for the background thread.
PROCESSLIST_HOST: the host name of the client associated with the foreground thread. It is NULL for the background thread.
PROCESSLIST_DB: the default database of the thread. If not, it is NULL.
PROCESSLIST_COMMAND: for the foreground thread, this value represents the command type being executed by the current client. If it is sleep, it indicates that the current session is idle
PROCESSLIST_TIME: the duration (in seconds) that the current thread has been in the current thread state
PROCESSLIST_STATE: indicates what the thread is doing.
PROCESSLIST_INFO: the statement that the thread is executing. If no statement is executed, it is NULL.
PARENT_THREAD_ID: if this thread is a child thread (generated by another thread), this field displays its parent thread ID
ROLE: not used yet
INSTRUMENTED: whether the event executed by the thread is detected. Valid values: YES, NO
HISTORY: whether to record the historical events of the thread. Valid values: YES, NO*
THREAD_OS_ID: thread or task identifier (ID) defined by the operating system layer:
be careful
In performance_ The schema library also contains many other libraries and tables, which can completely monitor the performance of the database. You need to refer to the official website for details.
7, performance_schema practice
After basically understanding the relevant information of tables, you can perform actual query operations through these tables for actual analysis.
--1,What kind SQL Maximum execution? SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --2,What kind SQL The highest average response time? SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --3,What kind SQL Maximum number of sorting records? SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --4,What kind SQL Maximum number of scan records? SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --5,What kind SQL Use the most temporary tables? SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --6,What kind SQL Return the most result sets? SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC --7,Which table is physical IO Most? SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC --8,Which table logic IO Most? SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC --9,Which index is accessed most? SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC --10,Which index has never been used? SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME; --11,Which wait event takes the most time? SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC --12-1,Dissect an article SQL Implementation of, including statement Information, stege Information, wait information SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%'; --12-2,View the time consumption of each phase SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553; --12-3,Check the lock waiting status of each stage SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event