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

Performance degradation with sub queries in MariaDB 10.5 and later

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.16, 10.6.8, 10.7.4, 10.8.3
    • Fix Version/s: 10.5, 10.6, 10.7, 10.8
    • Labels:
      None
    • Environment:
      CentOS Linux release 7.9.2009 (Core)
      Linux james-test 3.10.0-1160.71.1.el7.x86_64 #1 SMP Tue Jun 28 15:37:28 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
      Virtual Machine

      Description

      In investigating a report of an issue with Magento 2.4.2 Enterprise Edition having queries take upwards of 2 minutes to process that used to only take a few milliseconds in MariaDB 10.3, we have discovered a major performance degradation between MariaDB 10.4 and the later releases of MariaDB.

      Looking at the process list for MariaDB, the queries appear to be in statistics state:

      MariaDB [(none)]> show processlist;
      +--------+---------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
      | Id     | User    | Host            | db      | Command | Time | State        | Info                                                                                                 | Progress |
      +--------+---------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
      | 557804 | mage2ee | 127.0.0.1:48967 | mage2ee | Query   |   15 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 557805 | mage2ee | 127.0.0.1:48969 | mage2ee | Query   |   76 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558123 | mage2ee | 127.0.0.1:52219 | mage2ee | Query   |    8 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558148 | mage2ee | 127.0.0.1:52685 | mage2ee | Query   |  136 | Sending data | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558265 | mage2ee | 127.0.0.1:54449 | mage2ee | Query   |   30 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558524 | mage2ee | 127.0.0.1:57229 | mage2ee | Query   |  127 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558559 | mage2ee | 127.0.0.1:57549 | mage2ee | Query   |   12 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 558746 | mage2ee | 127.0.0.1:59659 | mage2ee | Query   |  144 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 559094 | mage2ee | 127.0.0.1:63031 | mage2ee | Query   |   42 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 559732 | mage2ee | 127.0.0.1:26585 | mage2ee | Query   |   40 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 559799 | mage2ee | 127.0.0.1:27695 | mage2ee | Query   |   13 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 559828 | mage2ee | 127.0.0.1:28109 | mage2ee | Query   |   24 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 559900 | mage2ee | 127.0.0.1:28501 | mage2ee | Query   |   22 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 560829 | mage2ee | 127.0.0.1:35231 | mage2ee | Query   |   27 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 561263 | mage2ee | 127.0.0.1:38325 | mage2ee | Query   |   30 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 561522 | mage2ee | 127.0.0.1:39811 | mage2ee | Query   |   48 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 561727 | mage2ee | 127.0.0.1:40905 | mage2ee | Query   |   88 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 561780 | mage2ee | 127.0.0.1:41085 | mage2ee | Query   |   23 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 562695 | mage2ee | 127.0.0.1:46377 | mage2ee | Query   |   53 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 562885 | mage2ee | 127.0.0.1:47351 | mage2ee | Query   |   14 | Statistics   | SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `eav`.`entity_id`, `eav`.`value`, `so |    0.000 |
      | 566453 | root    | localhost       | NULL    | Query   |    0 | starting     | show processlist                                                                                     |    0.000 |
      +--------+---------+-----------------+---------+---------+------+--------------+------------------------------------------------------------------------------------------------------+----------+
      

      In examining this issue, I have been able to reproduce consistently in a virtual machine with CentOS 7 installed and have tested MariaDB 10.5.16, 10.6.8, 10.7.4, and 10.8.3 to all suffer for this problem. To reproduce you just need a table with the InnoDB engine and some keys, adding more keys seems to cause the performance to worsen.

      create_table.sql

      DROP TABLE IF EXISTS test_table;
      CREATE TABLE test_table (
          `entity_id` int(10) unsigned NOT NULL,
          `value` int(10) unsigned NOT NULL,
          PRIMARY KEY (`entity_id`,`value`),
          KEY `TEST_TABLE_VALUE` (`value`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

      The test query which reproduces the performance issues is similar to the following, I have attached the test query I am running in my testing as a file as it is large.

      SELECT `sub`.`value`, COUNT(sub.value) AS `count` FROM (SELECT `test`.`entity_id`, `test`.`value` FROM `test_table` AS `test` WHERE test.entity_id IN (...)) AS `sub` GROUP BY `sub`.`value`;
      

      Each test, I have used the following commands to reinstall MariaDB with the version I am testing with:

      cat <<EOF > /etc/yum.repos.d/mariadb.repo
      # MariaDB CentOS repository list
      # http://downloads.mariadb.org/mariadb/repositories/
      [mariadb]
      name=MariaDB
      baseurl=http://yum.mariadb.org/10.4/centos7-amd64
      gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
      gpgcheck=1
      enabled=1
      EOF
       
      yum clean all
      yum -y remove 'MariaDB-*'
      yum -y remove 'galera-*'
      rm -Rf /var/lib/mysql
      yum -y --enablerepo=mariadb install MariaDB-server
       
      systemctl start mariadb
      

      MariaDB 10.4 results:

      [root@james-test ~]# mysql test
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 9
      Server version: 10.4.25-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> source create_table.sql
      Query OK, 0 rows affected, 1 warning (0.001 sec)
       
      Query OK, 0 rows affected (0.013 sec)
       
      MariaDB [test]> source query.sql
      Empty set (0.061 sec)
       
      MariaDB [test]> source query.sql
      Empty set (0.057 sec)
       
      MariaDB [test]>
      

      MariaDB 10.5 results:

      [root@james-test ~]# mysql test
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.5.16-MariaDB MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> source create_table.sql
      Query OK, 0 rows affected, 1 warning (0.001 sec)
       
      Query OK, 0 rows affected (0.010 sec)
       
      MariaDB [test]> source query.sql
      Empty set (7.881 sec)
       
      MariaDB [test]> source query.sql
      Empty set (7.628 sec)
      

      In-case it isn't obvious, this is testing on an empty table in an fresh install of MariaDB without customization to the my.cnf. We have tried the following options in multiple states without affect on the issue.

      optimizer_switch=rowid_filter=off
      optimizer_use_condition_selectivity=1
      optimizer_switch=optimize_join_buffer_size=off
      use_stat_tables=NEVER
      optimizer_search_depth=0
      

        Attachments

        1. mariadb10_5_8_optimizer_trace.json
          3.98 MB
        2. query.sql
          279 kB
        3. test.csv
          19 kB

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            james.coleman-ah James Coleman
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.