Ursachen und Lösungen für den MySQL-Fehler „zu viele Verbindungen“

Heute Mittag meldete der MySQL-Dienst in der Entwicklungs- und Testumgebung einen Fehler wegen zu vieler Verbindungen. Dem Problem nach zu urteilen, ist der Verbindungspool möglicherweise voll, sodass keine Verbindung mehr verfügbar ist.

In diesem Fall ist der direkteste Weg, die maximale Anzahl von Verbindungen zurückzusetzen. Überprüfen Sie die Datei my.cnf, die zwei Parameter zur Anzahl der Verbindungen enthält:

max_connections: Maximale Anzahl an Verbindungen

max_user_connections: Maximale Anzahl von Benutzerverbindungen

Der erste Parameter bestimmt die maximale Anzahl von Verbindungen für die Instanz und der zweite Parameter bestimmt die maximale Anzahl von Verbindungen für einen einzelnen Benutzer.

In allgemeinen Onlineumgebungen können diese beiden Parameter aus Sicherheitsgründen nicht gleich gesetzt werden. Der Parameter max_user_connections kann etwas kleiner gesetzt werden, um einen gewissen Spielraum zu lassen und zu verhindern, dass ein einzelner Benutzer alle Verbindungspools belegt.

Als ich das obige Problem sah, war meine erste Reaktion, mich einzuloggen und einige Verbindungen zu unterbrechen. Als ich jedoch versuchte, mich einzuloggen, stellte ich fest, dass ich mich nicht mehr einloggen konnte. Sogar das DBA-Verwaltungskonto dba_admin konnte keine Verbindung mehr herstellen. Sehen Sie sich das Fehlerprotokoll an:

2019-08-12T06:02:42.928412Z 0 [Warnung] Zu viele Verbindungen
2019-08-12T06:02:42.930181Z 0 [Warnung] Zu viele Verbindungen
2019-08-12T06:02:44.595199Z 0 [Warnung] Zu viele Verbindungen
2019-08-12T06:02:44.597160Z 0 [Warnung] Zu viele Verbindungen
2019-08-12T06:02:44.782042Z 0 [Warnung] Zu viele Verbindungen

In allen gibt es zu viele Verbindungen. Glücklicherweise handelt es sich um eine Entwicklungsumgebung. In diesem Fall können wir die Methode zum Stoppen des Datenbankdienstes verwenden, um ihn neu zu starten, und dann die maximale Anzahl von Verbindungen erneut erhöhen. Natürlich muss der Betrag dieser Erhöhung berücksichtigt werden. Im Allgemeinen muss er in Kombination mit dem wait_timeout-Wert festgelegt werden, d. h. dem Wartezeitlimit. Wenn der wait_timeout-Wert relativ groß ist, führt dies im Allgemeinen häufig zu einer großen Anzahl von Verbindungen, und eine Verbindung verbraucht etwa 2 MB Speicher. Wenn Sie mehr Verbindungen festlegen, ist es wahrscheinlich, dass der Speicher erschöpft ist. Wenn der wait_timeout-Wert relativ klein festgelegt ist, werden ständig Verbindungen erstellt und zerstört, wodurch bestimmte IO-Ressourcen verschwendet werden.

Der Schlüssel liegt daher darin, einen ausgeglichenen Wert zu erhalten, wie in der offiziellen MySQL-Dokumentation erläutert wird:

Laut der offiziellen Dokumentation können Sie mit mehreren GB Speicher die Anzahl der Verbindungen zwischen 500 und 1000 einstellen. Tatsächlich kann der Wert in diesem Bereich die meisten Anforderungen erfüllen.

Darüber hinaus wird im offiziellen Dokument auch ein Feature erwähnt, was durchaus gut ist.

mysqld erlaubt tatsächlich max_connections + 1 Client-Verbindungen. Die zusätzliche Verbindung ist für die Verwendung durch Konten mit dem SUPER-Privileg reserviert. Indem das Privileg Administratoren und nicht normalen Benutzern gewährt wird (die es nicht benötigen sollten), kann ein Administrator, der auch das PROCESS-Privileg hat, eine Verbindung zum Server herstellen und SHOW PROCESSLIST zur Problemdiagnose verwenden, selbst wenn die maximale Anzahl nicht privilegierter Clients verbunden ist.

Tatsächlich ist die maximal zulässige Anzahl von Verbindungen der Wert von max_connections + 1 Verbindung. Die letzte zusätzliche Verbindung ist für Benutzer mit Superberechtigungen. Auf diese Weise können Sie sich, wenn die Verbindungen erschöpft sind, mit Superberechtigungen anmelden, die Methode show processlist verwenden, um die Verbindungen anzuzeigen, und einige unnötige Verbindungen beenden, um den Dienst wieder verfügbar zu machen.

In tatsächlichen Anwendungen weisen wir jedoch der Einfachheit halber häufig einigen unnötigen Konten Superberechtigungen zu, was dazu führt, dass normale Benutzer mit Superberechtigungen eine zusätzliche Verbindung belegen. Bei dem heutigen Problem sind solche Einstellungen nicht hilfreich und es ist immer noch unmöglich, sich anzumelden, da eine Verbindung schließlich zu wenig ist. Wenn andere Konten diese Superverbindung bereits belegt haben, ist diese Instanz immer noch nicht zugänglich.

