Performance impact of MySQL performance-schema-instruments

Hi, there! I’m Otsuka (tom__bo), mainly responsible for MySQL operations and development of various DB administration and verification tools. In this post, I am going to write about my experiment on MySQL performance when its performance consumers, namely the performance_schema database and the innodb_metrics table of the information_schema database, are up and running. 

I got to learn about the performance_schema and the INFORMATION_SCHEMA.INNODB_METRICS from a book called 詳解MySQL5.7 (Japanese)[1].

Database and table for performance monitoring 

Usually when you want to get performance information on MySQL, you use the Performance Schema instruments or the INFORMATION_SCHEMA.INNODB_METRICS table (or the sys.metrics view with a summary of performance tables). With this performance information, you can understand the internal execution of MySQL, especially how many events are produced and how much time they take in the InnoDB storage engine. There is always a cost for performance monitoring — in other words, overhead. I ran a simple test to measure such overhead.

Before going into the experiment results, let me briefly explain how to use the performance_schema database and the INNODB_METRICS table. MySQL version for this experiment is MySQL Community 5.7.22.

performance_schema table

The MySQL Performance Schema would probably come to mind first when it comes to monitoring MySQL performance. The Performance Schema collects data such as how many times instrumented activities are performed and how much time they take. Such event data are collected using “instrumentation points” placed here and there in MySQL source code. Some of the frequently used instrumentation points for users are enabled by default, providing available data without additional configuration. If you want to enable specific instrumentation points, you need to change the configurations. See Performance Schema Startup Configuration for more information.

Two main components of the Performance Schema structure are:

  • instruments that collect data and 
  • consumers that store collected data. 

The Performance Schema setup tables including setup_instrumentssetup_objectssetup_consumersthreadssetup_actors and setup_timers allow you to define configurations for the corresponding record (i.e. how and what data are collected).  

By default, objects (i.e. database), threads, and actors (i.e. client users) are enabled for monitoring. For instruments and consumers, you can use setup_instruments and setup_consumers tables to enable the monitoring of certain instruments or consumers.[2]

 Available data

Let’s take a look at what performance data categories can be collected from instruments. You can check the list of categories with the performance_schema.setup_instruments table. As shown below, in MySQL 5.7.22 version, there are a total of 1,032 instruments and the category is distinguished using /.  The list of the top-level categories represented by the first / is as follows:

select substring_index(name, '/', 1) as category, count(*) from setup_instruments group by substring_index(name, '/', 1);
+-------------+----------+
| category    | count(*) |
+-------------+----------+
| idle        |        1 |
| memory      |      379 |
| stage       |      132 |
| statement   |      193 |
| transaction |        1 |
| wait        |      326 |
+-------------+----------+
6 rows in set (0.00 sec)

The List 5.2 on page 188 of the book, 詳解MySQL5.7[1], shows that the number of instruments increased except for the categories idle and transaction. For MySQL 8.0.13 version, the error category was added with the total number of instruments going up to 1,217. 

How to use performance_schema table

The setup_instruments table has ENABLED and TIMED columns for modification while the setup_consumers table has ENABLED column for modification. If you have the UPDATE privilege, you can change the value of these columns to YES for enabling or NO for disabling.

When the value of the TIMED column of the setup_instruments table is set to YES, the instrument will be timed. For memory instruments, the TIMED column in the setup_instruments table is ignored because memory operations are not timed. In other words, when the TIMED column is set to YES for memory instruments, the query will be successful but the record value will say NO.

You can refer to the following samples of each table.

select * from setup_instruments limit 1;
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO      | NO    |
+-------------------------------------------+---------+-------+
 
select * from setup_consumers limit 1;
+-----------------------+---------+
| NAME                  | ENABLED |
+-----------------------+---------+
| events_stages_current | NO      |
+-----------------------+---------+

INFORMATION_SCHEMA.INNODB_METRICS

