Hello world.
I'm playing with the Query cache in 5.0.13rc1 (Debian testing as it is now, I think), trying to overcome an issue where when I start a service, there are instantly a heap of requests for the SAME SQL statement, within nanoseconds of eachother. Following the theory, the first thread to connect and send its SQL to the server wll look at the QCache, and see there is nothing there (initially). So it goes on its merry way and starts opening tables and running, etc, as we know. Lets imagine that this SQL query takes 1 second on an unloaded system.
The second thread comes along, and does pretty much the same thing: sees no QCache entry, so starts to do the query. But the system is already busy, and now we have increase the system load.
The third thread..... etc.... and pretty quickly we're spiraling down into oblivion; each extra thread is delaying the first one from coming up with a result as the system sinks under load.
So, is it feasible fo rthe QCache to be extended so that, when the first thread looks at the cache, it inserts a placeholder into the QCache with a mark showing that it is now executing this query, and permitting other subsequent threads to see that they should wait for a few milliseconds and check the cache again to see if the result has been returns and the cache now populated? Sort of like a semaphore to prevent spiralling into a deadlock of resources. Perhaps have a timestamp as to when the lookup started, so we can see if it was recent (within 60 (configurable) seconds?) in case the executing thread was killed, to prevent a stale cache.
Anyway, an idea. Anyone else like to comment? Is this idea rubbish?
James
James Bromberger,
http://www.james.rcpt.to