slow insert on table with 1.6mm rows

MySQL & MariaDB Other MySQL® Questions
1

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

2

What does the output from:

SHOW GLOBAL VARIABLES;-- andSHOW GLOBAL STATUS;

look like?

And how big is the table in MB?

3

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:

4

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?

Are all inserts on this machine slow or is it only inserts to this table?

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.

Is the server under heavy load?
Do you have a lot of SELECT’s or UPDATES against this table during this time?

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.

5

KEY lid (id),

that key is redundant because id it is the same as your primary key.

6

That was a typo, its a key on “lid”.

7

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!