Two MySQL error messages
Earlier this week I ran into a problem with a website that I tracked down to a MySQL error message that looked like this:
can't create/write to file /var/lib/mysql/foo.bar.baz Errcode: 23
(The actual file referenced in that error message doesn't matter, as it will be different on most systems, but the "can't create/write to file" is important.) After digging through my log files even more, I also found this error message related to MySQL:
Got error 12 from select Error in accept: Too many open files in system
Both of these error messages ("can't create/write to file", "too many open files in system") are related to the same problem that I was able to solve: MySQL is trying to open more files on my Linux system than it was configured to allow.
Other related error messages
Before I move on to the solution, it's also worth noting that you may see different MySQL error messages based on this same root cause. In fact, the MySQL documentation shows that you can run into several different error messages that all have this same root cause.
Here are several of the error messages they show, which they display with the perror utility:
shell> perror 23 OS error code 23: File table overflow shell> perror 24 OS error code 24: Too many open files shell> perror 11 OS error code 11: Resource temporarily unavailable
Solving the root cause of the problem
Reading through the MySQL documentation, there appear to be a number of ways to solve this problem, but they all relate to the limit on the number of files a user can have open at one time.
In my case I solved this problem by changing the MySQL variable open-files-limit to a value that is much higher than its default value. I did this by editing the MySQL /etc/my.cnf configuration file on my CentOS Linux system, and setting the value like this:
[mysqld] open-files-limit=2048
One word of caution here: So far I don't know how to determine the number of open files my application and MySQL actually need, so I'm starting with 2,048, and I'll increase that number as needed.
MySQL documentation
The MySQL website has several documents that describe these error messages, and the potential steps to solve these problems. Here are the two most helpful links I found on their site:
(As I final note, let me apologize here that this article is a little rushed. I'll come back here and clean it up when I have a little more time.)
books by alvin
 |
 |
 |
 |