MySQL Bugs: #65129: mysqlcheck doesn't close file connections

Bug #65129	mysqlcheck doesn't close file connections
Submitted:	27 Apr 2012 8:59	Modified:	2 May 2012 14:30
Reporter:	Wolfgang Riedel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	2.5.0 (Distrib 5.1.61)	OS:	Linux (RHEL 6)
Assigned to:		CPU Architecture:	Any
Tags:	file descriptor, lsof

Description:
I have a server with about 800 databases and with 20000 tables. In testing an upgrade of the mysql server from 5.0.95 to 5.1.61 (as part of upgrade Redhat Enterprise 5 to 6), at first a had to increase the variable "open_files_limit" from 10000 to 50000 - otherwise the command mysql-upgrade (or better mysqlcheck) would fail. After the complete mysqlcheck-run I see 35000 open file connections (command /usr/sbin/lsof|fgrep mysqld|wc -l). I can restart the mysql daemon now, ok - but this is only a workaround, no clean solution. 

How to repeat:
mysqlcheck -u root -p --all-databases

Suggested fix:
a close for each attached table has to be included

mysqlcheck just generates and executes CHECK TABLE for every table in every database when called the way you described. Server processes them the same way as if they were just executed one by one from one client connection. See http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html for the details. So, mysqlcheck itself does NOT open any files. Server open tables as requested and cache information about them.

I wonder what are the value of max_connections and table_open_cache server variables do you have? Please, check your my.cnf and/or SHOW GLOBAL VARIABLES output and send the values used.

> mysqlcheck itself does NOT open any files. Server
open tables as requested and cache information about them.
OK, I'm understanding

> I wonder what are the value of max_connections and table_open_cache
server variables do you have? Please, check your my.cnf and/or SHOW
GLOBAL VARIABLES output and send the values used.
In my.cnf I have:
  max_connections=500
  table_open_cache  is not defined
SHOW GLOBAL VARIABLES shows:
  | max_connections | 500   |
  | table_open_cache | 524288 |

It seems, it was a big misunderstandig by myself, sorry.
That the mysql server holds connections to any time opened files within the cache, this is no problem for standard environments. The kernel variable file-max has current values (in different systems) of 500000 to 800000. So I think, that the 35000 open connections to mysql tables are only "pie nuts" :-)

Thank you,
Wolfgang