6

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:

db:
command:
  - --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 mysqltuner.pl, 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!

Hadrien
  • 61

3 Answers3

7

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.

Michael Hampton
  • 252,907
1

There is an article from Percona that may be helpful:

https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

This passage seems Partially relevant:

The next thing when it comes to OS configuration is setting the Out Of Memory killer. You may have seen a message like this in your kernel log file:

Apr 24 02:43:18 db01 kernel: Out of memory: Kill process 22211 (mysqld) score 986 or sacrifice child

When MySQL itself is at fault, it’s a pretty rational thing to do. However, it’s also possible the real problem was some of the batch activities you’re running: scripts, backups, etc. In this case, you probably want those processes to be terminated if the system does not have enough memory rather than MySQL.

To make MySQL a less likely candidate to be killed by the OOM killer, you can adjust the behavior to make MySQL less preferable with the following:

echo '-800' > /proc/$(pidof mysqld)/oom_score_adj

This will make the Linux kernel prefer killing other heavy memory consumers first.

========================================================================

Obviously you want to prioritize your mysqldump operation but if there are other processes using memory, you can kill processes any you feel are superfluous a higher priority for termination.

I am not sure how you are scripting your dumps. I am thinking it may help to break up your dumps into smaller batches groups (sets of table/database) to help keep memory getting to high. Just some thoughts on this.

Evolutionary
  • 51
0

If you don't want to give more memory to mysql, you can try setting replication server and do the mysqldump from there, problem is mysqldump is risky when your server having load. Please take a look performance related parameters which may also help https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-performance-options

Hope this will help.

asktyagi
  • 3,038