Hi I'm building a rails app and I imported lots of data and inserted into the database. Some tables are okay and some tables have some errors that says out of sort memory.
SQL ERROR
Mysql2::Error: Out of sort memory, consider increasing server sort buffer size
How do you increase server sort buffer size using the docker-compose.yml command?
I've tried this one:
version: '3'
services:
db:
image: mysql:latest
command:
- --default-authentication-plugin=mysql_native_password
- --innodb-buffer-pool-size=402653184
I did add this command:
--innodb-buffer-pool-size=402653184
but I'm still getting the same error that it is out of memory. Is there any way to increase the buffer size using the docker-compose.yml
Turns out when I've tested this, this resolves my error in the mysql container in docker.
Add this command in docker-compose.yml under your db services to increase your memory.
--sort_buffer_size=1073741824
To remove the error when your application is running on docker.
Error "1038 Out of sort memory, consider increasing server sort buffer size
Note that 1073741824 is equal to bytes and will result to 1GB memory allocation.
First thing, 402653184 (byte) which is around 400 MB, if you are not sure about this memory limit then increase a bit more
command:
- --default-authentication-plugin=mysql_native_password
- --innodb-buffer-pool-size=1G
And then verify the changes inside your container.
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
#it should return 1GB
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