Out of sort memory, consider increasing server sort buffer size

[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]

Thanks, I'll ask our host and will be back. 

[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.

[Square]

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