Impact of Querying Table Information From information_schema

On MySQL and Percona Server for MySQL, there is a schema called information_schema (I_S) which provides information about database tables, views, indexes, and more.

A lot of useful information can be retrieved from this schema, for example, table metadata and foreign key relations, but trying to query I_S can induce performance degradation if your server is under heavy load, as shown in the following example test.

Disclaimer: This blog post is meant to show a less-known problem but is not meant to be a serious benchmark. The percentage in degradation will vary depending on many factors {hardware, workload, number of tables, configuration, etc.}.

The test compares a baseline of how the server behaves while “under heavy load but no queries against I_S” vs. ” under heavy load + I_S queries” to check for performance degradation.

The same tests have been executed in Percona Server for MySQL 5.7 and 8.0. The queries executed against I_S to check performance degradation checks information about some table FKs (foreign keys) relationships.

Setup

The setup consists of creating 10K tables with sysbench and adding 20 FKs to 20 tables.

Hardware

Number of CPUs: 12

Memory size: 12288 MB

Main Percona Server for MySQL configuration variables tuned in my.cnf:

You can review the full my.cnf file here: https://github.com/ctutte/blog_IS/blob/master/my.cnf

Executed queries

To generate the database setup, I used sysbench with the following flags:

Then manually created 20 FKs:

After the scenario was set up, I executed three runs of sysbench with the following query:

To query against I_S, I used the following bash command line, which checks FK in a while loop from bash:

Considering that the server has 12 vCPUs and sysbench is using 10, then there is some spare CPU capacity for running the queries against I_S, right? Let’s see the results.

Results for Percona Server for MySQL 5.7

Three runs of 60 seconds and 1000 tables:

It can be seen that only running sysbench, 46320 queries per second, could be executed on the server.

In the scenario where I checked sysbench + I_S queries to check FKs continuously, 38472 queries per second were executed. That is (46320 – 38472) * 100 / 46320 = 16.9% performance degradation.

If the I_S queries were CPU bound, then I would expect the server to be able to do more than 46320 QPS, but something is going on, and actually, the amount of QPS did go down.

The reason for this performance degradation is that querying against I_S will need to open tables frm files (limited by table_open_cache variable).

In my example test, when running only sysbench, the workload shows few Table_open_cache_misses (the first column is initial value, each successive value is delta increase per second).

Table_open_cache_misses while NOT checking for FKs:

Whereas when running sysbench + I_S queries, there is a greater number of Table_open_cache_misses due to MySQL/Percona Server for MySQL 5.7 having to open each table.frm (and in which my test runs, I have purposely read a very high number of tables compared to “Table-open-cache” variable).

Table_open_cache_misses while checking for FKs:

The above outputs were generated using pt-stalk + pt-mext from Percona Toolkit.

This can also be seen using Percona Monitoring and Management (PMM) and checking the “MySQL overview” dashboard ->“MySQL table open cache status” graphic.

When running only sysbench, there is a high number of cache hits (~99.98%) and a low number of “misses,” which means the table_cache is big enough for the workload.

Whereas while running sysbench + I_S queries, there is a decrease in the number of cache hits at ~15:33:40 when FK checks executed until ~15:35:00, which also shows an increase in (table) “Openings” and “Misses” for the duration of the test.

Performance degradation when querying against I_S can be minimized if:

• table_open_cache was increased
• using a “WHERE” condition on certain columns to avoid a datadir/database folder scan. You can read some optimization examples in the official doc: https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html .

Results for Percona Server for MySQL 8.0

For MySQL and Percona Server for MySQL 8, executing sysbench + I_S queries shows there almost no cache misses, as can be seen in the following snapshot:

MySQL 8 improved data dictionary access, which avoids having to open all tables .frm files. This improvement has been ported to Percona Server for MySQL 8.

Conclusion

In conclusion, checking table information from 5.7 I_S relies on opening .frm files from disk to retrieve table information, which can cause a performance drop when the amount of opened tables does not fit in the table_cache.

If you rely heavily on queries against information_schema, then your workload will benefit from upgrading to MySQL/Percona Server for MySQL 8 because of the new data dictionary format. While you are on 5.7, you can also consider increasing table_open_cache to avoid table_misses or at least use some filters on the “WHERE” clause to avoid opening all .frm files and limit the query to a subset of the tables for a limited performance impact.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

Try Percona Distribution for MySQL today!

Subscribe

Connect with

Login

Label

0 Comments