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

BIGINT UNSIGNED Performance issue

    XMLWordPrintable

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

            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.