| Bug #78973 | increase default value for range_optimizer_max_mem_size | ||
|---|---|---|---|
| Submitted: | 27 Oct 2015 7:01 | Modified: | 8 Apr 2016 1:29 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S4 (Feature request) |
| Version: | 5.7.9 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Oct 2015 7:01]
Shane Bester
[27 Oct 2015 7:02]
MySQL Verification Team
memory consumption tester.
Attachment: bug78973.sql (application/octet-stream, text), 1.84 KiB.
[6 Nov 2015 7:16]
MySQL Verification Team
A small amendment to the initial report.
My original testcase was using larger number of items than I let on.
The simplest form query like this:
select count(*) from t where a=1 or a=2 or a=3 or ... a=N
will exceed the default value of range_optimizer_max_mem_size (1536000)
when N >= 2122
Therefore each item took ~724 bytes of memory.
[23 Dec 2015 16:15]
Joe Kislo
I agree with the above statements, this is going to burn a lot of unsuspecting people. It will burn them hard when it does, our queries went from .05 seconds to 700 seconds. These kinds of queries weren't even on our radar before. Our use case is multiple worker threads pulling back groups of records by primary key. So we had half a dozen of these queries running in parallel when this hit. I would expect this is a somewhat common pattern for people to be using when there isn't a data-driven way of splitting work between workers. Fortunately we're just testing 5.7 at this point. When I was googling to figure out what was going on, I found a few pages that list this and one other change as the biggest 'gotcha's that will blindside you when you migrate to Mysql 5.7. So this has surprised a lot of other people too. As for the default, I agree the default set is too low. Since forever 16,000 items was the limit, and it seemed to have treated us all very well. Changing the default to something that is approximately 2122 in the simplest case isn't replicating the previous generally acceptable behavior. In our case, we had a multi-part primary key, with a varchar on the table. We topped out in the hundreds. Given that MySQL is a database *server*, it seems like we can probably spare more than 1.5MB of transient ram to process queries. People who are really tight with their ram can tune down from the default, but I think the assumption for a default install should be much higher. Given that people already have a max packet size set (default 4MB), there's already a natural limit to how much ram could be taken up by these kinds of queries. I feel like we've traded a problem that burned 1 person over 10 years, with something that will burn 1000x as many people. As a side note, this problem is fairly hard to diagnose. The slow query log, which was the first place we checked, is fairly deceiving: # Query_time: 698.631980 Lock_time: 0.021423 Rows_sent: 1000 Rows_examined: 1000 My read of that is that it did a lookup using the PK index. However what actually happened was it did a full table scan. I would have thought the Rows_examined would have been the 100+million rows in this table. I'm not sure why it says it just examined 1,000 rows when it handled this query. It clearly did a table scan and examined each row. However kudos to whoever had the server throw a warning. However warnings don't come out in the slow query log, so we had to try to replicate the state so we could run the query manually.
[23 Dec 2015 16:26]
Joe Kislo
To be clear, I am advocating raising the default limit to try to hit 16,000, which was the previous limit, and was generally acceptable for many many years. In my limited searches, I couldn't find anybody complaining that their query 4MB queries taking too much ram to process. I feel like it's fair to assume if you send in a 4MB query (mysql default max packet size), MySQL could take 64mb of transient ram to process it (~16,000 limit)
[11 Aug 2016 11:48]
MySQL Verification Team
The default has been changed apparently, but this bug report not updated. mysql> select @@global.range_optimizer_max_mem_size, version(); +---------------------------------------+-----------+ | @@global.range_optimizer_max_mem_size | version() | +---------------------------------------+-----------+ | 8388608 | 5.7.14 | +---------------------------------------+-----------+ 1 row in set (0.00 sec)
