How many record insertions per second are typical?

[peter.hamilton-scott]

I know this is a skewed question but I was timing how long it would take to insert 50000 rows into a table. My table has 5 columns, two of which are the auto increment and timestamp columns. I have a SP which takes as input the other three parameters which are varchar(200), varchar(30), and integer. They are all defined as 'in' parameters. The SP starts and commits a transaction fore and aft of the insert statement. For audit purposes, I have triggers for the insert, update, and delete actions. The triggers do no more than extract the appropriate OLD and NEW fields and in turn are written to the audit table. So, as you can see, there's nothing really heavy taking place. Everything works as expected.

During the test I ran a select count(*) from the table. Adding the records using the transaction-based SP, I was able to insert on average, about 24-30 records per second. That would be doubled taking the audit record insert into account, so we can speak of roughly 48-60 records per second overall. If I insert the records using direct sql insertion as in 'insert into...' then I'm bypassing the transaction processing in the SP but of course, the trigger is still being used. Interestingly, the throughput was almost the same without the overheads of the transaction as it was with.

My tables are obviously all using the InnoDB engine. I was not disappointed with the throughput of 48-60 records per second but maybe someone could comment on this.

1. Is what I got fairly typical, within the curve, for InnoDB?

2. Would it have been any faster using MyIsam?

3. Is there a kind of unofficial world record for inserting records per second?

Thanks.



Edited 1 time(s). Last edit at 11/05/2005 06:13AM by peter.hamilton-scott.