470,631 Members | 1,640 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

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?

Mar 7 '06 #1
5 5949
<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.
Mar 7 '06 #2
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.

Mar 7 '06 #3
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.
Mar 9 '06 #4
"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.
Mar 9 '06 #5
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.
Mar 9 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Thomi Baechler | last post: by
12 posts views Thread by Neil | last post: by
reply views Thread by aaronk321 | last post: by
7 posts views Thread by Jim Lawton | last post: by
3 posts views Thread by jku | last post: by
4 posts views Thread by bluepete | last post: by
6 posts views Thread by Cirene | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.