So, I currently have a problem with my mySQL server opening far too many file handles.

This requires me to restart to server each day, as otherwise, I get the infamous "errno 24, too many open files" error.

I have already tried raising the system maximum count to 500k, but I still get that error.

At the time mySQL stops working, it uses about 384k file handles, far too many in my books.

System information:

VPS running on a Intel Xeon E5-2670 v3

12GB of DDR4 RAM


Debian 8 64-bit, latest updates

More than enough ram.


port        = 3306
socket      = /var/run/mysqld/mysqld.sock

pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    =

# Fine tuning
max_allowed_packet = 16M
thread_stack       = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP
max_connections = 384
key_buffer_size = 284M
tmp_table_size = 32M
max_heap_table_size = 32M

# Query cache
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 16M

# Join buffer
join_buffer_size = 600K
table_open_cache = 440

# InnoDB
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 32M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
log-error   = /var/log/mysql/error.log

# Other stuff
key_buffer = 16M

max_allowed_packet = 16M

# Recommended in standard MySQL setup

# Disabling symbolic-links is recommended to prevent assorted security risks

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
!includedir /etc/mysql/conf.d/

I am looking for a way to reduce the amount of file handles used, not raise it.


  • How many tables are there?
    – Philᵀᴹ
    Commented Jan 1, 2016 at 20:04
  • ~280, of the dbs which are actively used Commented Jan 1, 2016 at 21:15
  • check if you can relate to this - fromdual.com/… Commented Jan 1, 2016 at 22:31

1 Answer 1

SHOW VARIABLES LIKE 'open_files_limit';

That will reflect the OS's ulimit -n. In some OSs, that limit is set too low. Change it.

Then set table_open_cache to be about one-third of it.

  • The value of that variable here is 1024, table_open_cache 400 (a bit more than a third). Will try setting it to 340. ulimit -n is set to 65536. Commented Jan 2, 2016 at 12:35
  • Sounds like a plan. 340 sounds reasonable. Since it is a "cache", that does not have to be bigger than 280. And/or set open_files_limit higher than 1024 in my.cnf.
    – Rick James
    Commented Jan 2, 2016 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.