The InnoDB storage engine information collected by innodb_monitor is stored in the INNODB_METRICS table. You can collect information that is not listed by performance_schema and SHOW ENGINE INNODB STATUS. The INNODB_METRICS table has a NAME column for a unique name for the counter, SUBSYSTEM column for the category of a counter and other counters for performance diagnosis. You can find a description for each counter in the COMMENT column.

Here is a sample of an innodb_metrics table. 

select * from innodb_metrics limit 1\G
*************************** 1. row ***************************
           NAME: metadata_table_handles_opened
      SUBSYSTEM: metadata
          COUNT: 0
      MAX_COUNT: NULL
      MIN_COUNT: NULL
      AVG_COUNT: NULL
    COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: NULL
  TIME_DISABLED: NULL
   TIME_ELAPSED: NULL
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: counter
        COMMENT: Number of table handles opened

Available data

MySQL 5.7.22 has a total of 235 counters, grouped into 17 subsystems. As explained above, each subsystem allows you to collect statistical data for each counter such as status, monitoring results and aggregated data. 

The following table shows the number of counters for each subsystem. You can see a Module Name column in the table below. Module names can be used to enable, disable, or reset all counters for a particular subsystem. I don’t understand all the details, but you need to specify a module name when you want to enable counters at the subsystem level. 

Please refer to the following references for more information.

Module nameSubsystem nameCount
module_metadatametadata3
module_locklock16
module_bufferbuffer75
module_buf_pagebuffer_page_io32
module_osos9
module_trxtransaction12
module_purgepurge7
module_compresscompression4
module_filefile_system1
module_indexindex6
module_adaptive_hashadaptive_hash_index8
module_ibuf_systemchange_buffer8
module_srvserver25
module_ddlddl6
module_dmldml4
module_logrecovery15
module_icpicp4

How to use

You can enable, disable, and reset counters using the following variables. And, you can use one of the following configuration options to assign a counter: counter-namemodule_name (subsystem), pattern (string expression) or all

SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];
SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];

For instance, you can enable the index subsystem with the following query.

SET GLOBAL innodb_monitor_enable = module_index;

SYS.METRICS VIEW

MySQL 5.7.7 and higher versions include the sys schema, which provides a view that summarizes the following information: 

  • Global status variables from the performance_schema.global_status table 
  • InnoDB metrics from the INFORMATION_SCHEMA.INNODB_METRICS table
  • Current and total memory allocation based on the performance_schema memory instrumentation 

Please see the sys-metrics document for more information. 

The following shows a summary of the metrics view by each category (the type column). 

select type, count(*) from sys.metrics group by type;
+--------------------------------------+----------+
| type                                 | count(*) |
+--------------------------------------+----------+
| Global Status                        |      221 |
| InnoDB Metrics - adaptive_hash_index |        8 |
| InnoDB Metrics - buffer              |       57 |
| InnoDB Metrics - buffer_page_io      |       32 |
| InnoDB Metrics - change_buffer       |        8 |
| InnoDB Metrics - compression         |        4 |
| InnoDB Metrics - ddl                 |        6 |
| InnoDB Metrics - dml                 |        4 |
| InnoDB Metrics - icp                 |        4 |
| InnoDB Metrics - index               |        6 |
| InnoDB Metrics - lock                |       12 |
| InnoDB Metrics - metadata            |        3 |
| InnoDB Metrics - os                  |        5 |
| InnoDB Metrics - purge               |        7 |
| InnoDB Metrics - recovery            |       12 |
| InnoDB Metrics - server              |       22 |
| InnoDB Metrics - transaction         |       12 |
| Performance Schema                   |        2 |
| System Time                          |        2 |
+--------------------------------------+----------+
19 rows in set (0.01 sec)

Note that the numbers for “InnoDB Metrics – …” in the table above are equal to or smaller than the numbers from the INNODB_METRICS table. The metrics View provides an easy access to ballpark figures, but it is limited to Variable_name and Variable_value. It doesn’t add much value only to have partial information. Here, I’ll focus more on the INFORMATION_SCHEMA.INNODB_METRICS table.

MyExperiment

Setup

