Mysql too many open files at os level?

Hello I have mysql server (V14.14 Distrib 5.6.47) installed on freebsd (12.1-RELEASE-p2)

According to these 2 commands I only have 2 connections to the db
Code:
show status where `variable_name` = 'Threads_connected';
show processlist;

but yet I get
Code:
Can't create TCP/IP socket (24) (MySQL error code: 2004, SQLState: HY000)
about mid day when the 'select' load goes up.

Googling this seems to point to 'too many open files' at the OS level. How do I check or fix this on freebsd?

Thanks in advance for any help you can give
 
Googling this seems to point to 'too many open files' at the OS level. How do I check or fix this on freebsd?
Code:
# sysctl -d kern.openfiles
kern.openfiles: System-wide number of open files
# sysctl -d kern.maxfiles
kern.maxfiles: Maximum number of files
 
Code:
% sysctl -a | grep files
kern.maxfiles: 517593
...
kern.openfiles: 1158
...
If you search for kern.maxfiles and kern.openfiles you'll find more.

Not sure if it is your real problem, though.
 
You are connected to the mySQL server and execute a SELECT command when the error it's displayed

Why it's the mySQL trying to create a TCP/IP socket?

What user it's running the mySQL daemon?

Check this manuals fstat(1) and sockstat(1)

I am using a library called mysqlcpp that connects the c++ app to the mysql service. Researching this error seems to be related to a limitations of open files at the OS level.
 
Code:
% sysctl -a | grep files
kern.maxfiles: 517593
...
kern.openfiles: 1158
...
If you search for kern.maxfiles and kern.openfiles you'll find more.

Not sure if it is your real problem, though.
Thank you for the reponse. This doesnt happen until the SELECT load goes up on the db which happens towards the afternoon

Code:
sysctl -a | grep files
kern.maxfiles: 2092299
kern.maxfilesperproc: 1883061
kern.openfiles: 102
p1003_1b.mapped_files: 200112

right now the work load is low, I will run this command when I start getting the error.
 
You should see errors in the MySQL error log if you do happen to hit the max. But with a max of over 2 million I very much doubt that. You would have to open a LOT of tables to reach that.
 
The MySQL server and the PC running the code are 2 different machines?

With fstat | wc -l you can view the amount of open files at that moment
 
Then the message is displayed in the PC running your code, run the command when the problem is present in the PC running the code

DOHHH you are exactly right. I snooped around the logs on the application server and noticed that is where the error is happening. Thank you, this got me pointed in the right direction.

Thanks to everyone that helped me!
 
Back
Top