Can't open file: (errno: 24)

After a new database is created and the application is connected, the following exception is thrown:
120528 10:07:32 [ERROR]/usr/local/mysql/bin/mysqld: Can't open file: './ag4_1/raa4_1.frm' (errno: 24)

At the beginning, I did not carefully check the error code! I thought the file was corrupted and re-imported! Still throwing the above error!

Use perror to view specific errors!
Linux:/usr/local/mysql/bin #./perror 24
OS error code 24: Too enabled open files
The maximum number of opened files is exceeded! Ulimit-n: the maximum number of opened files in the system is 65535! The maximum number of opened files in the database exceeds the limit!

Run the show variables like 'open _ files_limit 'command to view the maximum number of opened files in mysql ';
If the value is too small, change it to 2048, restart mysql, and the application is normal!

 

When using MySQL, the following error occurs: (errcode: 24) "out of resources when opening file. /xxx. MYD "and (error: 24)" can't creat file. /xx. FRM ", found information, the reason is that the number of opened files exceeds the limit on the number of opened files.

In the past two days, I tried to modify the limit on the number of opened files. I learned through Baidu how to modify this value: modify my. the values of max_connections and table_open_cache In the INI file, and then restart the MySQL service. You can use the command line (show variables like '% open_files_limit %') to view details.

Relationship between max_connections and table_open_cache and open_files_limit:

 

Max_1 = 10 + max_connections + table_cache * 2;

Max_2 = max_connections * 5;

Max_3 = max_ OS _open_files; // a single process in the operating system allows a maximum of file handles (file descriptors) to be opened ).

 

Open_files_limit = max (max_1, max_2)> max_3? Max_3: max (max_1, max_2 );

 

The formula has been tested in mysql5.1. (However, in win7 mysql5.5, a base 2048 value must be added. Do you know if there are other configuration fields? I haven't figured it out yet)

 

How does one change user handle settings in windows?

Modify registryUSERProcessHandleQuota

(The default value is 2710 (hexadecimal)/10000 (decimal). The value range is 200 ~ 18000, adjust it to more values. Similarly, for systems with 2 GB or more physical memory, you may wish to set the number of user handles directly to a maximum of 18000 (10 hexadecimal);) not tested :)

 

MySQL engine: MyIASM. Each time a file is opened, two file handles must be opened.

 

Test Verification Method:

1) Modify max_connections = 8; table_open_cache = 1; restart the mysql service and obtain open_files_limit = 40.

2) Run select * from table_1, table_2, table_3, table_4, table_5, table_6, table_7, table_8, table_9, table_10, table_11, table_12, table_13, table_14, table_15, table_16, table_17, table_18, table_19, table_20, table_21; # Each table must be large enough to execute this statement for a period of time.

Open another command line: show status like '% Open_files % ';

Open_files = 42

Open_files (42) has exceeded open_files_limit (40), and no error: 24 is returned. Not clear :)

3) When you run an application (mainly preparing tables and inserting data), an error 24 is displayed.

4) re-Modify max_connections = 100; table_open_cache = 512; restart the mysql service and obtain open_files_limit = 1134.

5) Repeat Step 1. No error occurs.

 

The test shows that the limit on the number of opened files is increased by modifying these two parameters.

 

The error message is as follows :.....
070813 13:10:17 [ERROR]/usr/local/mysql/bin/mysqld: Can't open file: './yejr/access. frm' (errno: 24)
070813 13:10:17 [ERROR]/usr/local/mysql/bin/mysqld: Can't open file: './yejr/accesslog. frm' (errno: 24)
......
070813 13:10:17 [ERROR] Error in accept: Too open files
....
Note that the system error code is 24. Use perror to check the specific error message: [root @ yejr] #/usr/bin/perror 24
OS error code 24: Too enabled open files
It turns out that too many files are opened, which is easy to handle. Use sysctl to adjust it: [root @ yejr] # sysctl-w fs. file-max = 43621
[Root @ yejr] # sysctl-a | grep fs. file-max
Fs. file-max = 43621
Sysctl is also used in FreeBSD: [root @ yejr] # sysctl-w kern. maxfiles = 123280
[Root @ yejr] # sysctl-a | grep kern. maxfiles
Kern. maxfiles = 123280
Finally, the most important thing is to modify the mysqld configuration file my. cnf and add the following line: open_files_limit = 4096
# Adjust the value as needed. The default value is
# Max_connections * 5 or max_connections + table_cache * 2
Then, restart mysqld as root. here, even though my. the running user specified in cnf is not the root user. You can also start mysqld as the root user. Otherwise, the open_files_limit option cannot take effect because the kernel limits the maximum number of files opened by normal users.