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.
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.