How to make big MySQL database more diffable/rsyncable? (aka rsyncing big files)

[malayter at gmail.com]

On Jul 8, 2009 5:12am, Krzysztof Nosek <krzysztof.nosek at techland.pl> wrote:

> 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.

I use rsync against 50 GB MSSQL databases that contain lots of blobs, and  
it is very effective at finding matches when the block size is adjusted  
down to 32K. So I do not think the basic data structure is the problem,  
unless mysql hotcopy does something really strange like inserting a  
timestamp or other changing data info every few KB in the output stream.

I would suggest trying a tool like xdelta (on the same machine) against two  
consecutive backup files, just to see if it can extract similarities. If  
xdelta can find significant matched data, rsync should be able to as well.

Also, is the transfer CPU bound or network bound? Can you send the output  
of rsync with the --stats and -v options?
-------------- next part --------------
HTML attachment scrubbed and removed