Bug #43523 Open_files status variable does not report all opened file descriptors by the OS
Submitted: 10 Mar 2009 2:01 Modified: 10 Mar 2009 2:30
Reporter: Leandro Morgado Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.0.51, 5.1.32 OS:Linux (Possibly more)
Assigned to: CPU Architecture:Any

[10 Mar 2009 2:01] Leandro Morgado
Description:
The Open_files status variable doesn't count the files descriptors opened for InnoDB, as well as pipes and sockets. New client connections also don't contribute to Open_files.

Open_files can therefore be a misleading guide when calculating how to set Operating System limits (eg: ulimit -n).

How to repeat:
MySQL thinks it has 51 open file descriptor:
======================
mysql> show global status like "Open_files%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 51    |
+---------------+-------+
1 row in set (0.00 sec)
======================

However, the kernel tells us we have more:
======================
root@shell> ls -1 /proc/$(pgrep mysqld$)/fd   | wc -l
66
======================

If we filter out deleted FDs, pipes, sockets and InnoDB files we reach the 51 value that MySQL reports:
======================
root@shell> ll /proc/$(pgrep mysqld$)/fd |grep -v total | grep -v socket |  grep -v pipe | grep -v ibdata* | grep -v ibd$ | grep -v deleted | grep -v ib_logfile |grep -v "/dev/" |wc -l
======================

