Print Page - Out of sort memory, consider increasing server sort buffer size

Title: Out of sort memory, consider increasing server sort buffer size
Post by: Square on February 23, 2022, 06:17:05 AM

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

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Arantor on February 23, 2022, 07:06:36 AM

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")

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Doug Heffernan on February 23, 2022, 09:00:32 AM

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.

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Square on February 25, 2022, 06:55:56 PM

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

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Square on February 28, 2022, 08:02:05 AM

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.

:-\

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Arantor on February 28, 2022, 08:33:11 AM

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?

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: shawnb61 on February 28, 2022, 10:09:41 AM

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.

Title: Re: Out of sort memory, consider increasing server sort buffer size
Post by: Square on March 06, 2022, 11:15:30 AM

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

Simple Machines Community Forum

SMF Development => Bug Reports => Fixed or Bogus Bugs => Topic started by: Square on February 23, 2022, 06:17:05 AM