Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Performance loss in MySQL 5 and MariaDB schema introspection #5195

Closed
xprojects-de opened this issue Jan 21, 2022 · 14 comments · Fixed by #5202
Closed

Performance loss in MySQL 5 and MariaDB schema introspection #5195

xprojects-de opened this issue Jan 21, 2022 · 14 comments · Fixed by #5202

Comments

@xprojects-de
Copy link

xprojects-de commented Jan 21, 2022

Bug Report

Summary

After upgrading dbal from 3.2 to 3.3 there is a massive performance loss. (In my case over 50%)!
Same code, same query, same environment (LAMP-Stack, PHP 8.1, MariaDB 10.x).
Testet and profiled also on different servers and different requests. Same behaviour...

Also the database process is fully utilized!

The weird thing is that finally the native php::PDO->query is extremly slow! Don´t know exactly why, maybe there are different preconditions......

(see also Call-Tree in profile files...)

Expected behaviour

maybe as fast or faster than 3.2.x :-)
installtool_3.2.gz
installtool_3.3.gz

###Edit
Profilefiles are normal xdebug profiles files... :-)

@morozov
Copy link
Member

morozov commented Jan 21, 2022

@xprojects-de please provide the steps to reproduce the scenario where you see the performance loss.

@xprojects-de
Copy link
Author

xprojects-de commented Jan 21, 2022

It is best seen when you call the createSchema in the AbstractSchemaManager using an exsiting Connection.
In my case and environment it´f form 60ms in 3.2.2 to 826ms in 3.3.0 and the database process is fully utilized using the 3.3.0!

###EDIT
The Database does have round about 43 tables and especially the listTables (and in detail the listTableDetails) call is slower then.

Especially the fetchAllAssociative inside the listTableForeignKeys is much slower, finally at the PDO->query...

Do you need more informations?

@xprojects-de
Copy link
Author

but I think that the mass of requests make up the loss here. In the profile files you can see that every query itself is just a bit slower, e.g. from 1 to 2ms. This is not so noticeable with smaller queries ...

@morozov
Copy link
Member

morozov commented Jan 21, 2022

@xprojects-de given that only you can reproduce the issue, you could look into the 3.3.0 release notes and see if there are any changes that look like a root cause. Then try reverting them and comparing the resulting performance with 3.2.2.

If the offending change cannot be identified logically, you could try using git bisect. It should take 5-6 steps to find the offending change.

Once you point out a specific change, we can see if we can reproduce the performance issue in a generic way.

@xprojects-de
Copy link
Author

Hello,

the performance loss comes as follow:

From 932c596 (932c596)
To b46c1aa (b46c1aa)

Reason for that is the getListTableForeignKeysSQL().

from:

` public function getListTableForeignKeysSQL($table, $database = null)
{
$table = $this->quoteStringLiteral($table);

    if ($database !== null) {
        $database = $this->quoteStringLiteral($database);
    }

    $sql = 'SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ' .
        'k.`REFERENCED_COLUMN_NAME`, k.`ORDINAL_POSITION` /*!50116 , c.update_rule, c.delete_rule */ ' .
        'FROM information_schema.key_column_usage k /*!50116 ' .
        'INNER JOIN information_schema.referential_constraints c ON ' .
        '  c.constraint_name = k.constraint_name AND ' .
        '  c.table_name = ' . $table . ' */ WHERE k.table_name = ' . $table;

    $databaseNameSql = $database ?? 'DATABASE()';

    return $sql . ' AND k.table_schema = ' . $databaseNameSql
        . ' /*!50116 AND c.constraint_schema = ' . $databaseNameSql . ' */'
        . ' AND k.`REFERENCED_COLUMN_NAME` is not NULL'
        . ' ORDER BY k.`ORDINAL_POSITION`';
}`

To:

public function getListTableForeignKeysSQL($table, $database = null) { return 'SELECT k.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, ' . 'k.REFERENCED_COLUMN_NAME /*!50116 , c.UPDATE_RULE, c.DELETE_RULE */ ' . 'FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 ' . 'INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c ON ' . 'c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND ' . 'c.TABLE_NAME = k.TABLE_NAME AND ' . 'c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */ ' . 'WHERE k.TABLE_NAME = ' . $this->quoteStringLiteral($table) . ' ' . 'AND k.TABLE_SCHEMA = ' . $this->getDatabaseNameSQL($database) . ' ' . 'ORDER BY k.ORDINAL_POSITION'; }

