We are running Percona server 5.7 on a server with 128Gb of Memory and 32Gb of Swap Space.
We have had a lot of issues with the memory filling up and using up swap space on a daily basis requiring the database to be restarted daily in order to release the memory.
After adding some graphs, monitoring and physical testing, we found that the problem seems to be related to mysqldump.
The memory usage is fairly stable , increasing little bits until innodb_buffer_pool is full, however the moment we start a backup, the memory usage spikes and uses up all physical memory as well as Swap.
The symptoms we found was that the backup uses up all memory and starts to swap, however once the backup completes, the memory is not released at all and the database server eventually runs out of memory and kills mysql.
We have tried removing the compression of the backups as well using the --quick option but to no avail.
The size of the databases on the server totals about 350Gb in total, however we run backups on a per database setup and the biggest database being ±130Gb in size.
Is there anything else we can perhaps look at in order to try and troubleshoot why the mysqldump processes are not releasing the memory?
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.
If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, use the --skip-opt option instead of the --opt or --extended-insert option.
To select the effect of --opt except for some features, use the --skip option for each feature. To disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.
There is another option to try Percona XtraBackup.
We’re running Percona version 8.0.27-18 and we observe the same behavior: memory usage suddenly increases dramatically and never decreases until the Percona server is restarted. The sudden increase happens when ‘automydumper’ runs.
We explored two possible causes, so far without success:
is InnoDB ‘just’ filling its buffers (mismatch between configuration and available memory)
is Performance Scheme enabled (and taking all the memory)
So if anyone has found out ‘more’ we are much interested to hear it!