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:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Ubuntu
Assigned to: CPU Architecture:Any

[6 Apr 15:03] Saverio Miroddi
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

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

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.