Re: Mysql Tuning (Hitrate - keybuffer)

[Jonathan Lampe]

If MySQL just keeps returning the same information again and again, you may want to think about some additional caching in the application itself. (I don't think a hitrate of 100% is healthy in itself.) If you are getting 100% hits on your cache, increasing the size of the cache buffer isn't going to help...because you don't really need to add additional information to the cache very often. (Also check the "Qcache_free_memory" status.)

Oh...wait...this is about keybuffering and you said you are on MySQL 3.x. Let me back up...the first thing you need to do is upgrade to MySQL 4.0...which has QUERY caching.

Most of the time I've run into heavy MySQL usage I've been able to solve it with a new index or two...the queries which slip through the cracks are the ones which can tie up the rest of the system because a sequential read can really tie up the disk. I'd start logging raw queries based on frequency of use and then target those queries using complicated WHERE clauses and/or lots of joined tables. Those are the type of queries which typically tie up my apps.