INSERT DELAYED may come in handy when using MyISAM tables. It may in particular be useful for log tables, where one is required to issue frequent INSERTs on one hand, but does not usually want or need to wait for DB response on the other hand.
It may even offer some performance boost, by aggregating such frequent INSERTs in a single thread.
But it is NOT a performance solution.
That is, in a case I’ve seen, database performance was poor. INSERTs were taking a very long time. Lot’s of locks were involved. The solution offered was to change all slow INSERTs to INSERT DELAYED. Voila! All INSERT queries now completed in no time.
But the database performance remained poor. Just as poor as before, with the additional headache: nobody knew what caused the low performance.
Using INSERT DELAYED to improve overall INSERT performance is like sweeping the dust under the carpet. It’s still there, only you can’t actually see it. When your queries are slow to return, you know which queries or which parts of your application are the immediate suspects. When everything happens in the background you lose that feeling.
The slow query log, fortunately, still provides with the necessary information, and all the other metrics are just as before. Good. But it now takes a deeper level of analysis to find a problem that was previously in plain sight.
So: use INSERT DELAYED carefully, don’t just throw it at your slow queries like a magic potion.
Hi Shlomi,
Great post. While I agree with you, I would clarify one thing:
Performance is really the measure of two things; (a) response (b) capacity. INSERT delayed doesn’t improve capacity in any way, but it can reduce the amount of time a user is sitting there waiting if the app is single threaded (like many web apps are). Having said that, I’ve never liked it. I’ve often thought of it as like a poor man’s asynchronous queue.
In many cases the right tool is probably a message queuing system designed for this problem like openmq/activemq/rabbitmq etc.
Hi Morgan,
Thanks for the clarification š