MySQL Table Caching

February 25, 2010

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

tags: , , ,
posted in Commentary by mark

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.
HOME