Here is the environment I have setup for my experiment.

  • MySQL: 5.7.22 (community)
  • buffer_pool_size: 10 GB

Configuration details and hardware information that do not have a material impact on performance monitoring are not provided here. 

Experiment details

I set up the sysbench oltp_read_write scenario with the following options and ran the benchmark to compare the performance of each configuration based on the number of transactions (i.e. TPS, Transactions Per Second). The size of the database, provided with the prepare command of sysbench, was approx. 2.4 GB. I ran the oltp_read_write scenario as follows: 

sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=1000000 \
 --tables=10 \
 --mysql-host=xxx \
 --mysql-port=3306 \
 --mysql-user=sysbench \
 --mysql-password=sysbench \
 --mysql-db=sysbench \
 --time=600 \
 --threads=10 \
 run

I added the following configurations to my.cnf file and reran mysqld not to read buffer_pool in subsequent rounds.

innodb_buffer_pool_dump_at_shutdown = OFF
innodb_buffer_pool_load_at_startup  = OFF

Experiment Results

Using the options above, I ran a series of experiments to compare transactions per second (TPS) of different combinations of all ENABLED and TIMED columns of instruments/consumers of the performance_schema and innodb_metrics versus default setting. The results are shown in the table below.

SETUP_INSTRUMENTS ALL ‘ENABLED’ COLUMNSETUP_INSTRUMENTS ALL ‘TIMED’ COLUMNALL INNODB_METRICSTPSTPS ratio vs DEFAULTAVERAGE LATENCYCOMMENT
NONODISABLED3506.251.0312.28All DISABLED for the performance_schema and the innodb_metrics
NONODEFAULT3496.401.0292.29All DISABLED for the instruments of the performance_schema
DEFAULTDEFAULTDISABLED3391.850.9982.36All DISABLED for the innodb_metrics
DEFAULTDEFAULTDEFAULT3399.321.002.35DEFAULT
DEFAULTDEFAULTENABLED3389.690.9972.36All ENABLED for the innodb_metrics
YESDEFAULTDEFAULT2906.150.8552.75All ENABLED for the instruments of the performance_schema
YESYESDEFAULT2837.440.8352.82All ENABLED for the instruments of the performance_schema including TIMED
YESDEFAULTENABLED2908.340.8562.75All ENABLED for the performance_schema and the innodb_metrics
YESYESENABLED2825.310.8312.83All ENABLED for the performance_schema and the innodb_metrics including TIMED

Let’s analyze the results for each scenario, using TPS and TPS ratio vs DEFAULT. It is not likely that instruments would be disabled by default in the real world, but it sure speeds up the performance. The innodb_metrics configurations do not affect TPS much regardless of their status, ON or OFF. On the other hand, turning on all instruments of the performance_schema caused TPS to drop by circa. 15% although enabling or disabling all instruments would rarely happen. I admit this experiment was conducted with a simple scenario such as sysbench’s oltp_read_write scenario and under a temporary environment with the buffer_pool overflow with DB data. Nevertheless, these experiment results indicate it can be more efficient to avoid enabling all instruments of the performance_schema and take a selective approach while the innodb_metrics has little impact on performance. You can make choices as you go since these values can be changed at runtime.

Conclusion

I explained about Whats and Hows about the Performance Schema and the information_schema.innodb_metrics table and shared the benchmarking results under the sysbench’s oltp_read_write scenario for different combinations of disabling or enabling all values. When all things are enabled, performance dropped by about 15%. Like I said earlier, this experiment was based on a simple scenario (query) without significant overhead. So, in the real world, you need to set your configurations smartly, considering your situation. I hope my little experiment here can be your guiding light when you need to make a choice.


[1] Mikiya Okuno, MySQL 5.7 Elaborated: Technical Guide to Catch up with Ongoing Evolution, (Japan: Shoeisha, 2010). See page 5 for further details regarding this posting.

[2] The default object configuration is to instrument all tables except those in the MySQL, performance_schema and information_schema databases. You can find this information in the setup_objects table.