Harrison Fisk wrote:
aren't loaded into the query cache, they are loaded into the key cache (key_buffer_size).
Yes... you busted me ! :). I meant to say key cache though.
Yes... I realize. The issue is though that only a small percentage of our queries are actually using the query cache.
Now assuming that you have the query cache actually being used (the cache of the actual statement), then normally the SELECT won't wait for any locks. So someone can have a WRITE lock on the table, and your SELECT will still run. If you have a query cache miss, then it will need to acquire the READ lock like a normal SELECT.
Of course... I realize. But what if its blocked by 5-10 INSERTs. Then its going to have to WAIT for these INSERTs to complete even though it can resolve the query without waiting for the table :-/Not only THAT but it doesn't need to read the disk because the filesystem buffer has the blocks in memory.
In this config will the SELECTs block for the INSERTs? I guess they would!
Yes. If MySQL has to actually read the table or indexes then it will set locks to do so, as appropriate to the storage engine in use. Keep in mind, if everything is coming from cache (key_buffer + disk buffer), the lock will generally be very quick as there isn't any disk i/o to block on.
This type of scenario would yield dramatic performance imporovements by migrating to INNODB... would it not?
Either that or there's the option of using MyISAM with no DELETEd rows (since it can then support concurrent insert.)
Concurrent insert sounds like it would work well with your above mythical application (since you didn't mention any DELETEs). The only locking conflict you might have would be that your INSERT's would lock other INSERT's while it is occuring.
Yes... thats my current thinking. That our INSERTs are blocking SELECTs even if they can complete without hitting disk. Now we need to find out if we can use the concurrent select feature of myisam without migrating to INNODB. Its harder to migrate to innodb right now.
Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
