mysqldump | mysql yields 'too many open files' error. Why?

Question

I have a RHEL 5 system with a fresh new hard drive I just dedicated to the MySQL server. To get things started, I used "mysqldump --host otherhost -A | mysql", even though I noticed the manpage never explicitly recommends trying this (mysqldump into a file is a no-go. We're talking 500G of database).

This process fails at random intervals, complaining that too many files are open (at which point mysqld gets the relevant signal, and dies and respawns).

I tried upping it at sysctl and ulimit, but the problem persists. What do I do about it?

Answer 1

mysqldump by default performs a per-table lock of all involved tables. If you have many tables that can exceed the amount of file descriptors of the mysql server process. Try --skip-lock-tables or if locking is imperative --lock-all-tables.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

--lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

Answer 2

mysqldump has been reported to yeld that error for larger databases (1, 2, 3). Explanation and workaround from MySQL Bugs:

[3 Feb 2007 22:00] Sergei Golubchik This is not really a bug.

mysqldump by default has --lock-tables enabled, which means it tries to lock all tables to be dumped before starting the dump. And doing LOCK TABLES t1, t2, ... for really big number of tables will inevitably exhaust all available file descriptors, as LOCK needs all tables to be opened.

Workarounds: --skip-lock-tables will disable such a locking completely. Alternatively, --lock-all-tables will make mysqldump to use FLUSH TABLES WITH READ LOCK which locks all tables in all databases (without opening them). In this case mysqldump will automatically disable --lock-tables because it makes no sense when --lock-all-tables is used.

Edit: Please check Dave's workaround for InnoDB in the comment below.