After moving a server to a new machine with Ubuntu 16.10 I received some strange Postfix SMTP errors. Which turned out to be a connection issue to the MySQL server:
postfix/cleanup[30475]: warning: connect to mysql server 127.0.0.1: Too many connections
Oops, did I forgot to up max_connections during the migration:
# grep max_connections /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections = 8000
Nope, I didn’t. Did we all of a sudden have a surge in clients accessing the database. Let me check and ask MySQL, and the process list looked fine. But something was off. So let’s check the value in the SQL server itself:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in set (0.01 sec)
Wait, what?! A look into the error log gave the same result:
# grep max_connections /var/log/mysql/error.log
2017-06-14T01:23:29.804684Z 0 [Warning] Changed limits: max_connections: 214 (requested 8000)
Something is off here and ye olde oracle Google has quite some hits on that topic. And the problem lies with the maximum allowed number of open files. You can’t have more connections, than open files. Makes sense. Some people suggest to solve it using /etc/security/limits.conf to fix it. Which is not so simple on Ubuntu anymore, because you have to first enable pam_limits.so. And even then it doesn’t work, because since Ubuntu is using systemd (15.04 if I am not mistaken) this configuration is only valid for user sessions and not services/demons.
So let’s solve it using systemd’s settings to allow for more connections/open files. First you have to copy the configuration file, so that you can make the changes we need:
cp /lib/systemd/system/mysql.service /etc/systemd/system/
Append the following lines to the new file using vi (or whatever editor you want to use):
vi /etc/systemd/system/mysql.service
LimitNOFILE=infinity
LimitMEMLOCK=infinity
Reload systemd:
systemctl daemon-reload
After restarting MySQL it was finally obeying the setting:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 8000 |
+-----------------+-------+
1 row in set (0.01 sec)
The universe is balanced again.