MySQL 8.0.33 error when selecting json column : Out of sort memory, consider increasing server sort buffer size

Question

I have a table with a JSON column. I want to select a specific data within the JSON column. My query was working fine with MySQL 5.7 (Ubuntu 16.04), but does not work with MySQL 8.0.33 (Ubuntu 22.04) :

Query :

select json_unquote(json_extract(`campaigns`.`model`, '$.\"migration\"')) as `migration` 
from `campaigns` 
order by `created_at` desc 
limit 10 offset 0;

Error :

Out of sort memory, consider increasing server sort buffer size

The sort_buffer_size variable is currently :
SHOW global variables LIKE 'sort_buffer_size';
Result : 262144

I tried to increase the sort_buffer_size and restart the server, but without success.

Is this a bug in mysql 8 ? should I rewrite my query in a more optimized form ?

thanks

Answer 1

It's sort of a bug. It's the consequence of a supposed improvement in MySQL 8.0.20. See https://bugs.mysql.com/bug.php?id=103225

The best workaround is to make sure your query optimizes the sort, by creating an index on your created_at column. If the query reads the rows in index order which is the same order you want them, then it won't need to use a sort buffer at all.

If you can't create that index, MySQL must read the rows in some other order (probably primary key order), and must sort the rows in batches. These batches are stored in a sort buffer in memory, and the sort buffer must be large enough to hold several rows. The default sort buffer size is quite modest, as you found it is 256KB.

You said you tried to increase it, but if your JSON documents are large, you would have to increase the sort buffer size a lot. I'd guess 10-20x the largest JSON document you store in that table.

You can check how large your largest JSON document is (at least of the documents currently stored in your table):

SELECT MAX(JSON_STORAGE_SIZE(model)) FROM campaigns; 

Here is an explanation from the MySQL release notes about this change:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html says:

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.

In my opinion, this is one of many reasons why we should be cautious and hesitant to use JSON in MySQL. Use normal rows and columns wherever possible.