MySQL Forums
Forum List  »  Newbie

Re: Batch Insert
Posted by: Rick James
Date: October 19, 2011 08:39PM

100.

Why? Think of it this way. Let's start by assuming one row takes 1 unit of time to insert. On top of that, the INSERT statement, itself, takes 10 units of time. (The '10' is empirically derived; your mileage may vary.) So...
1 row in a batch (no batching) = 10+1 units of time = 11 units/row
10 rows at a time = 10+10 = 2/row
100 = 1.1/row (only 10% shy of the theoretical best)
1000 = 1.01/row
70K = 1.00004
Infinite = 1.0 (theoretical best, but unachievable)

There's not enough difference between 1.1 and 1.00014 to matter.

If you are using Replication, the 70K-INSERT would hog the replication stream unmercifully.

If you are using InnoDB, I recommend you do not put all the batched inserts in a single transaction (BEGIN...COMMIT). If the iblog file overflows, the inserts will slow down by something like a factor of 2. (There may be valid reasons for putting them in a transaction; I am only referring to the performance penalty of doing so.)

Options: ReplyQuote


Subject
Written By
Posted
October 18, 2011 02:58AM
Re: Batch Insert
October 19, 2011 08:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.