MySQL Bugs: #26114: mysqldump failes with "too many open files" on busy server

Bug #26114	mysqldump failes with "too many open files" on busy server
Submitted:	6 Feb 2007 13:28	Modified:	12 Apr 2007 16:25
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0.26	OS:	Linux (Centos 4.3)
Assigned to:		CPU Architecture:	Any
Tags:	bfsm_2007_02_15

Description:
On a server with a high load (300-400 qps, complicated multi-table joins), a mysqldump -d bp fails with an error 24: too many open files.

How to repeat:
Set up a server with the schema definition from support case #13722. Produce a sufficiently high load. Run "mysqldump -d bp".

Before dump:
mysql> show global status like "com_select";
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Com_select | 213735295 |
+---------------+-----------+
1 row in set (0.02 sec)

mysql> show global status like "qcache_hits";
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Qcache_hits | 114500944 |
+---------------+-----------+
1 row in set (0.01 sec)

mysql> show global status like "uptime";
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 976658 |
+---------------+--------+
1 row in set (0.01 sec)

That is an average qps of

mysql> select (213735295+114500944)/976658;
+------------------------------+
| (213735295+114500944)/976658 |
+------------------------------+
| 336.0810 |
+------------------------------+
1 row in set (0.00 sec)

Before the dump, there are plenty of slots free in the table cache:

mysql> show global status like "op%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 870 |
| Open_streams | 0 |
| Open_tables | 531 |
| Opened_tables | 9317 |
+---------------+-------+
4 rows in set (0.02 sec)

mysql> show global variables like "tab%";
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| table_cache | 1500 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
+-------------------------+--------+
3 rows in set (0.00 sec)

Running the mysqldump command fails:

[root@bc05rodb-01 ~]# mysqldump -d bp > bp-schema.sql
mysqldump: Couldn't execute 'show fields from `B_Rate_Room_Inventory`': File './bp/B_Rate_Room_Inventory_200703.MYD' not found (Errcode: 24) (29)
[root@bc05rodb-01 ~]# perror 24
OS error code 24: Too many open files

After the dump command, the table cache looks like this:

mysql> show global variables like "tab%";
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| table_cache | 1500 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
+-------------------------+--------+
3 rows in set (0.00 sec)

mysql> show global status like "op%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files | 3167 |
| Open_streams | 0 |
| Open_tables | 1479 |
| Opened_tables | 10538 |
+---------------+-------+
4 rows in set (0.02 sec)

Thank you for a problem report. Please, send the results of

ulimit -a

from the session of the same user that runs mysqld.

[root@bc08rodb-01 ~]# su - mysql
-bash-3.00$ ulimit -a -S
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 65535
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
-bash-3.00$ ulimit -a -H
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 32000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) unlimited
cpu time               (seconds, -t) unlimited
max user processes              (-u) 65535
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

This is apparently a duplicate of - ealier submitter - bug#26026

OK, so you may have up to 1024 open files. How many tables do you have in that database? If you have, say, only 100, and 3+ file handles are used per each table while mysqldump works, then I agree - we have a problem here. 

Anyway, this problem can be easily solved (on Linux), as it was already pointed out.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".