Announcement

Announcement Module
Collapse
No announcement yet.

TIP: Use cstream to prevent mysqldump flushing your disk cache

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • TIP: Use cstream to prevent mysqldump flushing your disk cache

    If you are using a linux platform, the usual backup command is:

    mysqldump [arguments] > file

    However, this has a downside - all the bytes written to the output file pass through the disk cache in case another program wants to read them again in the near future, which means if you are backing up a 1GB database, you could potentially have 1GB of MyISAM blocks purged from your OS disk cache to make room for blocks of the backup file. This is obviously a bad thing.

    A solution is to use the cstream program, which lets you use the linux kernel O_DIRECT feature when writing to bypass the cache. You can download it from:

    http://www.cons.org/cracauer/cstream.html

    The equivalent backup command is:

    mysqldump [arguments] | cstream -b1048576 -OD -o file

    Using this method the only disk cache usage is mysqld loading the tables being backed up into memory - cached data which your database server can reuse.

    If you use bzip2 to compress your backup file, you can pipe the output of bzip2 into cstream, but I prefer to write the file to disk in a non-compressed state, then compress it afterwards; on my server, this results in the backup phase being shorter so tables aren't locked for as long a period. To compress the file without using the disk cache, you will need an up-to-date version of 'dd' that supports the iflag arugment:

    dd iflag=direct if=file bs=900k | nice -n 20 bzip2 | cstream -OD -b1048576 file.bz2

    note: The optimal input blocksize to bzip2 is 100 times the compresison level, which defaults to 9.

    One final tip - you can further optimise disk cache usage if you specify your tables explicitly on the command line - put the most frequently hit tables last, so when the backup completes, those are the ones whose data blocks will still be in the disk cache.
Working...
X