MySql - ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files)

MySql - ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files

We are getting above error while showing database in mysql console.

mysql> show databases;

ERROR 1018 (HY000): Can't read dir of '.' (errno: 24 - Too many open files)

mysql>

This means already max file are open as per limit of Mysql. Let's check all open file using command lsof -u mysql

[root@14 ~]# lsof -u mysql

mysqld    54214 mysql 1017u   REG              253,0     36184  82904684 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000829W.MYD

mysqld    54214 mysql 1018u   REG              253,0      4096  82888505 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000PXHT.MYI

mysqld    54214 mysql 1019u   REG              253,0     23732  82888506 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000PXHT.MYD

mysqld    54214 mysql 1020u   REG              253,0      8192  82888508 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000RTU1.MYI

mysqld    54214 mysql 1021u   REG              253,0     63076  82888509 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000RTU1.MYD

mysqld    54214 mysql 1022u   REG              253,0      7168  82888511 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000S6EC.MYI

mysqld    54214 mysql 1023u   REG              253,0     51392  82904672 /var/lib/mysql/opm_AdCenter/HistoricLandingPageUserExperience_X000S6EC.MYD

In above query i have pasted some output, Let's count total number of open file using command.   lsof -u mysql | wc -l

[root@14 ~]# lsof -u mysql | wc -l

1075

[root@14 ~]#

In above example you can see total 1075 files are open, this is the reason we are getting error.

Let's fix it.

There are two option to fix this issue -

1- Restart MySql service.

2- Append open-files-limit = 2048 this line in my.cnf file and restart mysql.

Method 1-

[root@14 ~]# systemctl restart mysqld

[root@14 ~]# lsof -u mysql | wc -l

78

[root@14 ~]#

After MySql Restart only 78 files are open, Now i can see all the Databases and continue my work.

[root@14 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+---------------------------+

| Database                  |

+---------------------------+

| information_schema        |

| cand                      |

| dsa_AWR                   |

| dsa_Analytics             |

| dsa_CallMeasurement       |

| dsa_FormSubmit            |

| dsa_reporting             |

| mysql                     |

+---------------------------+

28 rows in set (0.00 sec)

mysql>

Method 2 - Add 

open-files-limit = 2048 end of my.cnf file.

[root@14 ~]# vi /etc/my.cnf

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

open-files-limit = 2048

Save and Exit from file.

 

[root@14 ~]# systemctl restart mysqld

That's ALL

!!!! Cheers !!!!