Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.8, 10.7.4, 10.8.3
-
Rocky 8.6
Intel i7-9750H (1) @ 2.591GHz 1G RAM
NVMe 493MiB/s 31.5k IOPS
Description
Attached file reproduces: a simple select using no where clause but using limit, with order-by on an unindexed attribute, against a table with a little over 500,000 rows, takes about 0.150s in 10.5.15, while in 10.6.8, 10.7.4, and 10.8.3 the same query consistently takes about 10s. When an index is added, the difference is much more dramatic. 10.5 drops from 0.150s to 0.004s, while 10.6, 10.7, and 10.8 all remain unchanged at 10s.
The explain plan shows that 10.6-10.8 always create a temporary table in addition to using filesort (or an index), while 10.5 just uses filesort in the first case and index in the second w/o needing to create a temporary table in either case. Increasing sort_buffer_size up to 512M makes no difference. One significant change is that the metadata (system) character set in 10.5 is utf8, whereas starting with 10.6 it's utf8mb3.
MariaDB [test]> show create table TEST_Table\G
|
*************************** 1. row ***************************
|
Table: TEST_Table
|
Create Table: CREATE TABLE `TEST_Table` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`categoryLookupId` int(11) DEFAULT NULL,
|
`date` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `aap_category` (`categoryLookupId`),
|
KEY `TEST_Table_date_cat` (`date`,`categoryLookupId`)
|
) ENGINE=InnoDB AUTO_INCREMENT=599441 DEFAULT CHARSET=utf8mb3
|
1 row in set (0.001 sec)
|
select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 25;
|
Unindexed 10.5 explain:
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
| 1 | SIMPLE | TEST_Table | ALL | NULL | NULL | NULL | NULL | 598694 | Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+--------+----------------+
|
Unindexed 10.6, 10.7, 10.8 explain
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
| 1 | SIMPLE | TEST_Table | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|
Typical indexed 10.5 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
|
| 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
|
| 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
|
+---------------------+----------------------------------+
|
5 rows in set (0.002 sec)
|
Typical unindexed 10.5 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:09 | 1a110af0481e6221693ec1ac8f9ebee6 |
|
| 2022-07-18 19:49:09 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:04 | 74968a188eb4f4dcc022669eff344f0f |
|
| 2022-07-18 19:49:01 | b323588d8d57ba607870ccf1df312b5c |
|
| 2022-07-18 19:48:57 | 822b38cf00f26ad6cdc0c59f5b0f6016 |
|
+---------------------+----------------------------------+
|
5 rows in set (0.104 sec)
|
Typical indexed 10.6 through 10.8 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
|
+---------------------+----------------------------------+
|
5 rows in set (9.803 sec)
|
Typical unindexed 10.6 through 10.8 performance
MariaDB [test]> select date, `TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` from `TEST_Table` order by date desc limit 5;
|
+---------------------+----------------------------------+
|
| date | propertyCategory |
|
+---------------------+----------------------------------+
|
| 2022-07-18 19:49:05 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:05 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:04 | 19736280721c57a798064fad686b684a |
|
| 2022-07-18 19:49:03 | 47fddf54db605db008ba78df7ab6c02e |
|
| 2022-07-18 19:49:00 | 5c884e67b699c62af82394735b4dfe1a |
|
+---------------------+----------------------------------+
|
5 rows in set (9.681 sec)
|
Attachments
Issue Links
- is caused by
-
MDEV-24089 support oracle syntax: rownum
-
- Closed
-
Activity
The change was introduced with
commit be093c81a7955aa8309e650b0a0c44a5afe113fb
Author: Monty <monty@mariadb.org>
Date: Mon Jan 4 17:03:16 2021 +0200
MDEV-24089 support oracle syntax: rownum
The workaround for this issue is to declare the function as DETERMINISTIC:
DELIMITER //
|
CREATE FUNCTION `TEST_RETURN_LOOKUP_VALUE`(LOOKUPID_IN INT) RETURNS varchar(255) CHARSET utf8 |
DETERMINISTIC
|
BEGIN
|
DECLARE LOOKUP_VALUE VARCHAR(255); |
|
|
SET LOOKUP_VALUE = (SELECT `value` FROM `TEST_Lookup` WHERE `lookupId` = LOOKUPID_IN); |
|
|
RETURN LOOKUP_VALUE; |
END; |
//
|
DELIMITER ;
|
We are currently not sure if it's a bug or not, will discuss it with the team.
Takeaways from the discussion at the SQL Processor Call:
It looks like Stored Routines that are not declared as DETERMINISTIC have properties similar to RAND(). The optimizer has very little freedom in optimizing how to invoke them, they should be computed for every row combination that's checked.
If this is so, the observed behavior is expected. If one wants the optimizer to be able to reduce the number of times the function is evaluated they should either declare the routine as DETERMINISTIC, or rewrite the query so that the stored function is invoked after the LIMIT is applied:
select |
date, |
`TEST_RETURN_LOOKUP_VALUE`(`categoryLookupId`) AS `propertyCategory` |
from |
(select date, categoryLookupId from `TEST_Table` order by date desc limit 25) as TBL; |
Closing this as not a bug since the customer is satisfied with the suggested solution.
Current code has two algorithms for ORDER BY:
1. Sort the first table.
2. Sort the temporary table.
When sorting the temporary table, the values of select list are computed when writing into the temporary table (This is the cause of the issues observed here)
Monty's patch added this:
if the select list has a function with RAND_TABLE_BIT, force use of temporary table.
Simple solution (#1)
Adjust Monty's patch: Set JOIN::rand_table_in_field_list only if the select list has
"truly random" functions.
Simple solution (#2)
Should a stored function call have RAND_TABLE_BIT?
Alternative solution (#3)
If the select list has EXPENSIVE_FUNC(t1.col1, t2.col2, ...)
then the temporary table's fields should include the arguments: (t1.col1, t2.col2, ...) but should not have the function call EXPENSIVE_FUNC(...). EXPENSIVE_FUNC(...) will be computed when reading from the temp. table.
Note that references to (t1.col1, t2.col2, ...) in EXPENSIVE_FUNC(t1.col1, t2.col2, ...) should be replaced with references to temp.table fields.
This all is implemented for GROUP BY already, consider
select EXPENSIVE_FUNC(col10, col11, ...)
|
from t1
|
group by
|
col10, col11, ...
|
We just need to enable it here. This is however much more risky than solution #1 or #2.
It's easy implement solution #1: https://github.com/MariaDB/server/tree/bb-10.6-mdev29129 ...
(EDIT: wait it might be incorrect)
Ok, the query is using a stored routine call:
I added
set @calls=@calls+1;into the TEST_RETURN_LOOKUP_VALUE function code.
On 10.9, after running the query, I get this:
+--------+| @calls |+--------+| 554293 |+--------+... while on 10.5, I get this:
MariaDB [test]> select @calls;+--------+| @calls |+--------+| 5 |+--------+