MySQL: Reducing amount of open files

Question

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

240GB SSD

Debian 8 64-bit, latest updates

More than enough ram.

my.cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

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

[mysqld]
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
skip-external-locking
explicit_defaults_for_timestamp

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

# 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
[isamchk]
key_buffer = 16M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

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

# * 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.

Thanks!

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.