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).
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.
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_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_consumers tables to enable the monitoring of certain instruments or consumers.
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, 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
setup_instruments table has
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 | +-----------------------+---------+
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
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
Here is a sample of an
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
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 name||Subsystem name||Count|
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:
pattern (string expression) or
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;
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
- InnoDB metrics from the
- Current and total memory allocation based on the
Please see the sys-metrics document for more information.
The following shows a summary of the metrics view by each category (the
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_value. It doesn’t add much value only to have partial information. Here, I’ll focus more on the
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.
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
Using the options above, I ran a series of experiments to compare transactions per second (TPS) of different combinations of all
TIMED columns of instruments/consumers of the
innodb_metrics versus default setting. The results are shown in the table below.
|SETUP_INSTRUMENTS ALL ‘ENABLED’ COLUMN||SETUP_INSTRUMENTS ALL ‘TIMED’ COLUMN||ALL INNODB_METRICS||TPS||TPS ratio vs DEFAULT||AVERAGE LATENCY||COMMENT|
|NO||NO||DISABLED||3506.25||1.031||2.28||All DISABLED for the performance_schema and the innodb_metrics|
|NO||NO||DEFAULT||3496.40||1.029||2.29||All DISABLED for the instruments of the performance_schema|
|DEFAULT||DEFAULT||DISABLED||3391.85||0.998||2.36||All DISABLED for the innodb_metrics|
|DEFAULT||DEFAULT||ENABLED||3389.69||0.997||2.36||All ENABLED for the innodb_metrics|
|YES||DEFAULT||DEFAULT||2906.15||0.855||2.75||All ENABLED for the instruments of the performance_schema|
|YES||YES||DEFAULT||2837.44||0.835||2.82||All ENABLED for the instruments of the performance_schema including TIMED|
|YES||DEFAULT||ENABLED||2908.34||0.856||2.75||All ENABLED for the performance_schema and the innodb_metrics|
|YES||YES||ENABLED||2825.31||0.831||2.83||All 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.
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.
 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.⤴
 The default object configuration is to instrument all tables except those in the MySQL,
information_schema databases. You can find this information in the