1

We have this problem on 2 separate installs. MariaDB 5.x installed. Once the daily backup is done, the database is almost unusable.

We have TS environments and a DB server - mysql 5.1 running on the TS machines but we have two older sites doing the same and working perfectly. MyISAM database.

Our batch file runs from one of the TS servers with the following command: mysqldump -u root -p --databases DB1 DB2 > dumpname_date.sql

Please help friends, this has become extremely oppressive.

Thank you in advance

MortalTree
  • 11
  • 1

1 Answers1

0

I have been struggling to get mysqldump to run faster (use more of VM) and figured out a couple of things.

  1. mysqldump sucks if the db is significantly bigger that the mariadb ram innodb_buffer_pool_size as all of the db does not fit into ram/buffer.
  2. when mysqldump runs it asks for all data from each table one by one, forcing mariadb server to load the data into the buffer and flush other data. (does your db speed recover after running for a while ?)
    • this is probably what your seeing, mariadb is good at keeping hot data in the buffer cache, but mysqldump destroys this and it takes time for it to recover.

The solution:

  • use mariadb-backup
    • I have seen a 600% speed improvement. (>90min to <15min)
    • mariadb-backup spin's up it's own sqlserver on the same data files and does a backup (can do it in parralel) thus it should not affect your running mariadb buffer/cache.
    • my situation is slightly different using mariadb+galera with dediated backup server, where is want as much backup speed as possible.
    • the backup generated by mariadb-backup is bigger (dir with files) but with the --stream option can be piped through gzip to compress.
Pieter
  • 1,916
  • 17
  • 17