Bug #43523 Open_files status variable does not report all opened file descriptors by the OS
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.