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

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)