Bug #103225 "Out of sort memory error" has an inconsistent relationship with the buffer size
Submitted: 6 Apr 15:03 Modified: 5 Aug 18:49
Reporter: Saverio Miroddi Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Ubuntu
Assigned to: CPU Architecture:Any

[6 Apr 15:03] Saverio Miroddi
Description:
On MySQL 8.0.23, a certain dataset and query cause an "Out of sort memory" (1038) error.

This is reproducible, and it does not happen on 8.0.17.

What is odd is that the relationship with `sort_buffer_size` is not consistent. The error suggests to increase it, however:

- the default, 262144 (256K) causes the error
- a smaller value, 256000, doesn't cause the error
- a slighly higher value, 263000, causes the error
- a higher value, but still not by much, 300000, doesn't cause the error

How to repeat:
I'm attaching the dataset and the query, in private.
[6 Apr 15:14] Saverio Miroddi
Minor addition:

The values that cause the error (or prevent it) can be slightly different, and they don't seem to be constant in time.

The inconsistency still holds:

- 256000 doesn't cause the error
- higher values (280000, 300000, ...) often do.
[6 Apr 16:02] Steinar Gunderson
Hi,

A potentially relevant change is that somewhere between those versions, we started sorting small blobs, such as TEXT, as addon fields instead of always doing sort-by-rowid. This is the reason why there's now more pressure on the sort buffer (but for most cases, sorts should still be faster). I see you have a TEXT field in your data (but I haven't looked at it apart from that).

Counterintuitively, there's not really a guarantee that a larger sort buffer will always be able to sort something a smaller sort buffer did, since you're dependent on a tiny bit of luck during merging; say you have three sort chunks (A,B,C) and two rows with very large blobs (say number 10 and 11), a larger sort buffer may cause chunk A to end a bit later, causing 10 to fall into chunk A and 11 into chunk B, which means both 10 and 11 may need to be visible during the final merge. But if you have a smaller sort buffer, you may get lucky and have both 10 and 11 go into chunk B, which means they won't be visible at the same time when merging A-B-C.

This is just general information, but I'd probably try to change from TEXT to LONGTEXT if you want the 8.0.17 behavior back. Optionally, just run with a larger sort buffer if you have many blobs close to 64 kB and want to include them in your sorts.
[6 Apr 17:15] Saverio Miroddi
Hello!

Thanks, this makes complete sense.

I think it's important to expand on this subject in the documentation of the setting. The reason is that there is lots of wrong information about the sort buffer size (even prompting Baron Schwartz to discourage changing it at all), so it's not obvious how to cautiously change it.

There's additional complexity that makes the subject even more complex (e.g. the setting description mentions thresholds in relationship to the O/S, and it even suggests to change it on a per-session basis).

Finally, due to the non-deterministic behavior, the advice given by the error is not strictly true - on the same query, say, compared to 256K, 250K and 280K may work, but not 300K.

I suggest to expand the guideline:

> At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer

