mySQL-Queue läuft über

[flashnfantasy]

Hintergrund:
Ich habe eine Website programmiert, auf die sehr viele Leute gleichzeitig zugreifen, in besten Zeiten habe ich etwa 10 Anfragen pro Sekunde und 300 User online.

Die Seiten werden komplett dynamisch aufgebaut und benötigen etwa 10 Datenbankabfragen, die meisten davon in weniger als 10mSec.

Eine Tabelle ist jedoch inzwischen so groß, daß sie von der Festplatte gelesen werden muß. Dauer dieser Aktion etwa 4sek !!!

Wärenddessen laufen die anderen Anfragen an den Server munter weiter.

Nun passiert anscheinend folgendes:
Jede neue Anfrage wird soweit bearbeitet, bis die erste DB-Abfrage kommt. Die Abfrage wird auf eine Queue geschoben und wird bei Bedarf abgearbeitet.

Kommen jedoch zuviele Anfragen, und auch zuviele von der großen Tabelle, dann läuft die Queue anscheinend über.

In diesem Moment kommt dann der Fehler 'more than max_data_connections'...

Ich vermute mal, daß wenn man jede Seite um eine halbe Sekunde in der Verarbeitung verzögert, dann läßt man den DB-Server genügend Zeit, alle Queues abzuarbeiten. Der User merkt davon kaum was.
Meine Frage also, kann man dem Server (in meinem Fall dem Apache) mitteilen, daß er jede Abfrage erstmal eine halbe Sekunde auf eine Queue schieben soll, und dann erst mit der Auswertung beginnen ?

Gruß,
Flash

[wahsaga]

hi,

Schon mit Optimizing the MySQL Server beschäftigt?

Meine Frage also, kann man dem Server (in meinem Fall dem Apache) mitteilen, daß er jede Abfrage erstmal eine halbe Sekunde auf eine Queue schieben soll, und dann erst mit der Auswertung beginnen ?

Am einfachsten vermutlich Script-seitig - in PHP bspw. per sleep().

Ob das aber das gewünschte bringt ...?

gruß,
wahsaga

[Sven Rautenberg]

Moin!

Eine Tabelle ist jedoch inzwischen so groß, daß sie von der Festplatte gelesen werden muß. Dauer dieser Aktion etwa 4sek !!!

Alle Datenbankdaten werden typischerweise von Festplatte gelesen - die häufig benutzten kommen aber vielleicht auch aus dem Cache.

Wärenddessen laufen die anderen Anfragen an den Server munter weiter.

Nun passiert anscheinend folgendes:
Jede neue Anfrage wird soweit bearbeitet, bis die erste DB-Abfrage kommt. Die Abfrage wird auf eine Queue geschoben und wird bei Bedarf abgearbeitet.

Kommen jedoch zuviele Anfragen, und auch zuviele von der großen Tabelle, dann läuft die Queue anscheinend über.

In diesem Moment kommt dann der Fehler 'more than max_data_connections'...

Nein, die Queue läuft nicht über.

MySQL hat, genau wie der Apache-Webserver auch, eine maximale Zahl an Clients, die gleichzeitig bedient werden können.

Offenbar ist die Zahl der Clients, die MySQL bedienen kann, kleiner als die Zahl, die Apache bedienen kann. Es kann also passieren, dass ein Browser eine Anfrage schickt, die nicht beantwortet werden kann, weil MySQL schon komplett ausgelastet ist - verbindungsmäßig gesehen.

Da hast du also zwei Stellschrauben: Entweder reduzierst du die Zahl der im Apache möglichen parallelen Prozesse (oder Threads o.ä., je nachdem, was du da benutzt), oder du erhöhst die Zahl der in MySQL erlaubten parallelen Connections  - am Ende sollten die beiden Zahlen mindestens identisch sein, MySQL sollte tendentiell vielleicht sogar ein paar Connections mehr erlauben.

Die zweite Möglichkeit ist natürlich, deine Querys zu optimieren. Kritisch sind in diesem Zusammenhang alle schreibenden Operationen, weil dazu immer Lesezugriffe auf die betroffene Tabelle gesperrt werden müssen (eventuell auch nur die betroffenen Spalten, je nach Version und DB-Möglichkeiten).

Schreiboperationen wären also ein Ziel der Optimierungen. Leseoperationen selbst sollten, wo immer möglich, einen Index benutzen.

Und wenn es möglich ist, kann man die Nutzung des Index auch nochmal genauer untersuchen und unter Umständen optimieren.

Ich vermute mal, daß wenn man jede Seite um eine halbe Sekunde in der Verarbeitung verzögert, dann läßt man den DB-Server genügend Zeit, alle Queues abzuarbeiten. Der User merkt davon kaum was.

Das ist, wie oben gesagt, nicht dein Problem. Abgesehen davon, dass man die Organisation der Abarbeitung in der DB sowieso nicht organisieren kann und es auch nicht tun sollte, kannst du Schreib-Querys von deiner Seite aus eigentlich nur als unwichtiger markieren, so dass diese nicht sofort, sondern erst später ausgeführt werden, wenn die DB dazu Zeit findet. Bei Leseoperationen kannst du hingegen Querys nur als wichtiger kennzeichnen.

