Bug #79450 Range optimizer utilizes too much memory with many OR conditions
Submitted: 30 Nov 2015 6:19 Modified: 15 Feb 2016 14:34
Reporter: Chaithra Gopala Reddy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2015 6:19] Chaithra Gopala Reddy
Description:
Range optimizer uses a lot of memory when creating SEL_TREE objects. Each SEL_TREE objest takes about 600 bytes. And for every OR condition there is a probability of creating one SEL_TREE object. This makes the default for new range_optimizer_max_mem_size exceed very easily when the query has lot of OR conditions.

How to repeat:
Check the code

Suggested fix:
SEL_TREE object has an array of SEL_ARG pointers. This is by default an array of 64 pointers based on MAX_KEy member. Instead use a Mem_root_array and reserve space using RANGE_OPT_PARAM's "key" member to know the maximum number of keys that are present in the table.
[15 Feb 2016 14:34] Paul DuBois
Noted in 5.7.12, 5.8.0 changelogs.

For queries ith many OR conditions, the optimizer now is more
memory-efficient and less likely to exceed the memory limit imposed
by the range_optimizer_max_mem_size system variable. Consequently,
the default value for that variable has been reduced from 1536000 to
8388608.
[15 Feb 2016 17:24] Paul DuBois
Correction: Default value has been raised, not reduced.