5

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
  • 51
  • 1
  • 2
  • did you execute mysqldump inside the container or from docker exec? If you try to dump the database from remote client is append the same? (for remote client I mean connect from the network instead of sock) – AtomiX84 Apr 29 '19 at 13:38
  • Is it running out of RAM? Or disk? How big is the database? – Rick James May 02 '19 at 01:33
  • The mysqldump was executed inside the DB container for a while, and now it is in its own container. No change from that. The execution is technically triggered from a remote client, and the dump is sent remotely as well, but it is still technically executed in a container on the local host. It's running out of RAM. Gz DB is ~500Mb – Hadrien May 05 '19 at 20:51

3 Answers3

6

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
  • 237,123
  • 42
  • 477
  • 940
  • Thanks, I'm trying that, we'll see ! – Hadrien May 05 '19 at 20:48
  • 1
    Note `--quick` is already enabled by default in recent versions of `mysqldump`. – bfontaine Mar 10 '21 at 09:19
  • @bfontaine How can you tell if any particular version of `mysqldump` has this default? Over the course of a day we might touch several different versions. – Michael Hampton Mar 10 '21 at 20:41
  • I don’t know; I wrote "recent" but in Twitter’s fork of MySQL [it was added](https://github.com/twitter-forks/mysql/commit/168077304d561c89368cc09cbdf3c2451f1c6be9) back in 2003. – bfontaine Mar 11 '21 at 14:53
0

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
  • 41
  • 5
  • Yes makes sense, but 1. I'd rather solve the problem than having anything be killed. It just does not make sense for mysql to take on so much RAM that it won't be able to run some operations at some point, it should free up some of it when necessary. 2. It's all dockerized, I don't want/can't really set up the host to prioritize some processes manually. By definition the containers can be stopped, restarted, etc. the processes change and it needs to be host-agnostic. – Hadrien May 05 '19 at 20:55
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
  • 2,401
  • 1
  • 5
  • 19
  • We do have some replication happening, and it's an interesting strategy to run the dumps on the replication server rather than the main machine. We'll explore that, but it has its limits since we do sometimes have problems with the replication, so we can't rely solely on this and I feel like we should have a way of making backups on the production database without having it crash when we do! – Hadrien May 05 '19 at 20:57