Matt, One last question and then I promise to drop the topic ... what would be the best way to force a complete load of an index into the key buffer ?
Thanks very much for your time. John "Matt W" <[EMAIL PROTECTED]> 2004-01-15 02:06 To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> cc: Subject: Re: How does key buffer work ? Classification: Hi John, ----- Original Message ----- From: <[EMAIL PROTECTED]> Sent: Wednesday, January 14, 2004 6:37 AM Subject: Re: How does key buffer work ? > Matt, > > Many thanks for the answer. It has helped enormously. > > First, I have been getting the odd index corruption that has proved to be > very annoying. I had checked the changes document for releases since > 4.0.13 and there didn't seem to be any mention of an index problem but now > I'll upgrade asap. Thanks for that. It was in 4.0.15: http://www.mysql.com/doc/en/News-4.0.15.html "Fixed rare bug in MyISAM introduced in 4.0.3 where the index file header was not updated directly after an UPDATE of split dynamic rows. The symptom was that the table had a corrupted delete-link if mysqld was shut down or the table was checked directly after the update." > Next, I had extended my key buffer too much. When I calculated the high > water mark for key buffer usage, I found that indeed it was considerably > less than the space I had allocated. I will modify accordingly. However, I > was just thinking about what you said about this only being a high water > mark ... I can't see any way, apart from dropping an index or table, that > information is going to be purged from the cache especially as you say > that MySQL updates the contents of the cache when an index is modified, so > won't that mean that during normal operation the key_blocks_used should > indicate exactly how many blocks are currently in use ? Some of the > contents may of not been used for a while but still they won't be purged > unless the maximum extent of the cache is reached ? When a table is closed, its blocks are released from the key_buffer. So after running FLUSH TABLES, for example, Key_blocks_used should be 0 if it was "current." You can see that the blocks are removed from the buffer by running a query that uses an index. Look at Key_reads. Run it again and Key_reads shouldn't change. Use FLUSH TABLES and run it again. Key_reads will be increased since the blocks were reloaded. > With regards to the caching on myd data, is the fact that MySQL doesn't > cache myd data a design choice ? It makes perfect sense for MySQL > installations on a dedicated machine as its a fair assumption that there's > no other nasty apps around filling your system cache with other data and > the OS is probably in the best position to cache the disks. However, in my > case (and I would guess in the proportion of the cases), the database > shares the machine and in my case this is with Apache which depending on > the usage on the website, is likely to flush the cache reasonably quickly. > It would be easier to get more consistent query execution times if MySQL > maintained it's own caches (for myd data as well) so that more control > could be kept on cache contents. In a previous life I worked with Sybase > and one of the advantages (only ?) is that the administrator has control > on the caching of index and data for each individual table. Very handy if > it was benficial to ensure the contents of specific tables were available > in a cache. InnoDB may cache full row data too with its buffer_pool. But I don't know much about that. :-) But when you have something like Apache running, you want it to be able to use the memory it needs. Isn't it better to not have .MYD data cached than to have other processes swapping? > Lastly, I'd love to use the query cache but I do have to update the > indexes every 5 minutes (the system revolves around retrieving SNMP data > from a bunch of routers every 5 mins then dumping it into the db ... a > user then requests a report periodically) so the QCache is invalidated > every 5 mins anyway. If you can get [repeated] queries to use the query cache for 5 minutes, I'd say that's a pretty long time. :-) > So, a) do you (or anyone else) know of any plans to > extend the caching functionality No. > and b) are there any other parameters > that may be worth a tweak ? One thing I had considered was to extend a > composite index to incorporate the data that is required in the > problematic query then the query should be able to extract the data > required without having to search the myd file. I understand that this > will increase the index size and slow the inserts but otherwise is this a > legitamate solution i.e. there must be some other downside surely ? No, that would probably work pretty well if you don't mind making the index a bit bigger (there's a limit of 16 cols/index or 512 (?) bytes, and no [full] TEXT/BLOB columns). I've done this on one of my tables. If EXPLAIN on your SELECTs says "Using index", then it's getting the data without going to the .MYD file (I guess you already know that). Making an index bigger (by adding columns) is not as bad as adding another separate index. With a bigger index, the only slowdown on inserts is writing the extra bytes -- not much. But for each separate index, MySQL needs to find where in the index to put the new row. So I don't think you'll notice any slowdown or other downsides. :-) > Anyway, thanks again for the response. > > John Hope that helps. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]