Memory Error - Out of sort memory, consider increasing sort buffer size

In this article, we will see how to fix a MySQL memory issue.

If you’re getting any error saying:

DatabaseError [SequelizeDatabaseError]: Out of sort memory, consider increasing server sort buffer size

It's because the internal buffer of the database is too small for the query.

Run the below query in MySQL command to increase the memory

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

To set permanent, edit the below file and add:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

sort_buffer_size = 512000000;

sudo service mysql restart

Note that 1073741824 is equal to bytes and will result to 1GB memory allocation.

Also, there is a possibility that something else causing the issue, you can check CPU, memory and slow query logs

SHOW ENGINE INNODB STATUS

you can then analyse the output base on current buffer pool size.

BUFFER POOL AND MEMORY
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 412090368
Dictionary memory allocated 380237
Buffer pool size   24576
Free buffers       23618
Database pages     954
Old database pages 372
Modified db pages  0
Pending reads      0

For more details, refer to MySQL docs: MySQL Optimization