This article was written by Zhai Weixiang.
The naming of the Performance Schema (PS) instrument is similar to a tree structure. The highest level is the type of instrument, with a total of four types, namely idle, wait, stage, and statement. The name at the next layer may be a submodule name, such as sync and io. The next layer, such as sync, can be divided into mutex, cond, and rwlock, and then it may be a specific synchronization lock object or a module of the next layer.
The idle object indicates the idle information of the socket. The setup_instrument table only contains one column, and the column name is idle. The corresponding event it produces is in socket_instances.
mysql> select count(*) from setup_instruments where name like ‘idle%';
+———-+
| count(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)The naming rule for stage is stage/code_area/stage_name, and the code_area value is sql/mysys.
stage_name indicates each stage in the execution of the statement, such as storing results in the query cache, waiting for global read lock, etc.
mysql> select count(*) from setup_instruments where name like ‘stage%';
+———-+
| count(*) |
+———-+
| 108 |
+———-+
1 row in set (0.00 sec)Its naming rule is statement/SQL or COM.
The next level of SQL indicates different SQL types, such as statement/sql/xa_commit and statement/sql/rollback.
In addition, statement/sql/select in the document is used in CREATE DATABASE and SELECT statements, which have not been confirmed yet.
COM corresponds to the server command type in enum_server_command. For example, statement/com/Ping represents COM_PING.
We should pay more attention to the instrument of wait type because the concurrent waiting of MySQL is a very noteworthy part and is generally the cause of server exceptions. The wait type also includes I/O-related instruments.
The operation time statistics on files (wait/io/file/ ) and time of socket operations (wait/io/socket) are included.
In addition, there are table I/O operations (wait/io/table/sql/handler ), including row-level operations on persistent tables and temporary tables and operations that affect rows (fetch, insert, and delete). Unlike other wait objects, the wait object of a table may contain other wait times. For example, the I/O of a table may contain file I/O or memory operations. Therefore, the I/O information in the table events_waits_current may also include wait/io/file objects. Two rows of data should be contained in the table.
Only the lock operation wait/lock/table/sql/handler on tables is included.
There are many synch objects, including condition variables (wait/synch/cond), mutex (wait/synch/mutex), and readers–writer lock (wait/synch/rwlock).
PS provides information to show that some statistics are not included in PS due to limited memory space.
mysql> SHOW STATUS LIKE ‘perf%';
+———————————————–+——-+
| Variable_name | Value |
+———————————————–+——-+
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
+———————————————–+——-+
23 rows in set (0.00 sec)The size of the corresponding allocated memory depends on the following system variables.
mysql> show variables like ‘%perf%';
+——————————————————–+——–+
| Variable_name | Value |
+——————————————————–+——–+
| performance_schema | ON |
| performance_schema_accounts_size | 100 |
| performance_schema_digests_size | 10000 |
| 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 |
| performance_schema_hosts_size | 100 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 20900 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 100824 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 35000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 12800 |
| performance_schema_max_socket_classes | 10 |
| performance_schema_max_socket_instances | 10020 |
| performance_schema_max_stage_classes | 150 |
| performance_schema_max_statement_classes | 167 |
| performance_schema_max_table_handles | 4000 |
| performance_schema_max_table_instances | 12500 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 10100 |
| performance_schema_session_connect_attrs_size | 512 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
| performance_schema_users_size | 100 |
+——————————————————–+——–+Please see this link for the configuration documentation of each option.
We can also check the memory usage of the current PS by SHOW ENGINE PERFORMANCE_SCHEMA STATUS\G.
Click here for detailed information. If your memory is large enough, you can adjust these parameters appropriately to store more collected information.
You can also enable all instruments at startup through the option performance_schema_instrument= '%=on'.
When statements_digest is enabled, PS will gather SQL statements of the same type in the table events_statements_summary_by_digest together. The data part in SQL statements is replaced by ?, and the blank part is adjusted. Some labels are retained, such as table names and library names. This is somewhat similar to the myawr function we use internally, which aggregates and presents similar SQL statements. In the event table corresponding to the statement, the DIGEST column stores the MD5 value of SQL statements, and DIGEST_TEXT stores the processed SQL statements.
For example, for the following SQL statements:
select * from sbtest where id < 10;
select * from sbtest where id < 20; They will be aggregated into the following records.
SCHEMA_NAME: sbtest
DIGEST: 4c3d9d47ee42d768152f70ee27f8e067
DIGEST_TEXT: SELECT * FROM `sbtest` WHERE `id` < ?
COUNT_STAR: 2
SUM_TIMER_WAIT: 3477357000
MIN_TIMER_WAIT: 340011000
AVG_TIMER_WAIT: 1738678000
MAX_TIMER_WAIT: 3137346000
SUM_LOCK_TIME: 284000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 28
SUM_ROWS_EXAMINED: 28
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 2
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2013-03-29 16:55:01
LAST_SEEN: 2013-03-29 16:55:04The DIEGEST_TEXT column can contain 1,024 records. Additional records will be replaced by the string “…”.
events_statements_current, events_statements_history, and events_statements_history_long record specific SQL statements instead of aggregated results.
The events_statements_summary_by_digest table has a fixed size and is controlled by the performance_schema_digests_size parameter. It can contain 10,000 records by default. When the recording space of the table is full, there is a special column whose SCHEMA_NAME and DIGEST columns are set to NULL. Additional records are added to this special column. If the record counter of the row is obviously high, the size of the table may need to be adjusted.
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.
ApsaraDB - May 24, 2022
Morningking - September 26, 2023
ApsaraDB - August 13, 2024
ApsaraDB - May 24, 2022
Morningking - September 26, 2023
Jack008 - June 10, 2020
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 More
Database for FinTech Solution
Leverage cloud-native database solutions dedicated for FinTech.
Learn MoreMore Posts by ApsaraDB
