MySQL Forums :: Performance :: Relationship between MySQL variable open_files_limit and table_open_cache


Advanced Search

Re: Relationship between MySQL variable open_files_limit and table_open_cache
Posted by: Rick James ()
Date: September 10, 2016 12:13AM

open_files_limit comes from an OS setting. (Which OS are you using?) table_open_cache needs to be less than that.

Two things come to mind that could cause that error:

* Failure to `DEALLOCATE PREPARE` in a stored procedure.

* PARTITIONed table(s) with a large number of partitions and innodb_file_per_table = ON. Recommend not having more than 50 partitions in a given table (for various reasons). (When "Native Partitions" become available, this advice may change.)

The obvious workaround is to set increase the OS limit:
To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent).
Then increase `open_files_limit` and `table_open_cache`.

As of 5.6.8, open_files_limit is auto-sized based on `max_connections`, but it is OK to change it from the default.

Options: ReplyQuote


Subject Views Written By Posted
Relationship between MySQL variable open_files_limit and table_open_cache 447 salman khan 09/09/2016 04:34AM
Re: Relationship between MySQL variable open_files_limit and table_open_cache 247 Rick James 09/10/2016 12:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.