Community

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

[MySQL 5.6] Configuration Items of Performance Schema

Part 1 of this 3-part series discusses the configuration items of Performance Schema (PS).

This article was written by Zhai Weixiang.

Performance Schema (PS) has appeared in 5.5, but it has not been used. Compared with 5.6, the number of PS tables in 5.5 is much less. I will share my notes in reading the PS official documents in the following section.

Please see this link for the official documents.

Contents

  1. Enable PS
  2. Configure PS

2.1 setup_timers Table Determines the Timer Types Used by Different Instruments

2.2 setup_instruments

2.3 setup_consumers Table Lists the Consumer Types of Event Information

2.4 setup_objects

2.5 setup_actors

1. Enable PS

Note: Enabling PS has a performance overhead. In a performance testing, I compared the internal version of Alibaba Percona Server 5.5.18 with the official MySQL 5.6.10. I found that under the same pressure, the 5.6 version has significantly higher CPU overhead (about 10~20% higher).

Check:
Compiling phase: -DWITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON
It is ON by default. It can be set to OFF to disable PS during compilation.

You can also disable the option performance_schema when starting mysqld.

If you see errors (such as PS table structure is incorrect or PS table cannot be found) in an error log, you can execute mysql_upgrade after enabling the instance.

[ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure [ERROR] Native table 'performance_schema'.'events_waits_history_long'

has the wrong structure

2. Configure PS

You can configure PS at run time by configuring setup tables, including the following tables.

mysql> show tables like '%setup%';
+—————————————-+
| Tables_in_performance_schema (%setup%) |
+—————————————-+
| setup_actors                           |
| setup_consumers                        |
| setup_instruments                      |
| setup_objects                          |
| setup_timers                           |
+—————————————-+
5 rows in set (0.00 sec)

The count settings for events have two related tables.

performance_timers lists available timers and their characteristics.

mysql> SELECT * FROM performance_timers;
+————-+—————–+——————+—————-+
| TIMER_NAME  | TIMER_FREQUENCY | TIMER_RESOLUTION | TIMER_OVERHEAD |
+————-+—————–+——————+—————-+
| CYCLE       |      2490706467 |                1 |             38 |
| NANOSECOND  |      1000000000 |                1 |            128 |
| MICROSECOND |         1000000 |                1 |            135 |
| MILLISECOND |            1036 |                1 |            150 |
| TICK        |             103 |                1 |            450 |
+————-+—————–+——————+—————-+

The CYCLE depends on the CPU cycle counter to determine the timer.

TIMER_FREQUENCY represents the number of counts per second, which is related to the speed of the CPU and the CYCLE type.

TICK depends on different platforms. For example, there are 103 ticks per second on my machine. Tick represents the time interval for each timer interrupt. Please see this link for more information.

TIMER_RESOLUTION represents the unit that increases the count each time. If it is ten, it means the value is increased by ten each time.

TIMER_OVERHEAD: The minimal number of cycles of overhead to obtain one timing with the given timer

2.1 setup_timers Table Determines the Timer Types Used by Different Instruments

mysql> SELECT * FROM setup_timers;
+———–+————-+
| NAME      | TIMER_NAME  |
+———–+————-+
| idle      | MICROSECOND |
| wait      | CYCLE       |
| stage     | NANOSECOND  | 
| statement | NANOSECOND  |
+———–+————-+

setup_timers can determine the timer type for each instrument. The timer must come from the performance_timers table and can be updated through the update statement.

The most important thing is to reduce OVERHEAD for the wait type. So CYCLE type is the best choice, but its cost is reduced timing accuracy.

The overall execution time of a statement or stage is one order of magnitude higher than the wait. The most important principle to time the statement is accurate measurement and no impact from the processor frequency. The default frequency is NANOSECOND, and its extra "OVERHEAD" is not clear compared with CYCLE TIMER. This is because the overhead of calling a timer twice (one at the start of the statement and the other at the end) is many orders of magnitude smaller than the CPU time of the statement execution itself. The usage of CYCLE only brings problems.

The accuracy of cycle counters depends on the speed of the CPU. The overhead of using CYCLE counters is lower than f using standard gettimeofday, which can generate hundreds of cycles in one call.

The modification on the setup_timers table takes effect immediately. Therefore, two different timers may be used at the beginning and end of an event.

2.2 setup_instruments

The table setup_instruments contains the instrument corresponding to the four types above (idle, wait, stage, and statement). Object can update the ENABLED and TIMED columns to determine whether to collect the corresponding event information.

mysql> select count(*) from  setup_instruments;
+———-+
| count(*) |
+———-+
|      545 |
+———-+
1 row in set (0.00 sec)


mysql> desc setup_instruments;
+———+——————+——+—–+———+——-+
| Field   | Type             | Null | Key | Default | Extra |
+———+——————+——+—–+———+——-+
| NAME    | varchar(128)     | NO   |     | NULL    |       |
| ENABLED | enum('YES','NO') | NO   |     | NULL    |       |
| TIMED   | enum('YES','NO') | NO   |     | NULL    |       |
+———+——————+——+—–+———+——-+

Currently, the 5.6.10 version has 545 instruments for configuration. The ENABLED column indicates whether the event is collected for the instrument, and the TIMED column indicates whether to time the instrument. If the value of the TIMED column is disabled, TIMER_START, TIMER_END, and TIMER_WAIT values will not be generated for the corresponding event.

Statistics of events are in nanoseconds, regardless of the timer used. This aims to adopt a unified time unit.

2.3 setup_consumers Table Lists the Consumer Types of Event Information

mysql> SELECT * FROM setup_consumers;
+——————————–+———+
| NAME                           | ENABLED |
+——————————–+———+
| events_stages_current          | YES     |
| events_stages_history          | YES     |
| events_stages_history_long     | YES     |
| events_statements_current      | YES     |
| events_statements_history      | YES     |
| events_statements_history_long | YES     |
| events_waits_current           | YES     |
| events_waits_history           | YES     |
| events_waits_history_long      | YES     |
| global_instrumentation         | YES     |
| thread_instrumentation         | YES     |
| statements_digest              | YES     |
+——————————–+———+
12 rows in set (0.00 sec)

If you do not care for the consumer, you can disable it so the server does not spend time maintaining it. For example, if you do not want to use historical event statistics, you can disable several historical events. Consumers are mainly of the following types.

Global and Thread Consumers

  1. global_instrumentation is the highest-level consumer. If it is set to NO, the global instrumentation will be disabled, and other consumers will be ignored, regardless of whether they are set to YES or NO. When global_instrumentation is set to YES, it will maintain the global state and check thread_instrumentation.

If only global_instrumentation is set to YES while other consumers are set to NO, the global status tables that get maintained include the following tables:

  • mutex_instances
  • rwlock_instances
  • cond_instances
  • file_instances
  • file_summary_by_instance
  • file_summary_by_event_name
  • objects_summary_global_by_type
  • table_lock_waits_summary_by_table
  • table_io_waits_summary_by_index_usage
  • table_io_waits_summary_by_table
  • events_waits_summary_by_instance
  • events_waits_summary_global_by_event_name
  1. Only when global_instrumentation is set to YES will thread_instrumentation be checked. If thread_instrumentation is set to NO, it will prohibit thread-level or independent events from collecting information. If it is set to YES, thread-level information will be maintained, and events_xxx_current consumer will also be checked.

Tables corresponding to thread-level information include: events_waits_summary_by_thread_by_event_name

Statement Digest Consumer

It is necessary to set global_instrumentation to YES. Otherwise, statements_digest will be ignored. It does not rely on Statement Event consumer, which means you can get statistics in each digest without collecting information in events_statements_current. This helps reduce overhead.

Wait Event Consumers

These consumers require global_instrumentation and thread_instrumentation to be set to YES at the same time. They include the following consumers.

  1. events_waits_current. If it is set to NO, independent wait events for the events_waits_current table will not be collected. If it is set to YES, the information collection of the events_waits_current table is enabled, and two consumers events_waits_history and events_waits_history_long will be checked.
  2. events_waits_history. The premise is that events_waits_current is set to YES. This consumer is used to determine whether the events_waits_history table collects information.
  3. events_waits_history. The premise is that events_waits_current is set to YES. This consumer is used to determine whether the events_waits_history_long table collects information.

Stage Event Consumers

These consumers require global_instrumentation and thread_instrumentation to be set to YES at the same time. They include the following consumers.

The hierarchical relationship is similar to Wait Event Consumer.

  • events_stages_current, corresponding to the events_stages_current table
  • events_stages_history, corresponding to the events_stages_history table
  • events_stages_history_long, corresponding to the events_stages_history_long table

Statement Event Consumers

  • events_statements_current, corresponding to the events_statements_current table, etc.
  • events_statements_history, corresponding to the events_stages_history table
  • events_statements_history_long, corresponding to the events_statements_history_long table

In summary, the consumer level structure is:

global_instrumentation

    |–thread_instrumentation
       |–events_waits_current
          |–events_waits_history
          |–events_waits_history_long
       |–events_stages_current
          |–events_stages_history
          |–events_stages_history_long
       |–events_statements_current
          |–events_statements_history
          |–events_statements_history_long
    |– statements_digest

The high-level consumers decide whether to check the low-level consumers.

2.4 setup_objects

setup_objects is used to determine which objects can be monitored. Currently, only table objects can be controlled. A maximum of 100 rows of records can be inserted into the table by default, but you can resize the table through the parameter performance_schema_setup_objects_size.

By default, the data in this table include:

mysql> select * from setup_objects;
+————-+——————–+————-+———+——-+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME | ENABLED | TIMED |
+————-+——————–+————-+———+——-+
| TABLE       | mysql              | %           | NO      | NO    |
| TABLE       | performance_schema | %           | NO      | NO    |
| TABLE       | information_schema | %           | NO      | NO    |
| TABLE       | %                  | %           | YES     | YES   |
+————-+——————–+————-+———+——-+

The monitored table objects exclude tables in the MySQL/PS/IS databases by default. Tables in the IS database are not monitored, regardless of whether they are enabled. According to setup_objects and setup_instruments, PS determines whether to enable an instrument and time it. Table objects in setup_objects must be enabled in both tables to collect event information. If timing is required, both TIEMD columns need to be set to YES.

2.5 setup_actors

setup_actors is used to determine the initial monitoring state of the new frontend thread. It includes all users by default.

mysql> select * from  setup_actors;
+——+——+——+
| HOST | USER | ROLE |
+——+——+——+
| %    | %    | %    |
+——+——+——+
1 row in set (0.00 sec)

The records in this table can determine which user threads need to be monitored. The threads table records the states of all frontend/backend threads (somewhat similar to the PROCESSLIST table) and whether they are monitored. thread_instrumentation in the setup_consumers table needs to be enabled to make sure the threads table is effective.

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 Configuration Item
0 0 0
Share on

Read previous post:

Details of the Architecture of MySQL 8.0 Server Layer

Read next post:

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

ApsaraDB

445 posts | 94 followers

You may also like

Comments

ApsaraDB

445 posts | 94 followers

Related Products

More Posts by ApsaraDB

See All