Unexpected INSERT DELAYED behavior
I'm checking to see if the behavior I'm seeing is intended, or a bug.
I have a good sized MyISAM table that has mixed reads/writes. But, there are never deletes or updates, so I've always had decent performance. Recently, I was having issues with large bursts of incoming writes. Given I don't need the ability to instantly read the incoming values, INSERT DELAYED seems like a quick win. Well, it's been a many day struggle, and I'm still not happy with my solution.
The problem is that there are some decently heavy SELECTs on that table (multiple seconds, some are 10-20 seconds). When I turned on INSERT DELAYED, things went downhill, and fast. After much debugging, I believe the effect I'm seeing is this sequence:
-I have a slow query in the "sending data" state
-INSERT DELAY thread goes into upgrading lock (which seems to lock the table!!! See my follow up on this at the end)
-All incoming queries (read or write) back up until both of the above queries complete
I found out that if I change the low-priority-updates setting to ON globally, the INSERT DELAYED thread uses it. Now, everything seemed better:
-I have a slow query in the "sending data" state
-INSERT DELAY decides it needs to update but doesn't lock the table
-INSERT DELAY gets its turn
-Nothing blocks (?)
Here's the catch. I have two paths in my software to INSERTS. One is high volume, which I fixed with the above. The other is low (or lower) volume, but much harder to switch to INSERT DELAYED. But, now it's getting really slow because all of its INSERTS are waiting for more or less everything before getting a turn. Based on the docs, if I add HIGH PRIORITY it will be ok, but then it will lock the table (even if it doesn't have to), which will itself get contentious (though, less so than the high volume path).
To roll back the conversation, it's the first problem I had that confuses me. Why does INSERT DELAYED grab a table lock while waiting for its turn?? I had expected the default behavior would be:
-the thread puts itself into the queue without a lock
-when it goes active it gets whatever lock it needs
Why does it need to lock the table while waiting in a queue??
Another question I now have: does an actively writing INSERT DELAYED thread allow concurrent reads? Looking at the real time process list, it appears the answer is no... but it's hard to say for sure. If it is blocking, it gives up writing in the DELAYED thread after a bounded period of time, which yes I know is a setting, but why does it block concurrent reads in the first place? This is the second assumption I had: the DELAYED thread would act like any other INSERT thread once it finally ran.
Thanks for any feedback to anything I have right or wrong in thinking about INSERT DELAYED, locks, and priorities.