How to understand PERFORMANCE_SCHEM in MySQL5.6 04/02 Update SLTechnology News&Howtos

How to understand PERFORMANCE_SCHEM in MySQL5.6

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand PERFORMANCE_SCHEM in MySQL5.6". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Find out what wait event you are going through through the sql statement!

The three-tier structure of Statement-> stage-> wait, associated through nesting_event_id, which represents the parent event _ id of an event.

For example, analyze a SQL statement containing count (*) as follows: (similar to v$sql, v$sqlstat, v$sqlarea of oracle)

SELECT

EVENT_ID

Sql_text

FROM events_statements_history

WHERE sql_text LIKE'% count (*)%'

+-- +

| | EVENT_ID | sql_text |

+-- +

| | 1690 | select count (*) from chuck.test_slow |

+-- +

a. View the time consumption of each phase: (similar to oracle's time model V$SYS_TIME_MODEL V$SESS_TIME_MODEL)

SELECT

Event_id

EVENT_NAME

SOURCE

TIMER_END-TIMER_START

FROM events_stages_history_long

WHERE NESTING_EVENT_ID = 1690

+-+

+-+

……

| | 2647 | stage/sql/Sending data | sql_executor.cc:192 | 7369072089000 |

b. View lock waiting at a certain stage (similar to oracle's v$session_wait)

For the lock waiting that may occur for each stage, one stage corresponds to one or more wait,events_waits_history_long. This table tends to be full [default threshold 10000]. Because select count (*) requires IO (logical IO or physical IO), there will be statistics of io waits during the stage/sql/Sending data phase. The event_id field of the stage_xxx table is associated with the nesting_event_id of the waits_xxx table.

SELECT

Event_id

Event_name

Source

Timer_wait

Object_name

Index_name

Operation

Nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 2647

+-- +

+-- +

Https://www.cnblogs.com/zhoujinyi/p/5236705.html

MySQL5.6 PERFORMANCE_SCHEMA description

Background:

MySQL 5.5 begins to add a new database, PERFORMANCE_SCHEMA, which is mainly used to collect database server performance parameters. And the storage engine of the library table is PERFORMANCE_SCHEMA, and the user cannot create a table with the storage engine of PERFORMANCE_SCHEMA. MySQL5.5 is disabled by default and needs to be opened manually. Add it to the configuration file:

[mysqld]

Performance_schema=ON

Check to see if it is on:

Mysql > show variables like 'performance_schema'

+-+ +

| | Variable_name | Value |

+-+ +

| | performance_schema | ON |

+-+ +

Starting from MySQL5.6, which is turned on by default, this article explains some of the more commonly used functions of PERFORMANCE_SCHEMA in the use of the database from MySQL5.6. You can check the official documentation for specific information.

Related table information:

1: configuration (setup) table:

Zjy@performance_schema 10:16:56 > show tables like'% setup%'

+-+

| | Tables_in_performance_schema (% setup%) |

+-+

| | setup_actors |

| | setup_consumers |

| | setup_instruments |

| | setup_objects |

| | setup_timers |

+-+

1According to the configuration of user latitude monitoring, it can monitor all users by default.

Zjy@performance_schema 10:19:11 > select * from setup_actors

+-+

+-+

| |% |

+-+

2 recording events consumers: configure the consumer type of events, that is, which statistical tables are written to the collected data.

Zjy@: performance_schema 10:23:35 > 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 |

+-+ +

What needs to be noted here is to update its ENABLED column YES which needs to be viewed. Such as:

Zjy@performance_schema 10:25:02 > update setup_consumers set ENABLED='YES' where NAME in ('events_stages_current','events_waits_current')

Query OK, 2 rows affected (0.00 sec)

It will take effect immediately after the update, but it will return to the default value after the server is restarted. To take effect permanently, you need to add it in the configuration file:

[mysqld]

# performance_schema

Performance_schema_consumer_events_waits_current=on

Performance_schema_consumer_events_stages_current=on

Performance_schema_consumer_events_statements_current=on

Performance_schema_consumer_events_waits_history=on

Performance_schema_consumer_events_stages_history=on

Performance_schema_consumer_events_statements_history=on

That is, precede these tables with: performance_schema_consumer_xxx. The values in the table setup_consumers have a hierarchical relationship:

Global_instrumentation > thread_instrumentation = statements_digest > events_stages_current = events_statements_current = events_waits_current > events_stages_history = events_statements_history = events_waits_history > events_stages_history_long = events_statements_history_long = events_waits_history_long

Only if the previous level is YES, will you continue to check that this layer is YES or NO. Global_instrumentation is the highest-level consumer, and if it is set to NO, all consumer will be ignored. Where history and history_long store the history entries of the current table, the history table records the last 10 events each thread has waited for, and the history_long table records the recent 10000 events generated by all threads, where 10 and 10000 are configurable. The structure of the three tables is the same, and the data of the history and history_ long tables are derived from the current table. The length is through the control parameters:

Zjy@performance_schema 11:10:03 > show variables like 'performance_schema%history%size'

+-+ +

| | 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 | |

+-+ +

3 instrument: configure specific instrument, including 4 categories: idle, stage/xxx, statement/xxx, and wait/xxx:

Zjy@performance_schema 10:56:35 > select name,count (*) from setup_instruments group by LEFT (name,5)

+-+ +

| | name | count (*) |

+-+ +

| | idle | 1 | |

| | stage/sql/After create | 111 | |

| | statement/sql/select | 179 |

| | wait/synch/mutex/sql/PAGE::lock | 296 |

+-+ +

Idle represents the idle time of socket, the stage class represents the statistics of each execution phase of the statement, the statement class counts the information of the statement dimension, and the wait class counts various wait events, such as IO,mutux,spin_lock,condition, and so on.

4. Monitor objects: configure monitoring objects. By default, tables in mysql,performance_schema and information_schema are not monitored, while all tables in other DB are monitored.

Zjy@performance_schema 11:00:18 > select * from setup_objects

+-+

+-+

| | TABLE | mysql |% | NO | NO | |

| | TABLE | performance_schema |% | NO | NO | |

| | TABLE | information_schema |% | NO | NO | |

| | TABLE |% |% | YES | YES | |

+-+

5recording setupkeeper timers: configure the statistical time unit for each type of instruction. MICROSECOND indicates that the statistical unit is subtle, CYCLE indicates that the statistical unit is the clock cycle, the time measure is related to the main frequency of CPU, and NANOSECOND indicates that the statistical unit is nanosecond. However, no matter which unit of measurement is used, the statistical time in the final statistical table will be changed to picoseconds. (1 second = 1000000000000 picoseconds)

Zjy@performance_schema 11:05:12 > select * from setup_timers

+-+ +

| | NAME | TIMER_NAME |

+-+ +

| | idle | MICROSECOND |

| | wait | CYCLE |

| | stage | NANOSECOND |

| | statement | NANOSECOND |

+-+ +

Two: instance table

1perfect conditons: conditional waiting for object instances

The table records the object of the condition variable used in the system, and OBJECT_INSTANCE_BEGIN is the memory address of the object.

2filewriting examples: file examples

The table records the objects in the system that have opened files, including ibdata files, redo files, binlog files, user table files, etc. Open_count shows the number of current file openings. If it is not opened again, it will not appear in the table.

Zjy@performance_schema 11:20:04 > select * from file_instances limit 2pm 5

+-+

+-+

| / var/lib/mysql/mysql/plugin.frm | wait/io/file/sql/FRM | |

+-+

3Gen mutexsynchronous instances: mutually exclusive synchronization object instances

All records of using mutex objects in the system are recorded in the table, where name is: wait/synch/mutex/*. LOCKED_BY_THREAD_ID shows which thread is holding the mutex, or NULL if no thread holds it.

4. Rwlocklocked synchronization: read-write lock synchronization object instance

All records in the system that use read-write lock objects are recorded in the table, where name is wait/synch/rwlock/*. WRITE_LOCKED_BY_THREAD_ID is the thread_id that is holding the object, or NULL if there is no thread holding it. READ_LOCKED_BY_COUNT records how many readers hold read locks at the same time. (you can tell which thread is waiting for the lock through the events_waits_current table; which thread holds the lock through the rwlock_instances. The drawback of rwlock_instances is that you can only record threads that hold write locks, and there is nothing you can do about read locks).

5Jing socketconversation object: an active session object instance

Thread_id,socket_id,ip and port are recorded in the table, and other tables can be associated with socket_instance through thread_id to obtain IP-PORT information and be able to dock with the application.

Event_name consists of three main categories:

Wait/io/socket/sql/server_unix_socket, server unix listens for socket

Wait/io/socket/sql/server_tcpip_socket, server tcp listens for socket

Wait/io/socket/sql/client_connection, client socket

Three: Wait table

1 event event waitswaiting current: records the events that the current thread is waiting for

2meme: record the last 10 events that each thread has been waiting for

3According to the 10000 events generated by all threads recently

The table structure is defined as follows:

CREATE TABLE `events_waits_ current` (

`THREAD_ ID`bigint (20) unsigned NOT NULL COMMENT 'thread ID'

`EVENT_ ID`bigint (20) unsigned NOT NULL COMMENT 'current thread event ID, and THREAD_ID determine uniqueness'

`END_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'when the event starts, this column is set to NULL. When the event ends, update to the current event ID'

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`SOURCE` varchar (64) DEFAULT NULL COMMENT 'source file when this event is generated'

`TIMER_ START`bigint (20) unsigned DEFAULT NULL COMMENT 'event start time (picosecond)'

`TIMER_ End`bigint (20) unsigned DEFAULT NULL COMMENT 'event end time (picosecond)'

`TIMER_ WAIT`bigint (20) unsigned DEFAULT NULL COMMENT 'event wait time (picosecond)'

`SPINS`int (10) unsigned DEFAULT NULL COMMENT''

`SCHEMA` varchar (64) DEFAULT NULL COMMENT 'library name'

`NAME`varchar (512) DEFAULT NULL COMMENT 'file name, table name, IP: sock value'

`TYPE` varchar (64) DEFAULT NULL COMMENT 'FILE, TABLE, TEMPORARY TABLE'

`NAME`varchar (64) DEFAULT NULL COMMENT 'index name' INDEX_

`BEGIN` bigint (20) unsigned NOT NULL COMMENT 'memory address'

`NESTING_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'the parent event ID' corresponding to this event

`TYPE`enum ('STATEMENT','STAGE','WAIT') DEFAULT NULL COMMENT' parent event type (STATEMENT, STAGE, WAIT)'

`OPERATION`varchar (32) NOT NULL COMMENT 'operation type (lock, read, write)'

`NUMBER_OF_ BYTES`bigint (20) DEFAULT NULL COMMENT''

`FLAGS` int (10) unsigned DEFAULT NULL COMMENT 'tag'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Four: Stage table

1 _ current: records the execution phase of the current thread.

2. Eventsrecording stageship history: records 10 historical records of the execution phase of the current thread

3According to the 10000 historical records of the execution phase of the current thread,

The table structure is defined as follows:

CREATE TABLE `events_stages_ current` (

`THREAD_ ID`bigint (20) unsigned NOT NULL COMMENT 'thread ID'

`EVENT_ ID`bigint (20) unsigned NOT NULL COMMENT 'event ID'

`END_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'end event ID'

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`SOURCE`varchar (64) DEFAULT NULL COMMENT 'source code location'

`TIMER_ START`bigint (20) unsigned DEFAULT NULL COMMENT 'event start time (picosecond)'

`TIMER_ End`bigint (20) unsigned DEFAULT NULL COMMENT 'event end time (picosecond)'

`TIMER_ WAIT`bigint (20) unsigned DEFAULT NULL COMMENT 'event wait time (picosecond)'

`NESTING_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'the parent event ID' corresponding to this event

`TYPE`enum ('STATEMENT','STAGE','WAIT') DEFAULT NULL COMMENT' parent event type (STATEMENT, STAGE, WAIT)'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Five: Statement table

1 thread_id+event_id: a record can be uniquely identified through a single record. The Statments table records only the top-level requests, SQL statements or COMMAND, with one row for each statement. The form of event_name is statement/sql/*, or statement/com/*

2,events_statements_history

3,events_statements_history_long

The table structure is defined as follows:

CREATE TABLE `events_statements_ current` (

`THREAD_ ID`bigint (20) unsigned NOT NULL COMMENT 'thread ID'

`EVENT_ ID`bigint (20) unsigned NOT NULL COMMENT 'event ID'

`END_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'end event ID'

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`SOURCE`varchar (64) DEFAULT NULL COMMENT 'source code location'

`TIMER_ START`bigint (20) unsigned DEFAULT NULL COMMENT 'event start time (picosecond)'

`TIMER_ End`bigint (20) unsigned DEFAULT NULL COMMENT 'event end time (picosecond)'

`TIMER_ WAIT`bigint (20) unsigned DEFAULT NULL COMMENT 'event wait time (picosecond)'

`TIME` bigint (20) unsigned NOT NULL COMMENT 'lock time'

`SQL_ TEXT`longtext COMMENT 'record SQL statement'

`DIGEST`DIGEST` varchar (32) DEFAULT NULL COMMENT'32-bit string generated by MD5 to SQL_TEXT'

`DIGEST_ TEXT`longtext COMMENT 'replaces the value part of the statement with a question mark, which is used to classify SQL statements.

`CURRENT_ SCHEMA` varchar (64) DEFAULT NULL COMMENT 'default database name'

`TYPE` varchar (64) DEFAULT NULL COMMENT 'reserved field'

`OBJECT_ schema` varchar (64) DEFAULT NULL COMMENT 'reserved field'

`NAME`varchar (64) DEFAULT NULL COMMENT 'reserved field'

`BEGIN` bigint (20) unsigned DEFAULT NULL COMMENT 'memory address'

`ERRNO` int (11) DEFAULT NULL COMMENT''MYSQL_

`SQLSTATE`varchar (5) DEFAULT NULL COMMENT''

`MESSAGE_ TEXT`varchar (128) DEFAULT NULL COMMENT 'Information'

`ERRORS` bigint (20) unsigned NOT NULL COMMENT 'number of errors'

`WARNINGS` bigint (20) unsigned NOT NULL COMMENT 'number of warnings'

`AFFECTED`bigint (20) unsigned NOT NULL COMMENT 'number of ROWS_ effects'

`SENT`bigint (20) unsigned NOT NULL COMMENT 'number of records returned'

`ROWS_ EXAMINED` bigint (20) unsigned NOT NULL COMMENT 'number of read scanned records'

`CREATED_TMP_DISK_ TABLES` bigint (20) unsigned NOT NULL COMMENT 'create disk temporary tables'

`CREATED_TMP_ TABLES` bigint (20) unsigned NOT NULL COMMENT 'number of temporary tables created'

When `SELECT_FULL_ JOIN` bigint (20) unsigned NOT NULL COMMENT 'join, the first table is the number of full table scans

`JOIN` bigint (20) unsigned NOT NULL COMMENT 'number of reference tables scanned in range mode'

`Range` bigint (20) unsigned NOT NULL COMMENT 'join, the number of first table scanned in range mode'

`SELECT_RANGE_ CHECK`bigint (20) unsigned NOT NULL COMMENT''

The number of full table scans of the first epitope when `SELECT_ SCAN` bigint (20) unsigned NOT NULL COMMENT 'join

`SORT_MERGE_ passs` bigint (20) unsigned NOT NULL COMMENT''

`Range`bigint (20) unsigned NOT NULL COMMENT 'range sort number'

`SORT_ ROWS` bigint (20) unsigned NOT NULL COMMENT 'number of records sorted'

`SORT_ SCAN` bigint (20) unsigned NOT NULL COMMENT 'number of sorts in the whole table'

`USED` bigint (20) unsigned NOT NULL COMMENT 'number of indexes not used'

`USED` bigint (20) unsigned NOT NULL COMMENT''

`NESTING_EVENT_ ID`bigint (20) unsigned DEFAULT NULL COMMENT 'the parent event ID' corresponding to this event

`TYPE`enum ('STATEMENT','STAGE','WAIT') DEFAULT NULL COMMENT' parent event type (STATEMENT, STAGE, WAIT)'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

Six: Connection table

1 users: record information on the number of user connections

2 hosts: the number of host connections is recorded.

3. Recording the number of connections between users and hosts.

Zjy@performance_schema 12:03:27 > select * from users

+-+

+-+

| | debian-sys-maint | | 36 |

| | zjy | 1 | 22285 | |

| | dchat_php | | 37864 | |

| | dxyslave | 2 | 9 | |

| | nagios | | 10770 | |

| | dchat_data | 140 | 2233023 | |

| | NULL | | 15866 | |

| | dchat_api | 160 | 2754212 | |

| | mha_data | 1 | 36 | |

| | backup | | 15 |

| | cacti | | 4312 | |

| | kol | 10 | 172414 | |

+-+

12 rows in set (0.00 sec)

Zjy@performance_schema 12:03:34 > select * from hosts

+-+

+-+

| | 192.168.100.218 | 150 | 2499422 | |

| | 192.168.100.240 | 10 | 172429 | |

| | 192.168.100.139 | | 698 |

| | 192.168.100.21 | | 2 |

| | 192.168.100.220 | 150 | 2526136 | |

| | 192.168.100.25 | 1 | 7 | |

| | NULL | | 15867 | |

| | 192.168.100.241 | | 21558 | |

| | 192.168.100.191 | 1 | 34 |

| | localhost | | 10807 | |

| | 192.168.100.118 | 1 | 2 |

| | 192.168.100.251 | | 4312 | |

| | 192.168.100.23 | 1 | 31 |

| | 192.168.100.193 | | 15 |

+-+

14 rows in set (0.01 sec)

Zjy@performance_schema 12:05:21 > select * from accounts

+-+

+-+

| | cacti | 192.168.100.251 | | 4313 |

| | backup | 192.168.100.193 | | 15 |

| | dchat_api | 192.168.100.220 | 80 | 1382585 | |

| | dchat_php | 192.168.100.220 | | 20292 |

| | zjy | 192.168.100.139 | | 698 |

| | zjy | 192.168.100.241 | | 21558 |

| | mha_data | 192.168.100.191 | 1 | 34 |

| | dxyslave | 192.168.100.118 | 1 | 2 |

| | kol | 192.168.100.240 | 10 | 172431 | |

| | dxyslave | 192.168.100.25 | 1 | 7 |

| | dchat_data | 192.168.100.218 | 70 | 1109974 | |

| | zjy | 192.168.100.23 | 1 | 31 |

| | dchat_php | 192.168.100.218 | | 17572 |

| | dchat_data | 192.168.100.220 | 70 | 1123306 | |

| | mha_data | 192.168.100.21 | | 2 |

| | dchat_api | 192.168.100.218 | 80 | 1371918 | |

+-+

View Code

Seven: Summary table: the Summary table aggregates statistical information of various dimensions, including table dimension, index dimension, session dimension, statement dimension and lock dimension.

1 event event: aggregate by waiting event type, with one record for each event

CREATE TABLE `events_waits_summary_global_by_event_ name` (

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`COUNT_ STAR`bigint (20) unsigned NOT NULL COMMENT 'event count'

`SUM_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'Total waiting time'

`MIN_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'minimum waiting time'

`WAIT` bigint (20) unsigned NOT NULL COMMENT 'average waiting time'

`MAX_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'maximum waiting time'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

2According to events, waiting, waiting

Event_name+object_instance_begin uniquely determines a record.

CREATE TABLE `events_waits_summary_by_ instance` (

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`BEGIN` bigint (20) unsigned NOT NULL COMMENT 'memory address'

`COUNT_ STAR`bigint (20) unsigned NOT NULL COMMENT 'event count'

`SUM_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'Total waiting time'

`MIN_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'minimum waiting time'

`WAIT` bigint (20) unsigned NOT NULL COMMENT 'average waiting time'

`MAX_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'maximum waiting time'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

3According to each thread and event, thread_id+event_name uniquely determines a record.

CREATE TABLE `events_waits_summary_by_thread_by_event_ name` (

`THREAD_ ID`bigint (20) unsigned NOT NULL COMMENT 'thread ID'

`NAME`varchar (EVENT_) NOT NULL COMMENT 'event name'

`COUNT_ STAR`bigint (20) unsigned NOT NULL COMMENT 'event count'

`SUM_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'Total waiting time'

`MIN_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'minimum waiting time'

`WAIT` bigint (20) unsigned NOT NULL COMMENT 'average waiting time'

`MAX_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'maximum waiting time'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

4According to the type of event stage, there is one record for each event. The table structure is the same as above.

5According to the statistics of each thread and event, the table structure is the same as above.

6According to events, events statementsaggregating by events digest: aggregates according to the statements of events.

CREATE TABLE `events_statements_summary_by_ digest` (

`NAME`varchar (64) DEFAULT NULL COMMENT 'library name'

`DIGEST` varchar (32) DEFAULT NULL COMMENT 'the 32-bit string generated by MD5 the SQL_TEXT. NULL' if the statement_digest option is not turned on in the consumer table

`TEXT`longtext COMMENT 'replaces the value in the statement with a question mark, which is used for SQL statement classification. NULL if the statement_digest option is not turned on in the consumer table.'

`COUNT_ STAR`bigint (20) unsigned NOT NULL COMMENT 'event count'

`SUM_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'Total waiting time'

`MIN_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'minimum waiting time'

`WAIT` bigint (20) unsigned NOT NULL COMMENT 'average waiting time'

`MAX_TIMER_ WAIT` bigint (20) unsigned NOT NULL COMMENT 'maximum waiting time'

`TIME`bigint (20) unsigned NOT NULL COMMENT 'total lock time'

`SUM_ erros` bigint (20) unsigned NOT NULL COMMENT 'Total number of errors'

`SUM_ WARNINGS` bigint (20) unsigned NOT NULL COMMENT 'Total number of warnings'

`AFFECTED`bigint (20) unsigned NOT NULL COMMENT 'total number of SUM_ROWS_ impacts'

`SENT`bigint (20) unsigned NOT NULL COMMENT 'return total number'

`SUM_ROWS_ EXAMINED` bigint (20) unsigned NOT NULL COMMENT 'Total number of scans'

`TABLES` bigint (20) unsigned NOT NULL COMMENT 'Total number of disk temporary tables created'

`SUM_CREATED_TMP_ TABLES` bigint (20) unsigned NOT NULL COMMENT 'Total number of temporary tables created'

`JOIN` bigint (20) unsigned NOT NULL COMMENT 'total number of full table scans for the first table'

`JOIN` bigint (20) unsigned NOT NULL COMMENT 'Total number of range scans'

`Range` bigint (20) unsigned NOT NULL COMMENT 'Total number of first table scanned in range mode'

`SUM_SELECT_RANGE_ CHECK`bigint (20) unsigned NOT NULL COMMENT''

`SUM_SELECT_ SCAN` bigint (20) unsigned NOT NULL COMMENT 'total number of full table scans for the first epitope'

`SUM_SORT_MERGE_ passs` bigint (20) unsigned NOT NULL COMMENT''

`Range`bigint (20) unsigned NOT NULL COMMENT 'range sort Total'

`SUM_SORT_ ROWS` bigint (20) unsigned NOT NULL COMMENT 'Total number of records sorted'

`Scan` bigint (20) unsigned NOT NULL COMMENT 'Total number of sorted scans in the first table'

`USED` bigint (20) unsigned NOT NULL COMMENT 'Total number of indexes not used'

`USED` bigint (20) unsigned NOT NULL COMMENT''

`SEEN` timestamp NOT NULL DEFAULT '0000-00-0000: 00 COMMENT' first execution time'

`SEEN` timestamp NOT NULL DEFAULT '0000-00-0000: 00 COMMENT' Last execution time'

) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8

7According to the statement of the event: aggregate according to the statement of the event. The table structure is the same as above.

8According to the statement of thread and event, the table structure is the same as above.

9 document filename statistics by event type (physical IO dimension)

10 document statistics, physical IO dimension: specific file statistics

9 and 10 explain together:

Statistical IO operation: COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

Statistical reading: COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ

Statistics write: COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE

Count other IO events, such as create,delete,open,close: COUNT_MISC,SUM_TIMER_MISC,MIN_TIMER_MISC,AVG_TIMER_MISC,MAX_TIMER_MISC

11Maple table: aggregates the Imax O operations of each table according to wait/io/table/sql/handler (logical IO latitude)

Statistical IO operation: COUNT_STAR,SUM_TIMER_WAIT,MIN_TIMER_WAIT,AVG_TIMER_WAIT,MAX_TIMER_WAIT

Statistical reading: COUNT_READ,SUM_TIMER_READ,MIN_TIMER_READ,AVG_TIMER_READ,MAX_TIMER_READ

: COUNT_FETCH,SUM_TIMER_FETCH,MIN_TIMER_FETCH,AVG_TIMER_FETCH, MAX_TIMER_FETCH

Statistics write: COUNT_WRITE,SUM_TIMER_WRITE,MIN_TIMER_WRITE,AVG_TIMER_WRITE,MAX_TIMER_WRITE

INSERT statistics, as well as DELETE and UPDATE statistics: COUNT_INSERT,SUM_TIMER_INSERT,MIN_TIMER_INSERT,AVG_TIMER_INSERT,MAX_TIMER_INSERT

12 Tablewise statistics waits: similar to table_io_waits_summary_by_table, statistics are based on index dimensions.

13 tablelock waiting table: aggregates table lock wait events, including internal lock and external lock

Internal lock is called through the SQL layer function thr_lock, and the operation value is:

Read normal 、 read with shared locks 、 read high priority 、 read no insert 、 write allow write 、 write concurrent insert 、 write delayed 、 write low priority 、 write normal

External lock calls the storage engine layer through the interface function handler::external_lock. The values of OPERATION column are: read external, write external.

14j connection Summaries table: account, user, host

Events_waits_summary_by_account_by_event_name

Events_waits_summary_by_user_by_event_name

Events_waits_summary_by_host_by_event_name

Events_stages_summary_by_account_by_event_name

Events_stages_summary_by_user_by_event_name

Events_stages_summary_by_host_by_event_name

Events_statements_summary_by_account_by_event_name

Events_statements_summary_by_user_by_event_name

Events_statements_summary_by_host_by_event_name

15 socket_summary_by_event_name:socket aggregate statistical tables.

Eight: other related tables

1Performancestatistical timers: statistical time units supported by the system

2PowerThreads: monitors the currently running threads on the server side

Statistical applications:

The statistical information about SQL dimensions is mainly concentrated in the events_statements_summary_by_ digest table. By abstracting SQL statements into digest, we can count the statistical information of certain SQL statements in each dimension.

1, which SQL executes the most:

Zjy@performance_schema 11:36:22 > SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR desc LIMIT 1\ G

* * 1. Row *

SCHEMA_NAME: dchat

DIGEST_TEXT: SELECT...

COUNT_STAR: 1161210102

SUM_ROWS_SENT: 1161207842

SUM_ROWS_EXAMINED:

FIRST_SEEN: 2016-02-17 00:36:46

LAST_SEEN: 2016-03-07 11:36:29

The comments for each field can be seen in the table structure above: the SQL was executed 1161210102 times from February 17 to March 7.

2, which SQL has the most average response time:

Zjy@performance_schema 11:36:28 > SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT desc LIMIT 1\ G

* * 1. Row *

SCHEMA_NAME: dchat

DIGEST_TEXT: SELECT...

COUNT_STAR: 1

AVG_TIMER_WAIT: 273238183964000

SUM_ROWS_SENT: 50208

SUM_ROWS_EXAMINED: 5565651

FIRST_SEEN: 2016-02-22 13:27:33

LAST_SEEN: 2016-02-22 13:27:33

The notes for each field can be found in the table structure above: the average response time of the SQL from February 17 to March 7 is 273238183964000 picoseconds (1000000000000 picoseconds = 1 second).

3, which SQL scans the most rows:

SUM_ROWS_EXAMINED

4. Which SQL uses the most temporary tables:

SUM_CREATED_TMP_DISK_TABLES 、 SUM_CREATED_TMP_TABLES

5, which SQL returns the most result sets:

SUM_ROWS_SENT

6, which SQL has the largest number of sorts:

SUM_SORT_ROWS

Through the above indicators, we can indirectly obtain the comparison of logical IO (SUM_ROWS_EXAMINED), CPU consumption (SUM_SORT_ROWS) and network bandwidth (SUM_ROWS_SENT) of a certain type of SQL.

Through the file_summary_by_instance table, you can get which file (table) has the most physical IO since the system is running, which may mean that this table often needs to access disk IO.

7, which table or file has the most logical IO (hot data):

Zjy@performance_schema 12:16:18 > SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2\ G

* * 1. Row *

FILE_NAME: / var/lib/mysql/ibdata1 # file

EVENT_NAME: wait/io/file/innodb/innodb_data_file

COUNT_READ: 544

SUM_NUMBER_OF_BYTES_READ: 10977280

COUNT_WRITE: 3700729

SUM_NUMBER_OF_BYTES_WRITE: 1433734217728

* 2. Row * *

FILE_NAME: / var/lib/mysql/dchat/fans.ibd # Table

EVENT_NAME: wait/io/file/innodb/innodb_data_file

COUNT_READ: 9370680

SUM_NUMBER_OF_BYTES_READ: 153529188352

COUNT_WRITE: 67576376

SUM_NUMBER_OF_BYTES_WRITE: 1107815432192

8, which index is most used:

Zjy@performance_schema 12:18:42 > 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 limit 1

+-+ +

+-+ +

| | fans | PRIMARY | 29002695158 | | 296373434 |

+-+ +

1 row in set (0.29 sec)

Through the table_io_waits_summary_by_index_usage table, you can get which table has the most used which index (including primary key index and secondary index) when the system is running so far.

9, which index has not been used:

Zjy@performance_schema 12:23:22 > SELECT OBJECT_SCHEMA,OBJECT_NAME, INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = AND OBJECT_SCHEMA 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME

10, which takes the most time to wait for the event:

Zjy@performance_schema 12:25:22 > 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 LIMIT 1

11, similar to profiling features:

Analyze a specific SQL, the time consumption of the SQL in each stage of execution, through the events_statements_ xxx table and events_stages_xxx table, you can achieve the goal. The two tables are associated with the nesting_event_id through event_id. The nesting_event_id of the stages table is the event_id; of the corresponding statements table for the lock waiting that may occur in each stage, and a stage corresponds to one or more wait, which is associated with the nesting_event_id of the waits_xxx table through the event_id field of the stage_xxx table. Such as:

For example, analyze a SQL statement that contains count (*), as follows:

SELECT

EVENT_ID

Sql_text

FROM events_statements_history

WHERE sql_text LIKE'% count (*)%'

+-- +

| | EVENT_ID | sql_text |

+-- +

| | 1690 | select count (*) from chuck.test_slow |

+-- +

First of all, it is obtained that the event_id of the statement is 1690, and the goal can be achieved by looking up the record in events_stages_xxx with a nesting_event_id of 1690.

a. View the time spent for each phase:

SELECT

Event_id

EVENT_NAME

SOURCE

TIMER_END-TIMER_START

FROM events_stages_history_long

WHERE NESTING_EVENT_ID = 1690

+-+

+-+

| | 1691 | stage/sql/init | mysqld.cc:990 | 316945000 | |

| | 1693 | stage/sql/checking permissions | sql_parse.cc:5776 | 26774000 | |

| | 1695 | stage/sql/Opening tables | sql_base.cc:4970 | 41436934000 |

| | 2638 | stage/sql/init | sql_select.cc:1050 | 85757000 | |

| | 2639 | stage/sql/System lock | lock.cc:303 | 40017000 | |

| | 2643 | stage/sql/optimizing | sql_optimizer.cc:138 | 38562000 | |

| | 2644 | stage/sql/statistics | sql_optimizer.cc:362 | 52845000 | |

| | 2645 | stage/sql/preparing | sql_optimizer.cc:485 | 53196000 | |

| | 2646 | stage/sql/executing | sql_executor.cc:112 | 3153000 | |

| | 2647 | stage/sql/Sending data | sql_executor.cc:192 | 7369072089000 |

| | 4304138 | stage/sql/end | sql_select.cc:1105 | 19920000 | |

| | 4304139 | stage/sql/query end | sql_parse.cc:5463 | 44721000 | |

| | 4304145 | stage/sql/closing tables | sql_parse.cc:5524 | 61723000 | |

| | 4304152 | stage/sql/freeing items | sql_parse.cc:6838 | 455678000 | |

| | 4304155 | stage/sql/logging slow query | sql_parse.cc:2258 | 83348000 | |

| | 4304159 | stage/sql/cleaning up | sql_parse.cc:2163 | 4433000 | |

+-+

Through indirect correlation, we can analyze the time consumption of the SQL statement at each stage, expressed in picoseconds. The results shown here are very similar to the profiling function, and with performance schema, the profiling feature is no longer needed. It is also important to note that since only the last 10 records are recorded for each connection in the events_stages_history table by default, to ensure that all records are obtained, you need to access the events_stages_history_ long table

b. Check the lock waiting at a certain stage

SELECT

Event_id

Event_name

Source

Timer_wait

Object_name

Index_name

Operation

Nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 2647

+-- +

+-- +

+-- +

Through the above experiment, we know the three-level structure of statement,stage,wait, which is associated with nesting_event_id, which represents the parent event _ id of an event.

(2)。 An example of simulating innodb row lock waiting

Session An executes the statement update test_icp set y=y+1 where xSecret1 (x is primary key), but session B does not execute the same statement update test_icp set y=y+1 where xpend1, which is blocked by session B and eventually reports an error. Through the join join query events_statements_history_long and events_stages_history_long, you can see that it took about 60s in the updating phase. This is mainly because the innodb_lock_wait_timeout on the instance is set to 60 and a timeout error is reported after waiting for 60 seconds.

SELECT

Statement.EVENT_ID

Stages.event_id

Statement.sql_text

Stages.event_name

Stages.timer_wait

FROM events_statements_history_long statement

Join events_stages_history_long stages

On statement.event_id=stages.nesting_event_id

WHERE statement.sql_text = 'update test_icp set y=y+1 where xero1'

+-+

+-+

| | 5816 | 5817 | update test_icp set y=y+1 where Xero1 | stage/sql/init | 195543000 | |

| | 5816 | 5819 | update test_icp set y=y+1 where Xero1 | stage/sql/checking permissions | 22730000 | |

| | 5816 | 5821 | update test_icp set y=y+1 where Xero1 | stage/sql/Opening tables | 66079000 | |

| | 5816 | 5827 | update test_icp set y=y+1 where Xero1 | stage/sql/init | 89116000 | |

| | 5816 | 5828 | update test_icp set y=y+1 where Xero1 | stage/sql/System lock | 218744000 | |

| | 5816 | 5832 | update test_icp set y=y+1 where Xero1 | stage/sql/updating | 6001362045000 |

| | 5816 | 5968 | update test_icp set y=y+1 where Xero1 | stage/sql/end | 10435000 | |

| | 5816 | 5969 | update test_icp set y=y+1 where Xero1 | stage/sql/query end | 85979000 | |

| | 5816 | 5983 | update test_icp set y=y+1 where Xero1 | stage/sql/closing tables | 56562000 | |

| | 5816 | 5990 | update test_icp set y=y+1 where Xero1 | stage/sql/freeing items | 83563000 | |

| | 5816 | 5992 | update test_icp set y=y+1 where Xero1 | stage/sql/cleaning up | 4589000 | |

+-+

View the wait event:

SELECT

Event_id

Event_name

Source

Timer_wait

Object_name

Index_name

Operation

Nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 5832

* * 1. Row *

Event_id: 5832

Event_name: wait/io/table/sql/handler

Source: handler.cc:2782

Timer_wait: 6005946156624

Object_name: test_icp

Index_name: PRIMARY

Operation: fetch

As a result, a fetch wait event is logged in the waits table, but there is no more detailed innodb row lock wait event statistics.

(3)。 An example of simulating MDL lock waiting

Session An executes a large query select count (*) from test_slow, and session B executes the table structure change alter table test_slow modify c2 varchar (152); the execution process of the alter statement can be obtained through the following statement, focusing on the "stage/sql/Waiting for table metadata lock" phase.

SELECT

Statement.EVENT_ID

Stages.event_id

Statement.sql_text

Stages.event_name as stage_name

Stages.timer_wait as stage_time

FROM events_statements_history_long statement

Left join events_stages_history_long stages

On statement.event_id=stages.nesting_event_id

WHERE statement.sql_text = 'alter table test_slow modify c2 varchar (152)'

+ -+-+

+ -+-+

| | 326526744 | 326526745 | alter table test_slow modify c2 varchar (152) | stage/sql/init | 216662000 |

| | 326526744 | 326526747 | alter table test_slow modify c2 varchar (152) | stage/sql/checking permissions | 18183000 |

| | 326526744 | 326526748 | alter table test_slow modify c2 varchar (152) | stage/sql/checking permissions | 10294000 |

| | 326526744 | 326526750 | alter table test_slow modify c2 varchar (152) | stage/sql/init | 4783000 |

| | 326526744 | 326526751 | alter table test_slow modify c2 varchar (152) | stage/sql/Opening tables | 140172000 |

| | 326526744 | 326526760 | alter table test_slow modify c2 varchar (152) | stage/sql/setup | 157643000 |

| | 326526744 | 326526769 | alter table test_slow modify c2 varchar (152) | stage/sql/creating table | 8723217000 |

| | 326526744 | 326526803 | alter table test_slow modify c2 varchar (152) | stage/sql/After create | 257332000 |

| | 326526744 | 326526832 | alter table test_slow modify c2 varchar (152) | stage/sql/Waiting for table metadata lock | 1000181831000 |

| | 326526744 | 326526835 | alter table test_slow modify c2 varchar (152) | stage/sql/After create | 33483000 |

| | 326526744 | 326526838 | alter table test_slow modify c2 varchar (152) | stage/sql/Waiting for table metadata lock | 1000091810000 |

| | 326526744 | 326526841 | alter table test_slow modify c2 varchar (152) | stage/sql/After create | 17187000 |

| | 326526744 | 326526844 | alter table test_slow modify c2 varchar (152) | stage/sql/Waiting for table metadata lock | 1000126464000 |

| | 326526744 | 326526847 | alter table test_slow modify c2 varchar (152) | stage/sql/After create | 27472000 |

| | 326526744 | 326526850 | alter table test_slow modify c2 varchar (152) | stage/sql/Waiting for table metadata lock | 561996133000 |

| | 326526744 | 326526853 | alter table test_slow modify c2 varchar (152) | stage/sql/After create | 124876000 |

| | 326526744 | 326526877 | alter table test_slow modify c2 varchar (152) | stage/sql/System lock | 30659000 |

| | 326526744 | 326526881 | alter table test_slow modify c2 varchar (152) | stage/sql/preparing for alter table | 40246000 |

| | 326526744 | 326526889 | alter table test_slow modify c2 varchar (152) | stage/sql/altering table | 36628000 |

| | 326526744 | 326528280 | alter table test_slow modify c2 varchar (152) | stage/sql/end | 43824000 |

| | 326526744 | 326528281 | alter table test_slow modify c2 varchar (152) | stage/sql/query end | 112557000 |

| | 326526744 | 326528299 | alter table test_slow modify c2 varchar (152) | stage/sql/closing tables | 27707000 |

| | 326526744 | 326528305 | alter table test_slow modify c2 varchar (152) | stage/sql/freeing items | 201614000 |

| | 326526744 | 326528308 | alter table test_slow modify c2 varchar (152) | stage/sql/cleaning up | 3584000 |

+ -+-+

As can be seen from the results, there are several stage/sql/Waiting for table metadata lock phases with an interval of 1 second, indicating that the judgment will be retried every 1 second. Find an event_id at this stage and use nesting_event_id association to determine which wait event you are waiting for.

SELECT

Event_id

Event_name

Source

Timer_wait

Object_name

Index_name

Operation

Nesting_event_id

FROM events_waits_history_long

WHERE nesting_event_id = 326526850

+- -+

+- -+

+- -+

As you can see from the results, the blocking is caused by the condition variable MDL_context::COND_wait_status and shows the location of the code.

View Code

This is the end of "how to understand PERFORMANCE_SCHEM in MySQL5.6". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.