Bug #44602 Table Cache Not Optimal rule does not account for renamed table_cache variable
Submitted: 1 May 2009 14:38 Modified: 18 Sep 2009 18:25
Reporter: Diego Medina
Status: Closed
Category:Monitoring: Advisors/Rules Severity:S3 (Non-critical)
Version:2.0 , 2.1.0.1034 OS:Any
Assigned to: Andy Bang Target Version:2.0 maint release
Triage: Needs Triage: D3 (Medium) / R1 (None/Negligible) / E2 (Low)

[1 May 2009 14:38] Diego Medina 
Description:
The recommendation for the rule "Table Cache Not Optima" says

Recommended Action
SET GLOBAL table_cache = (64 + 16);

But you get an error executing that query

mysql> SET GLOBAL table_cache = (64 + 16);
ERROR 1193 (HY000): Unknown system variable 'table_cache'
mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.31-log | 
+------------+
1 row in set (0.01 sec)

mysql> 

See
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_table_open_cach...
 for details about the renamed variable

How to repeat:
1- Install the service manager and start it
2- Go to the Advisors tab
3- Add to schedule
4- Expand Memory Usage
5- Click on Table Cache Not Optimal
6- See the variable name

Suggested fix:
Nice way: Have the variable name show up dynamically based on server version
Easier fix: Show both names on the Recommended steps, with a note about the new variable
name on versions greater than 5.1.3
[5 May 2009 2:59] Andy Bang 
In 2.0: Pushed up to revision 269.
In 2.1: Pushed up to revision 262.

Note that the fix applies to both the "Table Cache Not Optimal" and the "Table Cache Set
Too Low For Startup" rules.

Also note that I implemented the easy fix because I don't think we can do the nice fix
without a change to the Service Manager.
[14 May 2009 4:21] Keith Russell 
Patch installed in versions => 2.1.0.1039.
[18 May 2009 16:11] Diego Medina 
Verified fixed on 2.1.0.1039
[22 May 2009 15:41] Tony Bedford 
An entry was added to the 2.1.0 changelog:

The recommendation for the rule Table Cache Not Optimal says:

Recommended Action
SET GLOBAL table_cache = (64 + 16);

But an error was generated on executing that query:

mysql> SET GLOBAL table_cache = (64 + 16);
ERROR 1193 (HY000): Unknown system variable 'table_cache'

mysql> select version();
+------------+
| version()  |
+------------+
| 5.1.31-log | 
+------------+
1 row in set (0.01 sec)
[25 Jun 2009 17:33] Enterprise Tools JIRA Robot 
Andy Bang writes: 
Patch is already there, waiting for a 2.0 build.
[31 Jul 2009 4:55] Enterprise Tools JIRA Robot 
Keith Russell writes: 
Patch installed in versions => 2.0.6.7159.
[26 Aug 2009 19:36] Enterprise Tools JIRA Robot 
Diego Medina writes: 
Verified fixed on 2.0.6.7159
[18 Sep 2009 18:25] Tony Bedford 
Also added to 2.0.6 changelog.