Out of sort memory, consider increasing server sort buffer size

Started by Square, February 23, 2022, 06:17:05 AM

Previous topic - Next topic

Square

Hello,

When I search for old threads I sometimes get this message (see attached file). I couldn't find how to solve this problem. Any idea?

Version Information:
Forum version: SMF 2.0.19 (more detailed)
Current SMF version: SMF 2.0.19
GD version: bundled (2.1.0 compatible)
Database Server: Percona
MySQL version: 8.0.20-11
PHP: 7.4.3
Server version: Apache

Arantor

How much control do you have over the database server? This is a configuration change you'll need to make there (Google for "Out of sort memory MySQL")

Doug Heffernan

Indeed, this is a server related issue rather than a smf one. @Square, do you manage your own vps/dedicated box or are you on a shared hosting plan? If the later, you must contact your host about this.

Square


Square

Reply from our host:

Unfortunately we cannot increase the buffer size on your server as this is not a dedicated server. Due to the managed structure, there must be a limit for the buffer size so that the stability of the server can be guaranteed. The increase is actually not necessary and we recommend that you adapt the software used accordingly or ask the manufacturer.

 :-\

Arantor

Well I guess we could just rewrite the search function that was designed for much smaller servers from 15 years ago (which had much less memory and therefore had to be more efficient) or get a better host?

shawnb61

I suspect this bug, which affects mysql ~8.0.20 on. 

Fixed in mysql 8.0.28.

https://bugs.mysql.com/bug.php?id=103225

So... 

Please share with your host, maybe they can update to 8.0.28.

If not, I'd experiment with different search indexes, e.g., try custom instead of fulltext, or vice-versa, see if that helps.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Square

Our host agreed to increased slightly the buffer size. No more problem.
Thanks to all for your help.  :)


Advertisement: