1

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!

3

1 Answer 1

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.

2
  • 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. 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
    Jan 2, 2016 at 17:03

Your Answer

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

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