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

Querying INFORMATION_SCHEMA becomes slow in MariaDB 10.1

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.19, 10.1.24, 10.1(EOL), 10.2(EOL)
    • 10.1.29, 10.2.10, 10.3.3
    • Admin statements
    • 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

          Ascending order - Click to sort in descending order
          View 6 older comments
          niljoshi Nilnandan Joshi added a comment - - 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]$

          niljoshi Nilnandan Joshi added a comment - - 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]$
          serg Sergei Golubchik added a comment -

          without ORDER BY case is fixed in 10.3

          serg Sergei Golubchik added a comment - without ORDER BY case is fixed in 10.3
          ccalender Chris Calender (Inactive) added a comment -

          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.

          ccalender Chris Calender (Inactive) added a comment - 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.
          serg Sergei Golubchik added a comment -

          Please, show benchmarks with 2x difference

          serg Sergei Golubchik added a comment - Please, show benchmarks with 2x difference
          ccalender Chris Calender (Inactive) added a comment - - 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.

          ccalender Chris Calender (Inactive) added a comment - - 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.

          People

            serg Sergei Golubchik
            ccalender Chris Calender (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            9 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.