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.

Es gibt wohl kaum ein Thema, dass im Datenbankumfeld so umstritten ist, wie ein konkretes Schema für die Benennung von Objekten in Datenbanken.

Es gibt nicht nur keinen Standard, selbst so etwas wie eine einfache Konvention scheint ob der völlig konträren Positionen der Diskussionsteilnehmer völlig unmöglich zu sein.

Aus diesem Grund soll die folgende Liste von Stichpunkten auch keinerlei Anspruch auf die objektive Wahrheit erheben, sondern lediglich das wiedergeben, was sich in zahlreichen Projekten von mir als sinnvoll erwiesen hat.

• kein Abweichen von der ersten Normalform – d.h. niemals strukturierte Daten in einzelnen Datenbankfeldern
• so normalisiert wie möglich (insbesondere niemals Daten redundant speichern) – nur in Ausnahmefällen denormalisiert lassen (z.B. Anrede “Herr/Frau” nicht in separate Tabelle auslagern)
• grundsätzlich alle Bezeichner in Kleinbuchstaben; Unterstriche sind erlaubt (“person” statt “Person”)
• grundsätzlich alle Bezeichner in der gleichen Sprache
• Tabellen und Attributnamen im Singular (“person” statt “personen”)
• Attributnamen (außer Fremdschlüssel) mit Präfix, der Kurzform des Tabellennamens wiederspiegelt (“pers_name” statt “name”)
• Primärschlüssel ist immer eine separate Spalte namens Präfix + “_id” (“pers_id”) vom Typ Serial (PostgreSQL) bzw. Integer Auto Increment (MySQL)
• Fremdschlüsselnamen werden inklusive Präfix von der referenzierten Tabelle übernommen (“pers_id” bleibt in jeder referenzierenden Tabelle “pers_id”)
• keine Abkürzungen (“pers_geburtsjahr” statt “pers_geb”)
• keine “Anreicherung” von Attributnamen (“pers_geburtsjahr” statt “pers_geburtsjahr_int”)
• Spalten mit Booleschen Werte so benennen, dass sofort klar ist, wofür true/false stehen (“maennlich” statt “geschlecht”)

Diese Liste ist zwar offensichtlich unvollständig, aber selbst diese minimale Konvention hat mir schon sehr gute Dienste, vor allem bei der Erklärung der Datenbankschemata gegenüber Dritten, geleistet.

Unser täglich MySQL-Rant gib uns heute (ok, der erste Teil ist zugegebenermaßen nur ein Verständnisproblem)…

Ich wurde heute von der Frage eines Studenten überrascht, warum man, wenn man vom 19. April 19 Tage abzieht und dann noch einen weiteren Tag abzieht, als Ergebnis beim 1. April landet.

Genaugenommen ging es ihm um die folgende MySQL-Anweisung aus einem älteren Software-Projekt:

mysql> select CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY;
+----------------------------------------------------------+
| CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY |
+----------------------------------------------------------+
| 2016-04-01                                               |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Hmm, was ist denn, wenn wir den Tag am Ende nicht abziehen?

mysql> select CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 0 DAY;
+----------------------------------------------------------+
| CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 0 DAY |
+----------------------------------------------------------+
| 2016-03-31                                               |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Ok, …, auch interessant. Bei der Suche nach einer Erklärung irritierte mich erstmal, was das INTERVAL in der Anweisung sollte. Ist das ein Cast, oder was?

Die (nach wie vor grottige) MySQL-Doku kennt INTERVAL in der Übersicht der Datums- und Zeitfunktionen nicht als separate Anweisung oder Funktion, sondern lediglich als Schlüsselwort innerhalb von Datumsfunktionen wie z.B. DATE_ADD.

Erst bei deren Beschreibung findet man die nette Information, dass man mittels INTERVAL Datumsberechnungen durchführen kann und zwar mit der Syntax:

date + INTERVAL expr unit

Und damit erkennt man dann auch, wie die originale Anweisung interpretiert wird, nämlich so:

mysql> select CURRENT_DATE - INTERVAL (DAYOFMONTH(CURRENT_DATE) - 1) DAY;
+------------------------------------------------------------+
| CURRENT_DATE - INTERVAL (DAYOFMONTH(CURRENT_DATE) - 1) DAY |
+------------------------------------------------------------+
| 2016-04-01                                                 |
+------------------------------------------------------------+
1 row in set (0,00 sec)

Ok, Rätsel gelöst, ein paar Nebenerkenntnisse gibt es dann doch noch…

Spaßig wird die Sache z.B., wenn man mal ohne INTERVAL arbeitet:

mysql> select CURRENT_DATE - DAYOFMONTH(CURRENT_DATE);
+-----------------------------------------+
| CURRENT_DATE - DAYOFMONTH(CURRENT_DATE) |
+-----------------------------------------+
|                                20160400 |
+-----------------------------------------+
1 row in set (0.00 sec)

Und ja, derartig ungültige Daten darf man sogar in Tabellen abspeichern! Die Doku dazu liest sich MySQL-typisch unbesorgt – man darf halt einfach nicht zuviel “erwarten”, dann geht alles gut 🙂

MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME column. […] In this case, you simply store the date as ‘2009-00-00’ or ‘2009-01-00’.

If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates.[…]

Die Behandlung des nichtexistenten Jahres 0 kann dann auch niemanden mehr überraschen:

MySQL permits you to store a “zero” value of ‘0000-00-00’ as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space.

Dies bringt mich zu PostgreSQL, wo selbstverständlich jedwede ungültigen Daten verboten sind. Der zugehörige Abschnitt aus dem Manual gehört zu meinen Lieblingstellen:

The first century starts at 0001-01-01 00:00:00 AD, although they did not know it at the time. This definition applies to all Gregorian calendar countries.

There is no century number 0, you go from -1 century to 1 century. If you disagree with this, please write your complaint to: Pope, Cathedral Saint-Peter of Roma, Vatican.

Nur der Vollständig halber: in PostgreSQL kommt die Berechnung des Monatsersten ohne unschöne Subtraktionen aus – das Äquivalent zu ersten SQL-Anweisung in diesem Blogeintrag lautet:

#select date_trunc('month', current_date)::date;

 date_trunc 
------------
 2016-04-01
(1 row)