# slow insert on table with 1.6mm rows **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 28, 2010, 11:02pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544 "2010-10-28T23:02:18Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [October 28, 2010, 11:02pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/1 "2010-10-28T23:02:18Z")
The table below currently contains approx. 1.6mm records, inserts on this table are consistently slow, i.e. between 2 and 4 seconds. Any suggestions, thoughts as to where to look? CREATE TABLE `log` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `sid` int(11) NOT NULL, `activity_type` enum(‘a’,‘b’,'c) DEFAULT ‘a’, `activity_name` int(11) DEFAULT NULL, `activity_description` varchar(50) DEFAULT NULL, `activity_date` datetime NOT NULL, `activity_score` int(11) DEFAULT NULL, `lid` int(11) NOT NULL, `wrong` text, `num` tinyint(3) unsigned DEFAULT ‘0’, `num_wrong` tinyint(3) unsigned DEFAULT ‘0’, `num_right` tinyint(3) unsigned DEFAULT ‘0’, `num_missing` tinyint(3) unsigned DEFAULT ‘0’, `external_id` int(11) DEFAULT NULL, `end` datetime DEFAULT NULL, `time_offset` char(6) DEFAULT ‘-04:00’, PRIMARY KEY (`id`), KEY `lid` (`id`), KEY `slid` (`sid`,`lid`), KEY `aname` (`activity_name`) ) ENGINE=InnoDB AUTO\_INCREMENT=2319610 DEFAULT CHARSET=utf8 ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [October 29, 2010, 4:11am UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/2 "2010-10-29T04:11:16Z")
What does the output from: SHOW GLOBAL VARIABLES;-- andSHOW GLOBAL STATUS; look like? And how big is the table in MB? ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [October 29, 2010, 5:59am UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/3 "2010-10-29T05:59:15Z")
show global status and show global variables produce a large number of rows, are you looking for any particular values? doing a show table status on this table produces: Engine: InnoDB Version: 10 Row\_format: Compact Rows: 1501814 Avg\_row\_length: 112 Data\_length: 168476672 Max\_data\_length: 0 Index\_length: 128663552 Data\_free: 523239424 Auto\_increment: 2320137 Create\_time: 2010-10-25 21:02:06 Update\_time: NULL Check\_time: NULL Collation: utf8\_general\_ci Checksum: NULL Create\_options: Comment: ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [October 29, 2010, 5:11pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/4 "2010-10-29T17:11:54Z")
Well there are a bunch of them that can be interesting, but here are some ideas instead: 1. What is the server doing during this time? High CPU, high I/O load? 1. Are all inserts on this machine slow or is it only inserts to this table? 1. What kind of hardware (disks) are you using? What is the setting of: innodb\_flush\_log\_at\_trx\_commit If you don’t have a raid controller then you can try to set this variable to 2 instead. 1. Is the server under heavy load? Do you have a lot of SELECT’s or UPDATES against this table during this time? 1. If it’s only inserts to this table that are slow and this is the only big table, how large is your innodb\_buffer\_pool\_size? If the innodb buffer is too small then an insert can go very slow since it might have to swap in and out a lot of data. There a lot of things that can cause a problem so we have to get to know your server before we can say something useful. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [November 1, 2010, 1:52pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/5 "2010-11-01T13:52:11Z")
KEY `lid` (`id`), that key is redundant because id it is the same as your primary key. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [November 1, 2010, 1:57pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/6 "2010-11-01T13:57:00Z")
That was a typo, its a key on “lid”. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [November 1, 2010, 2:02pm UTC](https://forums.percona.com/t/slow-insert-on-table-with-1-6mm-rows/1544/7 "2010-11-01T14:02:41Z")
We have made the following changes to our system to try and resolve this issue and are observing. a) Changed table engine to myisam as this table is mostly an insert table, very little updates, some selects through a dedicated slave. b) Configured the server with “concurrent\_insert=2” to allow inserts during selects. In response to your questions… What is the server doing during this time? High CPU, high I/O load? – Dedicated system, currently not under heavy load Are all inserts on this machine slow or is it only inserts to this table? – This insert seems to be the one that comes up, in particular, in the slow log. What kind of hardware (disks) are you using? – This particular system, is not the fastest of the bunch in terms of RAID and disk configuration, we are looking at upgrading it. What is the setting of: innodb\_flush\_log\_at\_trx\_commit – innodb\_flush\_log\_at\_trx\_commit\_session | 3 If you don’t have a raid controller then you can try to set this variable to 2 instead. Is the server under heavy load? – Moderate load. Do you have a lot of SELECT’s or UPDATES against this table during this time? – Limited to no updates on this table, some selects. If it’s only inserts to this table that are slow and this is the only big table, how large is your innodb\_buffer\_pool\_size? – innodb\_buffer\_pool\_size | 5368709120 | – Although this does not matter at this time as we’ve changed to myisam engine. Thanks!