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
Top comments (0)