[#MDEV-13049] Querying INFORMATION_SCHEMA becomes slow in MariaDB 10.1

[MDEV-13049] Querying INFORMATION_SCHEMA becomes slow in MariaDB 10.1 Created: 2017-06-09 Updated: 2020-12-08 Resolved: 2017-11-30
Status:	Closed
Project:	MariaDB Server
Component/s:	Admin statements
Affects Version/s:	10.1, 10.1.19, 10.1.24, 10.2
Fix Version/s:	10.1.29, 10.2.10, 10.3.3

Type:

Bug

Priority:

Critical

Reporter:

Chris Calender (Inactive)

Assignee:

Sergei Golubchik

Resolution:

Fixed

Votes:

Labels:

None

Environment:

cat /etc/redhat-release
CentOS release 6.8 (Final)

uname -a
Linux db32119.bfi3.service-now.com 2.6.32-642.15.1.el6.x86_64 #1 SMP Fri Feb 24 14:31:22 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

Also see it on CentOS 6.2.

MariaDB 10.1.19 and 10.1.24 have been tested and exhibit the slowness

Sprint:

10.2.10

Description

Querying INFORMATION_SCHEMA repeatedly using CentOS 6 becomes slow in MariaDB 10.1 (10.1.19, 10.1.24).

With a basic table, and simple repeated SELECT from I_S, on CentOS 6, thus far, we see a slowdown. It remains sub-second from my testing on Windows, even up to 2K iterations.

I will provide the CREATE TABLE and test case in a private comment.

Here are a sample of times from 10.1.19 (compared to times on MySQL 5.5 and 5.6) on CentOS 6.8:

MariaDB 10.1.19

real 0m16.970s

user 0m2.804s

sys 0m0.303s

MySQL 5.6.16

real 0m11.291s

user 0m2.331s

sys 0m0.280s

MySQL 5.5.36

real 0m7.246s

user 0m2.349s

sys 0m0.277s

And here are times from Elena (10.1.24 on CentOS 6.2) using test case provided:

real    0m4.699s

user    0m1.641s

sys     0m2.590s

This latter one is faster than the first one, but still not sub-second.

Comments

Comment by Elena Stepanova [ 2017-06-16 ]

I am getting a similar difference between MySQL 5.6.16 and MariaDB 10.1.24 on a local clean installation of CentOS 6.9, using 64-bit release bintars with default settings.

I create 3001 tables in the schema and then run the same query.
Also, i tried to get rid of the client reconnect, to make sure that the slowness is not caused by it.

Here is the SQL to reproduce. It creates and calls a procedure which creates 3000 tables, creates yet another table which we will be searching for, and also creates a procedure which runs the query from I_S 2000 times, and puts the result into a variable (to avoid sending and displaying the result set).

DROP DATABASE IF EXISTS db;

CREATE DATABASE db;

USE db;

DELIMITER $

CREATE PROCEDURE pr_create()

BEGIN

  DECLARE i INT DEFAULT 0;

  WHILE i < 3000

DO

    SET @sql = CONCAT('CREATE TABLE t', i, ' (i INT)');

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

    SET i = i + 1;

  END WHILE;

END $

DELIMITER ;

DELIMITER $

CREATE PROCEDURE pr_is()

BEGIN

  DECLARE i INT DEFAULT 0;

  WHILE i < 2000

DO

    SELECT 1 INTO @a FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME;

    SET i = i + 1;

  END WHILE;

END $

DELIMITER ;

CALL pr_create();

CREATE TABLE `target_table` (`id` INT PRIMARY KEY);

Below is the timing of running the same query from a shell loop, as initially described, and calling the procedure which runs it:

MySQL 5.6.16
time for i in {1..2000}; do bin/mysql -uroot db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real 0m13.260s
user 0m0.474s
sys 0m1.967s

mysql> call pr_is();
Query OK, 1 row affected (7.29 sec)

mysql> call pr_is();
Query OK, 1 row affected (7.24 sec)

MariaDB 10.1.24
time for i in {1..2000}; do bin/mysql -uroot db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real 0m19.331s
user 0m0.495s
sys 0m1.962s

MariaDB [db]> call pr_is();
Query OK, 1 row affected (13.13 sec)

MariaDB [db]> call pr_is();
Query OK, 1 row affected (12.86 sec)

MariaDB 10.2.6
time for i in {1..2000}; do bin/mysql -uroot db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real 0m21.826s
user 0m1.398s
sys 0m3.115s

MariaDB [db]> CALL pr_is();
Query OK, 1 row affected (13.23 sec)

MariaDB [db]> CALL pr_is();
Query OK, 1 row affected (13.16 sec)

MySQL 5.7.17
time for i in {1..2000}; do bin/mysql -uroot db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real 0m12.444s
user 0m0.454s
sys 0m1.967s

mysql> CALL pr_is();
Query OK, 1 row affected (7.13 sec)

mysql> CALL pr_is();
Query OK, 1 row affected (7.06 sec)

Comment by Chris Calender (Inactive) [ 2017-06-20 ]

@Elena:

Thank you very much for the above!

Btw, were you able to test the non-I_S related queries? I think that would be great to know if there is an overall regression, of sorts, or is it just related to I_S, or is it a separate issue/bug.

Comment by Elena Stepanova [ 2017-06-20 ]

No, I was only checking the I_S ones, because that's what the bug report was complaining about, and took the comment for a side note, since there weren't any specifics about that part – what exactly does it mean "slow", how slow, comparing to what? What are the table statistics (number of rows, cardinality and such)? Was it collected prior to query execution? What were the execution plans on the slow server comparing to the faster server? What is the server configuration on the slow and fast server? The standard stuff for supposed optimizer performance issues.

Anyway, we shouldn't fix essentially different performance problems in the scope of one issue. If whoever ends up fixing the I_S one finds the underlying problem to be I_S specific, then we can extract the non-I_S part into a separate issue (with additional information of course). If the root cause for the I_S part turns out to be generic, then there is no need to deal with every query separately anyway.

Comment by Alexander Barkov [ 2017-10-30 ]

The 10.3 optimization was disabled during bb-10.2-ext -> 10.3 merge.
Will be enabled in a separate patch.

Comment by Alexander Barkov [ 2017-10-31 ]

Disabled automatic sorting in 10.3.3. Adjusted affected mtr tests accordingly.

Comment by Sergei Golubchik [ 2017-11-01 ]

querying information_schema in 10.1 is now almost as fast as in 10.0. And in 10.3 should be even faster.

Comment by Nilnandan Joshi [ 2017-11-28 ]

Customer still complains that 5.6.36 is about 1 second faster than 10.1.29. I've used above Elena's test case and I was able to reproduce it .

DROP DATABASE IF EXISTS db;

CREATE DATABASE db;

USE db;

DELIMITER $

CREATE PROCEDURE pr_create()

BEGIN

  DECLARE i INT DEFAULT 0;

  WHILE i < 3000

DO

    SET @sql = CONCAT('CREATE TABLE t', i, ' (i INT)');

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

    SET i = i + 1;

  END WHILE;

END $

DELIMITER ;

CALL pr_create();

CREATE TABLE `target_table` (`id` INT PRIMARY KEY);

MySQL 5.6.36 - with ORDER BY

[nil@centos68 msb_10_1_29]$ time for i in {1..2000}; do mysql -uroot -pmsandbox -P5636 -h127.0.0.1 db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real	0m27.219s

user	0m9.436s

sys	0m4.748s

[nil@centos68 msb_10_1_29]$

MariaDB 10.1.28 - with ORDER BY

[nil@centos68 msb_10_1_29]$ time for i in {1..2000}; do mysql -uroot -pmsandbox -P10129 -h127.0.0.1 db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; done

real	0m28.654s

user	0m10.636s

sys	0m5.115s

[nil@centos68 msb_10_1_29]$

MySQL 5.6.36 - Without ORDER BY

[nil@centos68 msb_10_1_29]$ time for i in {1..2000}; do mysql -uroot -pmsandbox -P5636 -h127.0.0.1 db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table');" > /dev/null; done

real	0m26.468s

user	0m8.656s

sys	0m5.076s

[nil@centos68 msb_10_1_29]$

MariaDB 10.1.29 - without ORDER BY

[nil@centos68 msb_10_1_29]$ time for i in {1..2000}; do mysql -uroot -pmsandbox -P10129 -h127.0.0.1 db -BNe "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT REMARKS, NULL TYPE_CAT, NULL TYPE_SCHEM, NULL TYPE_NAME, NULL SELF_REFERENCING_COL_NAME, NULL REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_SCHEMA = 'db') AND (TABLE_NAME LIKE 'target_table');" > /dev/null; done

real	0m27.543s

user	0m8.696s

sys	0m4.999s

[nil@centos68 msb_10_1_29]$

Comment by Sergei Golubchik [ 2017-11-28 ]

without ORDER BY case is fixed in 10.3

Comment by Chris Calender (Inactive) [ 2017-11-28 ]

Re-opening since performance regression is still noticeably different - about 1 second slower (in these tests) in newer MariaDB compared to old, but approximately 2x the time when compared to MySQL 5.6 and 5.7.

Comment by Sergei Golubchik [ 2017-11-28 ]

Please, show benchmarks with 2x difference

Comment by Chris Calender (Inactive) [ 2017-11-30 ]

Setting back to fixed. I cannot reproduce the same slowness seen by the reporter. They are using SSD, so perhaps that is involved, which none of us in Support had a machine with an SSD to test.

Generated at Thu Feb 08 08:02:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-13049] Querying INFORMATION_SCHEMA becomes slow in MariaDB 10.1 Created: 2017-06-09 Updated: 2020-12-08 Resolved: 2017-11-30