Unser täglich MySQL-Rant gib uns heute…
Ein geschätzter Kollege meldete mir heute erstmalig, dass sich seine Datenbank auf unserem MySQL-Server (in einer separaten Instanz, in der nur diese Datenbank läuft) über zu viele Verbindungen beschwert – soll heißen, sie verweigert die Annahme weiterer Datenbankverbindungen, da die maximale Anzahl an Verbindungen bereits ausgereizt wurde.
Nun, ein schneller Blick in das Manual – der entsprechende Schalter heißt max_connections. Diesen also ebenso schnell in der my.cnf auf 1000 gesetzt:
max_connections = 1000
MySQL neu gestartet und MySQL vermeldet stolz:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 214 |
+-----------------+-------+
1 row in set (0,00 sec)
Grmph. Natürlich gibt es keinen Hinweis in den Logfiles, was MySQL gestört haben könnte.
Probieren wir doch mal, im Live-System die Anzahl hochzusetzen:
mysql> set global max_connections = 1000;
Query OK, 0 rows affected (0,00 sec)
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0,00 sec)
Okay, das funktioniert – wirft aber eher neue Fragen auf…
Nur eine etwas längere Websuche später erfahre ich endlich hier, dass die Anzahl gleichzeitig offener Dateien hochgesetzt werden muss.
Dies kann man wahlweise umständlich über sysctl machen oder aber sehr elegant in der my.cnf selbst:
open_files_limit = 8192
max_connections = 1000
Kurzer Check:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0,01 sec)
Das sieht doch schon mal super aus – die Ursache war also open_files_limit, das wir ja auf 8192 gesetzt haben:
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0,00 sec)
Aehm, moment … 1024? Wo kommt denn jetzt 1024 her?
Kurzer Blick ins Manual: Default-Wert ist 0.
Hmm, was zeigt er denn an, wenn man keinen Wert in der my.cnf setzt?
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0,00 sec)
Okay … wir lernen also: bei open_files_limit wird der Default-Wert von 0 beim MySQL-Start auf 1024 aufgerundet während ein expliziter Wert von 8192 auf 1024 heruntergesetzt wird.
Was passiert denn, wenn man einfach die 1024 selbst setzt (denn ohne ein explizites Setzen wird ja max_connections ignoriert?
open_files_limit = 1024
max_connections = 1000
Nunja, ausnahmsweise mal das Erwartete:
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0,01 sec)
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+
1 row in set (0,00 sec)
Keine Ahnung was der Blödsinn soll, aber bis hierher könnte man das Gelernte folgendermaßen zusammenfassen:
Möchte man bei MySQL die maximale Anzahl gleichzeitiger Verbindungen (max_connections) erhöhen, muss man zusätzlich explizit die Anzahl gleichzeitig offener Verbindungen (open_files_limit) auf den Wert setzen, den sie so eh schon haben.
Wenn man jetzt noch ein gutes Gefühl hätte, dass die 1024 Filedeskriptoren wirklich für 1000 Verbindungen ausreichen werden…
So, genug geranted. Das eigentliche Problem liegt in der im Betriebssystem vorgegebenen Einstellung, wie hoch ein Nutzer die Anzahl offener Files setzen darf. Bei Ubuntu liegt diese standardmäßig bei 1024 und MySQL hat das lustige Verhalten, diesen Wert blind zu übernehmen (egal, was in der my.cnf steht – ja, auch wenn dort ein niedrigerer Wert steht).
Um also MySQL zu mehr Filedeskriptoren zu verhelfen, müssen wir doch beim Betriebssystem vorbeigucken (der vollständige Weg (OS+MySQL) ist u.a. hier beschrieben).
Ich habe also in /etc/security/limits.conf die beiden folgenden Zeilen hinzugefügt
mysql soft nofile 8192
mysql hard nofile 8192
und boshafterweise in my.cnf open_files_limit auf 4096 gesetzt und das Ergebnis lautet:
mysql> show variables like 'open%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 8192 |
+------------------+-------+
1 row in set (0,00 sec)
Das entspricht zwar immer noch nicht dem, was laut Handbuch passieren sollte, aber wer will schon kleinlich sein…
Zur eventuellen Einschränkung sei gesagt, dass wir hier von MySQL 5.5 sprechen – keine Ahnung, ob sich spätere Versionen anders verhalten.