to take into account the case where small blobs are involved; it could be added that in such cases, a tuple may be (I don't know the correct value) 64KiB, requiring a buffer size of 64KiB * 15.

Again, the reason for my suggestion is that this is a critical buffer, and increasing it should be done very cautiously (at least, this is what I know of it). Other similar cases like the packet buffer can be increased relatively liberally, so they don't require special care.
[7 Apr 12:49] MySQL Verification Team
Hi Mr. Miroddi,

Thank you for your bug report.

We agree with you that this change should be better documented, most probably in our Release Notes.

Verified as a documentation bug.
[7 Apr 12:50] MySQL Verification Team
Correct version.
[24 Apr 18:33] Rick James
This seems to be a dup of the two reports mentioned on
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html --

"One effect of this enhancement is that it is now possible for Out of memory errors to occur when trying to sort rows containing very large (multi-megabtye) JSON or GEOMETRY column values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the sort_buffer_size system variable. (Bug #30400985, Bug #30804356)"

Plus https://jira.mariadb.org/browse/MDEV-24015

This error seems to be caused by an "improvement" in the Optimizer.  The "right" way to deal with the error is to do one of these:

* Use statistics on the table to conservatively estimate that the JSON (etc) could be too big to avoid the error, then use the old sorting method.  Or
* Catch the "out of memory" and revert to the old way.

One reverts unnecessarily in some cases (where the stats are not precise enough); the other has to undo some work (making it slower).

Here is another test case:  https://stackoverflow.com/questions/67242726/mysql-8-order-by-created-at-out-of-sort-memor...
[25 Apr 7:14] MySQL Verification Team
Bug #103465 marked as duplicate of this one
[25 Apr 7:28] Chris Rollins
I agree with Rick on this. This seems like more than just a "documentation" bug. As I reported in https://bugs.mysql.com/bug.php?id=103465, I get this error for a small table (<100 rows) with JSON values < 1MB.

If I increase the buffer size from 1MB to 10MB the problem goes away, but I'm unsure whether it'll resurface as the table grows.

I don't feel increasing the buffer size is the correct action, however, and I don't feel adding indexes to every non-JSON field just so I can sort by them whenever I need to is the answer either.
[25 Apr 15:45] Rick James
Note that adding a few rows to a table (while changing nothing else) can cause the problem to occur.  Then adding a few more rows can cause it to go away.  This is really strange behavior.
[25 Apr 15:47] Rick James
Please change from away from "Category:	MySQL Server: Documentation"
[26 Apr 0:52] Ben Kuhl
Earlier today I upgraded to 8.0.20 and have started seeing this issue with queries as simple as below, where all fields in use are either primary or foreign keys.  The "messages" table in this example is a json field, which I can't switch to text or a blob because I query based on values within it and have an index on that field.

SELECT * FROM `messages`
WHERE
    EXISTS (
        SELECT * FROM `updates`
        WHERE
            `messages`.`update_id` = `updates`.`id`
            AND `game_id` = 18
    )
ORDER BY `id` DESC
LIMIT 6
[26 Apr 12:17] Ben Kuhl
We've decided to revert to 8.0.17 as it's more stable for our use cases than tweaking the buffer memory limit (which has been causing issues for us in production).  It seems reasonable to be able to order by the primary key on a table and not run into such memory issues.  I hope the team reconsiders the approach with this and considers changes to avoid the need for buffer memory tweaks.
[26 Apr 13:44] MySQL Verification Team
Hi,

We recommend to everybody to try our latest release, 8.0.24.
[26 Apr 14:14] Chris Rollins
Hi MySQL Verification Team,

If you take a look at https://bugs.mysql.com/bug.php?id=103465 you'll see that this issue occurs on version 8.0.24 as well.
[27 Apr 12:15] MySQL Verification Team
Hi Mr. Rollins,

Thank you for your feedback.
[11 Jun 14:58] Yaroslav Dobzhanski
On version 8.0.25 our dataset is still affected.

Getting error 

Error: ER_OUT_OF_SORTMEMORY: Out of sort memory, consider increasing server sort buffer size
Error code: ER_OUT_OF_SORTMEMORY

Then for experiment tried to increase sort-buffer-size, it stops failing on values > 900k

It's failing on querying not big at all (11 records) table. This table has some JSON values. 

Overall size of queried JSON values is around 100 KB
[14 Jun 12:44] MySQL Verification Team
Thank you for your feedback.
[15 Jun 3:38] Eric Birckelbaw
Hello,

We are also running version 8.0.25 and are also experiencing this error. There are no JSON fields in our table, however have multiple TEXT fields.

Changing the sort_buffer_size does not seem to make a difference in our situation. We tried 256k, 300k, numerous other values around there, as well as some very large values to ensure it is not actually running out of memory. All to no avail.

We did find however that any minor tweaks to our query resolves the issue. We currently are selecting ~1500 rows from a InnoDB table with ~10M rows, and ordering results by two columns: a varchar cast to a decimal, and a plain varchar. Removing the CAST, or either of the columns from the ORDER BY, seems to bypass the issue. I can provide more information about the table / query if this would be helpful to the investigation.

Cheers!
[15 Jun 12:55] MySQL Verification Team
Hi,

sort_buffer_size usually starts with circa 1 Mb, unless you have thousands of concurrent connections. Also, if your query is not using any kind of sorting, like filesort, then this variable is ignored.
[16 Jun 6:05] Eric Birckelbaw
Running explain on our query, filesort is being used, and we tried upwards of the GiB range on sort_buffer_size with no effect. Does this sound like an unrelated issue or likely this bug?
[2 Jul 21:08] Ted Hamilton
Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.
[2 Jul 21:08] Ted Hamilton
Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.
[5 Jul 11:39] MySQL Verification Team
Hi,

The message from the server is quite clear. You need some minimal memory for the sorting, even if disk is used, when the filesort algorithm is used. This is especially the case when the records that are to be sorted are of the variable length. Sort buffer simply, much be able to accommodate the longest record.

Also, as long as a bug is verified, the category can be changed, if it leads to the creation of the different algorithm.
[5 Jul 11:47] Rob E
You would expect the sort buffer to only take the columns into account that are used for sorting, but this bug is about big values in columns that the results are not sorted on.
[13 Jul 9:09] Aimilios Tsouvelekakis
Good morning,

this is something that affect us also in a table with BLOB data. We have not managed to reproduce it yet in another table or in the same table in the development database, so we just increased for the moment the parameter.

Since MySQL offers the upgrade checker utility which checks for utfmb3 to utfmb4 all the tables why you do not include one more check for tables with BLOB / TEXT columns and with a warning that these tables might be need to be converted into MEDIUMBLOB / LONGBLOB or MEDIUMTEXT / LONGTEXT.

Of course that won't solve the problem from people doing minor upgrades so another solution shall be found for that.
[13 Jul 11:35] MySQL Verification Team
Hi Mr. Tsouvelekakis ,

Your recommendations have been copied to the team in charge .....
[28 Jul 18:26] Shane Niebergall
Spent several frustrating hours on this today. Thankfully I found this bug report (I agree this is a bug, not a documentation issue).

Steinar mentioned changing TEXT to LONGTEXT. I can confirm that changing to MEDIUMTEXT also fixes the issue.
[28 Jul 23:47] Rick James
What does a failing case say in EXPLAIN FORMAT=JSON SELECT ... ? And what does the Optimizer Trace say?
[5 Aug 14:33] Stefan Hinz
Closing this bug since it's unclear from the discussion in this bug report what the documentation should point out in addition to what's been said in the 8.0.20 release notes entry.
[5 Aug 15:04] Chris Rollins
I'm sorry, but did you seriously just close this bug because you're unclear if there's an actual error? It's very clear that there is an error, as I reported in https://bugs.mysql.com/bug.php?id=103465, which you also closed because you incorrectly felt it was just a duplication of this issue.

This error still exists and still affects me. What do you recommend I do, create yet another ticket to reopen this yet again?
[5 Aug 15:57] Shane Niebergall
I feel for ya, Chris. Luckily there's a workaround for TEXT fields (use MEDIUMTEXT), but for JSON there is no reasonable workaround from my understanding.

If this isn't going to be fixed, I'd suggest that this information isn't just posted in the release notes, but that it's spread all throughout the normal documentation of any data type that is affected.
[5 Aug 16:34] Yaroslav Dobzhanski
MySQL team, are you sure, that introduced in 8.0.20 possibility for totally unexpected 'Out of memory errors' really can be described as ENHANCEMENT? 

Each project using JSON\LONGBLOB fields is under the risk of such totally unexpected bug now. What is enhanced actually? Everything was working well and fast from 5.x till 8.0.19, and 8.0.20 introduces breaking 'enhancement', which is hard to find and expect.

So great enhancement! How many such breaking enhancements can we expect in the future?
[5 Aug 16:48] Rick James
MySQL's reputation is tarnished.  MySQL has rarely had regressions.  Almost always regressions are promptly fixed by the next minor release.

But this time a regression is being swept under the rug as a "documentation" issue.

Shame on you!
[5 Aug 18:49] Saverio Miroddi
I haven't checked this for quite some time, but I think that the documentation has been updated in the meanwhile - there is a guideline, now:

> The optimizer tries to work out how much space is needed but can allocate more, up to the limit. Setting it larger than required globally slows down most queries that perform sorts. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload.

I think this is an arguable approach, but on the other hand, there is a working guideline (that is, to increase the buffer within an upper limit of 2 MB). This interval did work for our system, so my original request of extending the documentation is fulfilled. However, I can't speak for the other cases.
[6 Aug 11:40] MySQL Verification Team
Hi All,

A small clarification regarding this problem. 

It is true that our Release Notes documents properly that we have improved our index-merge algorithm, so that sorts are faster. This, however, comes at the expense of a need to have a larger sort buffer size, when ordering result set is required.

Hence, the workaround is simple:

* Save current local sort_buffer_size to a local user variable
* Set local sort_buffer_size to some larger value, that will not result in the error, as in the title
* Run the query / queries
* Reset local sort_buffer_size to the saved value.

It is possible that in the future we shall change minimum or default value for this configurational variable, but discussions on the subject are still ongoing.
[6 Aug 12:15] Chris Rollins
Hi MySQL team, as I mentioned before, if I increase the buffer size from 1M to 10M the problem goes away, but it doesn't seem right to me that I have to increase this just because my table has a lot of data in the JSON fields (which by the way should not even be used in the sort, as I'm sorting by other small, non-JSON fields).