This fits with the assumption that it is specifically due to the createSchema ...
Before that commit the performance is comparable to 3.2...

Greetings

@morozov
Copy link
Member

morozov commented Jan 21, 2022

Thanks, @xprojects-de. The changes you're referring to were made in #5071. Could you look at the description and see which of the following changes causes the issue:

  1. The DISTINCT clause has been removed since it's not necessary.
  2. The WHERE k.REFERENCED_COLUMN_NAME IS NOT NULL condition has been removed since it's not necessary.
  3. The WHERE c.constraint_schema = $databaseNameSql condition has been replaced with an analogous ON condition.

@xprojects-de
Copy link
Author

xprojects-de commented Jan 22, 2022

-- Good one
SELECT k.CONSTRAINT_NAME,
       k.COLUMN_NAME,
       k.REFERENCED_TABLE_NAME,
       k.REFERENCED_COLUMN_NAME /*!50116,
       c.UPDATE_RULE,
       c.DELETE_RULE */
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
         INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
                    ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = 'tl_alpdeskbookingcm_channels' */
WHERE k.TABLE_NAME = 'tl_alpdeskbookingcm_channels'
  AND k.TABLE_SCHEMA = 'contaoalpdeskbooking' /*!50116
  AND c.CONSTRAINT_SCHEMA = 'contaoalpdeskbooking' */
ORDER BY k.ORDINAL_POSITION;

-- Bad One
SELECT k.CONSTRAINT_NAME,
       k.COLUMN_NAME,
       k.REFERENCED_TABLE_NAME,
       k.REFERENCED_COLUMN_NAME /*!50116,
       c.UPDATE_RULE,
       c.DELETE_RULE */
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
         INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
                    ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND
                       c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
WHERE k.TABLE_NAME = 'tl_alpdeskbookingcm_channels'
  AND k.TABLE_SCHEMA = 'contaoalpdeskbooking'
ORDER BY k.ORDINAL_POSITION;

So it´s the third change on replacing the c.constraint_schema to an ON condition...

Greetings

@morozov
Copy link
Member

morozov commented Jan 22, 2022

When I run the above queries against an arbitrary test table that has foreign keys, I get identical execution plans:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> -- Good one
mysql> EXPLAIN SELECT k.CONSTRAINT_NAME,
    ->        k.COLUMN_NAME,
    ->        k.REFERENCED_TABLE_NAME,
    ->        k.REFERENCED_COLUMN_NAME /*!50116,
    ->        c.UPDATE_RULE,
    ->        c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    ->          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    ->                     ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = 'test_fk_rename' */
    -> WHERE k.TABLE_NAME = 'test_fk_rename'
    ->   AND k.TABLE_SCHEMA = 'doctrine' /*!50116
    ->   AND c.CONSTRAINT_SCHEMA = 'doctrine' */
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys          | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                  |    1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                  |    1 |   100.00 | Using index; Using join buffer (hash join)   |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const    |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const    |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | schema_id              | schema_id   | 202     | mysql.sch.id,const    |    1 |   100.00 | Rematerialize (<derived3>)                   |
|  1 | PRIMARY           | fk         | NULL       | ref    | schema_id,table_id     | schema_id   | 8       | mysql.sch.id          |   31 |   100.00 | NULL                                         |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | mysql.fk.table_id     |    1 |    10.00 | Using where                                  |
|  1 | PRIMARY           | <derived3> | NULL       | ref    | <auto_key0>            | <auto_key0> | 194     | func                  |    2 |   100.00 | Using where                                  |
|  1 | PRIMARY           | col        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | constraints.column_id |    1 |   100.00 | Using where                                  |
|  3 | DEPENDENT DERIVED | idx        | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id          |    1 |    40.00 | Using where                                  |
|  3 | DEPENDENT DERIVED | icu        | NULL       | ref    | index_id,index_id_2    | index_id    | 8       | mysql.idx.id          |    3 |   100.00 | NULL                                         |
|  4 | DEPENDENT UNION   | fk         | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id          |    2 |   100.00 | NULL                                         |
|  4 | DEPENDENT UNION   | fkcu       | NULL       | ref    | PRIMARY,foreign_key_id | PRIMARY     | 8       | mysql.fk.id           |    1 |   100.00 | NULL                                         |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
13 rows in set, 3 warnings (0.02 sec)

