This article was written by Zhai Weixiang.
We mentioned Performance Schema (PS) tables earlier. The PS library mainly contains the following types of tables:
We introduced this type of table in the previous article, so we will skip this topic. Click here for a detailed introduction.
This refers to a recent event table. For example, the events_waits_current table contains the most recent events of each thread.
This table lists the events the current thread is waiting for, including the following columns:
Thread_ID: The ID of the thread
EVENT_ID: The event ID of the current thread. It forms a primary key with THREAD_ID.
END_EVENT_ID: When the event starts, this column is set to NULL. When the event ends, it is updated to the current event ID.
EVENT_NAME: The INSTRUMENT name that generates the event
SOURCE: The source code file when the event was generated. If the thread waits for mutex, you can check the corresponding source code to see where it is blocked.
TIMER_START, TIMER_END, and TIMER_WAIT: The time when the event starts, ends, and waits (unit: picoseconds)
SPINS: Times that a mutex lock or readers–writer lock spins
OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE, and OBJECT_INSTANCE_BEGIN: The values of these columns depend on different object types.
cond, mutex, and rwlock types, the values of OBJECT_SCHEMA, OBJECT_NAME, and OBJECT_TYPE are NULL. OBJECT_INSTANCE_BEGIN indicates the memory address created by the synchronization object.OBJECT_NAME, FILE for OBJECT_TYPE, and the memory address for OBJECT_INSTANCE_BEGIN.OBJECT_SCHEMA is the SCHEMA name of the table, OBJECT_NAME is the table name, OBJECT_TYPE is TABLE or TEMPORARY TABLE, and OBJECT_INSTANCE_BEGIN is the memory address of the object.INDEX: The index name used.
NESTING_EVENT_ID: The ID of the event in which this event lock is nested.
NESTING_EVENT_TYPE: Nesting event type (STATEMENT, STAGE, WAIT)
OPERATION: Operation type (lock, read, write)
NUMBER_OF_BYTES: The number of bits of read or write operation. The NUMBER_OF_BYTES value is NULL for the I/O object of the table.
FLAGS: Reserved columns
The stage table lists the process of executing an SQL statement, including parsing an SQL statement, opening a table, or executing a filesort operation. It can be combined with show processlist. Each row of the table shows the most recent record of the thread.
It mainly contains the following columns:
Thread_ID: The ID of the thread
EVENT_ID: The ID of the event
END_EVENT_ID: The ID of the event that just ended. The meaning of the three columns above is the same as the wait table.
SOURCE: Source code location
TIMER_START, TIMER_END, and TIMER_WAIT: The start time, end time, and duration of this phase. If the event does not end, the values of TIMER_END and TIMER_WAIT are NULL. If the TIMED column of INSTRUMENT corresponding to the event is set to NO (SETUP_INSTRUMENTS), the values of these three columns are all NULL.
NESTING_EVENT_ID: The ID of the nesting event of the current event. The nesting event of a stage event is usually a STATEMENT event.
NESTING_EVENT_TYPE: Nesting event types (STATEMENT, STAGE, WAIT)
The monitoring of the statement covers the duration from becoming active to not being active in a thread. In other words, the monitoring starts when the server accepts the first communication packet from the client to when the server sends all the responses. Only the statements of the top level are monitored, and the stored procedure or subqueries contained in the statement are not listed separately.
A request sent from a client can be either a COMMAND or an SQL statement, which is divided into statement/com and statement/sql and then further divided into the specific SQL type or COMMAND.
There are also some instruments for error handling.
statement/com/Error is used to handle a COMMAND the server cannot understand.
statement/sql/error is used to handle SQL statements that cannot be parsed.
When the server receives an SQL statement, the server regards it as statement/com/Query. Then, it is set as statement/sql/* after parsing.
The table contains a large number of columns, mainly including:
THREAD_ID, EVENT_ID, END_EVENT_ID, EVENT_NAME, and SOURCE. The meaning of these columns is similar to the two tables described above and will not be repeated here.
TIMER_START, TIMER_END, and TIMER_WAIT. The meaning is similar to the table events_stages_current and will not be repeated in this article.
LOCK_TIME: The time to wait for a table lock
SQL_TEXT: SQL statement. Its value is NULL for COMMAND.
DIGEST: The MD5 value after the SQL statement is formatted. It takes effect only after statement_digest is enabled.
DIGEST_TEXT: The string obtained after the SQL statement is formatted. It takes effect only after statement_digest is enabled.
CURRENT_SCHEMA: The default database to which the SQL statement connects.
OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE: Reserved columns. Their values are NULL.
OBJECT_INSTANCE_BEGIN: It is used to indicate the address of the object in memory.
MYSQL_ERRNO: The error number generated by the SQL statement
RETURNED_SQLSTATE: SQLSTATE value
MESSAGE_TEXT: Error message
ERRORS: It indicates whether an error occurs. If the SQLSTATE value starts with 00 (completed) or 01 (warning), its value is 0; otherwise, it is 1.
WARNINGS: The number of warnings. The columns above are taken from the diagnostics area of the SQL statement.
ROWS_AFFECTED: The number of rows affected by the SQL statement
ROWS_SENT: The number of rows sent to the client
ROWS_EXAMINED: The number of records read from the storage engine during SQL execution
CREATED_TMP_DISK_TABLES, CREATED_TMP_TABLES, SELECT_FULL_JOIN, SELECT_FULL_RANGE_JOIN, SELECT_RANGE, SELECT_RANGE_CHECK, SELECT_SCAN, SORT_MERGE_PASSES, SORT_RANGE, SORT_ROWS, and SORT_SCAN: The names of these columns also have the corresponding status variables, which have the same meaning. Here, only those related to the current SQL statements are included in statistics.
NO_INDEX_USED: If SQL performs a full-table scan and does not use the index, the value is 1; otherwise, it is 0.
NO_GOOD_INDEX_USED: If the optimizer does not find a good execution plan for the SQL statement, the value is 1; otherwise, the value is 0. Please see Range checked for each record in the EXTRA column output by EXPLAIN for more information.
NESTING_EVENT_ID and NESTING_EVENT_TYPE: Reserved columns, whose current values are NULL
The table of historical events has more records than the CURRENT EVENT table. For example, events_waits_history contains the ten most recent events of every thread, and events_waits_history_long contains the 10,000 most recent events. You can configure the size of these two tables through option performance_schema_events_waits_history_size and performance_schema_events_waits_history_long_size.
The HISTORY/HISTORY LONG table has a similar structure to the CUREENT table, except the amount of data stored is different.
A summary table aggregates event information. The summary table is the focus of our attention because it omits the process of manually processing the data. Servers will aggregate the data. It mainly contains the following types:
events_waits_summary_global_by_event_name
events_waits_summary_by_instance
[events_waits_summary_by_thread_by_event_name](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-wait-summary-tables.html)
These three tables are aggregated based on EVENT name (EVENT_NAME, OBJECT_INSTANCE_BEGIN)/INSTANCE (THREAD_ID, EVENT_NAME), respectively. The aggregated columns include:
COUNT_STAR: Event count
SUM_TIMER_WAIT: Total waiting time
MIN_TIMER_WAIT: Minimum waiting time
AVG_TIMER_WAIT: Average waiting time
events_stages_summary_by_thread_by_event_name
events_stages_summary_global_by_event_name
Columns COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, and MAX_TIMER_WAIT are also included.
events_statements_summary_by_thread_by_event_name
events_statements_summary_global_by_event_name
In addition to COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, and MAX_TIMER_WAIT, the column below is included:
SUM_xxx: The SUM values of some status information, such as SUM_LOCK_TIME and SUM_ERRORS.
events_statements_summary_by_digest also contains:
FIRST_SEEN_TIMESTAMP and LAST_SEEN_TIMESTAMP, which indicate the timestamp when the SQL summary (DIGEST) information is generated for the first time and the most recent generation.
Rules of the aggregated SQL statement in the DIGEST table are listed below:
COUNT_STAR records of DIGEST=NULL row occupy less than 5% of the total, the summary record is representative. If it exceeds 50%, DBA may need to adjust the value of the performance_schema_digests_size variable.objects_summary_global_by_type
Please see this link for statistics related to file operations.
table_io_waits_summary_by_index_usage
table_io_waits_summary_by_table
table_lock_waits_summary_by_table
table_io_waits_summary_by_table is grouped according to wait/io/table/sql/handler and table names. We need to pay attention to table_lock_waits_summary_by_table because it aggregates table lock wait events, including internal lock and external lock.
The internal lock is called by the SQL layer function thr_lock. It is displayed in the OPERATION column and has the following values.
read normal
read with shared locks
read high priority
read no insert
write allow write
write concurrent insert
write delayed
write low priority
write normalThe external lock is used to call the storage engine layer through the interface function handler::external_lock. The value of the OPERATION column includes:
read external
write externalevents_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
The instance table records the status of the events being monitored, including the following types:
cond_instances: Instance of condition wait objectIt lists the condition wait object currently running and only contains two columns.
NAME: The name of INSTRUMENT under waiting
OBJECT_INSTANCE_BEGIN: The memory address when the condition is monitored
file_instances: File instanceThe file information when the file io instrument is executed. It will not be displayed when a file has never been opened. If the file is deleted from the disk, it will also be deleted from the table.
It contains the following three columns:
NAME: File name
EVENT_NAME: Instrument name
OPEN_COUNT: The times the current file is opened. If a file is opened first and then closed, the OPEN_COUNT value of this file is 0.
mutex_instances: Instances of mutex synchronization objectsAll currently waiting mutexes are listed. This table contains the following three columns:
NAME: Name of the mutex
`
OBJECT_INSTANCE_BEGIN`: Memory address of the object instance
LOCKED_BY_THREAD_ID: The ID of the thread that currently locks the mutex
PS provides the following information For each mutex instrument:
a.setup_instruments lists the names of mutexes with wait/synch/mutex as the naming prefix.
mutex_instances. OBJECT_INSTANCE_BEGIN can be considered as a unique identifier of the mutex.events_waits_current records a row of data for the thread, indicating that it is waiting for a mutex (shown in the EVENT_NAME column) and which mutex is being waited (shown in the OBJECT_INSTANCE_BEGIN column).(1) events_waits_current displays that the waiting for the mutex has ended (shown in TIMER_END and TIMER_WAIT columns).
(2) Ended wait events are added to the events_waits_history and events_waits_history_long tables.
(3) The mutex_instances table shows that the mutex has been occupied (shown in the LOCKED_BY_THREAD_ID column).
mutex_instances. You can perform queries on the following two tables to detect performance bottlenecks.events_waits_current: Find out what mutex the thread is waiting for
mutex_instances: Find out who currently occupies the mutex
rwlock_instances: Instances of readers–writer lock synchronization objectsThis table is similar to mutex_instances, except the readers–writer lock of the record contains the following columns.
NAME: Name of the readers–writer lock instrument
OBJECT_INSTANCE_BEGIN: Memory address of object instances when they are created
WRITE_LOCKED_BY_THREAD_ID: The ID of the thread currently holding the writer lock
READ_LOCKED_BY_COUNT: Reader lock counter
socket_instances: Instances of active session objectsIt records all current real-time socket connection objects. It mainly includes two kinds of monitoring sockets (server_tcpip_socket and server_unix_socket) and one client connection (client_connection). When a user thread is interrupted, the corresponding record is deleted.
This table contains the following columns:
EVENT_NAME: The name is wait/io/socket/*.
OBJECT_INSTANCE_BEGIN: The memory address of the object instance
Thread_ID: The ID of the thread
SOCKET_ID: Internal socket ID.
IP: Client IP address
PORT: Client port number
STATE: User thread status, which is IDLE or ACTIVE
Please see this link for more information.
Tables of client connection information, accounts, hosts, and users.
Please see this link for more information.
Link attribute table includes session_account_connect_attrs and session_connect_attrs.
The former records the current session, and the latter records all sessions.
Relatively speaking, these tables are not the focus in performance tuning. If you are interested, you can refer to the documents.
host_cache is used to display internal host cache information.
performance_timers shows which counters are available.
threads: current server threads, including frontend and backend threads.
Disclaimer: This is a translated work of Zhai Weixiang's article, all rights reserved to the original author. The views expressed herein are for reference only and don't necessarily represent the official views of Alibaba Cloud.
[MySQL 5.6] Learning Performance Schema: Naming Conventions, State Variables, and Others
Morningking - September 26, 2023
ApsaraDB - May 24, 2022
ApsaraDB - May 24, 2022
Morningking - September 26, 2023
Jack008 - June 10, 2020
ApsaraDB - October 21, 2020
Database Overview
ApsaraDB: Faster, Stronger, More Secure
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
AnalyticDB for MySQL
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.
Learn More
Elastic High Performance Computing Solution
High Performance Computing (HPC) and AI technology helps scientific research institutions to perform viral gene sequencing, conduct new drug research and development, and shorten the research and development cycle.
Learn MoreMore Posts by ApsaraDB
