Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.40, 10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.2
-
Ubuntu
Description
When performing a writing query on a referenced table, the planner opens all the tables referencing it, even if the modified fields are not the referenced ones and even if no record matches the WHERE clause. This behavior started to happen at some version between 10.1.20 and 10.1.40, as it is reproducible in the later and not in the former.
It is interesting to mention that the problem is triggered even if just the explain is executed which seems to be related to the planner rather than the excution of the query itself.
It takes a lot of time and resources, mainly when several FKs are involved.
To reproduce it just follow these steps:
1 . Create a DB with one table referenced by several tables. Here's a bash example of how to create it (and there is a dump file attached to this issue which you can just restore):
mysql -uroot -e "DROP DATABASE IF EXISTS fktest; CREATE DATABASE fktest" |
|
|
mysql -uroot fktest -e "
|
DROP TABLE IF EXISTS fkParent;
|
CREATE TABLE fkParent (
|
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, |
textField varchar(10) |
);"
|
|
|
for n in $(seq 12000); do |
[ $(($n % 100)) -eq 0 ] && echo $n |
mysql -uroot fktest -e "
|
CREATE TABLE fkChild$n (
|
id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, |
fkField$n int(10) unsigned, |
CONSTRAINT FK_$n FOREIGN KEY (fkField$n) REFERENCES fkParent(id)
|
);"
|
done
|
|
Then just access that DB and run this explain statement:
explain UPDATE fkParent SET textField="test"; |
It will take quite long and it seems that the planner is opnening all those referencing tables.
If you run exactly the same test in version 10.1.20 it just goes instantaneous.
To see it more clearly here are some evident tests:
In version 10.1.40 (or any later version):
MariaDB [fktest]> show status like "opened_table%"; |
+--------------------------+-------+ |
| Variable_name | Value |
|
+--------------------------+-------+ |
| Opened_table_definitions | 0 |
|
| Opened_tables | 0 |
|
+--------------------------+-------+ |
2 rows in set (0.00 sec) |
|
|
MariaDB [fktest]> explain UPDATE fkParent SET textField="test"; |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| 1 | SIMPLE | fkParent | index | NULL | PRIMARY | 4 | NULL | 1 | | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
1 row in set (16.18 sec) |
|
|
MariaDB [fktest]> show status like "opened_table%"; |
+--------------------------+-------+ |
| Variable_name | Value |
|
+--------------------------+-------+ |
| Opened_table_definitions | 3934 |
|
| Opened_tables | 3934 |
|
+--------------------------+-------+ |
2 rows in set (0.01 sec) |
|
In version 10.1.20 looks quite better and does it in 0 miliseconds
MariaDB [siemens]> show status like "opened_table%"; |
+--------------------------+-------+ |
| Variable_name | Value |
|
+--------------------------+-------+ |
| Opened_table_definitions | 0 |
|
| Opened_tables | 0 |
|
+--------------------------+-------+ |
2 rows in set (0.01 sec) |
|
|
MariaDB [fktest]> explain UPDATE fkParent SET textField="test"; |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| 1 | SIMPLE | fkParent | index | NULL | PRIMARY | 4 | NULL | 1 | | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [siemens]> show status like "opened_table%"; |
+--------------------------+-------+ |
| Variable_name | Value |
|
+--------------------------+-------+ |
| Opened_table_definitions | 0 |
|
| Opened_tables | 0 |
|
+--------------------------+-------+ |
2 rows in set (0.00 sec) |
|
Additionally, you can see it in the profiling detail for the failing version:
MariaDB [fktest]> set profiling =1; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [fktest]> explain UPDATE fkParent SET textField="test"; |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
| 1 | SIMPLE | fkParent | index | NULL | PRIMARY | 4 | NULL | 1 | | |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------+ |
1 row in set (12.49 sec) |
|
|
MariaDB [fktest]> show profile for query 1; |
+----------------------+-----------+ |
| Status | Duration |
|
+----------------------+-----------+ |
| starting | 0.000259 |
|
| checking permissions | 0.000055 |
|
| Opening tables | 12.409309 |
|
| After opening tables | 0.006020 | |
| System lock | 0.005769 |
|
| Table lock | 0.007041 | |
| init | 0.001493 |
|
| query end | 0.000017 | |
| closing tables | 0.003398 |
|
| Unlocking tables | 0.060393 |
|
| freeing items | 0.000021 |
|
| updating status | 0.000045 |
|
| cleaning up | 0.002870 |
|
+----------------------+-----------+ |
13 rows in set (0.01 sec) |
|
As you can see, all the time is spent opening tables.
Attachments
Issue Links
- causes
-
-
- Stalled
-
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Stalled
-
-
MDEV-30182 Optimize open_tables to take O(N) time
-
- In Review
-
-
MDEV-16417 Store Foreign Key metadata outside of InnoDB
-
- In Review
-
-
MDEV-21052
InnoDB foreign key refactoring for TABLE_SHARE::foreign_keys
-
- Stalled
-
-
-
- Closed
-