MySQL error: twomanyconnections/twomanyopenfiles Solution

Mysql reports [ERROR] Errorinaccept: Twomanyopenfiles. This friend also said that he hasn't opened so many files at the same time. Why?

Mysql reports the following ERROR: [Error] ERROR in accept: Two enabled open files. This friend also said that he hasn't opened so many files at the same time. Why?

Recently, I saw several friends posting for help in the Forum. The reason is that mysql reports an ERROR: [Error] ERROR in accept: Two too open files. This friend also said that he hasn't opened so many files at the same time. Why?

First, we need to understand that in linux, opening a file is in the form of a file descriptor (FD, file descripter). Each time a file is opened ,, then the OS assigns you a file descriptor. If multiple processes open the same file, you can allocate multiple file descriptors. Now let's go back to this question. This friend said that he hasn't opened so many files at a time. How can this problem happen? As mentioned above, each process may allocate an independent FD when opening the same file, and mysql does this. The descriptor of the data file opened by each session is independent, all sessions share the index file,
I will first post the original instructions in the manual:
MySQL is multi-threaded, so there may be using clients issuing queries for a given table simultaneously. to minimize the problem with multiple client sessions having different states on the same table, the table is opened independently by each concurrent session. this uses additional memory but normally increases performance. withMyISAMtables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared
Between all sessions .)
Well, now you should understand why there are not so many file descriptors while opening these files at the same time. Solve the problem now. The first step is to increase table_open_cache. For detailed usage instructions, refer to the Manual. Then, the fundamental solution is to increase the maximum number of open files of the mysqld thread on the OS, in linux, You can edit the file/etc/security/limits. conf and the information in it is as follows:
#/Etc/security/limits. conf
#
# Each line describes a limit for a user in the form:
#
#
#
# Where:
# Can be:
#-An user name
#-A group name, with @ group syntax
#-The wildcard *, for default entry
#-The wildcard %, can be also used with % group syntax,
# For maxlogin limit
#
# Can have the two values:
#-"Soft" for enforcing the soft limits
#-"Hard" for enforcing hard limits
#
# Can be one of the following:
#-Core-limits the core file size (KB)
#-Data-max data size (KB)
#-Fsize-maximum filesize (KB)
#-Memlock-max locked-in-memory address space (KB)
#-Nofile-max number of open files
#-Rss-max resident set size (KB)
#-Stack-max stack size (KB)
#-Cpu-max CPU time (MIN)
#-Nproc-max number of processes
#-As-address space limit
#-Maxlogins-max number of logins for this user
#-Maxsyslogins-max number of logins on the system
#-Priority-the priority to run user process
#-Locks-max number of file locks the user can hold
#-Sigpending-max number of pending signals
#-Msgqueue-max memory used by POSIX message queues (bytes)
#-Nice-max nice priority allowed to raise
#-Rtprio-max realtime priority
#
#
#

In this example, you can configure many resources required by the process. For example, you need to set the onfile option to open file. There are two Configuration Methods: first, the user group, second, the specific user form

For example, a mysql user

Mysql soft nofile 131072

The above limits the maximum number of files opened by mysql users. You can also run the ulimit-a command to view the information.

At the same time, it should be noted that the number of user-level open FD configured above can be viewed through cat/proc/sys/fs/file-max at the system level, there are two ways to edit:

1. echo n>/proc/sys/fs/file-max

2. vi/etc/sysctl. conf, add a line of fs. file-max = n, and then use sysctl-p to make it take effect.

However, sometimes we encounter two too open files but two too connections, which is the number of concurrent connections, which can be solved by modifying the max_connections parameter of mysql, however, it should be noted that when this parameter is increased, it may also cause two program open files. The reason has been explained above. I have never encountered this problem, but I just simulated it. If there is an error, please correct me.