For reference, here is the full list of FDs in /proc:
======================
/proc/$(pgrep mysqld$)/fd
total 0
lr-x------ 1 root root 64 2009-03-10 01:45 0 -> /dev/null
l-wx------ 1 root root 64 2009-03-10 01:45 1 -> pipe:[432098]
lrwx------ 1 root root 64 2009-03-10 01:45 10 -> /tmp/ibh4enYp (deleted)
lrwx------ 1 root root 64 2009-03-10 01:45 11 -> /var/lib/mysql/ib_logfile0
lrwx------ 1 root root 64 2009-03-10 01:45 12 -> /var/lib/mysql/ib_logfile1
l-wx------ 1 root root 64 2009-03-10 01:45 13 -> /var/lib/mysql/mysqld-bin.000206
lrwx------ 1 root root 64 2009-03-10 01:45 14 -> /tmp/ibQDfIW1 (deleted)
lrwx------ 1 root root 64 2009-03-10 01:45 15 -> socket:[432114]
lrwx------ 1 root root 64 2009-03-10 01:45 16 -> socket:[432115]
lrwx------ 1 root root 64 2009-03-10 01:45 17 -> /var/lib/mysql/mysql/host.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 18 -> /var/lib/mysql/mysql/host.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 19 -> /var/lib/mysql/mysql/user.MYI
l-wx------ 1 root root 64 2009-03-10 01:44 2 -> pipe:[432098]
lrwx------ 1 root root 64 2009-03-10 01:45 20 -> /var/lib/mysql/mysql/user.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 21 -> /var/lib/mysql/mysql/db.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 22 -> /var/lib/mysql/mysql/db.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 23 -> /var/lib/mysql/mysql/tables_priv.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 24 -> /var/lib/mysql/mysql/tables_priv.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 25 -> /var/lib/mysql/mysql/columns_priv.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 26 -> /var/lib/mysql/mysql/columns_priv.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 27 -> /var/lib/mysql/mysql/procs_priv.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 28 -> /var/lib/mysql/mysql/procs_priv.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 29 -> /var/lib/mysql/master.info
l-wx------ 1 root root 64 2009-03-10 01:45 3 -> /var/log/mysql/mysql.log
lrwx------ 1 root root 64 2009-03-10 01:45 30 -> /var/lib/mysql/mysqld-relay-bin.index
lrwx------ 1 root root 64 2009-03-10 01:45 31 -> /var/lib/mysql/mysqld-relay-bin.000035
lrwx------ 1 root root 64 2009-03-10 01:45 32 -> /var/lib/mysql/relay-log.info
lr-x------ 1 root root 64 2009-03-10 01:45 33 -> /var/lib/mysql/mysqld-relay-bin.000001
lrwx------ 1 root root 64 2009-03-10 01:45 34 -> socket:[432119]
lrwx------ 1 root root 64 2009-03-10 01:45 36 -> /var/lib/mysql/community/content_data_source.ibd
lrwx------ 1 root root 64 2009-03-10 01:45 37 -> /var/lib/mysql/mysql/cliptest.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 38 -> /var/lib/mysql/mysql/cliptest.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 39 -> /var/lib/mysql/mysql/func.MYI
l-wx------ 1 root root 64 2009-03-10 01:45 4 -> /var/log/mysql/mysql-slow.log
lrwx------ 1 root root 64 2009-03-10 01:45 40 -> /var/lib/mysql/mysql/func.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 41 -> /var/lib/mysql/mysql/help_category.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 42 -> /var/lib/mysql/mysql/help_category.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 43 -> /var/lib/mysql/mysql/help_keyword.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 44 -> /var/lib/mysql/mysql/help_keyword.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 45 -> /var/lib/mysql/mysql/help_relation.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 46 -> /var/lib/mysql/mysql/help_relation.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 47 -> /var/lib/mysql/mysql/help_topic.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 48 -> /var/lib/mysql/mysql/help_topic.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 49 -> /var/lib/mysql/mysql/inventory.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 5 -> /var/lib/mysql/mysqld-bin.index
lrwx------ 1 root root 64 2009-03-10 01:45 50 -> /var/lib/mysql/mysql/inventory.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 51 -> /var/lib/mysql/mysql/proc.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 52 -> /var/lib/mysql/mysql/proc.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 53 -> /var/lib/mysql/mysql/t.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 54 -> /var/lib/mysql/mysql/t.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 55 -> /var/lib/mysql/mysql/time_zone.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 56 -> /var/lib/mysql/mysql/time_zone.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 57 -> /var/lib/mysql/mysql/time_zone_leap_second.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 58 -> /var/lib/mysql/mysql/time_zone_leap_second.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 59 -> /var/lib/mysql/mysql/time_zone_name.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 6 -> /var/lib/mysql/ibdata1
lrwx------ 1 root root 64 2009-03-10 01:45 60 -> /var/lib/mysql/mysql/time_zone_name.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 61 -> /var/lib/mysql/mysql/time_zone_transition.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 62 -> /var/lib/mysql/mysql/time_zone_transition.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 63 -> /var/lib/mysql/mysql/time_zone_transition_type.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 64 -> /var/lib/mysql/mysql/time_zone_transition_type.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 65 -> /var/lib/mysql/mysql/user_info.MYI
lrwx------ 1 root root 64 2009-03-10 01:45 66 -> /var/lib/mysql/mysql/user_info.MYD
lrwx------ 1 root root 64 2009-03-10 01:45 7 -> /tmp/ib8hud8z (deleted)
lrwx------ 1 root root 64 2009-03-10 01:45 8 -> /tmp/ibZSeP4b (deleted)
lrwx------ 1 root root 64 2009-03-10 01:45 9 -> /tmp/ibKP8q1N (deleted)
======================

Suggested fix:
Most likely, there are some OS specifics here (Windows ??) but I think Open_files should at least report the count of the InnoDB related files. Ideally, it would report the same value as the kernel, so that you can adjust the kernel's maximum open FDs parameter (ulimit -n) based on a realistic Open_files value. 

Alternatively, create another variable (eg: Open_files_OS) that contains the amount of open FDs as the OS sees it.
[30 Mar 2009 17:01] Paul DuBois
Revised Open_files description:

          The number of files that are open. This count includes regular
          files opened by the server. It does not include other types of
          files such as sockets or pipes. Also, the count does not
          include files that storage engines open using their own
          internal functions rather than asking the server level to do
          so.