Matt Gray

Solving the Mystery of MySQL’s Open Files

Dec 23, 2008

On Friday, Clockwork’s sysadm and I were poking at MySQL server configurations. We ran out of available file handles after increasing table_cache, a symptom that the MySQL manual mentions explicitly.

Examining the system environment, ulimit -n reports 1024, yet we saw MySQL climb to its open_files_limit system variable: 2500. Hmmm.

I wrote a quick proof-of-concept C program (shown below) and experimented with the setrlimit call. I wanted to see how a program can raise its own process limits, since that must be what MySQL is doing. (There is a ulimit -n call in safe_mysqld that is not running in our setup; it must be systems that don’t support setrlimit?)


The following code will fail to increase its NOFILE limit when run as non-root, and increase the limit as root.

C p-of-c code

#include #include #include #include

int main ( int argc, char **argv ) {

int             i;
FILE          * fps\[4096\];
struct rlimit   nofile_lim;

nofile\_lim.rlim\_cur  =  (rlim_t) 2048;
nofile\_lim.rlim\_max  =  (rlim_t) 3072;

if ( setrlimit( RLIMIT\_NOFILE, &nofile\_lim ) == -1 ) {
    perror( "Error setting limit\\n" );
    return -1;

for ( i = 0; i < 4096; ++i ) {

    char filename\[255\];

    sprintf( filename, "tmp-%d", i );

    fps\[i\]  =  fopen( filename, "w" );

    if ( ! fps\[i\] ) {
        return -1;

    if ( i % 100 ) {
        printf( "File #%d\\n", i );

return 0;


← Previous Post Next Post →