Meine Frage also, kann man dem Server (in meinem Fall dem Apache) mitteilen, daß er jede Abfrage erstmal eine halbe Sekunde auf eine Queue schieben soll, und dann erst mit der Auswertung beginnen ?

Im Grunde genommen wäre damit ja nichts gewonnen! Diese Verzögerung könntest du selbst ja auch dadurch hinkriegen, dass du ein Javascript in den Link einbaust, dass das Linkauslösen um eine halbe Sekunde verzögert. Oder den Benutzer anweisen, einfach eine halbe Sekunde später zu klicken.

Das Resultat: Alles passiert eine halbe Sekunde später - und funktioniert genauso wenig. :)

- Sven Rautenberg

[flashnfantasy]

Hi Sven,
danke für die lange Antwort, ich muß mich mich da erstmal komplett durchworschteln und da alles ausprobieren...

Moin!

Eine Tabelle ist jedoch inzwischen so groß, daß sie von der Festplatte gelesen werden muß. Dauer dieser Aktion etwa 4sek !!!

Alle Datenbankdaten werden typischerweise von Festplatte gelesen - die häufig benutzten kommen aber vielleicht auch aus dem Cache.

Das mit den Cache stimmt nur teilweise, nämlich dann, wenn die Query absolut identisch ist zu einer Query davor,
http://dev.mysql.com/doc/refman/4.1/en/query-cache-how.html

Wie das mit dem Lesen und Schreiben auf Platte ist kann ich nicht  genau sagen. Ich glaube irgendwo gelesen zu haben, daß bei kleinen Tabellen die Tabelle im Speicher bleibt und Updates auf die Platte geschrieben werden. Es wird dann aber nicht die Tabelle geändert, sondern ein Änderungsanhang an der Tabelle anhgehängt.

Das das Problem an der Festplatte liegt, da bin ich mir fast sicher. Weil ich meine Probleme löse, wenn ich eine große Menge an Datensätzen aus dieser Tabelle lösche und die Tabelle optimiere.
Nur sowas klappt nicht auf Dauer.

Wärenddessen laufen die anderen Anfragen an den Server munter weiter.

Nun passiert anscheinend folgendes:
Jede neue Anfrage wird soweit bearbeitet, bis die erste DB-Abfrage kommt. Die Abfrage wird auf eine Queue geschoben und wird bei Bedarf abgearbeitet.

Kommen jedoch zuviele Anfragen, und auch zuviele von der großen Tabelle, dann läuft die Queue anscheinend über.

In diesem Moment kommt dann der Fehler 'more than max_data_connections'...

Nein, die Queue läuft nicht über.

MySQL hat, genau wie der Apache-Webserver auch, eine maximale Zahl an Clients, die gleichzeitig bedient werden können.

Offenbar ist die Zahl der Clients, die MySQL bedienen kann, kleiner als die Zahl, die Apache bedienen kann. Es kann also passieren, dass ein Browser eine Anfrage schickt, die nicht beantwortet werden kann, weil MySQL schon komplett ausgelastet ist - verbindungsmäßig gesehen.

Da hast du also zwei Stellschrauben: Entweder reduzierst du die Zahl der im Apache möglichen parallelen Prozesse (oder Threads o.ä., je nachdem, was du da benutzt), oder du erhöhst die Zahl der in MySQL erlaubten parallelen Connections  - am Ende sollten die beiden Zahlen mindestens identisch sein, MySQL sollte tendentiell vielleicht sogar ein paar Connections mehr erlauben.

Die zweite Möglichkeit ist natürlich, deine Querys zu optimieren. Kritisch sind in diesem Zusammenhang alle schreibenden Operationen, weil dazu immer Lesezugriffe auf die betroffene Tabelle gesperrt werden müssen (eventuell auch nur die betroffenen Spalten, je nach Version und DB-Möglichkeiten).

Schreiboperationen wären also ein Ziel der Optimierungen. Leseoperationen selbst sollten, wo immer möglich, einen Index benutzen.

Und wenn es möglich ist, kann man die Nutzung des Index auch nochmal genauer untersuchen und unter Umständen optimieren.

Anscheinend ist es so, daß der Apache-Server die Prozesse wirklich parallel abarbeiten kann, wärend der DB-Server nach dem FIFO-Prinzip arbeitet.
Meist merke ich ja, wenn die Datenbank anfängt in die Kniee zu gehen, die Abfragen dauern dann immer länger, bis zu etwa 30 sekunden.
In dieser Zeit kann ich aber reine HTML-Seiten so schnell wie immer vom Server runterladen.

Indezes sind eigentlich ausgereizt, wo fast nur lesend zugegriffen wird habe ich schon 2-3 Indezes.
Bei den meisten Queries wird ja über die ID eines oder zweier User gearbeitet, das ist fast schon straight forward dort den richtigen Index zu finden.

