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
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
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/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,
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 (
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
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:04
DIEGEST_TEXT column can contain 1,024 records. Additional records will be replaced by the string “…”.
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
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 - May 24, 2022
Morningking - September 26, 2023
Jack008 - June 10, 2020
Alibaba Cloud Community - April 20, 2022
ApsaraDB: Faster, Stronger, More SecureLearn More
AnalyticDB for MySQL is a real-time data warehousing service that can process petabytes of data with high concurrency and low latency.Learn More
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
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
More Posts by ApsaraDB