Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22180

Planner opens unnecessary tables when updated table is referenced by foreign keys

    XMLWordPrintable

Details

    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

          Activity

            People

              serg Sergei Golubchik
              gschulman Guillermo Schulman
              Votes:
              4 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.