# Mysql too many open files at os level?



## EverydayDiesel (May 27, 2020)

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

```
show status where `variable_name` = 'Threads_connected';
show processlist;
```

but yet I get

```
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 (May 27, 2020)

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 (May 27, 2020)

EverydayDiesel said:


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




```
# sysctl -d kern.openfiles
kern.openfiles: System-wide number of open files
# sysctl -d kern.maxfiles
kern.maxfiles: Maximum number of files
```


----------



## richardtoohey2 (May 27, 2020)

```
% 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.


----------



## EverydayDiesel (May 27, 2020)

Martin Paredes said:


> 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?
> 
> ...



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.


----------



## EverydayDiesel (May 27, 2020)

richardtoohey2 said:


> ```
> % sysctl -a | grep files
> kern.maxfiles: 517593
> ...
> ...


Thank you for the reponse.  This doesnt happen until the SELECT load goes up on the db which happens towards the afternoon


```
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 (May 27, 2020)

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 (May 27, 2020)

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


----------



## EverydayDiesel (May 27, 2020)

Martin Paredes said:


> The MySQL server and the PC running the code are 2 different machines?



Yes, they are two different machines on pretty new server hardware. (about 5 months old)


----------



## Martin Paredes (May 27, 2020)

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


----------



## EverydayDiesel (May 27, 2020)

Martin Paredes said:


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


----------

