I'm pretty much new in MySQL so hope you guys can guide me. I would like to know what will be the ideal value to be set for open table and open files in mysql. I believe the default value would be 470 for open tables. What criteria do i need to look upon before modifying this value? Basically, the normal open table range in MySQL server would be in the range of 150-200. But last week we receive a high amount of open table in the range of 450- 480. What would be the cause/reason that trigger this scenario. There is no lock or any error in mysql log file. Anyone encounter this scenario what is root cause for the trigger and how can i avoid it.
There are several criteria for determining the table_cache parameter. These include the number of processes, the number of tables opened at any one time for a query and the application and how it has been developed.
There is no one size fits all, you should have a look at the number of open tables versus the number of opened tables and determine whether the number of opened tables is growing very quickly.