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

Performance regression starting in 10.6: unlimited "select order by limit" always using temporary, taking between 60x and 2000x longer in 10.6, 10.7, and 10.8 than in 10.5

    XMLWordPrintable

    Details

      Description

      Attached file reproduces: a simple select using no where clause but using limit, with order-by on an unindexed attribute, against a table with a little over 500,000 rows, takes about 0.150s in 10.5.15, while in 10.6.8, 10.7.4, and 10.8.3 the same query consistently takes about 10s. When an index is added, the difference is much more dramatic. 10.5 drops from 0.150s to 0.004s, while 10.6, 10.7, and 10.8 all remain unchanged at 10s.

      The explain plan shows that 10.6-10.8 always create a temporary table in addition to using filesort (or an index), while 10.5 just uses filesort in the first case and index in the second w/o needing to create a temporary table in either case. Increasing sort_buffer_size up to 512M makes no difference. One significant change is that the metadata (system) character set in 10.5 is utf8, whereas starting with 10.6 it's utf8mb3.

      MariaDB [test]> show create table TEST_Table\G
      *************************** 1. row ***************************
             Table: TEST_Table
      Create Table: CREATE TABLE `TEST_Table` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `categoryLookupId` int(11) DEFAULT NULL,
        `date` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `aap_category` (`categoryLookupId`),
        KEY `TEST_Table_date_cat` (`date`,`categoryLookupId`)
      ) ENGINE=InnoDB AUTO_INCREMENT=599441 DEFAULT CHARSET=utf8mb3
      1 row in set (0.001 sec)
      

      select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 25;
      

      Unindexed 10.5 explain:

      +------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
      +------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
      |    1 | SIMPLE      | TEST_Table | ALL  | NULL          | NULL | NULL    | NULL | 598694 | Using filesort |
      +------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
      

      Unindexed 10.6, 10.7, 10.8 explain

      +------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
      | id   | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
      +------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
      |    1 | SIMPLE      | TEST_Table | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using temporary; Using filesort |
      +------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
      

      Typical indexed 10.5 performance

      MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
      +---------------------+----------------------------------+
      | date                | propertyCategory                 |
      +---------------------+----------------------------------+
      | 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
      | 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
      | 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
      | 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
      | 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
      +---------------------+----------------------------------+
      5 rows in set (0.002 sec)
      

      Typical unindexed 10.5 performance

      MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
      +---------------------+----------------------------------+
      | date                | propertyCategory                 |
      +---------------------+----------------------------------+
      | 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
      | 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
      | 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
      | 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
      | 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
      +---------------------+----------------------------------+
      5 rows in set (0.104 sec)
      

      Typical indexed 10.6 through 10.8 performance

      MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
      +---------------------+----------------------------------+
      | date                | propertyCategory                 |
      +---------------------+----------------------------------+
      | 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
      | 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
      | 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
      | 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
      | 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
      +---------------------+----------------------------------+
      5 rows in set (9.803 sec)
      

      Typical unindexed 10.6 through 10.8 performance

      MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
      +---------------------+----------------------------------+
      | date                | propertyCategory                 |
      +---------------------+----------------------------------+
      | 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
      | 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
      | 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
      | 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
      | 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
      +---------------------+----------------------------------+
      5 rows in set (9.681 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              oleg.smirnov Oleg Smirnov
              Reporter:
              juan.vera Juan
              Votes:
              3 Vote for this issue
              Watchers:
              12 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.