How to resolve MySQL Errcode: 24 - Too many open files (Ubuntu)

[Sergio 750]

All of a sudden, with nothing changed on the database or server, a website was getting error when doing a search:

Error: Exception: SQLSTATE[HY000]: General error: 23 Out of resources when opening file './your-database-name/pages_parents.MYD' (Errcode: 24 - Too many open files) (in /home/forge/example.com/public/wire/core/PageFinder.php line 413)

#0 /home/forge/example.com/public/wire/core/Wire.php(386): ProcessWire\PageFinder->___find(Object(ProcessWire\Selectors), Array)
#1 /home/forge/example.com/public/wire/core/WireHooks.php(723): ProcessWire\Wire->_callMethod('___find', Array)
#2 /home/forge/example.com/public/wire/core/Wire.php(442): ProcessWire\WireHooks->runHooks(Object(ProcessWire\PageFinder), 'find', Array)
#3 /home/forge/example.com/public/wire/core/PagesLoader.php(248): ProcessWire\Wire->__call('find', Array)
#4 /home/forge/example.com/public/wire/core/Pages.php(232): ProcessWire\PagesLoader->find('title~=EAP, lim...', Array)
#5 /home/forge/example.com/public/wire/core/Wire.php(383): ProcessWire\Pages->___find('title~=EAP, lim...')
#6 /home/forge/example.com/public/wire

This error message was shown because: you are logged in as a Superuser. Error has been logged.

 

I tried several things, listed in this thread: https://serverfault.com/questions/791729/ubuntu-16-04-server-mysql-open-file-limit-wont-go-higher-than-65536

But for some reason, MySQL was not getting its limit increased, but in the end, the one that did the trick was this:

This worked for me on Ubuntu Xenial 16.04:

Create the dir /etc/systemd/system/mysql.service.d

Put in /etc/systemd/system/mysql.service.d/override.conf:

[Service]
LimitNOFILE=1024000

Now execute

systemctl daemon-reload
systemctl restart mysql.service

Yes indeed, LimitNOFILE=infinity actually seems to set it to 65536.

You can validate the above after starting MySQL by doing:

cat /proc/$(pgrep mysql)/limits | grep files