Resolved Not enough filehandles during back-up for mysqldump

[mr-wolf]

All of a sudden I'm getting:

Warning: mysql "upgrade_"
Not all the data was backed up into /var/lib/psa/dumps/clients/artitec/domains/xxxxx/databases/upgrade__1 successfully. mysqldump: Got error: 23: Out of resources when opening file './upgrade_/art_zoo_category_item.MYD' (Errcode: 24 - Too many open files) when using LOCK TABLES

AFAIK nothing has changed lately to this database.
It happened the last 3 back-ups. All was fine the days before.
2 weeks ago this site was on another server, so I don't care much about that change.
I want to focus on how to get this permanently resolved by increasing the amount of file handles.

I tested the amount of files I'm able to open and that's 1024

mysql> SHOW VARIABLES LIKE 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)

On the previous server this was

mysql> SHOW VARIABLES LIKE 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 2458  |
+------------------+-------+
1 row in set (0.00 sec)

I expected a quick fix after I found this page:
Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces

Alas, no....

At first I wanted to do clean and elegant by adding a new file named "open_files.cnf" into
/etc/mysql/mysql.conf.d
But when that didn't work I directly edited the mysqld.cnf that was there.
After restarting mysql that limit stayed 1024.

It's an Ubuntu 16.04 TLS with Plesk Onyx 17.0.17
Is there maybe some other setting that is stopping it from increasing the filehandles??
This one is a pre-installed Plesk from a big German provider.

I think that the database that's causing this error is superfluous, so maybe getting rid of it is a solution, but on the other hand it intrigues me why the amount of files doesn't change and is so much lower than that of the other server.

 

[mr-wolf]

I'm a bit closer now:
MySQL open_files_limit - cannot change this variable

The recipes given are for CentOS
I just need to find the way to do this elegantly in Ubuntu

 

[mr-wolf]

FOUND IT

The explanation can be found here:
Can not increase max_open_files for Mysql max-connections in Ubuntu 15

The last post is what I implemented and that worked.
I doubled it to open files to 2048 and I will see how it works out for the back-up

Thanks Fredrick on stackoverflow.com:

As of MySQL 5.7.7, this is what the documentation recommends for Red Hat Enterprise Linux 7, Oracle Linux 7, CentOS 7, SUSE Linux Enterprise Server 12, Fedora 24 and 25:

MySQL :: MySQL 5.7 Reference Manual :: 2.5.10 Managing MySQL Server with systemd

On Ubuntu 16.04 the service is called mysql, not mysqld, so this is what I did:

sudo mkdir /etc/systemd/system/mysql.service.d
sudo vi /etc/systemd/system/mysql.service.d/override.conf
Added this in the new file override.conf:

[Service]
LimitNOFILE=5000
Then restarted the service:

sudo systemctl daemon-reload
sudo systemctl restart mysql