| Bug #46558 | Speed of restore is very slow when compared to backup | ||
|---|---|---|---|
| Submitted: | 5 Aug 2009 3:45 | Modified: | 27 Aug 2009 19:32 |
| Reporter: | Hema Sridharan | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | mysql-6.0 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[5 Aug 2009 3:45]
Hema Sridharan
[5 Aug 2009 5:36]
Rafal Somla
If possible, it would be interesting to see what are the times when only MyISAM and only InnoDB tables are used. This is because for these types of tables different backup methods are used (backup/restore drivers). That would help us to isolate where the problem lies. My suspicion is that the performance bottleneck is in the default restore driver when it inserts rows into table using handlerton methods. If this is confirmed then we could try to improve performance using e.g., bulk inserts. We should also make sure that table indexes are not updated after each insert.
[5 Aug 2009 12:36]
Sveta Smirnova
Thank you for the report. Verified as described. In my case InnoDB tables caused this slowdown. To repeat: load 'Flight Stat' database (http://flightstats.us/) like in wl #4044. If just backup, then restore it restore takes same time like backup: 4 min on my box. But after restore I converted all tables to InnoDB. Backup took 6 min while restore is running for 2 hours already and haven't stopped. <?php mysql_connect('127.0.0.1:33051','root',''); $result = mysql_query('show tables from flightstats'); while($row = mysql_fetch_row($result)) { mysql_query('alter table flightstats.' . $row[0] . ' engine=innodb'); var_dump(mysql_error()); } ?>
[5 Aug 2009 18:11]
Chuck Bell
The speed is not surprising albeit really poor. I would like to see results of running the restore locally and *not* over a LAN. Please provide these values for the bug report. I want to eliminate the possibility of network latency as a factor for the results. Note: Some ISPs use bandwidth throttling for long-running high usage connections. That could explain why it took 4 days. Also, consider InnoDB is a row-based restore so that 350 Gb of data is essentially being inserted one row at a time whereas MyISAM is a block copy of the files.
[5 Aug 2009 18:30]
Sveta Smirnova
Chuck,
restore was running locally.
Here is data from backup_history:
InnoDB backup:
mysql> select * from backup_history where backup_id=554\G
*************************** 1. row ***************************
backup_id: 554
process_id: 0
binlog_start_pos: 0
binlog_file:
backup_state: complete
operation: backup
error_num: 0
num_objects: 84
total_bytes: 2307898965
validity_point_time: 2009-08-05 09:59:54
start_time: 2009-08-05 09:59:54
stop_time: 2009-08-05 10:05:03
host_or_server_name: localhost
username: root
backup_file: flightstats_innodb.bkp
backup_file_path: /users/ssmirnova/blade12/build/mysql-6.0-backup/data/
user_comment:
command: backup database flightstats to 'flightstats_innodb.bkp'
drivers: Snapshot, MyISAM
1 row in set (0.00 sec)
InnoDB restore:
mysql> select * from backup_history where backup_id=555\G
*************************** 1. row ***************************
backup_id: 555
process_id: 0
binlog_start_pos: 0
binlog_file:
backup_state: complete
operation: restore
error_num: 0
num_objects: 84
total_bytes: 2297351671
validity_point_time: 2009-08-05 09:59:54
start_time: 2009-08-05 10:45:09
stop_time: 2009-08-05 10:05:03
host_or_server_name: localhost
username: root
backup_file: flightstats_innodb.bkp
backup_file_path: /users/ssmirnova/blade12/build/mysql-6.0-backup/data/
user_comment:
command: restore from 'flightstats_innodb.bkp'
drivers: Snapshot, MyISAM
1 row in set (0.00 sec)
Interesting why it shows such start and stop time while I had:
mysql> show processlist;
+----+------+-----------+-------------+---------+------+-------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------------+---------+------+-------+----------------------------------------+
| 10 | root | localhost | flightstats | Query | 6745 | NULL | restore from 'flightstats_innodb.bkp' |
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+-------------+---------+------+-------+----------------------------------------+
2 rows in set (0.00 sec)
MyISAM backup:
mysql> select * from backup_history where backup_id=551\G
*************************** 1. row ***************************
backup_id: 551
process_id: 0
binlog_start_pos: 0
binlog_file:
backup_state: complete
operation: backup
error_num: 0
num_objects: 84
total_bytes: 3808172878
validity_point_time: 2009-08-05 05:49:30
start_time: 2009-08-05 05:46:12
stop_time: 2009-08-05 05:49:30
host_or_server_name: localhost
username: root
backup_file: flightstats.bkp
backup_file_path: /users/ssmirnova/blade12/build/mysql-6.0-backup/data/
user_comment:
command: backup database flightstats to 'flightstats.bkp'
drivers: MyISAM
1 row in set (0.00 sec)
MyISAM restore:
mysql> select * from backup_history where backup_id=552\G
*************************** 1. row ***************************
backup_id: 552
process_id: 0
binlog_start_pos: 0
binlog_file:
backup_state: complete
operation: restore
error_num: 0
num_objects: 84
total_bytes: 3808172878
validity_point_time: 2009-08-05 05:49:30
start_time: 2009-08-05 05:54:19
stop_time: 2009-08-05 05:49:30
host_or_server_name: localhost
username: root
backup_file: flightstats.bkp
backup_file_path: /users/ssmirnova/blade12/build/mysql-6.0-backup/data/
user_comment:
command: restore from 'flightstats.bkp'
drivers: MyISAM
1 row in set (0.00 sec)
[5 Aug 2009 19:58]
Hema Sridharan
I thought sveta has already submitted the feedback very precisely. Anyways, I checked with Elena and she said that the backup and restore of 510Gb database was performed locally and following are the results that she provided: mysql> backup database * to '/data0/qauser/tpch.backup.20090604'; +-----------+ | backup_id | +-----------+ | 549 | +-----------+ 1 row in set, 6 warnings (1 hour 45 min 42.04 sec) mysql> restore from '/data0/qauser/tpch.backup.20090604' overwrite; +-----------+ | backup_id | +-----------+ | 270 | +-----------+ 1 row in set, 16 warnings (4 days 12 hours 18 min 9.26 sec) Please see WL#4044 (work progress report) for any further details.
[27 Aug 2009 10:45]
Jørgen Løland
Testing performed by Ranger has shown that MySQL RESTORE of InnoDB records performs as good as load data infile and restore of a mysqldump. Here's an excerpt of the findings:
60M InnoDB rows (~23GB), dumped to file using BACKUP and mysqldump tools:
\ InnoDB log size
Operation\ 2x100MB 2x1500MB
------------------------------------------
RESTORE 3h02m 1h12m
LOAD DATA INFILE 3h08m 1h08m
RESTORE DUMP 3h20m 1h28m
[27 Aug 2009 19:32]
Jørgen Løland
The reason for the long restore time for InnoDB is that there is no native BACKUP driver for this engine yet. Hence, BACKUP/RESTORE reads and inserts the records in a logical format, similar to what mysqldump does. The case for MyISAM is very different since there is a native BACKUP driver for this engine, and that driver is physical. The conclusion is that this problem cannot be fixed without implementing a native BACKUP driver for InnoDB. There are plans for doing this.
