Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.31
-
None
-
Ubuntu 20.04.3 LTS
Description
We moved a database from MariaDB 10.2.39 to a new server with MariaDB version 10.3.31 and
noticed a query being very slow on the new server.
After troubleshooting for hours, we found out that the new split_materialized optimizer was causing the issue, which was enabled by default. With it enabled the query below takes over 2 minutes while it takes less than a second with it disabled.
The query:
SELECT DISTINCT |
post.userID AS ownPosts, |
meta.participants,
|
meta.totalLikes,
|
thread.*
|
FROM wbb1_thread thread |
LEFT JOIN wbb1_post post ON (post.threadID = thread.threadID AND post.userID = 29630) |
LEFT JOIN ( |
SELECT threadID, COUNT(DISTINCT userID) AS participants, SUM(cumulativeLikes) AS totalLikes |
FROM wbb1_post |
WHERE `time` > UNIX_TIMESTAMP() - 2592000 |
GROUP BY threadID |
) meta ON thread.threadID = meta.threadID |
WHERE thread.threadID IN (103361,99668,96785,113398,125293) |
ORDER BY thread.lastPostTime DESC,thread.threadID DESC; |
I also noticed that removing one of the joins on the wbb1_post table will make the query to complete fast with split_materialized enabled.
Create table:
CREATE TABLE `wbb1_post` ( |
`postID` int(10) NOT NULL AUTO_INCREMENT, |
`threadID` int(10) NOT NULL, |
`userID` int(10) DEFAULT NULL, |
`username` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', |
`subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', |
`message` longtext COLLATE utf8mb4_unicode_ci NOT NULL, |
`time` int(10) NOT NULL DEFAULT 0, |
`isDeleted` tinyint(1) NOT NULL DEFAULT 0, |
`isDisabled` tinyint(1) NOT NULL DEFAULT 0, |
`isClosed` tinyint(1) NOT NULL DEFAULT 0, |
`editorID` int(10) DEFAULT NULL, |
`editor` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', |
`lastEditTime` int(10) NOT NULL DEFAULT 0, |
`editCount` mediumint(7) NOT NULL DEFAULT 0, |
`editReason` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, |
`attachments` smallint(5) NOT NULL DEFAULT 0, |
`pollID` int(10) DEFAULT NULL, |
`enableHtml` tinyint(1) NOT NULL DEFAULT 0, |
`ipAddress` varchar(39) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', |
`cumulativeLikes` mediumint(7) NOT NULL DEFAULT 0, |
`deleteTime` int(10) NOT NULL DEFAULT 0, |
`lastVersionTime` int(10) NOT NULL DEFAULT 0, |
`enableTime` int(10) NOT NULL DEFAULT 0, |
`hasEmbeddedObjects` tinyint(1) NOT NULL DEFAULT 0, |
`isUzbot` tinyint(1) DEFAULT 0, |
PRIMARY KEY (`postID`), |
KEY `threadID` (`threadID`,`userID`), |
KEY `threadID_2` (`threadID`,`isDeleted`,`isDisabled`,`time`), |
KEY `isDeleted` (`isDeleted`), |
KEY `isDisabled` (`isDisabled`), |
KEY `ipAddress` (`ipAddress`), |
KEY `time` (`time`), |
KEY `abbc0439063d333ac79065f1379ac5ef_fk` (`userID`), |
KEY `fe4ab714a15afd3495126fede5ad0b6c_fk` (`editorID`), |
KEY `33917154f7a46447d164b6fb12be8f36_fk` (`pollID`), |
KEY `postEnableTime` (`enableTime`), |
KEY `userToPost` (`userID`,`isDeleted`,`isDisabled`,`threadID`), |
KEY `thread_3` (`threadID`,`isDisabled`,`userID`,`time`), |
CONSTRAINT `33917154f7a46447d164b6fb12be8f36_fk` FOREIGN KEY (`pollID`) REFERENCES `wcf1_poll` (`pollID`) ON DELETE SET NULL, |
CONSTRAINT `87e5b9c2dcf7bf276097178fc13ee88c_fk` FOREIGN KEY (`threadID`) REFERENCES `wbb1_thread` (`threadID`) ON DELETE CASCADE, |
CONSTRAINT `abbc0439063d333ac79065f1379ac5ef_fk` FOREIGN KEY (`userID`) REFERENCES `wcf1_user` (`userID`) ON DELETE SET NULL, |
CONSTRAINT `fe4ab714a15afd3495126fede5ad0b6c_fk` FOREIGN KEY (`editorID`) REFERENCES `wcf1_user` (`userID`) ON DELETE SET NULL |
) ENGINE=InnoDB AUTO_INCREMENT=885294 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
The table has 638543 entries.
Query explain:
MariaDB 10.2.39:
|
+------+-------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|
| 1 | PRIMARY | thread | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where; Using temporary; Using filesort |
|
| 1 | PRIMARY | post | ref | threadID,threadID_2,abbc0439063d333ac79065f1379ac5ef_fk,userToPost,thread_3 | threadID | 9 | hayday_forum.thread.threadID,const | 1 | Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | hayday_forum.thread.threadID | 10 | |
|
| 2 | DERIVED | wbb1_post | range | time | time | 4 | NULL | 4231 | Using index condition; Using filesort |
|
+------+-------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|
 |
MariaDB 10.3.31 (split_materialized=on):
|
+------+-----------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|
| 1 | PRIMARY | thread | range | PRIMARY | PRIMARY | 4 | NULL | 5 | Using where; Using temporary; Using filesort |
|
| 1 | PRIMARY | post | ref | threadID,threadID_2,abbc0439063d333ac79065f1379ac5ef_fk,userToPost,thread_3 | threadID | 9 | hayday_forum.thread.threadID,const | 1 | Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | hayday_forum.thread.threadID | 2 | |
|
| 2 | LATERAL DERIVED | wbb1_post | ref | threadID,threadID_2,time,thread_3 | threadID | 4 | hayday_forum.thread.threadID | 4 | Using where |
|
+------+-----------------+------------+-------+-----------------------------------------------------------------------------+----------+---------+------------------------------------+------+----------------------------------------------+
|