MySQL Bugs: #39027: Open table upper limit ignored or confusing resulting in high load

Bug #39027	Open table upper limit ignored or confusing resulting in high load
Submitted:	25 Aug 2008 22:34	Modified:	26 Sep 2008 9:12
Reporter:	Sean Kelly	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.58-log	OS:	Linux (2.6.18-53.1.14.el5)
Assigned to:		CPU Architecture:	Any
Tags:	open table cache

Description:
MySQL enterprise manager suggests that Table Cache is not Optimal

Advice:
Increase the value of table_cache dynamically and monitor the ratio of Open_tables to Opened_tables. When it reaches an acceptable level, put the corresponding value of table_cache in your my.cnf/my.ini file so the variable is set properly when the server is restarted. Your current number of open tables is 1024 and your cache size is 1024, whereas you have had to open 2732.

Took the advice. 

Some kind of resource leak arose where table cache was set to 3000 during run time (as suggested) but open_tables doubled and then some.

How to repeat:
Step 1 - Be in a high contention - high traffic environment
Step 2 - Up the table cache a lot and watch the presumed resource leak take load to unusable level.

Before behavior:

  Load around 3.5

  mysql> show status like 'open%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | Open_files    | 10    | 
 | Open_streams  | 0     | 
 | Open_tables   | 1025  | 
 | Opened_tables | 0     | 
 +---------------+-------+
 4 rows in set (0.51 sec)

After Behavior:

 Load in low 20's.

 MySQL enterprise manager suggests upping open tables limit:

 mysql> SET GLOBAL table_cache = 3000 ;

 mysql> show status like 'open%';

 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | Open_files    | 10    | 
 | Open_streams  | 0     | 
 | Open_tables   | 6472  | 
 | Opened_tables | 0     | 
 +---------------+-------+
 4 rows in set (8.16 sec)

Suggested fix:
Restart and rewarm cache.

Thank you for the report.

Please explain what you consider a bug:

Having  Open_tables = 6472 while table_cache = 3000 or Advisor suggestion which didn't help you?

Also please run FLUSH TABLES next time when you want to clear table cache instead of server restart.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".