Skip navigation links

MySQL Forums :: Performance :: Query Cache and simultaneous execution


Advanced Search

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 2905 James Bromberger 11/15/2005 05:43PM
Re: Query Cache and simultaneous execution 1123 Jeff Ambrosino 11/17/2005 09:35AM
Re: Query Cache and simultaneous execution 1086 Jay Pipes 11/17/2005 10:40AM
Re: Query Cache and simultaneous execution 1131 James Bromberger 11/17/2005 04:27PM
Re: Query Cache and simultaneous execution 1115 Jay Pipes 11/17/2005 05:19PM
Re: Query Cache and simultaneous execution 990 James Bromberger 11/17/2005 05:46PM
Re: Query Cache and simultaneous execution 1102 KimSeong Loh 11/17/2005 07:13PM
Re: Query Cache and simultaneous execution 995 James Bromberger 02/04/2006 08:48PM


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.