-- Bad One
EXPLAIN SELECT k.CONSTRAINT_NAME,
       k.COLUMN_NAME,
       k.REFERENCED_TABLE_NAME,
       k.REFERENCED_COLUMN_NAME /*!50116,
       c.UPDATE_RULE,
       c.DELETE_RULE */
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
         INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
                    ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND
                       c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
WHERE k.TABLE_NAME = 'test_fk_rename'
  AND k.TABLE_SCHEMA = 'doctrine'
ORDER BY k.ORDINAL_POSITION;

mysql> -- Bad One
mysql> EXPLAIN SELECT k.CONSTRAINT_NAME,
    ->        k.COLUMN_NAME,
    ->        k.REFERENCED_TABLE_NAME,
    ->        k.REFERENCED_COLUMN_NAME /*!50116,
    ->        c.UPDATE_RULE,
    ->        c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    ->          INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    ->                     ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND
    ->                        c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
    -> WHERE k.TABLE_NAME = 'test_fk_rename'
    ->   AND k.TABLE_SCHEMA = 'doctrine'
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys          | key         | key_len | ref                   | rows | filtered | Extra                                        |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                  |    1 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY                | name        | 194     | NULL                  |    1 |   100.00 | Using index; Using join buffer (hash join)   |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const    |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id     | catalog_id  | 202     | mysql.cat.id,const    |    1 |   100.00 | Using index                                  |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | schema_id              | schema_id   | 202     | mysql.sch.id,const    |    1 |   100.00 | Rematerialize (<derived3>)                   |
|  1 | PRIMARY           | fk         | NULL       | ref    | schema_id,table_id     | schema_id   | 8       | mysql.sch.id          |   31 |   100.00 | NULL                                         |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | mysql.fk.table_id     |    1 |    10.00 | Using where                                  |
|  1 | PRIMARY           | <derived3> | NULL       | ref    | <auto_key0>            | <auto_key0> | 194     | func                  |    2 |   100.00 | Using where                                  |
|  1 | PRIMARY           | col        | NULL       | eq_ref | PRIMARY                | PRIMARY     | 8       | constraints.column_id |    1 |   100.00 | Using where                                  |
|  3 | DEPENDENT DERIVED | idx        | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id          |    1 |    40.00 | Using where                                  |
|  3 | DEPENDENT DERIVED | icu        | NULL       | ref    | index_id,index_id_2    | index_id    | 8       | mysql.idx.id          |    3 |   100.00 | NULL                                         |
|  4 | DEPENDENT UNION   | fk         | NULL       | ref    | PRIMARY,table_id       | table_id    | 8       | mysql.tbl.id          |    2 |   100.00 | NULL                                         |
|  4 | DEPENDENT UNION   | fkcu       | NULL       | ref    | PRIMARY,foreign_key_id | PRIMARY     | 8       | mysql.fk.id           |    1 |   100.00 | NULL                                         |
+----+-------------------+------------+------------+--------+------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
13 rows in set, 3 warnings (0.00 sec)

@xprojects-de, could you do the same on some table(s) from your schema and see if the two queries yield different plans?

Additionally, it's not clear if it's the WHERE vs. ON that makes the difference or it's the fixed value in the WHERE vs. k.CONSTRAINT_SCHEMA in the ON. I'd expect the latter to be the problem since there may be no appropriate index on KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA.

@xprojects-de
Copy link
Author

xprojects-de commented Jan 23, 2022

Now i don´t understand it anymore:

Try on MYSQL:

mysql> EXPLAIN SELECT k.CONSTRAINT_NAME,
    -> k.COLUMN_NAME,
    -> k.REFERENCED_TABLE_NAME,
    -> k.REFERENCED_COLUMN_NAME /*!50116,
    -> c.UPDATE_RULE,
    -> c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = 'tl_page' */
    -> WHERE k.TABLE_NAME = 'tl_page'
    -> AND k.TABLE_SCHEMA = 'contaoalpdeskbooking' /*!50116
    -> AND c.CONSTRAINT_SCHEMA = 'contaoalpdeskbooking' */
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------+-------+------------+------+---------------+------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key                          | key_len | ref  | rows | filtered | Extra                                                                                    |
+----+-------------+-------+------------+------+---------------+------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME      | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort       |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | CONSTRAINT_SCHEMA,TABLE_NAME | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 0 databases; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

