Description:
Hello MySQL Team!
Is there any reason, why MySQL 8.0+ has higher memory requirements for the range optimizer as compared to version 5.7 given the same table and query?
This introduces a regression problem where after the major version upgrade, and while using the same range_optimizer_max_mem_size, some queries will fall back to a much slower execution plan.
How to repeat:
This can be confirmed with a simple sysbench table (populated with 10k rows):
mysql > show create table test.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
On version 5.7.44, with memory instrumentation enabled:
mysql > update performance_schema.setup_instruments set enabled="yes" WHERE NAME like 'memory%quick%';
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql > select count(*) from test.sbtest1 where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100);
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
mysql > select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=28 and event_name='memory/sql/test_quick_select'\G
*************************** 1. row ***************************
THREAD_ID: 28
EVENT_NAME: memory/sql/test_quick_select
COUNT_ALLOC: 5
COUNT_FREE: 5
SUM_NUMBER_OF_BYTES_ALLOC: 24384
SUM_NUMBER_OF_BYTES_FREE: 24384
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 5
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 24384
1 row in set (0.01 sec)
But the very same test on 8.0.37 or 8.4.0, gives over two times higher memory footprint:
mysql > select count(*) from test.sbtest1 where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100);
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
mysql > select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=PS_CURRENT_THREAD_ID() and event_name='memory/sql/test_quick_select'\G
*************************** 1. row ***************************
THREAD_ID: 47
EVENT_NAME: memory/sql/test_quick_select
COUNT_ALLOC: 5
COUNT_FREE: 5
SUM_NUMBER_OF_BYTES_ALLOC: 54256
SUM_NUMBER_OF_BYTES_FREE: 54256
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 5
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 54256
1 row in set (0.00 sec)
It is not a problem of wrong memory estimation or instrumentation, as in both cases, lowering the range_optimizer_max_mem_size below the result value, makes the optimizer skip this optimization, for example on 8.0:
mysql > show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Warning | 3170 | Memory capacity of 54000 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Suggested fix:
If the higher memory usage in 8.0 is a result of unintended regression, revert the behavior.