A naukri.com initiative
Mysql
1M
256
Image Credit: Mysql
MySQL: information_schema.tables performance regression
- Queries against i_s.tables should be fast and scale to a million tables. In MySQL 8.x, queries against data dictionary tables do not scale well in case of a large number of tables in a single schema e.g. 313k tables. MySQL 8.x uses hidden InnoDB tables to store the data dictionary. Since queries against the data dictionary are run very often and often implicit, it is a matter of great concern when the system behaves so poorly in such cases.
- Queries against the DD do not scale well, in my case against 313k tables in a single database. Even explicit queries against information_schema tables or SHOW TABLES LIKE statements are slow and a drop database on a schema with 313k tables would never terminate.
- This issue arises due to the heavy usage of these queries for almost everything, including internally and implicitly. This slow down is even worse when the number of tables is large in a single schema, rendering it a matter of concern.
- Asking for a table in the million schema is slow even when asking for tables from other schemas with only 6 tables is slow. Even command show tables like is affected by the slowdown.
- Queries against i_s.tables should be fast and scale to a million tables. But, not only are explicit queries against I_S.TABLES and SHOW TABLES LIKE statements slower, it's also a matter of great concern when it fails in cases such as
- MySQL 8.0 uses hidden InnoDB tables to store the data dictionary, and queries are not scaled well in cases with a large number of tables in a single schema; this causes concern as queries against DD are run often and often implicit.
- Drop a database on a schema with 313k tables would never terminate. Several queries against I_S.TABLES are simpler than the query plan shows and experience worse performance than anticipated.
- This issue causes a severe slowdown since these queries are run often and often implicit, and not just when they're physically sent to the server.e.g. events processing, and distributed transactions.
- Even queries with table_schema from a schema with only 6 tables are slow. Queries against i_s.tables should scale well to millions of tables, but MySQL 8.x has hidden InnoDB tables to store a data dictionary and causes slow downs.
- Queries against the data dictionary tables in MySQL7 were mostly CPU-bound and started to be more IO-bound from MySQL 8. As a result, heavy-disk activity with long latencies caused performance regression in MySQL8 when more than a few thousand tables exist in a schema.
Read Full Article
15 Likes
For uninterrupted reading, download the app