MySQL Bugs: #71876: table cache advisor should give hints about limits

Bug #71876	table cache advisor should give hints about limits
Submitted:	28 Feb 2014 8:29	Modified:	11 Mar 2015 9:39
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S3 (Non-critical)
Version:	3.0.6, 3.0.15	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	table_open_cache

Description:
The "Table Cache Not Optimal" advisor gives hints when the table_open_cache is too small and tells you to increase it if it's too small.

The advise is:
In MySQL server versions prior to 5.1.3: SET GLOBAL table_cache = (400 + 16);
In MySQL server versions 5.1.3 and later: SET GLOBAL table_open_cache = (400 + 16); 

There are few issues with this advice:
- MEM should known if I'm running 5.1.3 or newer...
- This setting must be made permanent in /etc/my.cnf
- On a default redhat installation /etc/limits.d/90-nproc.conf and/or /etc/limitsconf limits the number of open files and processes to 1024

Related:
- http://www.mysqlperformanceblog.com/2013/02/04/cant_create_thread_errno_11/
- Bug #70999

How to repeat:
Try to follow the advice (and set it in /etc/my.cnf) will on RHEL6 result in:

2014-02-28 08:50:10 31749 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 6954)

2014-02-28 08:50:10 31749 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)

2014-02-28 08:50:10 31749 [Warning] Buffered warning: Changed limits: table_cache: 400 (requested 3072)

Suggested fix:
Add advice to add /etc/security/limits.d/91-mysqld.conf
mysql         soft    nproc   4096
mysql         hard    nproc   4096
mysql         soft    nofile  8192
mysql         hard    nofile  8192

The user should depend on the user/group set in my.cnf

Hello Daniel,

Thank you for the bug report.

Thanks,
Umesh

This query can be used to calculate the maximum table_open_cache size as modeled after adjust_table_cache_size() from mysqld.cc

https://github.com/mysql/mysql-server/blob/5.6/sql/mysqld.cc#L6926

select round((@@open_files_limit - 10 - @@max_connections) / 2) as max_table_open_cache;

Updated versions