Description:
Hello MySQL Team!
The documented guidelines we can find here:
https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-optimization-memory-...
suggest that we could expect about 230 bytes per predicate in a simple IN (1,2...N) query. However, when we check the real amount used, as advised in:
https://bugs.mysql.com/bug.php?id=78752, it turns out to be much more than that.
How to repeat:
Populate a simple sysbench table:
mysql> show create table 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 `pad` (`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=2024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql > truncate table performance_schema.memory_summary_by_thread_by_event_name;
Query OK, 0 rows affected (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: 0
COUNT_FREE: 0
SUM_NUMBER_OF_BYTES_ALLOC: 0
SUM_NUMBER_OF_BYTES_FREE: 0
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 0
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 0
1 row in set (0.00 sec)
mysql > select count(*) from 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.01 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)
So, 542 bytes instead of 230 here!
Indeed, lowering the memory limit below the P_S reported value disables the optimization:
mysql > set range_optimizer_max_mem_size=54000;
Query OK, 0 rows affected (0.00 sec)
mysql > select count(*) from 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, 1 warning (0.00 sec)
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)
Interestingly, when a non-related secondary key is dropped, the memory requirement goes down:
mysql > alter table sbtest1 drop key pad;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql > truncate table performance_schema.memory_summary_by_thread_by_event_name;
Query OK, 0 rows affected (0.00 sec)
mysql > select count(*) from 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: 67
EVENT_NAME: memory/sql/test_quick_select
COUNT_ALLOC: 4
COUNT_FREE: 4
SUM_NUMBER_OF_BYTES_ALLOC: 33472
SUM_NUMBER_OF_BYTES_FREE: 33472
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 4
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 33472
1 row in set (0.00 sec)
But, still higher than 230 bytes.
Suggested fix:
Provide more accurate memory guidelines for range optimization.
