Re: Optimizing table_cache - Open files?
Hi,
I don't know where you have got the table_cache_size variables from? I don't have it on my system, and according to the documentation it's not either a global status variable (http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html) or a global variable (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html). I hope I didn't make a typo in a previous post and introduce the term by mistake!
Based on your max_used_connections=16, and your number of tables=200, then if every connection wanted to use every table at the same time without swapping those tables on and off disk, you would need to set table_cache=3200. This is an upper bound.
If instead you wanted to make sure that all connections could run the biggest JOIN query, and ensure that all tables needed could be open in memory, then you would need [max_used_connections]x[maximum tables in a join statement]=16x6=96. This is a lower bound.
I think the thing to do is to play with you query cache between these two values and keep a close eye on the opened_tables server status variable. Make a note of opened_tables at the same time every day and see how much it has increased since yesterday.
As connections come and go, flushes are performed, and different tables are opened, then the opened_tables number will increase, so I don't think it's possible to not have opened_tables increase over time, no matter how high your table_cache is set. As to what is an acceptable increase in opened_tables per day, I really don't know.