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".