Too many open files

I am using latest Percona/MySQL 8.0.15. The server was up for about 25 days. Today, it became inaccessible by IP and socket. In PHP I got “resource temporarily unavailable” and in mysql log:

2019-04-07T11:16:46.496996Z 0 [System] [MY-011323] [Server] X Plugin ready for connections...
2019-05-01T04:59:01.212412Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:19:37.455224Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:23:53.497133Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:28:09.539736Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:32:25.579582Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:36:41.621105Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:40:57.664604Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:45:13.704270Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:49:29.743013Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:53:45.783448Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T04:58:02.425095Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T11:41:25.660976Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T11:45:41.694016Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-05-02T11:49:57.728520Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files

I restarted the process and it came back up.

Any idea why this happened and how to prevent it?

1 Like

Hi acerola,

Can you share what distribution and version you are on?

1 Like

Centos 7. All up to date.

1 Like

Please check open file limit on the database server using ulimit -a and make sure it has sufficient open-file limit for a user with respect to count of the database files.

1 Like

Today it happened again.

2019-06-07T08:06:51.730482Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/mysql_socket/mysqlx.sock' bind-address: '::' port: 
2019-07-10T14:30:08.645268Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-07-10T14:34:24.682315Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-07-10T14:38:40.711770Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-07-10T14:42:56.741497Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-07-10T14:47:12.771797Z 0 [ERROR] [MY-010283] [Server] Error in accept: Too many open files
2019-07-10T14:50:56.141127Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15-6) Percona Server (GPL), Release 6, Revision 63abd08.
# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 513402
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 513402
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
1 Like

I am monitoring the open files with lsof. I noticed that mysql is opening more than one handle to the slow query log file. A couple hours ago there were only 5. Now 17. It looks like a resource leak.

# lsof -a -p `pidof mysqld`

mysqld 15929 mysql 111r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 112u REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 130r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 131r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 140r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 142r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 143r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 145r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 146r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 150r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 151r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 152r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 155r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 157r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 158r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 159r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
mysqld 15929 mysql 160r REG 259,3 1883109219 14286853 /<path>/slow_log.CSV
1 Like

hi, we also have this
and it is huge problem

when is release of 8.0.16 planned? it has fix i think
thanx

1 Like

Hello, thanks for the updates.

You are correct that it is a known issue, and I am sorry for the challenges that this is bringing to you. There is a fix pending release in Percona Server for MySQL, and the bug also affects upstream software (i.e. MySQL),

The best thing you can do to get earliest advice of the availability of the fix is to subscribe to this bug ticket as that will notify you when the release is made available [URL][PS-5582] The table '/tmp/#...' is full! - Percona JIRA .

The release is in the foreseeable future, but we do not provide advance dates for software releases, so I am unable to provide you with a specific date. I will try to update this post, but the most reliable and quickest way to receive notification is to (a) subscribe to the JIRA report and (b) subscribe to the blog where we publish release notes on the day the release is made available.

Thanks for your patience.

Such problem should be fixed using following option,

Increase limit as per your need

example:
mysql my.cnf
[mysqld]
open_files_limit=60000
OS:
ulimit -n 60000

1 Like

Wow. Didn’t expect to get a reply after over 2 years.
As explained above, this was a bug in that version of MySQL/Percona. After updating it, the problem was resolved.
Thanks all for the help.

2 Likes

Thank you for the update, Good to have a possible solution as it might help others.

1 Like