Although how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this.
What problem does this variable cause if it is not properly sized? Let’s find out with an example!
Given the following table definition:
1 2 3 4 5 6 7 | CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1 |
With ~2M rows
1 2 3 4 5 6 | select count(*) from joinit ; +----------+ | count(*) | +----------+ | 2097152 | +----------+ |
With the default value of range_optimizer_max_mem_size = 8388608, the following query on PK executes very fast!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> select * from joinit where i in (1,2,3,4,5,6,7,8,9,10); +---+--------------------------------------+----------+----+ | i | s | t | g | +---+--------------------------------------+----------+----+ | 1 | ecb5774f-4e92-11eb-9465-00163e462f55 | 13:44:10 | 38 | | 2 | ecb9f236-4e92-11eb-9465-00163e462f55 | 13:44:10 | 16 | | 3 | ecbac904-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 | | 4 | ecbacaf0-4e92-11eb-9465-00163e462f55 | 13:44:10 | 22 | | 6 | ecbb4b04-4e92-11eb-9465-00163e462f55 | 13:44:10 | 30 | | 7 | ecbb4d68-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 | | 8 | ecbb4dc6-4e92-11eb-9465-00163e462f55 | 13:44:10 | 37 | | 9 | ecbb4e01-4e92-11eb-9465-00163e462f55 | 13:44:10 | 49 | +---+--------------------------------------+----------+----+ 8 rows in set (0.00 sec) |
But what if we decrease range_optimizer_max_mem_size to let’s say, 500?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> set range_optimizer_max_mem_size = 500; Query OK, 0 rows affected (0.00 sec) mysql> select * from joinit where i in (1,2,3,4,5,6,7,8,9,10); +---+--------------------------------------+----------+----+ | i | s | t | g | +---+--------------------------------------+----------+----+ | 1 | ecb5774f-4e92-11eb-9465-00163e462f55 | 13:44:10 | 38 | | 2 | ecb9f236-4e92-11eb-9465-00163e462f55 | 13:44:10 | 16 | | 3 | ecbac904-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 | | 4 | ecbacaf0-4e92-11eb-9465-00163e462f55 | 13:44:10 | 22 | | 6 | ecbb4b04-4e92-11eb-9465-00163e462f55 | 13:44:10 | 30 | | 7 | ecbb4d68-4e92-11eb-9465-00163e462f55 | 13:44:10 | 26 | | 8 | ecbb4dc6-4e92-11eb-9465-00163e462f55 | 13:44:10 | 37 | | 9 | ecbb4e01-4e92-11eb-9465-00163e462f55 | 13:44:10 | 49 | +---+--------------------------------------+----------+----+ 8 rows in set, 1 warning (0.82 sec) |
Woooh! Query execution time increased from 0.00 seconds to 0.82 seconds! What might be happening? Let’s check the explain and handlers:
With range_optimizer_max_mem_size = 8388608:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain select * from joinit where i in (1,2,3,4,5,6,7,8,9,10); +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | joinit | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show status like '%handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ ... | Handler_read_key | 10 | |
With range_optimizer_max_mem_size = 500:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> explain select * from joinit where i in (1,2,3,4,5,6,7,8,9,10); +----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | joinit | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2088128 | 50.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> show status like '%handler%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ ... | Handler_read_rnd_next | 2097153 | |
It can be seen that there was a query plan execution change. With default value, the execution plan used the primary key to scan only 10 rows, but after decreasing range_optimizer_max_mem_size, a full table scan occurred which resulted in 2097153 row reads! This can be detrimental to performance.
The formal explanation for this behavior can be found at the end of the official documentation.
To estimate the amount of memory needed to process a range expression, you should check the query condition, and in the case of IN() predicate, it will require 230 bytes per value. For completeness, it’s worth knowing that each “OR” condition in the where clause will require an additional 230 bytes, and each “AND” condition an additional 125 bytes.
Having the default value of 8388608 (MySQL version 5.7.12 and above) should be enough for up to an IN condition of ~36473 clauses.
In conclusion, it can be seen that the default value should be more than enough for most (if not all) the queries, but after a tipping point (or if the variable is misconfigured), it might happen that a well-performing query turns into a full table scan that can be detrimental to performance.
Interesting insights, thank you!
Do you know if there is an equivalent in MongoDB? I mean, does MongoDB behaves similarly when the IN condition contains many values and does MongoDB also provides a parameter to tune it?
Hi Kay. Am not familiar with any similar setting in MongoDB