# MySQL Write performance bottleneck **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 16, 2015, 1:48am UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456 "2015-10-16T01:48:44Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Archak](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@Archak](https://forums.percona.com/u/Archak)\ **Post date:** [October 16, 2015, 1:48am UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456/1 "2015-10-16T01:48:44Z")
MySQL Config: table\_cache =1000 thread\_cache =60 max\_heap\_table\_size =8192M join\_buffer\_size =256K sort\_buffer\_size =256K query\_cache\_size =512M query\_cache\_limit =512M key\_buffer\_size =8000M bulk\_insert\_buffer\_size =2000M myisam\_sort\_buffer\_size =8000M tmp\_table\_size =8192M myisam\_repair\_threads =5 innodb\_open\_files =10000 open\_files\_limit =10000 concurrent\_insert =2 #innodb innodb\_flush\_log\_at\_trx\_commit =2 innodb\_support\_xa =0 innodb\_buffer\_pool\_size =128G innodb\_buffer\_pool\_instances =8 innodb\_flush\_method=O\_DIRECT innodb\_log\_file\_size=1G innodb\_log\_buffer\_size=256M innodb\_thread\_concurrency=8 innodb\_file\_per\_table innodb\_stats\_on\_metadata =0 innodb\_io\_capacity=10000 mysql\>[COLOR=#00008B]SELECT EVENT\_NAME, SUM\_TIMER\_WAIT [COLOR=#00008B]FROM events\_waits\_summary\_global\_by\_event\_name [COLOR=#00008B]where event\_name [COLOR=#00008B]not[COLOR=#00008B]like’%innodb%'[COLOR=#00008B]ORDER[COLOR=#00008B]BY SUM\_TIMER\_WAIT [COLOR=#00008B]DESC LIMIT 20;±------------------------------------------------------±----------------+| EVENT\_NAME | SUM\_TIMER\_WAIT |±------------------------------------------------------±----------------+| wait/synch/cond/sql/MDL\_context::COND\_wait\_status |247003180269200|| wait/synch/mutex/mysys/KEY\_CACHE::cache\_lock |225136157662400|| wait/io/[COLOR=#00008B]file/myisam/dfile |112586106594800|| wait/synch/cond/mysys/my\_thread\_var::suspend |36942967073600|| wait/io/[COLOR=#00008B]file/sql/[COLOR=#00008B]load|28575680068400|| wait/synch/mutex/sql/HA\_DATA\_PARTITION::LOCK\_auto\_inc |15671510821200|| wait/synch/rwlock/myisam/MYISAM\_SHARE::key\_root\_lock |7580580832000|| wait/io/[COLOR=#00008B]file/sql/global\_ddl\_log |759675521600|| wait/io/[COLOR=#00008B]file/myisam/kfile |478606412800|| wait/synch/mutex/sql/LOCK\_open |317343598000|| wait/io/[COLOR=#00008B]file/sql/FRM |281702406000|| wait/synch/mutex/myisam/MYISAM\_SHARE::intern\_lock |237524998400|| wait/synch/mutex/mysys/THR\_LOCK::mutex |148067737600|| wait/synch/cond/myisam/MI\_SORT\_INFO::cond |53355489600|| wait/synch/mutex/sql/TABLE\_SHARE::LOCK\_ha\_data |40690076800|| wait/io/[COLOR=#00008B]file/sql/slow\_log |32342937200|| wait/io/[COLOR=#00008B]file/sql/[COLOR=#00008B]partition|32000921600|| wait/synch/cond/mysys/IO\_CACHE\_SHARE::cond |20865078800|| wait/synch/mutex/mysys/THR\_LOCK\_open |18582940800|| wait/synch/mutex/sql/THD::LOCK\_thd\_data |15040836800|±------------------------------------------------------±----------------+ mysql\>[COLOR=#00008B]SELECT EVENT\_NAME, SUM\_TIMER\_WAIT [COLOR=#00008B]FROM events\_waits\_summary\_global\_by\_event\_name [COLOR=#00008B]where event\_name [COLOR=#00008B]like’%innodb%'[COLOR=#00008B]ORDER[COLOR=#00008B]BY SUM\_TIMER\_WAIT [COLOR=#00008B]DESC LIMIT 20;±----------------------------------------------±----------------+| EVENT\_NAME | SUM\_TIMER\_WAIT |±----------------------------------------------±----------------+| wait/io/[COLOR=#00008B]file/innodb/innodb\_data\_file |161894926356400|| wait/synch/mutex/innodb/log\_sys\_mutex |78901379729200|| wait/io/[COLOR=#00008B]file/innodb/innodb\_log\_file |54154967108000|| wait/synch/mutex/innodb/log\_flush\_order\_mutex |31350108798800|| wait/synch/rwlock/innodb/btr\_search\_latch |29658726116800|| wait/synch/mutex/innodb/buf\_pool\_mutex |26124486124000|| wait/synch/mutex/innodb/kernel\_mutex |9542359283600|| wait/synch/mutex/innodb/trx\_undo\_mutex |4636908475600|| wait/synch/mutex/innodb/autoinc\_mutex |2176569546400|| wait/synch/mutex/innodb/fil\_system\_mutex |1580851583600|| wait/synch/mutex/innodb/dict\_sys\_mutex |764196921200|| wait/synch/mutex/innodb/flush\_list\_mutex |493146643200|| wait/synch/mutex/innodb/ibuf\_mutex |415302790000|| wait/synch/rwlock/innodb/dict\_table\_stats |346864396800|| wait/synch/mutex/innodb/mutex\_list\_mutex |341627459200|| wait/synch/rwlock/innodb/checkpoint\_lock |263200847600|| wait/synch/mutex/innodb/rw\_lock\_list\_mutex |247598360000|| wait/synch/mutex/innodb/trx\_doublewrite\_mutex |84526368000|| wait/synch/mutex/innodb/rseg\_mutex |76199049600|| wait/synch/mutex/innodb/ibuf\_bitmap\_mutex |12117140800|±----------------------------------------------±----------------+ ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [October 16, 2015, 10:44am UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456/2 "2015-10-16T10:44:40Z")
Hi archak; I see you have innodb\_io\_capacity set to 10k. Is that something you’ve tested and found to be the max? Can’t speak for your specific drive config, but that seems low. Another one I would try is setting innodb\_thread\_concurrency to 0 to not limit the concurrency. Not sure what you’ve tried for innodb\_write\_io\_threads, but I would try something around 8 if you have not gone that high. I would also look at innodb\_purge\_threads, and try setting that to 1 if you are on 5.5.x and maybe something larger like 2-4 if you are on 5.6.x. I’d start with that and see where you go. The key will be to make small changes and really see the impact, as if you change to much at once you won’t have any idea what’s what (which I’m sure you are well aware of by this point). -Scott ---
**Author:** ![Archak](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@Archak](https://forums.percona.com/u/Archak)\ **Post date:** [October 16, 2015, 12:58pm UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456/3 "2015-10-16T12:58:35Z")
Thank you for your response Scott. We did try ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [October 16, 2015, 7:23pm UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456/4 "2015-10-16T19:23:35Z")
Hi Archak; I’d also try upping your innodb\_io\_capacity if you have not already; that will still likely be your biggest tuning knob. As for your wait times, your largest one, wait/io/file/innodb/innodb\_data\_file, is waiting on an InnoDB tablespace file, which makes sense as it sounds like you are doing all of your heavy inserts into one table. So the fact that everything is happening that one table will hurt you. Another possible option to test is setting innodb\_autoinc\_lock\_mode = 2, but it has a lot of implications that make it a bit dangerous so research carefully: [url][http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-configurable.html[/url]](http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-configurable.html%5B/url%5D) You also mentioned the table had a primary key; does it have any other indexes or foreign keys? those would definitely slow down inserts, but you likely already took that into account. -Scott ---
**Author:** ![Archak](https://avatars.discourse-cdn.com/v4/letter/a/58f4c7/32.png) [@Archak](https://forums.percona.com/u/Archak)\ **Post date:** [October 19, 2015, 7:23pm UTC](https://forums.percona.com/t/mysql-write-performance-bottleneck/4456/5 "2015-10-19T19:23:59Z")
I did try increasing So far, it seems like spawning multiple processes to load data (using LOAD DATA INFILE) seems to increase the ingestion rate (not linearly but substantially) but when running a single process seems to cap out at 20K rows (average innodb insertion rate over an hour). What I am trying to understand is why is limiting at 20K, what could be the bottleneck. What else do you recommend I should look at, I feel the multiple process approach is just a patch that will mask the real problem and the come back to bite us later on.