MySQL Performance-Schema (2) theory, performanceschema

MySQL Performance-Schema (2) theory, performanceschema

MySQL Performance-Schema contains a total of 52 tables, which are mainly divided into the Setup table, Instance table, Wait Event table, Stage Event TABLE Statement Event table, Connection table, and Summary table. The previous article focuses on the Setup table. This article will describe each type of table in detail.

Instance table
The instance contains five tables: cond_instances, file_instances, mutex_instances, rwlock_instances, and socket_instances.
(1) cond_instances: Conditional waiting for the object instance
The table records the objects of the condition variables used in the system. OBJECT_INSTANCE_BEGIN is the memory address of the objects. For example, the name of the timer_cond instance in the thread pool is wait/synch/cond/threadpool/timer_cond.

(2) file_instances: file instance
The table records the objects opened in the system, including ibdata files, redo files, binlog files, and user table files, such as redo log files: /u01/my3306/data/ib_logfile0. Open_count indicates the number of opened files. If the files are not opened again, they are not displayed in the table.

(3) mutex_instances: mutex synchronization object instance
The table records all records using mutex objects in the system. The name is wait/synch/mutex /*. For example, the volume of mutex for opening a file: wait/synch/mutex/mysys/THR_LOCK_open. LOCKED_BY_THREAD_ID indicates which thread is holding the mutex. If no thread is holding the mutex, the value is NULL.

(4) rwlock_instances: read/write lock synchronization object instance
The table records all records that use the read/write lock object in the system. The name is wait/synch/rwlock /*. WRITE_LOCKED_BY_THREAD_ID is the thread_id of the object being held. If no thread is held, it is NULL. READ_LOCKED_BY_COUNT records the number of readers holding the read lock at the same time. You can use the events_waits_current table to know which thread is waiting for the lock and rwlock_instances to know which thread is holding the lock. The drawback of rwlock_instances is that only the threads holding write locks can be recorded, and there is nothing to do with read locks.

(5) socket_instances: Active session object instance
The thread_id, socket_id, ip and port are recorded in the Table. Other tables can be associated with socket_instance through thread_id to obtain IP-PORT information, which can be connected with the application.
Event_name mainly includes three types:
Wait/io/socket/SQL/server_unix_socket, server unix listening socket
Wait/io/socket/SQL/server_tcpip_socket, server tcp listener socket
Wait/io/socket/SQL/client_connection, client socket

Wait Event table
The Wait table contains three tables: events_waits_current, events_waits_history, and events_waits_history_long. You can use thread_id + event_id to uniquely identify a record. The current table records the waiting events of the current thread, the history Table records the 10 events that each thread has recently waited for, and the history_long table records the 10000 events generated by all recent threads, both 10 and 10000 can be configured. The three tables have the same structure. The data in the history and history_long tables comes from the current table. Duplicate events may occur in the current table and history Table, and events in the history Table are completed. Events that do not end are not added to the history Table.
THREAD_ID: thread ID
EVENT_ID: The Event ID of the current thread, and THREAD_ID constitute a Primary Key.
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.
SOURCE: SOURCE code file when the event is generated
TIMER_START, TIMER_END, TIMER_WAIT: the start time, End Time, And wait time of the event. The unit is second (picoseconds)

OBJECT_SCHEMA, OBJECT_NAME, and OBJECT_TYPE depend on the situation
For synchronization objects (cond, mutex, rwlock), all three values are NULL.
For file io objects, OBJECT_SCHEMA is NULL, OBJECT_NAME is the FILE name, And OBJECT_TYPE is FILE
For a SOCKET object, OBJECT_NAME is the IP address of the socket: SOCK value.
For table I/O objects, OBJECT_SCHEMA is the TABLE SCHEMA name, OBJECT_NAME is the TABLE name, And OBJECT_TYPE is the TABLE or TEMPORARY TABLE
NESTING_EVENT_ID: ID of the parent event corresponding to the event
NESTING_EVENT_TYPE: parent event type (STATEMENT, STAGE, WAIT)
OPERATION: OPERATION Type (lock, read, write)

Stage Event table

The Stage table contains three tables: events_stages_current, events_stages_history, and events_stages_history_long. The thread_id + event_id can uniquely identify a record. The table records the execution stages of the current thread. Because you can know the execution time of each stage, you can use the stage table to obtain the time consumed by SQL in each stage.

THREAD_ID: thread ID
EVENT_ID: Event ID
END_EVENT_ID: ID of the last event
SOURCE: SOURCE Code Location
TIMER_START, TIMER_END, TIMER_WAIT: the start time, End Time, And wait time of the event. The unit is second (picoseconds)
NESTING_EVENT_ID: ID of the parent event corresponding to the event
NESTING_EVENT_TYPE: parent event type (STATEMENT, STAGE, WAIT)

Statement Event table
The Statement table contains three tables: events_statements_current, events_statements_history, and events_statements_history_long. You can use thread_id + event_id to uniquely identify a record. The Statments table only records the top-level requests, SQL statements, or COMMAND. Each statement row is not listed separately for nested subqueries or stored procedures. Event_name format: statement/SQL/*, or statement/com /*
SQL _TEXT: records SQL statements
DIGEST: A 32-bit string generated by MD5 for SQL _TEXT. If the statement_digest option is not enabled for the consumer table, it is NULL.
DIGEST_TEXT: replaces the median part of a statement with a question mark for classifying SQL statements. If the statement_digest option is not enabled for the consumer table, it is NULL.
CURRENT_SCHEMA: default database name
OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE: reserved field, all are NULL
ROWS_AFFECTED: Number of impacts
ROWS_SENT: number of returned records
ROWS_EXAMINED: number of records read
CREATED_TMP_DISK_TABLES: Number of physical temporary tables created
CREATED_TMP_TABLES: number of temporary tables created
SELECT_FULL_JOIN: When join is performed, the first table is the number of full table scans.
SELECT_FULL_RANGE_JOIN: Number of referenced tables scanned in range mode during join.
SELECT_RANGE: number of rows scanned by range in the first table during join.
SELECT_SCAN: number of full table scans for the first outer table in join Operation
SORT_ROWS: Number of sorted records
NESTING_EVENT_ID, NESTING_EVENT_TYPE, reserved field, NULL.

Connection table
The Connection table records client information, including three tables: users, hosts, and account. accounts contains information about hosts and users.
USER: USER Name
HOST: User's IP address

Summary table
The Summary table collects statistics from various dimensions, including the table dimension, index dimension, session dimension, statement dimension, and lock dimension.
(1). wait-summary table
Events_waits_summary_global_by_event_name
Scenario: events are aggregated by the waiting event type. Each event is recorded by record.
Events_waits_summary_by_instance
Scenario: aggregation by the waiting event object. The same waiting event may have multiple instances with different memory addresses. Therefore
Event_name + object_instance_begin uniquely identifies a record.
Events_waits_summary_by_thread_by_event_name
Scenario: statistics are made based on each thread and event. thread_id + event_name uniquely identifies a record.
COUNT_STAR: Event count
SUM_TIMER_WAIT: Total wait time
MIN_TIMER_WAIT: minimum wait time
MAX_TIMER_WAIT: maximum wait time
AVG_TIMER_WAIT: average wait time

(2). stage-summary table
Events_stages_summary_by_thread_by_event_name
Events_stages_summary_global_by_event_name
Similar

(3). statements-summary table
The events_statements_summary_by_thread_by_event_name table and events_statements_summary_global_by_event_name table are similar to the preceding table. For the events_statements_summary_by_digest table,
FIRST_SEEN_TIMESTAMP: time when the first statement is executed
LAST_SEEN_TIMESTAMP: the time when the last statement was executed.
Scenario: used to count top SQL statements in a certain period of time

(4). file I/O summary table
File_summary_by_event_name [statistics by event type]
File_summary_by_instance [statistics by specific files]
Scenario: physical I/O dimension
FILE_NAME: Specific file name, for example:/u01/my3306/data/tcbuyer_0168/tc_biz_order_2695.ibd
EVENT_NAME: event name, for example: wait/io/file/innodb/innodb_data_file
COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT
Statistics IO operations
COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ, SUM_NUMBER_OF_BYTES_READ
Statistics read
COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE, SUM_NUMBER_OF_BYTES_WRITE
Statistics writing
COUNT_MISC, SUM_TIMER_MISC, MIN_TIMER_MISC, AVG_TIMER_MISC, MAX_TIMER_MISC
Collects statistics on other IO events, such as create, delete, open, and close.

(5). Table I/O and Lock Wait Summaries-Table
Table_io_waits_summary_by_table
Aggregate the I/O operations of each table according to wait/io/table/SQL/handler. [logical IO]
COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT
Statistics IO operations
COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT
Statistics read
COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE
Statistics writing
COUNT_FETCH, SUM_TIMER_FETCH, MIN_TIMER_FETCH, AVG_TIMER_FETCH, MAX_TIMER_FETCH
Same as read
COUNT_INSERT, SUM_TIMER_INSERT, MIN_TIMER_INSERT, AVG_TIMER_INSERT, MAX_TIMER_INSERT
INSERT statistics, including DELETE and UPDATE statistics.

(6). table_io_waits_summary_by_index_usage
Similar to table_io_waits_summary_by_table

(7). table_lock_waits_summary_by_table
Aggregate table lock wait events, including internal lock and external lock.
Internal lock is called through the SQL layer function thr_lock. 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 OPERATION column value is:
Read external
Write external

(8). Connection Summaries table
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

(9). socket-summaries table
Socket_summary_by_instance
Socket_summary_by_event_name

Other tables
Performance_timers: time unit supported by the system
Threads: monitors the current running threads on the server.