slow restore

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?

Re: slow restore

wrote in message
news:1141753300.862676.253230@j33g2000cwa.googlegroups.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/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.

Re: slow restore

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.

Re: slow restore

patrick@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.

Re: slow restore

"Ian Pawson" wrote in message
news:ODSPf.78127$K42.71291@newsfe7-win.ntli.net...
> patrick@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/en/server-system-variabl es.html

Regards,
Bill K.

Re: slow restore

Bill Karwin wrote:
> "Ian Pawson" wrote in message
> news:ODSPf.78127$K42.71291@newsfe7-win.ntli.net...
>> patrick@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/en/server-system-variabl es.html
>
> Regards,
> Bill K.
>
>
Thanks for the info. I will give it a try.