Hi Kevin,
On May 8, 2005, at 1:07 PM, Kevin Burton wrote:
OK.
Lets take a mythical application. The app is spending about 50% of its time inserting into table FOO. The other 50% of the time its spent doing SELECT against the table.
The SELECTs can use an index which is already full loaded into the query cache.
Indexes aren't loaded into the query cache, they are loaded into the key cache (key_buffer_size).
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.
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.
Regards,
Harrison
-- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
