MySQL Bugs: #105089: Allow optional configuration to make warning 3170 an error

Bug #105089	Allow optional configuration to make warning 3170 an error
Submitted:	30 Sep 2021 16:57	Modified:	6 Oct 2021 10:21
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ER_CAPACITY_EXCEEDED_IN_RANGE_OPTIMIZER, error, warning

Description:
This error: ER_CAPACITY_EXCEEDED_IN_RANGE_OPTIMIZER

This error:

3170 | Memory capacity of 201326592 bytes for
'range_optimizer_max_mem_size' exceeded. Range optimization was not done for
this query.

is actually a warning. It indicates the server won't do a point lookup query but will do a table scan because the configuration setting for range_optimizer_max_mem_size has been exceeded.

That significantly changes the expected performance characteristics of a query from ms to minutes to run even if storage if fast and you have a SSD if the table is large.

With a heavy query rate modifying only a small amount the number of ids provided you can full overload the server without warning. I have seen this happen on a number of occasions.

Documentation on how to calculate the number of ids is incorrect (see bug#10400) so you can not check and prevent this happening.

Workarounds are good and using MAX_EXECUTION_TIME settings or query hints help but it's probably better to have an optional setting to make this warning an error and thus fail immediately. This would prevent the server doing the query at all with the given numnber of range arguments and possibly allows the app to adapt the number and make multiple queries instead. Not ideal but doable and certainly better than taking down the server with a minor change in the number of elements provided in a range based query.

How to repeat:

See: documentation on the behaviour of range_optimizer_max_mem_size
See: that there are docs on trying to find out how much memory will be used based on the query but then see this is buggy and hard for automation to use effectively

Suggested fix:
Other bug reports ask to fix the docs. Please do that and make it easier to calculate the number of range elements that can be used. This in theory would prevent the problem occurring.

The change requested here is for a way to prevent the table scan:
* a session variable to do this
* a global variable to do this (same name)
* query hint to do this

A suggested name might be something like:

range_optimizer_fail_on_memory_exceeded

Such a change would almost not touch any existing logic apart from requiring access to the appropriate variable(s) and/or query hint infrastructure.

This very old bug while closed indicates the problem we have wanting to be able to safely configure and use the right number of lookup entries: bug#78973

So the problem space is not new. Now we can go past (or could but now memory usage does not seem to allow us to get to 16k entries) 16k values we then make a query going over the new limit and go back to where we were before.

I'd like to be able to use 16k entries but that doesn't seem possible at the moment and can't figure out how many entries to use which is worse.

So if we go over the limit and do it quickly we can readjust and try again, and this fail fast approach provides that as an option and also prevents unintentionally overloading the server which is the worst thing that can happen.

Hello Simon,

Thank you for the feature request!

regards,
Umesh

Adjust synopsis slightly to give the warning number.

An initial look seems to show the error is handled here: https://github.com/mysql/mysql-server/blob/8.0/sql/opt_range_internal.h#L69-L102

and interestingly enough the error is converted into a warning.

So making the behaviour configurable would give me the flexibility I'm after.