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_instruments, setup_objects, setup_consumers, threads, setup_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 1G
*************************** 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 openedAvailable 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 name | Subsystem name | Count |
|---|---|---|
| module_metadata | metadata | 3 |
| module_lock | lock | 16 |
| module_buffer | buffer | 75 |
| module_buf_page | buffer_page_io | 32 |
| module_os | os | 9 |
| module_trx | transaction | 12 |
| module_purge | purge | 7 |
| module_compress | compression | 4 |
| module_file | file_system | 1 |
| module_index | index | 6 |
| module_adaptive_hash | adaptive_hash_index | 8 |
| module_ibuf_system | change_buffer | 8 |
| module_srv | server | 25 |
| module_ddl | ddl | 6 |
| module_dml | dml | 4 |
| module_log | recovery | 15 |
| module_icp | icp | 4 |
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-name, module_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_statustable - InnoDB metrics from the
INFORMATION_SCHEMA.INNODB_METRICStable - Current and total memory allocation based on the
performance_schemamemory 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
runI 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 = OFFExperiment 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' 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 | DEFAULT | 3399.32 | 1.00 | 2.35 | DEFAULT |
| 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.
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.⤴