Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.3.12
    • N/A
    • Server
    • Linux Debian 9.6 (Stretch) APT packages

    Description

      I noticed a significant drop in performance after upgrading MariaDB from 10.2.19 to 10.3.12.

      A simple query over a BIGINT UNSIGNED column (PK) takes less than 1ms on MariaDB 10.2.19 but more than 30ms on MariaDB 10.3.12.

      This occurs when the condition (using the IN clause) exceeds the maximum value of BIGINT (signed!), that is 9223372036854775807

      CREATE TABLE `tt` (
        `id` bigint(20) unsigned NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      

      Table tt contains about 100,000 rows.

      MariaDB [np]> SELECT MIN(id), MAX(id), COUNT(*) FROM tt;
      +--------------+--------------------+----------+
      | MIN(id)      | MAX(id)            | COUNT(*) |
      +--------------+--------------------+----------+
      | 514477679209 | 696605562020073273 |   100001 |
      +--------------+--------------------+----------+
      1 row in set (0.033 sec)
      

      MariaDB 10.2.19

      Response time is ok

      MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807);
      Empty set (0.00 sec)
       
      MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
      Empty set (0.00 sec)
       
      MariaDB [np]> explain SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | tt    | range | PRIMARY       | PRIMARY | 8       | NULL |    2 | Using where; Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
      1 row in set (0.00 sec)
      

      MariaDB 10.3.12

      Poor response time for values greater than 9223372036854775808

      MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807);
      Empty set (0.000 sec)
       
      MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
      Empty set (0.034 sec)
       
      MariaDB [np]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
      Empty set (0.068 sec)
       
      MariaDB [np]> explain SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
      +------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
      +------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
      |    1 | SIMPLE      | tt    | index | PRIMARY       | PRIMARY | 8       | NULL | 100275 | Using where; Using index |
      +------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------+
      1 row in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            axel, could you please check it out?

            elenst Elena Stepanova added a comment - axel , could you please check it out?
            axel Axel Schwenke added a comment -

            Reproduced as reported. This looks similar to MDEV-17698 and certainly has a similar background - literals being promoted to DECIMAL in order to compare numbers that cannot be represented as BIGINT.

            I suspected this to be related to the fix for MDEV-17698, but it isn't. All releases in 10.2 (up to 10.2.22), even those with that fix do the query fast. Several releases in 10.3 (I tested 10.3.9 - 10.3.12) do it slow, regardles they have that fix or not. As does 10.4.1. So this is something that has been changed in 10.3.

            axel Axel Schwenke added a comment - Reproduced as reported. This looks similar to MDEV-17698 and certainly has a similar background - literals being promoted to DECIMAL in order to compare numbers that cannot be represented as BIGINT. I suspected this to be related to the fix for MDEV-17698 , but it isn't. All releases in 10.2 (up to 10.2.22), even those with that fix do the query fast. Several releases in 10.3 (I tested 10.3.9 - 10.3.12) do it slow, regardles they have that fix or not. As does 10.4.1. So this is something that has been changed in 10.3.

            Any news from that? Regression Bug is still present in 10.3.13

            nico Nicolas Payart added a comment - Any news from that? Regression Bug is still present in 10.3.13

            For information, I just tested the latest MariaDB 10.3.15 and 10.4.4-RC (Linux Debian 9) and I still notice the same performance issue.

            However, the last 10.2 series (10.2.24) is still not affected by this bug.

            nico Nicolas Payart added a comment - For information, I just tested the latest MariaDB 10.3.15 and 10.4.4-RC (Linux Debian 9) and I still notice the same performance issue. However, the last 10.2 series (10.2.24) is still not affected by this bug.
            serg Sergei Golubchik added a comment - for the reference: https://github.com/MariaDB/server/commit/8a990ad17746927c6d395ec755a262eda59191fb
            f_razzoli Federico Razzoli added a comment - - edited

            Same in 10.5.6:

            MariaDB [test]> SELECT MIN(id), MAX(id), COUNT(*) FROM tt;
            +---------------+--------------------+----------+
            | MIN(id)       | MAX(id)            | COUNT(*) |
            +---------------+--------------------+----------+
            | 4942292026789 | 696599152880448512 |   100000 |
            +---------------+--------------------+----------+
            1 row in set (0.027 sec)
             
            MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807);
            Empty set (0.001 sec)
             
            MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
            Empty set (0.039 sec)
             
            MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808);
            Empty set (0.037 sec)
            

            f_razzoli Federico Razzoli added a comment - - edited Same in 10.5.6: MariaDB [test]> SELECT MIN (id), MAX (id), COUNT (*) FROM tt; + ---------------+--------------------+----------+ | MIN (id) | MAX (id) | COUNT (*) | + ---------------+--------------------+----------+ | 4942292026789 | 696599152880448512 | 100000 | + ---------------+--------------------+----------+ 1 row in set (0.027 sec)   MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775806, 9223372036854775807); Empty set (0.001 sec)   MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808); Empty set (0.039 sec)   MariaDB [test]> SELECT id FROM tt WHERE id IN (9223372036854775807, 9223372036854775808); Empty set (0.037 sec)

            This bug was earlier fixed by:
            MDEV-31303 Key not used when IN clause has both signed and unsigned values

            Closing as "Can't reproduce"

            bar Alexander Barkov added a comment - This bug was earlier fixed by: MDEV-31303 Key not used when IN clause has both signed and unsigned values Closing as "Can't reproduce"

            People

              bar Alexander Barkov
              nico Nicolas Payart
              Votes:
              2 Vote for this issue
              Watchers:
              10 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.