DEV Community

Cover image for Memory Error - Out of sort memory, consider increasing sort buffer size
Manoj Swami
Manoj Swami

Posted on

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

#mysql #memory #buffer #ubuntu

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
Enter fullscreen mode Exit fullscreen mode

Error

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

For more details, refer to MySQL docs: MySQL Optimization

Top comments (0)