MySQL Forums
Forum List  »  Performance

Re: Why INSERT performance has not increase in sub-table?
Posted by: Rick James
Date: November 03, 2014 01:12PM

With autocommit=1 and innodb_flush_log_at_trx_commit=1, each INSERT of one row will require (at least) one disk write.

Improvements (somewhat independent):
* Increasing buffer_pool (as you found)
* innodb_flush_log_at_trx_commit=2 -- faster, less safe
* BEGIN INSERT several rows COMMIT -- one write for several rows
* Batch insert: INSERT INTO tbl (a,b,c) VALUES (1,2,3), (3,4,5), ...; -- 100 rows will run 10 times as fast.
* Is 50 connections sacred? If not, try 20 and 100.

50 connections is about the limit of what 5.6 can handle without bogging down.

Inserting into different tables _may_ speed up the process by decreasing "lock contention". (But it is not as likely to help as my suggestions above.)

I tend to dislike benchmarks -- they rarely reflect "real life".

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Why INSERT performance has not increase in sub-table?
966
November 03, 2014 01:12PM


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.