Bug #3816 mysqldump option to wrap chunks of inserts into transactions
Submitted: 18 May 2004 18:43 Modified: 2 Sep 2007 18:17
Reporter: Timothy Smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:any OS:Any (any)
Assigned to: CPU Architecture:Any

[18 May 2004 18:43] Timothy Smith 
Description:
When doing many inserts into InnoDB (maybe BDB, too), the overhead of many transactions can be the limiting factor on how fast the import happens.  It would be great if mysqldump had a --add-begin-commit flag, which would wrap chunks of insert statements with BEGIN/COMMIT.

How to repeat:
$ mysql < innodb_table_dump.sql

Even if the dump was made with --opt (extended inserts), it's likely that the transaction overhead will be a very significant part of the time it takes to do the inserts.

Suggested fix:
Probably --add-begin-commit would take an argument, either in rows or in bytes, for how big each chunk should be.  It might also look at innodb_log_file_size and log_files_in_group, and default to an optimal chunk size based on those values.
[2 Sep 2007 18:17] Valeriy Kravchuk 
Thank you for a reasonable feature request.