Mysql InnoDB Insertion Speed Too Slow?

Question

I have a InnoDb table in Mysql that needs to handle insertions very quickly (everything else can be as slow as it wants). The table has no relations or indexes an id which is auto incremented and a time stamp.

I ran a script from multiple clients to insert as many records as possible in the allotted time (loop insert) and calculated the number of insertions per second.

I am only getting on average 200 insertions per second and I need around 20000. The performance doesn't change with the number of clients running the script or the machine the script is running on.

Is there any way to speed up the performance of these insertions?

------ edit --------

Thanks for all your help. I couldn't group any of the insertions together because when we launch all the insertions will be coming from multiple connections. I ended up switching the engine for that table to MyISAM and the insertions per second immediately shot up to 40,000.

What is the hardware of the server (CPU, RAM, Disks)? Is it a dedicated MySQL server or is it performing other work? A good idea is to run some sort of benchmark like tpcc-mysql by Percona or some OLTP benchmark to get real results as to what your system can handle. — drew010, Aug 07 '12 at 20:44

score 2 · Answer 1 · answered Sep 22 '12 at 23:20

Which primary key did you used?

InnoDB uses clustered index so all datas are in same order as its primary key indexes.

if you don't use auto-increment type primary key, then it makes large disk operations for each inserts. it pushes all other data and inserts new element.

for longer reference, try check http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html

Ross Smith II · Accepted Answer · 2012-08-07T20:51:05.650

First, execute the INSERTs in a TRANSACTION:

START TRANSACTION;

INSERT ...

COMMIT;

Second, batch up multiple rows into a single INSERT statement:

START TRANSACTION;

INSERT INTO table (only,include,fields,that,need,non_default,values) VALUES
(1,1,1,1,1,1,1),
(2,1,1,1,1,1,1),
(3,1,1,1,1,1,1),
...;

COMMIT;

Lastly, you might find LOAD DATA INFILE performs better than INSERT, if the input data is in the proper format.

score 0 · Answer 3 · answered Aug 07 '12 at 20:44

i suggest to try with multiple inserts in one query for example

INSERT INTO table 
   (title, description) 
VALUES 
   ('test1', 'description1'), 
   ('test2', 'description2'), 
   ('test3', 'description3'), 
   ('test4', 'description4')

or try to use procedures

Mysql InnoDB Insertion Speed Too Slow?

3 Answers3

Linked