MySQL Bugs: #15044: Query Cache synchronisation for multiple identical threads

Bug #15044	Query Cache synchronisation for multiple identical threads
Submitted:	18 Nov 2005 9:09	Modified:	23 Dec 2008 13:14
Reporter:	James Bromberger	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: Query Cache	Severity:	S4 (Feature request)
Version:	5.0.13	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
If multiple queries are submitted in a very short space of time (ie, shorter time than the actual query takes to run) and the query cache is enabled, then currently all threads doa cache miss, and all threads execute the query. It may be better to have the first thread populate the query cache with a placeholder, indicating that it has started to process the request, and giving subsequent threads the ability to block (for a configurable timeout) and retest the Query Cache to see if it has a result after this pause.

See http://forums.mysql.com/read.php?24,54799,54799#msg-54799 for a discussion.

How to repeat:
Create a few large tables, and for the purpose of emphasising th issue, create a query that does not use indexes and requires some sorting to increase the execute time.

In two windows, execute this query almost simultaneously. Both threads will run the real query as the cache is not populated when they start running. Both threads will now take double the time to execute becuase of contention for I/O resources.

Suggested fix:
On cache miss, mark the query in the cache as being executed, possibly with the timestamp of when it was being executed, and then start executing.If there are results, update the cache, otherwise remove the mark against this query.

On cache hit, see if the result is 'pending' being put in and was made pending within a configurable timeout period, in which case block (or wait for a semaphore) and then check again for a result in the cache. If the timeout period has elapsed, then assume the thread that started to populate the cache has died, and perform a normal cache miss load.

James,

yes, this has been suggested earlier by James Day.

Regards,

Heikki

Any progress on this? Given that MySQL still only uses one index per query, any complicated query that could take advantage of that feature request could take significant advantage from this feature request. This may be easier/quicker to implement too...

Any updates?

This is probably WL#1293. It's unlikely that new features will be added to the stable releases (5.0 and 5.1). Improvements to the query cache can are also being listed at WL#1468.