Monday, July 07, 2008

Faster online backup with MyISAM driver

Sydney Wildlife Centre
Remember the first test of online backup? I tested the new feature, which was performing quite well, compared to mysqldump. OK. Get ready for a surprise.
I tested the native MyISAM driver from the mysql-6.0-backup tree, and I compared the results with the normal backup.
versionbackup timerestore time
standard25.5879.11
MyISAM driver4.1511.53

Please be amazed!
The difference is also visible from the metadata. The standard version says:
select * from mysql.online_backup\G
*************************** 1. row ***************************
backup_id: 1
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: backup
error_num: 0
num_objects: 8
total_bytes: 69970045
validity_point_time: 2008-07-07 11:39:04
start_time: 2008-07-07 11:38:39
stop_time: 2008-07-07 11:39:04
host_or_server_name: localhost
username: msandbox
backup_file: emp.bkp
user_comment:
command: backup database employees to 'emp.bkp'
engines: Default, Snapshot
And the native driver version says:
select * from mysql.online_backup\G
*************************** 1. row ***************************
backup_id: 1
process_id: 0
binlog_pos: 0
binlog_file: NULL
backup_state: complete
operation: backup
error_num: 0
num_objects: 8
total_bytes: 335531054
validity_point_time: 2008-07-07 11:32:04
start_time: 2008-07-07 11:32:00
stop_time: 2008-07-07 11:32:04
host_or_server_name: localhost
username: msandbox
backup_file: emp.bkp
user_comment:
command: backup database employees to 'emp.bkp'
engines: Snapshot, MyISAM
Doesn't it just make you want to rush to download the code at once?
The information about total_bytes is somehow puzzling. The size of the backup file is actually 89 MB for the standard version and 160 MB for the MyISAM driver. Thus, you pay a faster backup and restore with a bigger file. Personally, I can live with that.


As you may suspect, the picture does not have any relationship with backup, but it's cute, isn't it?

1 comment:

lsmith said...

Cool stuff .. nit pickung .. why is the order different in the engine section for the two listings?

Vote on Planet MySQL