Gibt es in diesem Fall außer dem Neustart der MySQL-Instanz und dem Ändern der maximalen Anzahl von Verbindungen in der Datei my.cnf eine Möglichkeit, das Problem zu lösen, ohne den Dienst zu stoppen? Die Antwort lautet ja. Es gibt jedoch eine Voraussetzung: Ihr Dienst muss ein Percona-Server sein. Wenn Sie die offizielle Version von MySQL verwenden, funktioniert diese Methode nicht. Es wird gesagt, dass MariaDB diese Methode auch verwenden kann. Ich habe es nicht getestet und verstehe es nicht ganz. Wenn Sie interessiert sind, können Sie es testen.

Ab Percona Server 5.5.29 können Sie einfach „extra_port“ zu Ihrer my.cnf hinzufügen. Beim nächsten Neustart wird der Port verfügbar und lauscht auf derselben Bind-Adresse wie normale Verbindungen. Wenn die Variable extra_port nicht gesetzt ist, ist standardmäßig kein zusätzlicher Port verfügbar. Sie können auch „extra_max_connections“ definieren, die Anzahl der Verbindungen, die dieser Port verarbeiten wird. Der Standardwert ist 1.

Das Obige ist die Beschreibung auf der offiziellen Website von Percona-Server. Das Anmeldeproblem, nachdem der Verbindungspool belegt ist, kann durch zwei weitere Parameter gelöst werden:

mysql:(keine) 22:12:51>>Variablen wie „%extra%“ anzeigen;
+----------------------------------+--------+
| Variablenname | Wert |
+----------------------------------+--------+
| extra_max_connections | 10 || extra_port | 43130 |
+----------------------------------+--------+
2 Zeilen im Satz (0,00 Sek.)

Dies sind die beiden Parameter, einer ist extra_max_connections und der andere ist extra_port. Diese beiden Parameter ermöglichen uns zusätzliche Verbindungen und zusätzliche Ports für die Verbindung mit dem Server, sodass wir mit show processlist den Verbindungsstatus beobachten, einige unnötige Verbindungen beenden und den Server wieder verfügbar machen können.

Beachten Sie jedoch, dass extra_port in Percona Server 8.0.14 und neuer entfernt wurde, da die MySQL-Community admin_port implementiert hat, das diese Funktionalität dupliziert. Bearbeiten Sie daher beim Upgrade auf Percona Server 8.0.14 unbedingt my.cnf und aktualisieren Sie extra_port, falls es dort bereits definiert ist!

Schauen wir uns zum Schluss noch die Verbindung an. In diesem Beispiel setzen wir extra_port auf 43130:

[root@log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4313
Passwort eingeben:
 Willkommen beim MySQL-Monitor. Befehle enden mit ; ​​oder \g.
Ihre MySQL-Verbindungs-ID lautet 71920
Serverversion: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle und/oder Tochtergesellschaften. Alle Rechte vorbehalten.

Oracle ist eine eingetragene Marke der Oracle Corporation und/oder ihrer
Tochtergesellschaften. Andere Namen können Marken ihrer jeweiligen
Eigentümer.

Geben Sie „help;“ oder „\h“ ein, um Hilfe zu erhalten. Geben Sie „\c“ ein, um die aktuelle Eingabeanweisung zu löschen.

[email protected]:(keine) 22:40:59>>beenden
Tschüss

---------------------------------------------------------------------------

[root@log]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P43130
Passwort eingeben:
 Willkommen beim MySQL-Monitor. Befehle enden mit ; ​​oder \g.
Ihre MySQL-Verbindungs-ID lautet 71941
Serverversion: 5.7.16-10-log Percona Server (GPL), Release 10, Revision a0c7d0d

Copyright (c) 2000, 2011, Oracle und/oder Tochtergesellschaften. Alle Rechte vorbehalten.

Oracle ist eine eingetragene Marke der Oracle Corporation und/oder ihrer
Tochtergesellschaften. Andere Namen können Marken ihrer jeweiligen
Eigentümer.

Geben Sie „help;“ oder „\h“ ein, um Hilfe zu erhalten. Geben Sie „\c“ ein, um die aktuelle Eingabeanweisung zu löschen.

[email protected]:(keine) 22:41:05>>

Kurzzusammenfassung

• Bei Verwendung der offiziellen MySQL-Version:

Wenn die Anzahl der Verbindungen den Maximalwert überschreitet, melden Sie sich zunächst mit einem brandneuen Superkonto an, um zu prüfen, ob Sie sich direkt anmelden können. Wenn nicht, können Sie den Dienst nur stoppen, dann die Anzahl der Verbindungen zurücksetzen und den Dienst dann neu starten.

• Bei Verwendung von Percona-Server oder MariaDB:

Sie können versuchen, extra_port im Voraus zu konfigurieren, um zu verhindern, dass die Anzahl der Verbindungen erschöpft wird. Wenn dies passiert und nicht im Voraus konfiguriert wurde, ergänzen Sie es am besten nach dem Beenden des Dienstes, um ein erneutes Auftreten zu verhindern.

Oben finden Sie ausführliche Informationen zu Ursache und Lösung des MySQL-Fehlers „zu viele Verbindungen“. Weitere Informationen zum MySQL-Fehler „zu viele Verbindungen“ finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!