Selects grinding to a halt with 16 inserts/min

[Mark Zeman]

I've just switched a table with 2.5 million rows to InnoDB to avoid table locking but I'm still seeing a significant slow down of select queries when the table is being updated.

When the table is not being updated I can run 10k select queries in 891ms but when the table is being updated at 16 inserts/min the same 10k select queries will slow right down to 51,800ms.

The table updates happen for a period of 8 hours each day and during that time the performance of the selects slowly degrades and consumes more CPU over time even though the insert rate is fairly consistent.

There's plenty of free ram in the buffer pool and a high cache hit rate so I was hoping that InnoDB's row level locking would improve select speed.

With the performance degrading over the 8 hours it feels like the table never gets a chance to rebuild it's index and get progressively worse, or something to that effect. What else could be degrading performance overtime with a consistent insert rate?

Any performance tuning advice or settings to check would be greatly appreciated.