Hi,
On May 8, 2005, at 1:47 PM, Kevin Burton wrote:
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.
Alright, I assumed a typo or such.
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.
It is waiting for the table, it isn't a matter of waiting for disk i/o, but instead making sure it doesn't read anything in an inconsistent state. Every operation has to be atomic and make sure not to read half-written information. Also concurrent insert would prevent this from happening as well.
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.
There isn't really any way to "use" concurrent INSERT. It happens automatically if possible. However there are a few things you can do to help it along, such as OPTIMIZE after you DELETE large portions of the table. Also it does have to enabled in LOAD DATA INFILE manually. However, regular INSERTs and SELECTs should do this automatically.
Why do you think this is your bottleneck? Have you measured anything quantitatively? What is your table_locks_immediate vs. your table_locks_waited? What is your rate of INSERTs?
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]
