Error "1038 Out of sort memory, consider increasing sort buffer size

Question

In symfony2, doctrine2, I have a query which triggers an error :

Error "1038 Out of sort memory, consider increasing server sort buffer size

The query :

$queryBuilder = $this
    ->createQueryBuilder('object')
    ->leftJoin('object.objectCategory', 'c')
    ->leftJoin('object.medias', 'm')
    ->leftJoin('object.recipients', 'r')
    ->leftJoin('object.answers', 'a')
    ->leftJoin('object.tags', 't')
    ->leftJoin('object.user', 'u')
    ->leftJoin('object.votes', 'v')
    ->leftJoin('object.comments', 'comments')
    ->leftJoin('v.user', 'vuser')
    ->addSelect('c, t, v, u')
    ->groupBy('object, c, t, v, u')
    ->where('object.isVisible = :isVisible')
    ->orderBy('object.createdAt', 'DESC')
    ->setParameter('isVisible', true)
    ->addSelect('SUM(v.value) AS HIDDEN vote_value')
    ->orderBy('vote_value', 'DESC')
    ;

If I omit the group by, it runs just fine. If I add select and group by less elements, it runs fine also but then I have more subqueries launched in my twig templates.

How can I either optimize this query to not have the error OR get rid of the error by allocating more memory (ideally just for this query) ?

Answer 1

Just run mysql query

SET GLOBAL sort_buffer_size = 256000000 // It'll reset after server restart 

To set permanent

Edit below file and add

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf  sort_buffer_size = 256000000  sudo service mysql restart 

Answer 2

There's an outstanding issue with MySQL which causes this error when trying to sort on a table with a json column. It looks like it impacts MySQL >= 8.0.18. As of my reply here, the team is still considering whether this is a bug or not. Please provide feedback on that thread if you're impacted by this.

While increasing the buffer size can be a solution here, IMO it really shouldn't be necessary as even ordering by the numeric primary key on a table with a json column can cause this issue.

In my experience, tweaking the buffer size isn't very reliable as the buffer size is relative to the length of the contents in the json column, so while it may solve the immediate issue, if your data set grows it'll need to be tweaked again.

Answer 3

You probably need to increase the buffer size of mysql in the /etc/mysql/my.cnf, something like:

[mysqld]
sort_buffer_size=256k