Elena Stepanova added a comment - 2017-06-16 22:36 - edited 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)

Chris Calender (Inactive) added a comment - 2017-06-20 18:27 @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.

Elena Stepanova added a comment - 2017-06-20 20:10 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.

Alexander Barkov added a comment - 2017-10-30 16:50 The 10.3 optimization was disabled during bb-10.2-ext -> 10.3 merge. Will be enabled in a separate patch.

Alexander Barkov added a comment - 2017-10-31 09:06 Disabled automatic sorting in 10.3.3. Adjusted affected mtr tests accordingly.

Sergei Golubchik added a comment - 2017-11-01 12:57 querying information_schema in 10.1 is now almost as fast as in 10.0. And in 10.3 should be even faster.

Nilnandan Joshi added a comment - 2017-11-28 07:04 - edited 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]$

Sergei Golubchik added a comment - 2017-11-28 10:03 without ORDER BY case is fixed in 10.3

Chris Calender (Inactive) added a comment - 2017-11-28 15:50 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.

Sergei Golubchik added a comment - 2017-11-28 16:24 Please, show benchmarks with 2x difference

Chris Calender (Inactive) added a comment - 2017-11-30 21:56 - edited 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.