0

Using mysqldump to dump to backup file. The backup is running to long and looking at the VM performance mariadb is not utilising it well.

Every 2hours we stop galera sync and do mysqldump on dedicated backup server.

The VM has 8vCPU, 64Gram (32G buffer pool) and disk with 5000iops, and 200MB/s throughput. $ mariadb --version mariadb Ver 15.1 Distrib 10.4.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

During the dump, that is taking around 90min for 100GB db the vm is under utilised.

mysqld cpu 20%, mysqldump 12% iops ave 400, rMB/s around 18, sometimes when starting new tables jumps to couple of thousand iops and 90MB/s disk throughput. While busy with large tables the mariadb throughput drops to 5MB/s. The dump is saved on a seperate disk from the db data disk.

The problem seem to be that the buffer_pool (32G) is useless as it contains the last 32G from pervious backup, and mariadb is to slow reading data from disk.

Tried to adjust some settings. innodb_read_io_threads 4 -> 16 innodb_read_ahead_threshold 56 -> 16,8,4 to try and get more aggressive disk usage innodb_old_blocks_pct 37 -> 80

Interesting is that mariadb performs better if we restart the db between backups. Also saw some success with warming the cache with select * for first tables that would fit in buffer.

What settings is recommended to get more aggressive mariadb read performance ? The cloud storage performs better with parallel reads.

1
  • Which MariaDB version?
    – danblack
    Commented Jan 19, 2023 at 4:52

1 Answer 1

1

I switched from mysqldump to mariadb-backup and it is a huge improvement.

  • backup is bigger x2.5

  • but much faster >90min down to <15min

    /usr/bin/mariadb-backup --backup --stream=xbstream --galera-info --parallel=4 | pigz > backup.xb.gz

mysqldump is probably fine if your db and ram size match, but for larger DB i have not been able to tune mariadb to utilize the disk better as it streams the tables out to the backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.