# Innodb inserts/updates per second is too low **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 26, 2013, 1:57pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910 "2013-08-26T13:57:42Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [August 26, 2013, 1:57pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/1 "2013-08-26T13:57:42Z")
Hi, Inserts or updates to innodb tables are too slow. Ins/sec or upd/sec is around 50. I use Percona Server version: 5.5.32-31.0 I increased the innodb buffer pool size to 4GB. My RAM is 7.5GB innodb\_flush\_log\_at\_trx\_commit is 1. CPU is almost idle. It is a dual core processor. Can anyone please suggest ways to trouble shoot this issue? Thanks, Vijay ---
**Author:** ![yogesh777](https://avatars.discourse-cdn.com/v4/letter/y/87869e/32.png) [@yogesh777](https://forums.percona.com/u/yogesh777)\ **Post date:** [August 26, 2013, 10:57pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/2 "2013-08-26T22:57:10Z")
Performance of insert/updates depends not only h/w configuration of server, it also depends on size of table, number of rows table have, number of indexes, primary keys and foreign key constraints. provide the above details? ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [August 27, 2013, 4:32am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/3 "2013-08-27T04:32:32Z")
Hi Yogesh, I am using load data infile utility to load data in a dat files in to few tables. After the data is loaded, I run few tests. My tests would truncate the data in these tables. So I would load it again for further tests. These insertions were going through fine without any performance issues. But after 6th or 7th iteration, I am facing this issue even with empty table. Each table has a primary key and a foreign key reference to other table. Any idea why this could be slow? Regards, Vijay ---
**Author:** ![yogesh777](https://avatars.discourse-cdn.com/v4/letter/y/87869e/32.png) [@yogesh777](https://forums.percona.com/u/yogesh777)\ **Post date:** [August 27, 2013, 5:44am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/4 "2013-08-27T05:44:51Z")
Hi, How many records are there in load file for insertion? are you using innodb\_file\_per\_table=1 ( separate ibd file for each tables ) or single ibdata1 for all databases and tables? if you have single ibdata file for all databases/tables then multiple truncate and data load increase the size of file on file that might cause the problem. you can try this : drop and recreate the table before every load file operation and see if it helps you. ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [August 28, 2013, 9:58am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/5 "2013-08-28T09:58:48Z")
Hi, I tried with 100k records. Reduced the number to 10k and even 1k. All the times, the insertions are too slow. Yes innodb\_file\_per\_table option is enabled. ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [August 29, 2013, 12:41pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/6 "2013-08-29T12:41:02Z")
What is the size of InnoDB transaction logs on this server? Also flushing on every transaction, but do you have battery backed write cache? ---
**Author:** ![Serge\_Shakhov](https://avatars.discourse-cdn.com/v4/letter/s/6a8cbe/32.png) [@Serge\_Shakhov](https://forums.percona.com/u/Serge_Shakhov)\ **Post date:** [August 29, 2013, 5:53pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/7 "2013-08-29T17:53:33Z")
I’m having the same issue on Percona 5.6 (Ubuntu 13.04) running on AWS m1.xlarge instance XFS filesystem build with default values or software RAID (mdadm) Server configuration based on values provided by [tools.percona.com](http://tools.percona.com) Replication catch up rate is EXTREMELY slow (1-3-5 records per second, expecting 100-200 per second)! Attached SHOW ENGINE INNODB STATUS As you can see server is almost idling. Can you help me to identify bottlenecks? Update: AWS Cloudwatch shows constant high EBS IO (1500-2000 IOPS) but average write size is 5Kb/op whish seems very low. [SHOW ENGINE INNODB STATUS.txt](https://forums.percona.com/uploads/short-url/gasmxIaLoJjeltXW3EdHJnwwFCx.txt) (10.9 KB) ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [September 10, 2013, 6:16am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/8 "2013-09-10T06:16:56Z")
innodb\_log\_file\_size=50M Server does not have a battery backed write cache. ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [September 10, 2013, 7:29am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/9 "2013-09-10T07:29:59Z")
Vijay, so you have innodb\_flush\_log\_at\_trx\_commit = 1 but there is no write cache enabled. This would be not such a problem if you had SSD disks, but otherwise each flushing to disk is very slow. I would recommend to use innodb\_flush\_log\_at\_trx\_commit = 2 - you can loose up to 1 second of transactions in case of OS crash but the IO pressure will be a lot smaller. Now the innoDB logs are too small for any production purpose. Think about 512M or similar. But check first how to change them: [url][http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb\_log\_file\_size-safely/[/url]](http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/%5B/url%5D) Serge, I don’t think your problem is similar. Indeed InnoDB looks idle. So your replication is not keeping up? Is this IO thread not being able to pull binary logs from it’s master in time, or is this SQL thread slowly processing updates? In first case - check the network between master and slave. If the second case - check if your master is using ROW based replication and if all the tables being updated have primary keys. ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [September 10, 2013, 11:48pm UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/10 "2013-09-10T23:48:14Z")
This is not on Production. I am doing this test on a non-prod node. Only point is, with the same mysql configuration, with mysql 5.1, inserts are pretty fast. This problem is only with 5.5 I am going to try a few things. ​I will have the following settings in my.cnf innodb\_flush\_method=O\_DSYNC innodb\_log\_file\_size=512M autocommit=OFF Currently, innodb\_log\_buffer\_size=10M. Will check this number too. ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [September 11, 2013, 2:52am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/11 "2013-09-11T02:52:30Z")
OK, so 5.5 slave, with exactly the same settings and on exactly the same hardware is slower then 5.1 slave? Why are you going to disable autocommit? How this would help? If you have large insert sessions, you can wrap them into a transactions without changing this variable. Also turning off autocommit on slave won’t change anything regarding replication as it’s the master who wraps each insert into BEGIN … COMMIT sections. So you either can use per session on master: set autocommit=0; insert … insert … … set autocommit=1; or (IMHO better) wrap the inserts between BEGIN and COMMIT. This way you may achieve better speed for some group inserts, updates, etc. innodb\_log\_buffer\_size at 10M should be big enough, unless you have really big transactions. ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [September 11, 2013, 4:21am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/12 "2013-09-11T04:21:37Z")
I think you mistook my comments for Serge’s comments. 🙂 I don’t have a replication setup. I have my data in a dat file. I am using mysql load in file utility for bulk data loading ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [September 11, 2013, 5:14am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/13 "2013-09-11T05:14:52Z")
Vijay, indeed I confused a bit with Serge’s comment, but at least part of the autocommit comment was for you 🙂 So, regarding 5.1 vs 5.5 performance of LOAD DATA queries, it may be very simple explanation - in MySQL 5.1 the default engine for tables is MyISAM, while for MySQL 5.5+ it is InnoDB. And surely LOAD DATA can be faster for MyISAM as it’s much simpler process (no transactions, no doublewrite, no redo logs, etc.). Please confirm if the table was InnoDB for 5.5 and MyISAM for 5.1. ---
**Author:** ![vijay](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vijay](https://forums.percona.com/u/vijay)\ **Post date:** [September 11, 2013, 5:50am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/14 "2013-09-11T05:50:31Z")
I am using Innodb in both 5.1 as well as 5.5 ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [September 11, 2013, 9:02am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/15 "2013-09-11T09:02:56Z")
OK, let me just double check then - you are using the same settings in 5.5 as they were in 5.1? For example, the transaction log size is the same? This is exactly the same hardware? ---
**Author:** ![deadmanalive](https://avatars.discourse-cdn.com/v4/letter/d/d07c76/32.png) [@deadmanalive](https://forums.percona.com/u/deadmanalive)\ **Post date:** [September 16, 2013, 2:22am UTC](https://forums.percona.com/t/innodb-inserts-updates-per-second-is-too-low/2910/16 "2013-09-16T02:22:26Z")
Hi Vijay, If you are running script “LOAD DATA INFILE” from non-cloud instance it will take time and some time its very slow insert/update based on your AWS Region & Instance type + Network /bandwidth (between your machine to AWS instance). Suggestion: a) Move the file in the cloud instance (instead of running from non-cloud instance) and run the command of LOAD DATA INFILE from local-host or remot-host exists in same region . b) disable the indexes & FK Checks → import the data → enable indexes for more detail on aws instance : [url][http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/instance-types.html[/url]](http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/instance-types.html%5B/url%5D)