MySQL Forums
Forum List  »  Performance

Query Cache and simultaneous execution
Posted by: James Bromberger
Date: November 15, 2005 05:43PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Cache and simultaneous execution
3473
November 15, 2005 05:43PM
1324
November 17, 2005 10:40AM
1373
November 17, 2005 05:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.