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

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

Part 2 of this 3-part series explains the naming conventions and state variables of Performance Schema (PS).

This article was written by Zhai Weixiang.

Naming Conventions of PS Instrument

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.

1.1 Idle

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)

1.2 Stage

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)

1.3 Statement

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.

1.4 Wait

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.

  • wait/io

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.

  • wait/lock

Only the lock operation wait/lock/table/sql/handler on tables is included.

  • wait/synch

There are many synch objects, including condition variables (wait/synch/cond), mutex (wait/synch/mutex), and readers–writer lock (wait/synch/rwlock).

PS State Variable

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

STATEMENT_DIGEST

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

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

0 0 0
Share on

ApsaraDB

182 posts | 14 followers

You may also like

Comments

ApsaraDB

182 posts | 14 followers

Related Products