I've been trying to optimise the operation of a MySQL (4.0.13) installation on a Windows 2000 based web server. After going through the docs and browsing the net for some time, it seems that after ensuring that your database design is sound and your queries correctly structured with the right indexes present then further optimisation can be realised by tinkering with the MySQL server's startup parameters, principally the key buffer size. It seems that the key buffer is solely used as an index cache and that extending this, up to a point, potentially will significantly improve performance. However, after playing with this value on my system for a while, I have a number of questions about how it works... 1) I assume that the key buffer caches the contents of the myi files (I'm only talking MyISAM tables here) but is this a direct copy of the contents ? i.e. if you extend the key buffer so that it is bigger than the sum of the size of the myi files on your system, then will this be sufficient to be able to cache all the indexes all the time ? 2) Does the whole index get loaded into the cache on the first time it's accessed or do only 'parts' of the index get loaded as they are used for various queries ? 3) If an index is updated for any reason, is the whole cache copy of the index then invalidated or is the cache copy updated at the same time as the disk file?
One idea I was toying with was to 'delay' all inserts to the sensitive tables (an update is done every five minutes for my particular system) so that the tables are updated pretty much in one single go and then ensure the key buffer is refreshed so that all select queries on these tables for the next five minute period will use only cached indexes. Does this sound plausible and or sensible ? Thanks and Regards, John Everitt PGN MSS Philips C/IT.