mysql> EXPLAIN SELECT k.CONSTRAINT_NAME,
    -> k.COLUMN_NAME,
    -> k.REFERENCED_TABLE_NAME,
    -> k.REFERENCED_COLUMN_NAME /*!50116,
    -> c.UPDATE_RULE,
    -> c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND
    -> c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
    -> WHERE k.TABLE_NAME = 'tl_page'
    -> AND k.TABLE_SCHEMA = 'contaoalpdeskbooking'
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                                                                                      |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort         |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL                    | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

Try on MariaDB:

MariaDB [d035ad48]> EXPLAIN SELECT k.CONSTRAINT_NAME,
    -> k.COLUMN_NAME,
    -> k.REFERENCED_TABLE_NAME,
    -> k.REFERENCED_COLUMN_NAME /*!50116,
    -> c.UPDATE_RULE,
    -> c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND
    -> c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */
    -> WHERE k.TABLE_NAME = 'tl_page'
    -> AND k.TABLE_SCHEMA = 'd035ad48'
    -> ORDER BY k.ORDINAL_POSITION;
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key                     | key_len | ref  | rows | Extra                                                                                   |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
|    1 | SIMPLE      | k     | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort      |
|    1 | SIMPLE      | c     | ALL  | NULL          | NULL                    | NULL    | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [d035ad48]> EXPLAIN SELECT k.CONSTRAINT_NAME,
    -> k.COLUMN_NAME,
    -> k.REFERENCED_TABLE_NAME,
    -> k.REFERENCED_COLUMN_NAME /*!50116,
    -> c.UPDATE_RULE,
    -> c.DELETE_RULE */
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = 'tl_page' */
    -> WHERE k.TABLE_NAME = 'tl_page'
    -> AND k.TABLE_SCHEMA = 'd035ad48' /*!50116
    -> AND c.CONSTRAINT_SCHEMA = 'd035ad48' */
    -> ORDER BY k.ORDINAL_POSITION;
+------+-------------+-------+------+---------------+------------------------------+---------+------+------+---------------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key                          | key_len | ref  | rows | Extra                                                                                 |
+------+-------------+-------+------+---------------+------------------------------+---------+------+------+---------------------------------------------------------------------------------------+
|    1 | SIMPLE      | k     | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME      | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort    |
|    1 | SIMPLE      | c     | ALL  | NULL          | CONSTRAINT_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------------------------------+---------+------+------+---------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

But doing the same using PHP there is a massive performance loss!?!?!? Does the "Scanned all databases" has an impact here?

Maybe we have to keep in mind that every query itself is just a bit slower (in my case from 3ms to 18ms) and maybe the time adds up. But you should also see that via mysql !?!?

###edit

Also logged the real SQL-Code from PHP and it differs just a bit but result is the same...

