Bug #103225 "Out of sort memory error" has an inconsistent relationship with the buffer size
Submitted: 6 Apr 15:03 Modified: 7 Apr 12:49
Reporter: Saverio Miroddi Email Updates:
Status: Verified 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?