Not the answer you need?
Register and ask your own question!

mysqldump not releasing memory after completion

MachielMachiel ContributorCurrent User Role Beginner
Good day all

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?

Any suggestions would be greatly appreciated.


Regards

Comments

  • ABmercuryABmercury Current User Role Beginner
    Hello,

    Have you found a solution?
    I have the same problem, mysqldump does not free the memory when bacup is finished.

    Regards.
  • mwarblemwarble Contributor Current User Role Supporter
    I will add that I also have the same problem.  
  • KryKry Current User Role Contributor

    Hey.

    Perhaps it will help.


    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.

    Or a table backup.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.