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.
