Too much of a good thing…

I have had a lot of people asking me about MySQL lately so I thought is was time to share something I learned.  Too much caching space in MySQL can be a bad thing.

You would thing the more you can stuff your entire database into memory the better off you would be and the faster your system would work.  That is not completely true. I have made mprovements by lowering table_cache variable on every version my MySQL 5. From what I have read this seems to be due to overhead managing file descriptors and caching tables.  Like most things there is a point of diminishing returns when dealing with cache tables.  At some point your hit rate is overtaken by the management threads.

This seems to work best.  The read_rnd should be four times the sort and join buffer and they are four times the read buffer.  Something like this;

sort_buffer_size = 16M
read_buffer_size = 4M
read_rnd_buffer_size = 64M
join_buffer_size = 16M

Send me feedback at Mark at Grennan.com

Tweet