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

[6 Apr 2021 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 2021 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 2021 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 2021 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 2021 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 2021 12:50] MySQL Verification Team
Correct version.
[24 Apr 2021 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 2021 7:14] MySQL Verification Team
Bug #103465 marked as duplicate of this one
[25 Apr 2021 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 2021 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 2021 15:47] Rick James
Please change from away from "Category:	MySQL Server: Documentation"
[26 Apr 2021 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 2021 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 2021 13:44] MySQL Verification Team
Hi,

We recommend to everybody to try our latest release, 8.0.24.
[26 Apr 2021 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 2021 12:15] MySQL Verification Team
Hi Mr. Rollins,

Thank you for your feedback.
[11 Jun 2021 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 2021 12:44] MySQL Verification Team
Thank you for your feedback.
[15 Jun 2021 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 2021 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 2021 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 2021 21:08] Ted Hamilton
Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.
[2 Jul 2021 21:08] Ted Hamilton
Ran into this today as well.  I can't believe this is a "MySQL Server: Documentation" category.
[5 Jul 2021 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 2021 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 2021 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 2021 11:35] MySQL Verification Team
Hi Mr. Tsouvelekakis ,

Your recommendations have been copied to the team in charge .....
[28 Jul 2021 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 2021 23:47] Rick James
What does a failing case say in EXPLAIN FORMAT=JSON SELECT ... ? And what does the Optimizer Trace say?
[5 Aug 2021 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 12:46] Rob E
Could you explain why columns that aren't used in sorting are counted towards the sort buffer size?
[6 Aug 2021 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 16:28] Arnaud Becher
Any news ?
[9 Sep 2021 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 2021 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.
[5 Nov 2021 13:43] MySQL Verification Team
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.
[5 Nov 2021 14:40] Jon Stephens
This is being fixed together with BUG#105304.

Please see that bug report for updates.

Thanks.
[5 Nov 2021 14:59] MySQL Verification Team
Thank you, Jon !!!!!
[5 Nov 2021 16:37] Shane Niebergall
Woohoo! Thank you for listening and being open :)
[5 Nov 2021 17:58] Yaroslav Dobzhanski
Great news! Thanks to development team for getting into problem and looking for solution!
[12 Nov 2021 20:59] Jon Stephens
Fixed in MySQL 8.0.28 by BUG#105304.
[15 Nov 2021 13:10] MySQL Verification Team
Thank you, Jon.
[19 Apr 2022 11:48] Mike Pascual
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.
[27 Apr 2022 12:47] MySQL Verification Team
Hi Mr. Miroddi,

What you describe is still a possibility. Hence, try increasing the sort buffer size just for that query.
[27 Apr 2022 15:46] Shane Niebergall
>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.
[28 Apr 2022 13:15] MySQL Verification Team
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.
[20 Jun 2022 10:47] Jakub PomykaƂa
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.
[20 Jun 2022 12:07] MySQL Verification Team
Hi,

Adding the index is OK, but it could be more costly then just increasing the sort buffer.
[20 Jun 2022 19:22] Rick James
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)
[21 Jun 2022 11:58] MySQL Verification Team
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.
[2 Dec 18:46] Robert Mason
We are having this problem in a Drupal site. Drupal core's basic code for loading values from a field is failing if it's a JSON field. It seems like it's impossible to use a JSON field in newer versions.

The database table in question is the following:

CREATE TABLE `node__field_name` (
  `bundle` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `deleted` tinyint NOT NULL DEFAULT '0',
  `entity_id` int unsigned NOT NULL,
  `revision_id` int unsigned NOT NULL,
  `langcode` varchar(32) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `delta` int unsigned NOT NULL,
  `field_name_value` json DEFAULT NULL,
  PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
  KEY `bundle` (`bundle`),
  KEY `revision_id` (`revision_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

All of the fields in Drupal have this same table structure, just with the field_value having a different name and type. I can't change the type now, nor can I downgrade MySQL to an older version.

The table has 1443 rows. This is the query being performed:

SELECT t.*
FROM node__field_name t
WHERE (
    entity_id IN (
      4004107, 6471427, 6471839, 6472187, 6490599, 6491623, 6492062, 6493316,
      6494618, 6495256, 6495974, 6497083, 6497612, 6497621, 6498587, 6498595,
      6498602, 6499162, 6500997, 6501434, 6502735, 6504597, 6504598, 7108375,
      8298490)
    ) 
  AND (deleted = 0)
  AND (langcode IN ('en', 'und', 'zxx'))
  ORDER BY delta ASC;

This returns only 24 rows.

The problem here is the ORDER BY delta. Without it there is no problem. The only value for delta in the entire table is 0. So it's unclear what this sort algorithm is even doing or why it's running out of memory.

You'll also notice that there is an index on the delta column.

I can only assume that it has to load the field_name_value into the sort buffer, which might be large JSON data. But it doesn't need to sort by that.

We've tried increasing the sort buffer size, but it's futile because whilst it occasionally works, it still often fails. Sometimes it works with a smaller sort buffers size, and sometimes it needs to be larger. There is a goldilocks zone where it will work for a particular query, but adding another row or changing the WHERE conditions might make it fail again, and the sort buffer size will need to be increased or even reduced to make it work again.

This surely can't be the right behaviour?
[3 Dec 10:52] MySQL Verification Team
Hi Mr. Mason,

This bug was verified and fixed.

Do make sure that you are using 8.0.40 or newer release.