Description:
In theory, querying information_schema.innodb_tables is not slow when the number of tables is small. But when a table has many partitions, querying innodb_tables may become very very slow.
In my test environment, 2048 partitions took 3 minutes, and 4096 partitions took more than 15 minutes.
How to repeat:
# 1) Create table with 2048 partitions
CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT,
col1 int unsigned NOT NULL,
col2 tinyint(1) NOT NULL,
t1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
t2 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id,col1),
KEY ind1 (col1,col2,t1),
KEY ind2 (t2)
) ENGINE=InnoDB /*!50100 PARTITION BY HASH (col1) PARTITIONS 2048 */
# 2) Restart MySQL-server (It is necessary!!!)
# 3) Execute query. It is very slow.
select count(*) from information_schema.innodb_tables;
# 4) Show create table
show create table test.t;
# 5) Execute query again, it is fast now.
select count(*) from information_schema.innodb_tables;
Suggested fix:
I did some simple analysis:
Before the table is cached, querying innodb_tables will open the table row by row according to the DD table records, build metadata, etc., and the table will still not be cached after the query is completed.
Since all partition tables belong to the same table object, the same table is opened repeatedly, which is unnecessary.
Why does the query time of `select count(*) from information_schema.innodb_tables;` statement increase with the number of partitions? First of all, n partitions need to read and build n dd::table objects, and each dd::table object also needs to build n dd::partition objects, that is, n*n DD partitions need to be built, which is exponential.
Perf message:
+ 63.95% 0.00% connection mysqld [.] do_fill_information_schema_table
+ 63.95% 0.00% connection mysqld [.] i_s_innodb_tables_fill_table
+ 63.95% 0.00% connection mysqld [.] dd_process_dd_partitions_rec_and_mtr_commit
+ 63.95% 0.00% connection mysqld [.] dd_table_open_on_id
+ 63.95% 0.10% connection mysqld [.] dd_table_open_on_id_low
+ 57.90% 0.00% connection mysqld [.] dd::cache::Dictionary_client::get_table_name_by_partition_se_private_i
+ 57.90% 0.00% connection mysqld [.] dd::cache::Dictionary_client::acquire_uncached_table_by_partition_se_p
+ 57.77% 0.00% connection mysqld [.] dd::cache::Dictionary_client::acquire_uncached<dd::Table>
+ 57.74% 0.00% connection mysqld [.] dd::cache::Storage_adapter::get<dd::Primary_id_key, dd::Abstract_table
+ 57.74% 0.00% connection mysqld [.] dd::Entity_object_table_impl::restore_object_from_record
+ 57.74% 0.00% connection mysqld [.] dd::Table_impl::restore_children
+ 57.30% 0.07% connection mysqld [.] dd::Collection<dd::Partition*>::restore_items<dd::Table_impl, dd::Part
+ 38.41% 0.10% connection mysqld [.] dd::Partition_impl::restore_children
+ 34.24% 0.13% connection mysqld [.] dd::Collection<dd::Partition_index*>::restore_items<dd::Partition_impl
+ 23.40% 0.03% connection mysqld [.] dd::Properties_impl::insert_values
