slow restore

Posted by patrick on March 7, 2006, 12:41 pm

Please log in for more thread options

Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

show/hide quoted text

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

Posted by Bill Karwin on March 7, 2006, 2:16 pm

Please log in for more thread options

show/hide quoted text

I believe the --single-transaction executes the backup in a single
transaction; it doesn't create a script to execute the restore in a single
transaction.

show/hide quoted text

I think you _want_ the --opt option! Did you read the description of --opt?
http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

There are a large number of tips for InnoDB performance here:
http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html

For example, following the tips on that page, I'd do the following (sorry, I
am using UNIX/Linux shell syntax, writing the Windows equivalent is left as
an exercise for the reader):

(
echo "SET AUTOCOMMIT=0;"
echo "SET UNIQUE_CHECKS=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat mydumpfile.sql
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "UNIQUE_CHECKS=1;"
echo "COMMIT;"
) | mysql -u username -p mydatabase

Regards,
Bill K.

Posted by patrick on March 7, 2006, 4:10 pm

Please log in for more thread options

Thanks. I was able to do that from a redhat box I have sitting next to
it. I was under the impression that I need single transaction with
InnoDB. Anyway, thank you.

Posted by Ian Pawson on March 9, 2006, 4:33 am

Please log in for more thread options

patrick@digital-horizons.com wrote:
show/hide quoted text
The GUI backup has a new INSERT line about every 21 rows, which makes
the file larger but much quicker to restore. I have not been able to get
mysqldump to mimic this behaviour. I only seems to output a single INSERT.

Posted by Bill Karwin on March 9, 2006, 1:44 pm

Please log in for more thread options

show/hide quoted text

I wonder if this optimization is related to the bulk_insert_buffer_size
server parameter? You may be able to make very long INSERTs run faster if
you increase that buffer. See
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Regards,
Bill K.