MySQL Bugs: #105304: Unmanageable Sort Buffer Behavior in 8.0.20+

Bug #105304	Unmanageable Sort Buffer Behavior in 8.0.20+
Submitted:	22 Oct 2021 19:00	Modified:	12 Nov 2021 20:56
Reporter:	Matt Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.20+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	json, Optimizer, sort

Description:
Please see https://bugs.mysql.com/bug.php?id=103225 for background and context.

In MySQL 8.0.20 a feature was added which improved sort times in certain cases (e.g. with JSON columns): 
  - https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html (Bug #30400985, Bug #30804356)

However, this came at a tremendous UX cost -- making reliable JSON data type usage in production nearly impossible.

Unless I'm missing something...the suggestions made in Bug#103225 are not acceptable IMO. There's no reliable way AFAIK for a user to deterministically query the related state to calculate what the necessary sort buffer size would be before executing the query. And even if there were, suggesting that this is somehow a reasonable UX is not great (to say the least):
  - Determine when you are executing a query that will be doing a sort using a JSON column
  - Try and estimate what size of sort buffer will be required 
  - Save the current sort_buffer_size session variable
  - Set that session variable to the estimated size needed 
  - Execute your query 
  - Set the session variable back to its original value

And you can't simply increase the global sort_buffer_size variable because 1) that can have a potentially huge impact on memory usage as this is used per session/connection that needs to do a sort 2) AFAIK at a certain size you go from kernel space memory allocation (brk) to a slower user space memory allocation, even when 99.9% of the time a small buffer is needed.
 
Improving the performance is nice, but of course not if it makes a feature (virtually) unusable. I would enable the older behavior if I could, but there does not appear to be an optimizer switch for this? Aren't behavioral changes made after 8.0 GA supposed to be either backwards compatible or opt-in? This seems like a violation of those  guarantees, no?

Please let me know if I'm missing/misunderstanding something or can provide additional information. 

Thank you!

How to repeat:
N/A (known issue)

Suggested fix:
A similar problem was fixed in MariaDB this way: https://github.com/mariadb/server/commit/db56f9b852

Bug #105303 marked as duplicate of this one.

Hello Matt,

Thank you for the report and valuable feedback.
Ideally, this should be closed as duplicate of Bug #103225 but verifying since base bug is closed. Thank you!

regards,
Umesh

Regarding: "1) that can have a potentially huge impact on memory usage as this is used per session/connection that needs to do a sort"

Note that since 8.0.12, the sort buffer is allocated incrementally.  Hence, for small data volumes, only 32kB will be allocated for the sort buffer regardless of the setting of sort_buffer_size.

Thank you, Umesh! Nice to see your name again. :-) 

And thanks, Øystein! I totally missed that change which then in effect turns sort_buffer_size into [max_]sort_buffer_size in recent 8.0 versions. That definitely helps improve the situation and makes setting a larger global value more palatable as a potential workaround until the issue is addressed more "properly". Nice to see your name again too! :-)

> Aren't behavioral changes made after 8.0 GA supposed to be either backwards compatible or opt-in? This seems like a violation of those  guarantees, no?

Welcome to evergreen!  MySQL GA often incorporates incompatible changes.  Even 5.7 recently introduced a new compression scheme that broke downgrades if compressed tables were created after the upgrade.

Documented fix as follows in the MySQL 8.0.28 changelog:

    Sorts of some column types, including JSON and TEXT, sometimes
    exhausted the sort buffer if its size was not at least 15 times
    that of the largest row in the sort. Now the sort buffer need
    only be only 15 times as large as the largest sort key.

Closed.

This also fixes BUG#103225.

Jon, we users need something more prescriptive than your paragraph.

What is the remedy?  The user should sort_buffer_size at least 15 times the largest JSON string?  Or largest row?  And/or does max_sort_buffer_size need to be at least that size?

So, 1MB is enough for sorting a row with just a TEXT column.  But JSON has some bigger limit?