Mysql runs out of memory during backup dumps (Docker container)

Hadrien asked:

We have a container running MariaDB, and a few other small containers on the host. The Mysql container was allocated 21G of memory (out of 32G total), as well as a few other parameters, with the following commands in the docker-compose:

  - --innodb_buffer_pool_size=4294967296
  - --query_cache_size=268435456
  - --tmp_table_size=1073741824
  - --max_heap_table_size=1073741824
  - --table_open_cache=20000
  - --max_connections=1000
  - --performance_schema
mem_limit: 21g

The issue we have is that the mysql container runs out of memory during some routine backup operations, namely mysqldump commands, and the container just crashes.

Basically the container’s memory usage creeps up to the 21G over about a week of use, and I think can stay there if we do not launch any “big” operation, but if a mysqldump command is launched, at some point during the dump that just puts it over its allocated limit, and it crashes (it does not when we have not reached ~95% of memory usage earlier in the week).

I don’t understand why MySQL does not manage its memory better and frees up some of it to launch the new commands it needs to execute ?

We tried putting the mysqldump commands in a different container, to try to “isolate” this big operation, but that does not seem to change anything, the bulk of the work is still done by the Mysql container which ultimately crashes when the other container performs the dump.

What should we look into? Are our settings just totally out of whack? We set them up after running a, and I can do a new run if you think that’s where the problem lies.

We have about 700 databases, with ~40 tables each, and maybe about 10 simultaneous mysql connections in average with some spikes at 30 or 50. DBs run between 10Mb and 200Mb in size.

Any help appreciated, thanks!

My answer:

Run mysqldump with the --quick option.

By default mysqldump tries to dump entire tables at once, meaning it must load the entire table into memory, and when memory is constrained mysqldump may fail. The --quick option switches to dumping by row, which is slightly slower and makes slightly larger dump files, but uses far less memory.

No, I do not know why they named the option --quick.

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.