Mysql too many open files at os level?

EverydayDiesel

Member


Messages: 31

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
 

Martin Paredes

Active Member

Reaction score: 35
Messages: 144

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)
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,926
Messages: 33,312

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
 

richardtoohey2

Active Member

Reaction score: 36
Messages: 100

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.
 
OP
E

EverydayDiesel

Member


Messages: 31

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.
 
OP
E

EverydayDiesel

Member


Messages: 31

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.
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,926
Messages: 33,312

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.
 

Martin Paredes

Active Member

Reaction score: 35
Messages: 144

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
 
OP
E

EverydayDiesel

Member


Messages: 31

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!
 
Top