Ich vermute mal, daß wenn man jede Seite um eine halbe Sekunde in der Verarbeitung verzögert, dann läßt man den DB-Server genügend Zeit, alle Queues abzuarbeiten. Der User merkt davon kaum was.

Das ist, wie oben gesagt, nicht dein Problem. Abgesehen davon, dass man die Organisation der Abarbeitung in der DB sowieso nicht organisieren kann und es auch nicht tun sollte, kannst du Schreib-Querys von deiner Seite aus eigentlich nur als unwichtiger markieren, so dass diese nicht sofort, sondern erst später ausgeführt werden, wenn die DB dazu Zeit findet. Bei Leseoperationen kannst du hingegen Querys nur als wichtiger kennzeichnen.

Es gibt die Möglichkeit, schreibende Zugriffe zu verzögern (LOW_PRIORITY, DELAYED), in meinem Fall wäre das sogar anwendungslogisch kein Problem (es wäre eins, wenn zB die nächste Seite in einer Handlungsfolge auf die geänderte Tabelle aufbauen würde).
Jedoch treten meine Probleme schon beim reinen Lesen auf.

Meine Frage also, kann man dem Server (in meinem Fall dem Apache) mitteilen, daß er jede Abfrage erstmal eine halbe Sekunde auf eine Queue schieben soll, und dann erst mit der Auswertung beginnen ?

Im Grunde genommen wäre damit ja nichts gewonnen! Diese Verzögerung könntest du selbst ja auch dadurch hinkriegen, dass du ein Javascript in den Link einbaust, dass das Linkauslösen um eine halbe Sekunde verzögert. Oder den Benutzer anweisen, einfach eine halbe Sekunde später zu klicken.

Das Resultat: Alles passiert eine halbe Sekunde später - und funktioniert genauso wenig. :)

• Sven Rautenberg

[wahsaga]

hi,

Alle Datenbankdaten werden typischerweise von Festplatte gelesen - die häufig benutzten kommen aber vielleicht auch aus dem Cache.

Das mit den Cache stimmt nur teilweise, nämlich dann, wenn die Query absolut identisch ist zu einer Query davor,
http://dev.mysql.com/doc/refman/4.1/en/query-cache-how.html

Wie der URL schon sagt, dass bezieht sich auf den query cache.

Sven sprach aber wohl eher davon, dass der Inhalt einer Tabelle komplett im RAM gehalten werden könnte.

Ich glaube irgendwo gelesen zu haben, daß bei kleinen Tabellen die Tabelle im Speicher bleibt und Updates auf die Platte geschrieben werden.

Eben dieses.

Das das Problem an der Festplatte liegt, da bin ich mir fast sicher. Weil ich meine Probleme löse, wenn ich eine große Menge an Datensätzen aus dieser Tabelle lösche und die Tabelle optimiere.

Dann sag doch lieber, es könnte an der Tabellengröße liegen - denn "die Festplatte" würde ich auf Grund dieser Beobachtung nicht gleich als (Haupt)Ursache sehen.

Ja, vielleicht wird die Tabelle jetzt im Speicher gehalten, weil sie kleiner ist und damit "reinpasst".
Dann ist die Festplatte nur in so fern Schuld, dass Lesen von und Schreiben auf diese natürlich zeitaufwendiger ist, als analoge Operationen im RAM.

Anscheinend ist es so, daß der Apache-Server die Prozesse wirklich parallel abarbeiten kann, wärend der DB-Server nach dem FIFO-Prinzip arbeitet.

Wie Sven schon schrieb: Das _muss_ die DB ja teilweise sogar, wenn für Operationen Tabellen/Datensätze gesperrt werden müssen.

Meist merke ich ja, wenn die Datenbank anfängt in die Kniee zu gehen, die Abfragen dauern dann immer länger, bis zu etwa 30 sekunden.
In dieser Zeit kann ich aber reine HTML-Seiten so schnell wie immer vom Server runterladen.

Die sind ja auch vom Locking auf der DB in keinster Weise betroffen.

Es gibt die Möglichkeit, schreibende Zugriffe zu verzögern (LOW_PRIORITY, DELAYED), in meinem Fall wäre das sogar anwendungslogisch kein Problem (es wäre eins, wenn zB die nächste Seite in einer Handlungsfolge auf die geänderte Tabelle aufbauen würde).
Jedoch treten meine Probleme schon beim reinen Lesen auf.

Es könnte ja sein, dass die Lesezugriffe eben deshalb warten müssen, weil von einem anderen Request gerade ein Schreibvorgang ausgelöst wurde.
Da könnte verzögertes Schreiben also u.U. schon was bringen.

Und wenn du sagst,

Indezes sind eigentlich ausgereizt, wo fast nur lesend zugegriffen wird habe ich schon 2-3 Indezes.

• auch das kann ja eine zusätzliche Bremse darstellen, wenn im Rahmen jeder Schreiboperation auch noch die Indizes aktualisiert werden müssen.

gruß,
wahsaga