Hi!
Steve corrected us: the option to use in mysqldump is
-e
or
--extended-insert
to generate multirow insert statements in the dump.
Then even autocommit=1 does not slow down table import to InnoDB
too much, because it does the commit only after several rows.
Regards,
Heikki
>Date: Thu, 17 May 2001 15:53:59
>To: [EMAIL PROTECTED]
>From: Heikki Tuuri <[EMAIL PROTECTED]>
>Subject: Re: PROPOSAL mysqldump change for InnoDB
>
>Steve and Monty,
>
>yes, Monty is right, with
>
>mysqldump --quick
>
>you should get much faster import speed. But I have to correct
>Monty: a big rollback segment does not slow down import
>considerably.
>
>Regards,
>
>Heikki
>
>At 12:25 AM 5/17/01 +0300, you wrote:
>>
>>Hi!
>>
>>>>>>> "Steve" == Steve Ruby <[EMAIL PROTECTED]> writes:
>>
>>Steve> Due to the fact that piping a table from mysqldump to an INNODB table
>>Steve> can be as much as 30X slower if the insert is not done with
>>Steve> transactions,
>>Steve> would it make sense to add some option to mysqldump to make every X
>>Steve> inserts a transaction.
>>
>>Steve> for example
>>
>>Steve> mysqldump --transactionrows=100000 mydatabase | mysql -hsomeserver
>>Steve> database
>>
>>Steve> where the above would wrap every 100,000 inserts with begin; commit;
>>Steve> right now it is non-trivial to insert the transaction
>>Steve> lines every so often, or even around all of the data.
>>
>>I don't think we actually need the above option.
>>
>>Try using the --quick option to mysqldump.
>>
>>This will MUCH faster for InnoDB, because it inserts up to 16M of data
>>at a time (instead of just single rows).
>>
>>Could you test the above on your tables? If it doesn't give adequate
>>performance, then we have to consider adding begin-ends to mysqldump.
>>
>>The problem with having very long transactions is that they could actually
>>make things go slower because you get much bigger rollback segments.
>>(I assume that if I am wrong, Heikki will correct me ...)
>>
>>Regards,
>>Monty
>>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
