Mysql – What are reasonable values for –throttle on xtrabackup

backupinnodbMySQL

Percona's xtrabackup utility has a –throttle option to reduce the IO load of the backup job.

The docs say that the value passed is the number of read/write pairs per second. Is 1000/sec an appropriate value on modern hardware? How about 5? I cannot find any meaningful frame of reference for these values.

For reference – I am reading from and writing to the same drive array (10k SAS). A 55gb backup job with –throttle=20 ran in roughly an hour with no apparent strain on the system during off-peak hours. But I honestly don't know if this is a high or low value for the throttle.

Best Answer

it all depends but you can guesstimate something with this approach: take a look at iostat and look at iops/sec on your disk. if you have typical database you're most probably limited by number of random seeks/sec, not the bandwidth.

  1. in maintenance window - run xtrabackup without throttling and see again what numbers of iops/sec can your system generate. say it's x.
  2. after that look how many iops/sec is typical for system during off-peak hours. say it's y.

based on this make some estimation how many iops/sec can you dedicate to backup job. i would calculate it as x - 2 * y or x - 3 * y to leave some headroom for spikes.

i think xtrabackup's parameter will be linearly proportional to iops/sec but not equal - so in last step use trial and error to tune throttle value so iostat shows you desired number of operations/sec.

alternativly use ionice [ a little about it here ], give your backup job low priority and do not throttle it at all. i'm doing it for rdiff-backup jobs - works quite nicely. note that ionice [ afaik ] works only with some io schedulers in linux.

Related Topic