MySQL Bugs: #70999: Table Cache Event Not Optimal alert seems overly sensitive

Bug #70999	Table Cache Event Not Optimal alert seems overly sensitive
Submitted:	25 Nov 2013 12:07	Modified:	26 Nov 2013 10:05
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S3 (Non-critical)
Version:	3.0.3.2912	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I get an alert for my MEM server (I have a standalone db setup for this):

"myhostname, mysqhostname Table Cache Not Optima l19 minutes ago" ALERT (in red).

This indicates (or seems to) a critical alert.
Yet looking at the global status Opened Tables counter I see that the number is basically static and not changing. The (currently) open_tables setting does indeed match my currently set settings of:

| table_definition_cache | 800 |
| table_open_cache | 800 |

MEM tables on the instance are currently 258 tables.

How to repeat:
See above.

Suggested fix:
Given the situation I do not think this is CRITICAL. It might not be ideal to have the table cache "full", but given it's precisely that I do not see an issue at least while the Opened_tables counter does not increase rapidly as that would suggest the need for mysqld to close an existing handle for a table and open a new one, something which will have some sort of overhead.

I am not sure if P_S currently records "open table latency" as if ti does not then this would be a good metric to add. If it does perhaps the advisor should be providing information on the metric as an indication of what performance gain would be obtained by increasing the table_open_cache and table_definition_cache settings.

So my suggestion would be to change the alert type from WARNING to CRITICAL even if the number of open tables reaches the cache sizes and provide better diagnostic information as to whether the performance hit because of this "suboptimal" setting is important or not.

In my case I'm not completely convinced it is that important.

Hello Simon,

Thank you for the report.
Verified as described.

Thanks,
Umesh

The table_cache could suggest get a list of tables which are opened the most:
SELECT FILE_NAME, OPEN_COUNT FROM performance_schema.file_instances ORDER BY 
OPEN_COUNT DESC LIMIT 30;

And to get open latency:
update performance_schema.setup_consumers set enabled='YES' where name like 'events_waits_%';
SELECT OBJECT_NAME, SUM(TIMER_WAIT) SUM_OPEN_WAIT FROM performance_schema.events_waits_history_long WHERE OBJECT_TYPE='FILE' AND OPERATION='open' GROUP BY OBJECT_NAME ORDER BY SUM_OPEN_WAIT DESC;
For me this only showed binlog/frm/myisam/ps files (no InnoDB).