How to make big MySQL database more diffable/rsyncable? (aka
rsyncing big files)
krzysztof.nosek at techland.pl
Wed Jul 8 10:12:02 GMT 2009
thanks for your tips... even if I'm still stuck with my problem :)
> * Does the output of mysqldump compress well with gzip?
No it doesn't. The biggest table we talk about here consists mainly of
large binary blobs which are media files in compressed formats such as
jpg, avi, png, etc.
But I was actually trying to rsync uncompressed dumps, being aware that
compression brings a huge load of entropy.
> * If you're confident that you have a good line-oriented dump, how
> does diff cope when comparing 2 dumps?
I was never patient enough to measure the effectiveness of such diffs.
But comparing only parts of the dumps I can see that they are _very_
stable - just single lines added/changed across the dumps. Diffing dumps
(or fragments) is not quite feasible because even if it's properly
line-oriented, the lines are very very VERY long. It therefore takes
lots of memory and time to get anything.
> * If you're not telling rsync to transfer just a single file, do the
> filenames match at both ends of the transfer?
Anyway, I'm slowly becoming convinced that one has to remove the file
blobs from the database entirely, moving them to the flat structure in a
directory which would be rsynced separately. There is such feature in
Mantis, as far as I know. I think it would give huge boost from the file
list being basically constant, which is nearly impossible to achieve
when all the files are glued together in a single large binary something.
More information about the rsync