Re: Unexpected INSERT DELAYED behavior

[Rick James]

I'm not going to address INSERT DELAYED locks, etc, but rather discuss other solutions to the performance problem...

If you never DELETE or UPDATE, and never have, then there is another optimization -- it will insert off the end of the table, without taking a lock. Sounds like you did some DELETE/UPDATE in the past. Perhaps
ALTER TABLE foo ENGINE=MyISAM;
will clear that flag.

What are your INSERTs like? Are they "batched"? I recommend, when possible, to batch INSERTs at about 100 per batch. This tends to speed up the insert 10-fold, thereby competing with SELECTs less. More than 100 rows tends to lock the table too long; you will notice the lock. Less than 100 is less efficient.

From another approach... Let's see if we can optimize the SELECTs; please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]