Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog [MySQL 5.6] Table Type Overview of Performance Schema

[MySQL 5.6] Table Type Overview of Performance Schema

Part 3 of this 3-part series discusses the table types in Performance Schema (PS).

This article was written by Zhai Weixiang.

We mentioned Performance Schema (PS) tables earlier. The PS library mainly contains the following types of tables:

1. SETUP Table

We introduced this type of table in the previous article, so we will skip this topic. Click here for a detailed introduction.

2. Current Event Table

This refers to a recent event table. For example, the events_waits_current table contains the most recent events of each thread.

2.1 events_waits_current

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.

  1. For 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.
  2. For file I/O objects, the value is NULL for OBJECT_SCHEMA, the file name for OBJECT_NAME, FILE for OBJECT_TYPE, and the memory address for OBJECT_INSTANCE_BEGIN.
  3. For a SOCKET object, OBJECT_NAME is the IP:SOCK value of the socket, and OBJECT_INSTANCE_BEGIN is the object memory address.
  4. For table I/O objects, 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

2.2 events_stages_current

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)

2.3 events_statements_current

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

3. HISTORY Table

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.

4. SUMMARY Table

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:

4.1 Event Wait Summaries

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

4.2 Stage Summaries

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.

4.3 Statement Summaries

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:

  1. If there is a corresponding formatted SQL statement, aggregate its information into the record and update LAST_SEEN_TIMESTAMP.
  2. New records are added to the table (when it is not full), and FIRST_SEEN_TIMESTAMP and LAST_SEEN_TIMESTAMP are updated to the current time.
  3. If the table is full, new records are aggregated to the DIGEST=NULL column. This column can help you confirm whether the records in the DIGEST SUMMARY table are representative. If the 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.

4.4 Object Wait Summaries

objects_summary_global_by_type

4.5 File I/O Summaries

file_summary_by_event_name

file_summary_by_instance

Please see this link for statistics related to file operations.

4.6 Table I/O and Lock Wait Summaries

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 normal

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

4.7 Connection Summaries

Wait Event Summary Tables

events_waits_summary_by_account_by_event_name

events_waits_summary_by_user_by_event_name

events_waits_summary_by_host_by_event_name

Stage Summary Tables

events_stages_summary_by_account_by_event_name

events_stages_summary_by_user_by_event_name

events_stages_summary_by_host_by_event_name

Statement Summary Tables

events_statements_summary_by_account_by_event_name

events_statements_summary_by_user_by_event_name

events_statements_summary_by_host_by_event_name

4.8 Socket Summaries

socket_summary_by_instance

socket_summary_by_event_name

5. INSTANCE Table

The instance table records the status of the events being monitored, including the following types:

It 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

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

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

  1. When a mutex is created in the code, a record row is added to mutex_instances. OBJECT_INSTANCE_BEGIN can be considered as a unique identifier of the mutex.
  2. When a thread tries to lock a 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).
  3. When a thread successfully locks a mutex:

(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).

  1. When the mutex is released, the LOCKED_BY_THREAD_ID column is set to NULL.
  2. When the mutex object is destroyed, the corresponding record is removed from 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

This 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

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

6. Other Tables

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.

Related Article

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 Database Developers Command Performance Schema Table Type
0 0 0
Share on

Read previous post:

[MySQL 5.6] Learning Performance Schema: Naming Conventions, State Variables, and Others

Read next post:

An In-Depth Analysis of REDO Logs in InnoDB

ApsaraDB

445 posts | 93 followers

You may also like

Comments

ApsaraDB

445 posts | 93 followers

Related Products

More Posts by ApsaraDB

See All