Sometimes, we will encounter error messages similar to the following:

..... [ ERROR ] / usr / local / MySQL / bin / mysqld : Can 'T Open File:' ./ yejr / Access . FRM '(errno: 24)  [ERROR] /usr/local/mysql/bin/mysqld: Can' t open file : './yejr/accesslog.frm' ( errno : 24 ) ...... [ ERROR ] Error in accept : Too many open files ....

The prompt message is obvious, the number of open files has reached the upper limit, and the upper limit needs to be increased, or some opened table file descriptors need to be released .

In MySQL, there are several file descriptor restrictions:

1 , in the Server layer , the whole mysqld instance to open the total number of files exceeds a user-process-level file limit, you need to check the kernel FS . File - max limit, a process level limit ulimit - the n- and MySQL in Open - Files - limit the options, if there is One of them exceeded the limit. If any one of the conditions exceeds the limit, an error will be thrown.      2 , although the Server Layer file does not exceed the total, but InnoDB layer is also limited , all InnoDB relevant documents can not exceed the total number of open InnoDB - Open - Files option limits. Otherwise, the earliest opened InnoDB file descriptor will be closed before the new file can be opened, but no error will be thrown, only warning information.

Correspondingly, if you are prompted to exceed the limit, you can use the following methods to increase the upper limit:

1. First, increase the overall limit of the kernel level. Implementation: sysctl - w fs . File - max = 3264018 ;2. Second, increase the kernel's limit on the number of open files at the user process level. Implementation: ulimit - n 204800 ;3. Finally, appropriately improve several parameters of the MySQL layer: open - files - limit , innodb - open - files , table - open - cache , table - definition - cache .

There are a lot of detailed explanations about the first two restrictions on the Internet, so I won't say much, but I will focus on the 4 MySQL-related options.

1.Open-files-limit
limits the maximum number of open files that the mysqld process can hold, which is equivalent to the total power switch of a community.Once the limit is exceeded, all residents in the community will have to power off.
Before 5.6.7 (inclusive), the default value is 0, and the maximum is related to the OS kernel limit; after
5.6.8 (inclusive), the default value will be automatically calculated, and the maximum is related to the OS kernel limit.

In 5.6.8 and later, the several limitation rules of its automatic calculation are as follows, whichever calculation result is the largest is the upper limit:

1 ) 10 + max_connections + ( table_open_cache * 2 ) 2 ) max_connections * 5 3 ) open_files_limit value specified at startup , 5000 if none

2.innodb-open-files

Limiting the maximum number of open tablespace files in the InnoDB engine is equivalent to a certain circuit insurance in the electric box of your own home.If the circuit is short-circuited, it will automatically trip without affecting other circuits.Remove the short-circuit source and press it again.use.

The minimum value is 20, and the default is 400.Only three types of files including ibdata*, ib_logfile* and *.ibd are calculated.Redo log is not included.After 5.6, the log can be undone independently.I haven't tested it yet, and it shouldn't.It is counted, and interested friends can verify it.

3.table-definition-cache

This cache is used to cache.frm files, and this option also indicates the maximum number of.frm files that can be opened at the same time.
The default value before 5.6.7 is 400;
after 5.6.7, it is automatically calculated, and the minimum is 400, the automatic calculation formula: 400 + (table-open-cache / 2).

For InnoDB, this option is only a soft limit.If the limit is exceeded, the old entries will be deleted and new entries will be added according to the LRU principle.

In addition, innodb-open-files also controls the maximum number of tables that can be opened, and table-definition-cache both play a limiting role, whichever is larger. If no limit is configured, table-definition-cache is usually selected as the upper limit, because its default value is 200, which is relatively large.

4.table-open-cache

The cache is used to cache file descriptors of all data tables.
Before 5.6.7, the default value is 400, the range: 1 – 524288;
5.6.8 – 5.6.11, the default value is 2000, the range: 1 – 524288; After
5.6.12, the default value is 2000 (and can be automatically calculated), the range: 1-524288.

Supplementary Note 1: Suggestions on how to calculate table file descriptors:

Table-open-cache is usually related to max-connections.It is recommended to set it to max_connections * N.The value of N is the average number of tables that may be used in each query.At the same time, it must also take into account the possibility of temporary tables.

Supplementary Note 2: MySQL will delete the table from the table cache in the following situations:

. 1 , Table Cache is full, and was about to open a new table; 2 , Table Cache number of entries exceeds table_open_cache set value, and some of the tables have not been accessed for a long time;3. When performing table refresh operations, such as executing FLUSH TABLES , or mysqladmin flush - tables or mysqladmin refresh

Supplementary Note 3: MySQL uses the following method to allocate table cache:

1. The currently unused tables will be released, starting from the least recently used table;2. When a new table is to be opened and the current cache is full and no table can be released, the table cache will be temporarily enlarged. After the tables in the temporarily enlarged table cache are not used, they will be released immediately.