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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.