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

[6 Feb 2007 13:28] Kristian Koehntopp
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)
[6 Feb 2007 14:33] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of

ulimit -a

from the session of the same user that runs mysqld.
[6 Feb 2007 14:41] Kristian Koehntopp
[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
[6 Feb 2007 15:09] Sergei Golubchik
This is apparently a duplicate of - ealier submitter - bug#26026
[6 Feb 2007 16:08] Valeriy Kravchuk
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.
[7 Mar 2007 0:00] Bugs System
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.078 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.