[SOLVED] Mariadb102 and open_files_limit (Errcode: 24 "Too many open files")

1

Hi all,

I have a problem with daily backup and open_files_limit.
- The problem happens only once in a while.

Aug 28 03:00:37 dorgee esmith::event[29767]: mysqldump: Couldn't execute 'show fields from `BouMN_woocommerce_shipping_zone_locations`': Out of resources when opening file '/var/tmp/#sql_770_0.MAI' (Errcode: 24 "Too many open files") (23)
Aug 28 03:00:37 dorgee esmith::event[29767]: Action: /etc/e-smith/events/pre-backup-data/S50rh-mariadb102-dump-tables FAILED: 1 [11.164038]
Aug 28 03:00:37 dorgee esmith::event[29767]: Event: pre-backup-data FAILED

Inside the Mariadb102 console:

# mysql102
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9455
Server version: 10.2.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>


MariaDB [(none)]> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0,01 sec)

MariaDB [(none)]>

I would like to increase the limit to 2048.

I tried to include it in /etc/opt/rh/rh-mariadb102/my.cnf.

# Added 2020-08-29_11h27
open_files_limit = 2048

# Added 2020-08-29_11h27
[mysqld_safe]
open_files_limit = 2048

Then restarted Mariadb102

# systemctl restart rh-mariadb102-mariadb.service
#

But the limit is still at 1024:

MariaDB [(none)]> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0,00 sec)

MariaDB [(none)]>


There is a file /etc/opt/rh/rh-mariadb102/my.cnf.d/mariadb-server.cnf.

QUESTION:
- Do I have to make a custom-template and include the limit in it instead of in /etc/opt/rh/rh-mariadb102/my.cnf ?
- Is there another way ?
- Will this affect the number of connections ?

All suggestions appreciated,

Michel-André

1 Like
2

Hi all,

# cat /usr/lib/systemd/system/rh-mariadb102-mariadb.service

...
# For example, if you want to increase mysql's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/rh-mariadb102-mariadb.service.d/limits.conf" containing:
#       [Service]
#       LimitNOFILE=10000
...

I did what it is said above but it doesn’t work.

Michel-André

3

Create /etc/opt/rh/rh-mariadb102/my.cnf.d/custom.cnf with following content:

open_files_limit = 10000

Create custom service dir:

mkdir -p /etc/systemd/system/rh-mariadb102-mariadb.service.d

Create /etc/systemd/system/rh-mariadb102-mariadb.service.d/limits.conf with following content

[Service]
LimitNOFILE=10000

or

LimitNOFILE=infinity

Reload services and restart service:

systemctl daemon-reload

systemctl restart rh-mariadb102-mariadb

Source:

2 Likes
4

Hi Markus,

After the creation/editing of the file, I didn’t restart rh-mariadb102-mariadb.service.

If I set the LimitNOFILE=10000 and:

# systemctl daemon-reload
#

# systemctl restart  rh-mariadb102-mariadb.service
#


MariaDB [(none)]> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 4184  |
+------------------+-------+
1 row in set (0,00 sec)

MariaDB [(none)]>

If I set the limit to infinity or 2048, the answer is always the same: open_files_limit 4184.

What do you think Markus ?

Michel-André

5

I think 4184 is the maximum possible.

It goes down if you set open_files_limit to 1000 and LimitNOFILE=1000

1 Like
6

Hi Markus,

Thank you again Markus.

I did the modifications on my main server with 10000;

MariaDB [(none)]> show global variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 4190  |
+------------------+-------+
1 row in set (0,00 sec)

MariaDB [(none)]>

I will leave it like that and see what will happen.

Michel-André

2 Likes
7

Here I’m stuck with 4183 when setting to 10000…

2 Likes
8

Hi Markus,

On a VM it gives 4184.

Michel-André

9

Playing today with mariadb, I read indeed a warning about the default 1024, maybe we could increase it with the systemd service

1 Like
10

Hi Stéphane,

With a custom-template ?

Michel-André

11

a systemd service can be customised easily by you

else I could add it myself in a rpm

12

Hi guys!

Maybe could help:

13 
Aug 29 19:26:35 ns7dev9 mysqld-scl-helper: 2020-08-29 19:26:35 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 4184)
Aug 29 19:26:35 ns7dev9 mysqld-scl-helper: 2020-08-29 19:26:35 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)

What I can found in my tests today

[root@ns7dev9 ~]# mysql103
MariaDB [(none)]> SHOW VARIABLES LIKE 'open_files_limit';
14

Salut Stéphane,

My problem was with Mariadb 102.

I think that if you add it with a RPM (Mariadb 102/103, 101 ?), it will bw better as it will be done for everyone.

Michel-André

1 Like
15

I found this:

“MariaDB sets the limit with setrlimit . MariaDB cannot set this to exceed the hard limit imposed by the operating system. Therefore, you may also need to change the hard limit. There are a few ways to do so.”

from here:

1 Like
16

Salut Gabriel,

I saw something about connections while googling while searching for a solution.

https://support.plesk.com/hc/en-us/articles/213938885-Plesk-or-website-are-not-working-Unable-to-connect-to-database-MySQL-server-has-gone-away-Full-server-backup-is-created-with-warning-errno-24-Too-many-open-files

Resolution

Warning: Increasing the open file limits might affect the amount of used connections. In order to avoid instability, consider increasing it as well by following instructions from this KB article.

Michel-André

17

Hi Gabriel,

Your link: Server System Variables - MariaDB Knowledge Base gives all the possible solutions…

It also explains our displayed limits.

MAX(max_connections*5, max_connections +table_open_cache*2)

I am confident that Stéphane will take the best solution.

Michel-André

2 Likes
18

@stephdl always solve anything!

3 Likes
19

This forum and the people who participate in it are truly the best I have seen.

Michel-André

2 Likes
20

Yes, that’s right!
And, if you were to name a few, you would definitely ignore everyone else!

Gabriel