# Query slowness in Delete query with many child table in Mysql 8 **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Tags:** mysql\ **Created:** [December 20, 2023, 4:15pm UTC](https://forums.percona.com/t/query-slowness-in-delete-query-with-many-child-table-in-mysql-8/27285 "2023-12-20T16:15:34Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![tamil\_maran](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tamil_maran/32/14292_2.png) [@tamil\_maran](https://forums.percona.com/u/tamil_maran)\ **Post date:** [December 20, 2023, 4:15pm UTC](https://forums.percona.com/t/query-slowness-in-delete-query-with-many-child-table-in-mysql-8/27285/1 "2023-12-20T16:15:34Z")
When i planned to migrate from mysql 5.7 to 8, facing slowness in Delete query for a table which has many child tables. Even the explain query for that delete query itself slow. It is consistently reproducing. Same issue didn’t occur in Mysql 5.7.30. ```auto mysql> delete from parent_table_2 where id =1; Query OK, 0 rows affected (4.45 sec) mysql> explain delete from parent_table_2 where id =1; +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | DELETE | parent_table_2 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (3.28 sec) ``` Creating a parent with 10000 direct child table with FK ON DELETE CONSTRAINT. ```auto CREATE TABLE parent_table_2 ( id INT PRIMARY KEY, name VARCHAR(255) ); ``` Using a shell script created 10k child tables for that table. ```auto #!/bin/bash MYSQL_USER="your_username" MYSQL_PASSWORD="your_password" MYSQL_DATABASE="your_database" for ((i=1; i<=10000; i++)); do TABLE_NAME="table_child_FK_WITH_ON_DELETE_$i" QUERY="CREATE TABLE IF NOT EXISTS $TABLE_NAME ( id INT PRIMARY KEY, column1 INT, column2 VARCHAR(255), FOREIGN KEY (column1) REFERENCES parent_table_2(id) ON DELETE CASCADE );" mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" -e "$QUERY" done ``` parent\_table\_2 has no data at all. Even though delete query fired to this table is facing slowness. ```auto mysql> delete from parent_table_2 where id =1; Query OK, 0 rows affected (4.45 sec) +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000119 | | Executing hook on transaction | 0.000010 | | starting | 0.000010 | | checking permissions | 0.000007 | | Opening tables | 1.876346 | | init | 0.000048 | | System lock | 0.000092 | | updating | 0.000035 | | end | 0.000005 | | query end | 0.000004 | | waiting for handler commit | 0.000065 | | Waiting for semi-sync ACK from | 0.000511 | | waiting for handler commit | 0.000015 | | closing tables | 2.571113 | | freeing items | 0.000098 | | logging slow query | 0.000056 | | cleaning up | 0.001471 | +--------------------------------+----------+ ``` Query is taking time in Opening tables and closing tables. From mysql documentation Opening tables The thread is trying to open a table. This is should be a very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table\_open\_cache value is large enough. closing tables The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use. Checked both: table\_open\_cache is 524288 and Disk usage usage very minimal. When tried explain query for that delete, it is also slow. ```auto mysql> explain delete from parent_table_2 where id =1; +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | DELETE | parent_table_2 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (2.77 sec) +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000123 | | Executing hook on transaction | 0.000020 | | starting | 0.000016 | | checking permissions | 0.000010 | | Opening tables | 1.616061 | | init | 0.000047 | | System lock | 0.000057 | | end | 0.000005 | | query end | 0.000004 | | waiting for handler commit | 0.000007 | | closing tables | 1.160547 | | freeing items | 0.000085 | | cleaning up | 0.000666 | +--------------------------------+----------+ ``` When checked other mysql show status parameters, i can find increase in Opened\_table\_definitions. ```auto mysql> show status like "table_open_cache%"; show status like "open_table%"; show status like "opened_table%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Table_open_cache_hits | 0 | | Table_open_cache_misses | 0 | | Table_open_cache_overflows | 0 | +----------------------------+-------+ 3 rows in set (0.01 sec) +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | Open_table_definitions | 15659 | | Open_tables | 128380 | +------------------------+--------+ 2 rows in set (0.00 sec) +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Opened_table_definitions | 0 | | Opened_tables | 0 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> explain delete from parent_table_2 where id =1; +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | DELETE | parent_table_2 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+----------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (3.28 sec) mysql> show status like "table_open_cache%"; show status like "open_table%"; show status like "opened_table%"; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | Table_open_cache_hits | 179997 | | Table_open_cache_misses | 1 | | Table_open_cache_overflows | 0 | +----------------------------+--------+ 3 rows in set (0.00 sec) +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | Open_table_definitions | 15659 | | Open_tables | 128381 | +------------------------+--------+ 2 rows in set (0.00 sec) +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Opened_table_definitions | 9999 | | Opened_tables | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) ``` In Mysql 5.7.30 this issue is not occurring and also there is no increase in Opened\_table\_definitions. ---
**Author:** ![yves.trudeau](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/yves.trudeau/32/2825_2.png) [@yves.trudeau](https://forums.percona.com/u/yves.trudeau)\ **Post date:** [December 21, 2023, 3:58pm UTC](https://forums.percona.com/t/query-slowness-in-delete-query-with-many-child-table-in-mysql-8/27285/2 "2023-12-21T15:58:42Z")
Hi, with MySQL 5.7.x, the table definition was stored primarily in the frm files and the OS has the opportunity to cache those file for faster access. With 8.0, all that is internal now. Essentially in this cache, the table\_definition\_cache must be large enough to contains _all_ the tables. See below after I increased table\_definition\_cache from 2000 to 12000 on a 8.0.33 database prepared with your script. ```auto mysql> set global table_definition_cache=12000; Query OK, 0 rows affected (0.00 sec) mysql> delete from parent_table_2 where id =1;delete from parent_table_2 where id =1;delete from parent_table_2 where id =1;delete from parent_table_2 where i d =1;delete from parent_table_2 where id =1; Query OK, 0 rows affected (2.71 sec) Query OK, 0 rows affected (0.18 sec) Query OK, 0 rows affected (0.13 sec) Query OK, 0 rows affected (0.14 sec) Query OK, 0 rows affected (0.13 sec) ``` The first is as slow because the cache is empty but after the time drops considerably. –Yves ---
**Author:** ![tamil\_maran](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/tamil_maran/32/14292_2.png) [@tamil\_maran](https://forums.percona.com/u/tamil_maran)\ **Post date:** [December 22, 2023, 9:16am UTC](https://forums.percona.com/t/query-slowness-in-delete-query-with-many-child-table-in-mysql-8/27285/3 "2023-12-22T09:16:53Z")
Yes, after increasing table\_definition\_cache, didn’t face the issue. Still we took flamegraph for mysql and went through slow processing functions. ![Screenshot 2023-12-21 at 10.55.40 PM](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/31e01039c4c0b8252c9bc8e16b7e2c9b48068e9f.jpeg) 1. sql\_base.open\_and\_process\_routine When CUD query is fired to Parent, MySQL requires SW lock for all its child. In this work item - [https://dev.MySQL.com/worklog/task/?id=6049](https://dev.MySQL.com/worklog/task/?id=6049), MySQL team has mentioned they are not intended to open all the child tables. Just needed to acquire SW lock for the child. But when we checked the code, all the child table is opened and its respective info is stored in def cache(table share). 2. sql\_base.get\_table\_share Also, in our test case, a parent table with 4K child table explain delete query took 21sec. In this get\_table\_share alone took 20.84sec. And inside this get\_table\_share, read histogram contributes around 10-11sec(50%), we haven’t created any histogram but still 8.0 tries to load histogram statistics in table\_share. So when we commented this read\_histogram method we were able to gain 50% faster execution speed. Is there any possible ways to fix/handle this in better way?