slow restore

[patrick]

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:

mysqldump %dbname% --single-transaction > %dbname%.sql

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?

[Bill Karwin]

<pa*****@digital-horizons.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

mysqldump %dbname% --single-transaction > %dbname%.sql
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.
creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything.

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

[patrick]

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.

[Ian Pawson]

pa*****@digital-horizons.com wrote:

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:

mysqldump %dbname% --single-transaction > %dbname%.sql

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?

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.

[Bill Karwin]

"Ian Pawson" <ia*@ipawson.com> wrote in message
news:OD*******************@newsfe7-win.ntli.net...

pa*****@digital-horizons.com wrote:
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.

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/...variables.html

Regards,
Bill K.

[Ian Pawson]

Bill Karwin wrote:

"Ian Pawson" <ia*@ipawson.com> wrote in message
news:OD*******************@newsfe7-win.ntli.net...

pa*****@digital-horizons.com wrote:
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.

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/...variables.html

Regards,
Bill K.

Thanks for the info. I will give it a try.