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

[Krzysztof Nosek]

Hello,
> 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.
>   
No, really,  mysqlhotcopy performs just a raw file system copy of 
/var/lib/mysql taken from the locked database. If nothing particular 
happens meanwhile in the running database, the copy is 1:1 with the 
original. Easy to check with any smaller database.
> 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.
>   
I'd love to do that but I can't make it actually working:
xdelta: open ../mantis_game_20090707/mantis_bug_file_table.MYD failed: 
Value too large for defined data type

Same for dumps. I think it's running out of memory just like diff does 
with files that large, isn't it?

> Also, is the transfer CPU bound or network bound? Can you send the output  
> of rsync with the --stats and -v options?
>   
I'm pretty sure it's the network. The rsync jobs on both machines use no 
more than 30-50% of the CPU. I may be wrong - please find the log attached.
Perhaps I am memory bound, could it be?

Regards,
nosek
-------------- next part --------------
$ rsync -kavzO --del --stats --block-size=32768 --delete-excluded \
	--exclude=phpThumb/cache \
	--exclude=glpi.techland.pl/files/_cache \
	--exclude=glpi.techland.pl/files/_sessions \
	--exclude=moinmoin/data/cache \
	--exclude=tmp/cgisess \
	--exclude=*.old --exclude=*_old \
	knosek at mantis.techland.pl:/ftp2/backup/mysql-mirror /home/nosek/rsync-test2/ftp2 2>&1 \
	| tee -a /home/nosek/rsync-test2/ftp2/mysql-mirror.log
receiving incremental file list
# ...27 entries of some smaller databases here...
mysql-mirror/mantis_game/mantis_bug_file_table.MYD
mysql-mirror/mantis_game/mantis_bug_file_table.MYI
mysql-mirror/mantis_game/mantis_bug_history_table.MYD
mysql-mirror/mantis_game/mantis_bug_history_table.MYI
mysql-mirror/mantis_game/mantis_bug_monitor_table.MYD
mysql-mirror/mantis_game/mantis_bug_monitor_table.MYI
mysql-mirror/mantis_game/mantis_bug_relationship_table.MYD
mysql-mirror/mantis_game/mantis_bug_relationship_table.MYI
mysql-mirror/mantis_game/mantis_bug_table.MYD
mysql-mirror/mantis_game/mantis_bug_table.MYI
mysql-mirror/mantis_game/mantis_bug_text_table.MYD
mysql-mirror/mantis_game/mantis_bug_text_table.MYI
mysql-mirror/mantis_game/mantis_bugnote_table.MYD
mysql-mirror/mantis_game/mantis_bugnote_table.MYI
mysql-mirror/mantis_game/mantis_bugnote_text_table.MYD
mysql-mirror/mantis_game/mantis_bugnote_text_table.MYI
mysql-mirror/mantis_game/mantis_custom_field_string_table.MYD
mysql-mirror/mantis_game/mantis_custom_field_string_table.MYI
mysql-mirror/mantis_game/mantis_email_table.MYD
mysql-mirror/mantis_game/mantis_email_table.MYI
mysql-mirror/mantis_game/mantis_filters_table.MYD
mysql-mirror/mantis_game/mantis_filters_table.MYI
mysql-mirror/mantis_game/mantis_project_version_table.MYD
mysql-mirror/mantis_game/mantis_project_version_table.MYI
mysql-mirror/mantis_game/mantis_tokens_table.MYD
mysql-mirror/mantis_game/mantis_tokens_table.MYI
mysql-mirror/mantis_game/mantis_user_table.MYD
mysql-mirror/mantis_game/mantis_user_table.MYI
# ... 32 entries of some smaller databases here ...

Number of files: 1617
Number of files transferred: 87
Total file size: 18029881002 bytes
Total transferred file size: 18013399877 bytes
Literal data: 123736377 bytes
Matched data: 17889663500 bytes
File list size: 43382
File list generation time: 0.020 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 4361279
Total bytes received: 103628292

sent 4361279 bytes  received 103628292 bytes  31406.01 bytes/sec
total size is 18029881002  speedup is 166.96