MySQL Bugs: #103225: "Out of sort memory error" has an inconsistent relationship with the buffer size

Bug #103225	"Out of sort memory error" has an inconsistent relationship with the buffer size
Submitted:	6 Apr 2021 15:03	Modified:	5 Nov 2021 14:40
Reporter:	Saverio Miroddi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

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.

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.

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.

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.

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.

Correct version.

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

Bug #103465 marked as duplicate of this one

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.

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.

Please change from away from "Category:	MySQL Server: Documentation"

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

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.

Hi,

We recommend to everybody to try our latest release, 8.0.24.

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.

Hi Mr. Rollins,

Thank you for your feedback.

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

Thank you for your feedback.

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!

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.

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?

Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.

Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.

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.

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.

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.

Hi Mr. Tsouvelekakis ,

Your recommendations have been copied to the team in charge .....

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.

What does a failing case say in EXPLAIN FORMAT=JSON SELECT ... ? And what does the Optimizer Trace say?

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.

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?

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.

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?

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!

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.

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.

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?

The simplest answer to your question is: No.

The size depends on the columns width, not on the number of rows.

Could you explain why columns that aren't used in sorting are counted towards the sort buffer size?

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

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.

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?

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.

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.

The reply to your question depends on the timing and depends on the decisions that will be made.

Any news ?

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.

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.

Hi All,

This is just to inform all the subscribers that it was decided that this bug gets fixed. We do not know, nor does anybody else, which release will see that fix.

This is being fixed together with BUG#105304.

Please see that bug report for updates.

Thanks.

Thank you, Jon !!!!!

Woohoo! Thank you for listening and being open :)

Great news! Thanks to development team for getting into problem and looking for solution!

Fixed in MySQL 8.0.28 by BUG#105304.

Thank you, Jon.

It seems like this problem is not completely fixed.

In 8.0.28 I encountered this error message:
"ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size"

This is the query I used:
SELECT * FROM my_table ORDER BY date_created DESC LIMIT 1;

In 8.0.28 I got the failure when there was no index on date_created. When there was an index on date_created, it did succeed in 8.0.28. I tested the same query in 8.0.16 and 8.0.19 and it succeeded there with and without the index. The failure isn't new to 8.0.28, it also failed (without the index) in 8.0.20 and 8.0.27. Therefore it looks like the problem was introduced in 8.0.20.

Everything about the database instances where this was tested was the same, except for the MySQL versions. This was tested on AWS RDS using a size of db.t3.micro. my_table had only 39K rows, but it did have a lot of data inside JSON columns. The mysqldump of the database was 808 MB. date_created had a type of datetime.

Hi Mr. Miroddi,

What you describe is still a possibility. Hence, try increasing the sort buffer size just for that query.

>What you describe is still a possibility

Can you be more specific about this? Are there known cases where this issue is not fixed?

Been waiting to upgrade until this is confirmed resolved.

Hi Mr. Miroddi,

This issue is fixed.

Problem is that you need a buffer large enough to store N rows that are to be sorted. Your setting for the sort buffer is simply slower then this minimal amount.

The length of rows depends on the SELECT list columns and the invisible columns that are present in other places, like GROUP BY or ORDER BY.

If you have specified a smaller sort buffer size, then algorithm will never function.

Hence, everything is fixed, but you do have some tuning to do for some of your queries.

I'm using 8.0.28 (Amazon RDS) and I just encountered the same error. I have JSON column, and tried to order by 'created_at' (datetime). Adding an index to 'created_at' solved the problem for now.

Hi,

Adding the index is OK, but it could be more costly then just increasing the sort buffer.

A previous comment implicated 8.0.20.  Perhaps this code was is relevant.  (from https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html )

Important Change: Previously, including any column of a blob type larger than TINYBLOB or BLOB as the payload in an ordering operation caused the server to revert to sorting row IDs only, rather than complete rows; this resulted in a second pass to fetch the rows themselves from disk after the sort was completed. Since JSON and GEOMETRY columns are implemented internally as LONGBLOB, this caused the same behavior with these types of columns even though they are almost always much shorter than the 4GB maximum for LONGBLOB (or even the 16 MB maximum for MEDIUMBLOB). The server now converts columns of these types into packed addons in such cases, just as it does TINYBLOB and BLOB columns, which in testing showed a significant performance increase. The handling of MEDIUMBLOB and LONGBLOB columns in this regard remains unchanged.

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)

Hi,

What you wrote above is correct. However, since 8.0.28, improvements have been made to sorting algorithm, so that less memory is required. 

It should be noted that the change that you quoted drastically improves queries with ORDER BY, in a manner that, after sort is done, the reads of the rows are done from sorted file ONLY, without touching the table(s) involved.

In spite of the reduction of the sort buffer size required, you still have to have a sort buffer large enough so that several rows can be read and written back.