Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.19, 10.1.24, 10.1(EOL), 10.2(EOL)
-
None
-
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.
Attachments
Activity
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.
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.
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()BEGINDECLARE i INT DEFAULT 0;WHILE i < 3000DOSET @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"; donereal 0m27.219suser 0m9.436ssys 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"; donereal 0m28.654suser 0m10.636ssys 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; donereal 0m26.468suser 0m8.656ssys 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; donereal 0m27.543suser 0m8.696ssys 0m4.999s[nil@centos68 msb_10_1_29]$