MariaDB [d035ad48]> EXPLAIN SELECT k.CONSTRAINT_NAME, 
    -> k.COLUMN_NAME, 
    -> k.REFERENCED_TABLE_NAME, 
    -> k.REFERENCED_COLUMN_NAME /*!50116 , 
    -> c.UPDATE_RULE, 
    -> c.DELETE_RULE */ 
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c 
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME */ 
    -> WHERE k.TABLE_NAME = 'tl_page' 
    -> AND k.TABLE_SCHEMA = 'd035ad48' /*!50116 
    -> AND c.constraint_schema = 'd035ad48' */ 
    -> ORDER BY k.ORDINAL_POSITION;
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+--------------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key                     | key_len | ref  | rows | Extra                                                                                |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+--------------------------------------------------------------------------------------+
|    1 | SIMPLE      | k     | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort   |
|    1 | SIMPLE      | c     | ALL  | NULL          | CONSTRAINT_SCHEMA       | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 1 database; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+--------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [d035ad48]> EXPLAIN SELECT k.CONSTRAINT_NAME, 
    -> k.COLUMN_NAME, 
    -> k.REFERENCED_TABLE_NAME, 
    -> k.REFERENCED_COLUMN_NAME /*!50116 , 
    -> c.UPDATE_RULE, 
    -> c.DELETE_RULE */ 
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c 
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */ 
    -> WHERE k.TABLE_NAME = 'tl_page' 
    -> AND k.TABLE_SCHEMA = 'd035ad48' 
    -> ORDER BY k.ORDINAL_POSITION;
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key                     | key_len | ref  | rows | Extra                                                                                   |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
|    1 | SIMPLE      | k     | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort      |
|    1 | SIMPLE      | c     | ALL  | NULL          | NULL                    | NULL    | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+-------------------------+---------+------+------+-----------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
mysql> EXPLAIN SELECT k.CONSTRAINT_NAME, 
    -> k.COLUMN_NAME, 
    -> k.REFERENCED_TABLE_NAME, 
    -> k.REFERENCED_COLUMN_NAME /*!50116 , 
    -> c.UPDATE_RULE, 
    -> c.DELETE_RULE */ 
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c 
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME */ 
    -> WHERE k.TABLE_NAME = 'tl_page' 
    -> AND k.TABLE_SCHEMA = 'contaoalpdeskbooking' /*!50116 
    -> AND c.CONSTRAINT_SCHEMA = 'contaoalpdeskbooking' */ 
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+-----------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                                                                                   |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+-----------------------------------------------------------------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort      |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | CONSTRAINT_SCHEMA       | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 1 database; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+-----------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

mysql> EXPLAIN SELECT k.CONSTRAINT_NAME, 
    -> k.COLUMN_NAME, 
    -> k.REFERENCED_TABLE_NAME, 
    -> k.REFERENCED_COLUMN_NAME /*!50116 , 
    -> c.UPDATE_RULE, 
    -> c.DELETE_RULE */ 
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k /*!50116 
    -> INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c 
    -> ON c.CONSTRAINT_NAME = k.CONSTRAINT_NAME AND c.TABLE_NAME = k.TABLE_NAME AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA */ 
    -> WHERE k.TABLE_NAME = 'tl_page' 
    -> AND k.TABLE_SCHEMA = 'contaoalpdeskbooking' 
    -> ORDER BY k.ORDINAL_POSITION;
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                                                                                      |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 0 databases; Using temporary; Using filesort         |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL                    | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+-------------------------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0,00 sec)

@morozov
Copy link
Member

morozov commented Jan 23, 2022

Yeah, probably the "Scanned all databases" is the culprit. As I understand it, using KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA in a condition triggers scanning all databases to get its possible values.

What MySQL version are you using? Your execution plans yield just two rows while mine yield thirteen on MySQL 8.0.23. Although I see the two-row plans on Maria DB 10.6.5 (which corresponds to some older MySQL).

From the documentation:

OPEN_FULL_TABLE: The unoptimized information lookup. The .frm, .MYD, and .MYI files must be opened.

It looks like MySQL stores the information schema (specifically, the k and the c tables above) in a regular database which isn't sensitive to the SQL syntax details while MariaDB and the older MySQL version introspect the storage files to fulfill the query and are sensitive. UPD: there are some more details on StackOverflow.

Unless I find a good way to avoid scanning all databases, I'll revert the offending change in the query.

@xprojects-de
Copy link
Author

Thanks a lot! Sound great. Yeah!!

... currently i use MYSql5 and MariaDB 10.5...

@m-vo Thanks for help and the hint "Scanning all databases"!!!

@morozov morozov changed the title massive performance loss from 3.2.x to 3.3.x Performance loss in MySQL 5 and MariaDB schema introspection Jan 23, 2022
@derrabus
Copy link
Member

@xprojects-de Can you check against the latest 3.3.x-dev version and tell us if the problem is gone now?

@xprojects-de
Copy link
Author

Tested! Now it works great! Thanks to all...

@github-actions
Copy link

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

@github-actions github-actions bot locked as resolved and limited conversation to collaborators Jul 21, 2022
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants