MySQL Open files

The error message is as follows:

..... 150905 13:10:17 [ERROR]/usr/local/mysql/bin/mysqld:can ' t open file: './mytest/t1.frm ' (errno:24) 150905 13:10:17 [Error ]/usr/local/mysql/bin/mysqld:can ' t open file: './mytest/t2.frm ' (errno:24) ... 150905 13:10:17 [ERROR] Error in Accept:too many open files ....

Notice that the system error code is 24, use perror to see what the specific error message is:

[[Email protected]]#/usr/local/mysql/bin/perror 24OS error code  :  Too many open files

It turns out that there are too many open files. It's fine to adjust it with sysctl:

[Email protected]]# sysctl-w fs.file-max=43621[[email protected]]# sysctl-a | grep Fs.file-maxfs.file-max = 43621

Finally, the most important point is to modify the mysqld configuration file my.cnf, adding the following line:

Open_files_limit = 4096 #根据自己的情况适当调整, system default is # Max_connections*5 or max_connections + table_cache*2

Then, restart mysqld as root. In this case, although the running user specified in my.cnf is not root, you can start mysqld as root, otherwise the OPEN_FILES_LIMIT option cannot take effect because the kernel limits the maximum number of open files for a normal user.

The current open file exceeds limit problem:

In addition to Socket,binlog, relay log, Ibdata, logfile and so on these fixed need to open the handle outside,

The number of Open data files for the remaining various storage engines should only be related to the amount of instant IO concurrent requests, and the user's request, pressure, and how many tables do not matter.

So, there are too many problems with open file, which belongs to the code defect, which is currently focused on the MyISAM and TUKODB engines:

InnoDB engine strictly according to Innodb_open_files settings, using LRU to manage open file, do not encounter this problem.

Flush tables, in fact, does not close the meaning of the file, just mysiam the handler close interface will just try to close the Myd,myi file, this operation on the InnoDB table and no egg.

The partition table is too many, also belongs to the MyISAM table flaw, the partition how much also should have no relation with the open file quantity.

How do I see the number of files opened by the mysqld process?

Assuming PID = 26206

ls/proc/26206/fd | Wc-l

In MySQL, there are several places where file descriptor restrictions exist:

1. At the server level, the total number of open files for the entire mysqld instance exceeds the user process level file limit, check the kernel Fs.file-max limit, process level limit ulimit-n and the open-files-limit option in MySQL, whether there is a certain overrun. If any one of the conditions is overrun, an error will be thrown.

2, although the total number of server layer files are not super, but the INNODB layer is also limited, all InnoDB related file opening totals cannot exceed the innodb-open-files option limit. Otherwise, the earliest open InnoDB file descriptor is closed before the new file can be opened, but no error is thrown, only the alarm message. 

Accordingly, if the hint exceeds the limit, you can use the following method to increase the upper limit:

1. First, increase the total kernel level limit. Execute: Sysctl-w fs.file-max=3264018;2, second, increase the kernel to the user process level Open file number limit. Execution: Ulimit-n 204800;3, finally, appropriate to improve the MySQL layer of several parameters: Open-files-limit, Innodb-open-files, Table-open-cache, Table-definition-cache.

1, Open-files-limit
It limits the maximum number of open files that the mysqld process can hold , which is equivalent to the total switch in a cell, which, once overrun, has a power outage for all tenants in the community.
5.6.7 (including) previously, the default value of 0, the maximum and OS kernel limitations;
After 5.6.8 (included), the default value is automatically calculated, with the maximum and OS kernel limitations.

In the 5.6.8 and later, its automatic calculation of a few restrictions on the rules, which is the maximum calculation of which is the upper limit:

1) + Max_connections + (Table_open_cache * 2) 2) max_connections * +) Open_files_limit value specified at startup, 5000 If none

2, Innodb-open-files

limit the number of table space files in the InnoDB engine , the equivalent of one of the circuit insurance in your home box, the circuit will automatically trip without affecting other circuits, remove the short-circuit source and then press it again to use.

It has a value of 20, the default of 400, only the calculation contains ibdata*, ib_logfile*, *.IBD and other three types of files, redo log is not counted, 5.6 can be independent of the undo log, I have not tested, should also not be counted, interested friends can be verified.

3, Table-definition-cache

The cache is used for caching. frm files, which also indicates that the. frm file can open the maximum number at the same time.
5.6.7 previous default value 400;
The 5.6.7 is automatically calculated and the minimum is 400, and the formula is calculated automatically: + (TABLE-OPEN-CACHE/2).

For InnoDB, this option is only a soft limit, and if the limit is exceeded, the old entry is deleted and the new entry is added according to the LRU principle.

In addition, Innodb-open-files also controls the maximum number of open tables, and table-definition-cache all play a limiting role, whichever is greater. If no limit is configured, Table-definition-cache is usually selected as the upper limit, because its default value is 200, which is larger.

4, Table-open-cache

The cache is used for caching all kinds of data table file descriptors.
5.6.7 before, default value 400, Range: 1–524288;
5.6.8–5.6.11, default value 2000, range: 1–524288;
After 5.6.12, the default value is 2000 (and can be calculated automatically), Range: 1–524288.

Supplemental NOTE 1: Recommendations on how to calculate table file descriptors:

Table-open-cache is usually related to max-connections, it is recommended to set the value of Max_connections * N,n to the average number of tables that may be used in each query, as well as the possibility of creating temporary tables.

Supplemental Note 2:mysql will delete the table from the tables cache in the following cases:

1, table cache is full, and is about to open a new table, 2, table cache entries more than Table_open_cache set value, and some of the tables have not been accessed for a long time, 3, perform refresh table operations, such as the execution of flush TABLES, or mysqladmin flush-tables or mysqladmin refresh

Supplemental Note 3:mysql uses the following method to assign the table cache:

1. Tables that are not currently in use will be released, starting with the least recently used tables;

2. When a new table is opened and the current cache is full and no table can be freed, the table cache will be temporarily enlarged, and the tables in the temporarily enlarged list cache will be released immediately after they are not used.

MySQL Open files