Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.16, 10.6.8, 10.7.4, 10.8.3
-
None
-
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
|