MySQL Forums
Forum List  »  Performance

Re: Query Cache and simultaneous execution
Posted by: James Bromberger
Date: November 17, 2005 04:27PM

I witnessed this with MYSQL 3.23.xx recently. In fact, what happened was multiple identical queries fired off, and use /tmp for temporary tables. So many of them fired off that there were hundreds of temporary tables in /tmp, such that it filled /tmp - what was around 1GB or so. Doing "show full processlist" showed the identical queries all running (or waiting for their temp tables do find more disk space). This lead me to testing the execution in MySQL 5.0, but in a very rough way - do a query that takes a few seconds (with no query cache, but with the tables already buffered in memory, etc). Then try and do two simultanously and compare the time it takes (ie, two windows, change window and execute). With a clean but enabled cache ("reset query cache"), both queries took some (considerable) time; neither showed signs of blocking waiting for the first thread to populate the query cache.

Does anyone here know this section of the code? I haven't looked at source, but am guessing that the cache is populated when results return from executing; given we have multiple threads accessing the cache to fetch results, if one thread has already started executing, what value is there in a second thread starting the identical same query -- if subsequent threads waited a few cycles the cache would be populated and a huge performance win? Obviously once the cache is populated this is irrelevent.

James Bromberger, http://www.james.rcpt.to

Options: ReplyQuote


Subject
Views
Written By
Posted
1542
November 17, 2005 10:40AM
Re: Query Cache and simultaneous execution
1615
November 17, 2005 04:27PM
1580
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.