brashquido Posted March 9, 2007 Share Posted March 9, 2007 Hi All, Seems my server is not benefiting from having the query cache enabled and I'm wanting to turn it off. Just wondering how you set have_query_cache to off in my.ini? I've tried just have_query_cache=no but MySQL doesn't restart. Any ideas? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 11, 2007 Share Posted March 11, 2007 You should take a look here; the same page also has some diagnostics... what makes you think you're not getting any benefits? Quote Link to comment Share on other sites More sharing options...
brashquido Posted March 12, 2007 Author Share Posted March 12, 2007 My query cache hitrate was only about 65~70%. I've read that to get performance benefits your hitrate needs to be in the high 90's. Is this not correct? Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2007 Share Posted March 13, 2007 How is 70% more caching worse than none? Quote Link to comment Share on other sites More sharing options...
brashquido Posted March 13, 2007 Author Share Posted March 13, 2007 Because of the overhead of having to maintain the cache. I haven't done any testing on this yet, but after asking a few questions on the webyog forums I was told that if my query cache had a low hitrate and there was still plenty of room left in the cache (i.e queries were not byte exact, or set not to use the cache) then I might very well see slightly better performance by disabling it. As I use a VPS with very limited RAM resources the memory freed up by disabling the cache is also worth considering. Apart from that my only real choice is examine all the queries in my webapps and optimise them to better utilize the cache. Unfortunately that is well beyond my capabilities at the moment. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 13, 2007 Share Posted March 13, 2007 Well, easiest way is simply to drop the size by half, and wait and see. Quote Link to comment Share on other sites More sharing options...
brashquido Posted March 13, 2007 Author Share Posted March 13, 2007 Yeah, I figure if I lower the cache until Qcache_lowmem_prunes starts registering something and then just up it a bit I should have the sweet spot for my server. Not sure what to do about queries not using the cache though. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 14, 2007 Share Posted March 14, 2007 Which ones aren't using the cache? You mean ones with certain expressions, like NOW()? Quote Link to comment Share on other sites More sharing options...
brashquido Posted March 14, 2007 Author Share Posted March 14, 2007 No, the ones I'll try looking for are the ones that should be able to use the cache, but are not byte exact. Quote Link to comment Share on other sites More sharing options...
fenway Posted March 15, 2007 Share Posted March 15, 2007 "byte exact"? I've never heard that term before. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.