Also, as my table grows, am I going to continually need to increase buffer size to accommodate? If my table reaches 1GB in size and I need to sort thousands of records, will I need to increase the buffer size to 1GB?
[6 Aug 12:27] MySQL Verification Team
The simplest answer to your question is: No.

The size depends on the columns width, not on the number of rows.
[6 Aug 12:46] Rob E
Could you explain why columns that aren't used in sorting are counted towards the sort buffer size?
[6 Aug 12:52] MySQL Verification Team
Hi,

We wrote about a comment in the Release Notes. Well, this optimisation does include, in certain cases, column(s) that is not in sorting criteria, but its order is affected by the sorting.

This eliminates the need for several passes, which is why sorting is performed in the single pass, for the performance reasons. There is a logic, of course, when and how is it done, but that goes beyond our documentation .......
[6 Aug 13:26] Rick James
That "workaround" is incomplete.

* Today I tweak sort_buffer_size and push it to production.  (As described in the Verification Team's comment.)  But...
* Tomorrow some new data arrives and/or an existing JSON changes in size.
* And production crashes again.
[6 Aug 13:30] Rob E
I don't think it's a good idea that this optimisation includes JSON columns that are not in the sorting criteria. Before 8.0.20 this was only done for blobs of size tinyblob and blob, which are 64KiB at most. JSON values can be up to 64MiB by default (max_allowed_packet), which is a huge difference. 

It requires me to limit the contents of what I put in a JSON column to be sure not to overrun the sort buffer size, even though I don't use it for sorting.

If I don't want to limit the contents, to what do I have to increase the sort_buffer_size? If I understand the documentation well ("At minimum the sort_buffer_size value must be large enough to accommodate fifteen tuples in the sort buffer."), should I then increase it to more than 15 * 64MiB, even though the documentation also recommends me to keep it below 2MiB?
[9 Aug 11:58] MySQL Verification Team
Just to inform you all that there is an internal discussion on the subject, but decision, when made, will be visible only in the Manual and / or in the Release Notes.
[9 Aug 12:06] Rob E
Thanks for the update, I'm looking forward to the outcome. 

Can you explain why it will not be posted here? I think it would help everyone that is subscribed to this thread and everyone visiting this page in the future.
[9 Aug 12:16] MySQL Verification Team
The reply to your question depends on the timing and depends on the decisions that will be made.
[8 Sep 16:28] Arnaud Becher
Any news ?
[9 Sep 11:59] MySQL Verification Team
Hi,

We are the team for verifying reports and we do not have access to the scheduling of the bug fixes nor of the arrival of new features.
[21 Sep 13:59] Olivier Kessler
Hello,

We are experiencing the same error over a 8.0.25 MySQL Community Server installation.

One of our query fails since the migration from MySQL 5.7, the execution plan shows only const, index, and eq_ref operations.

All the sorting is done on indexed fields with high value cardinality.

Yet a text field (not used in sorting) in the select statement seems to overflow the sort buffer.

We will probably follow the instructions and tweak the settings, hoping that a more generic solution will be published soon.

Cheers,
Olivier.