MySQL-Abfrage verzögert sich zufällig

Question 1

Ich habe eine Abfrage, die so aussieht:

SELECT id FROM user WHERE id='47'

Die ID wird indiziert und Lesevorgänge für diese Abfrage sind immer schnell, wenn Profildaten wie diese verwendet werden.

SET profiling = 1;
SHOW PROFILES;

Die Abfragen werden immer in etwa 0,0002 Sekunden ausgeführt.

Wenn ich die Abfrage jedoch von der PHP-Seite aus profiliere, wie folgt:

$current = microtime(true);
$data = $conn->query($full_query);
$elapsed = microtime(true) - $current;

Dann gelegentlich vielleicht dauert 1 von 50 dieser Abfragen etwa 0,2 Sekunden. In meinem Testskript habe ich jedoch Code, um dies zu testen, der die Abfrage mit SET profiling = 1 profiliert; und obwohl der PHP-Roundtrip durch PDO 0,2 Sekunden betragen könnte, war die Abfragezeit immer noch 0,0002.

Dinge, die ich weiß oder weiß, die das Problem nicht verursachen:

Die Abfrage ist nicht langsam. Wenn ich mir dieselbe Abfrage anschaue, aus demselben Abfragelauf, profiliert in PHP und profiliert mit SET PROFILING, ist die Abfrage immer schnell und wird nie im Protokoll für langsame Abfragen protokolliert, selbst wenn sie zeigt, dass sie von der PHP-Seite 0,2 Sekunden dauert.
Dies hängt nicht mit skip-name-resolve zusammen – dies ist inkonsistent und ich habe skip-name-resolve bereits aktiviert

Dies bezieht sich nicht auf den Abfrage-Cache, das Verhalten existiert in beiden
Dieses Verhalten tritt auch bei Abfragen auf, die aus dem Cache kommen.
Die Abfrage wählt die ID nicht wirklich aus, aber ich verwende diese Abfrage zum Testen, um zu zeigen, dass es sich nicht um ein Problem mit dem Festplattenzugriff handelt, da dieses Feld definitiv indiziert ist.

Diese Tabellen sind nur 10-20 MB groß mit so etwas wie einem 1-MB-Index. Die Maschine zeigt sehr wenig Last und innodb verwendet nicht alle seine Puffer.
Dies wird gegen eine Tabelle getestet, die außer meinen Testabfragen keine andere Aktivität hat.

Hat jemand eine Idee, was man noch prüfen könnte? Dies scheint mir ein Netzwerkproblem zu sein, aber ich muss in der Lage sein, es zu sehen und das Problem zu finden, um es zu beheben, und mir gehen die Orte aus, an denen ich als nächstes nachsehen kann. Irgendwelche Ideen?

Question 2

Ich würde die Maschine profilieren.

Sie sagen, dass dies ungefähr 1 von 50 Mal vorkommt und dass jede Abfrage einen 0,2-Sekunden-Benchmark hat. Sie sollten in der Lage sein, top in einen Bildschirm zu setzen und dann eine Schleife von Abfragen in PHP auszuführen, um das RDBMS einem Lasttest zu unterziehen und Leistungsstatistiken zu sammeln.

Sie müssen wahrscheinlich mehr als laufen 50 * 0.2 =10 seconds, da Ihre “1 von 50”-Statistik wahrscheinlich auf manuell ausgeführten Einzelabfragen basiert – basierend auf dem, was ich in Ihrer Beschreibung gelesen habe. Probieren Sie 30-sekündige und 90-sekündige Belastungstests aus.

Achten Sie während dieser Zeit auf Ihre top Prozessbildschirm. Sortieren Sie es nach CPU, indem Sie drücken P. Jedes Mal, wenn Sie ‘P’ drücken, ändert sich die Sortierreihenfolge für den Prozessor-CPU-Verbrauch, stellen Sie also sicher, dass Sie den Verbrauchsten oben haben. (drücken M sortiert nach Speichernutzung. überprüfen die Manpage für mehr)

Suchen Sie nach allem, was während der Zeit(en) Ihres Belastungstests nach oben sprudelt. Du solltest etwas springen sehen höher – aber momentan.
(Beachten Sie, dass ein solcher Prozess möglicherweise nicht ganz oben auf der Liste steht – er muss es nicht, könnte aber dennoch genügend Festplattenlast oder andere Aktivitäten verursachen, um den MySQL-Server zu verzögern.)

Question 3

Ich habe das gleiche Phänomen auf meinen Systemen festgestellt. Abfragen, die normalerweise eine Millisekunde dauern, dauern plötzlich 1-2 Sekunden. Alle meine Fälle sind einfache INSERT/UPDATE/REPLACE-Anweisungen für einzelne Tabellen – nicht für SELECTs. Es ist keine Belastung, Verriegelung oder Gewindeaufbau erkennbar.

Ich hatte vermutet, dass es am Löschen von schmutzigen Seiten, dem Löschen von Änderungen auf der Festplatte oder einem versteckten Mutex liegt, aber ich muss es noch eingrenzen.

Auch ausgeschlossen

Serverlast – keine Korrelation mit hoch
load Engine – geschieht mit InnoDB/MyISAM/Memory MySQL Query

Cache – geschieht unabhängig davon, ob er ein- oder ausgeschaltet ist
Protokollrotationen – keine Korrelation bei Ereignissen

Question 4

Gut, dass Sie den Abfrage-Profiler bereits verwendet haben. Wenn Sie MySQL 5.6 verwenden, haben Sie auch Zugriff auf viele neue Leistungsmessungen in der LEISTUNGS_SCHEMA. Dies hat die Fähigkeit, viel mehr Details zu messen als der Abfrage-Profiler, und es misst auch global statt nur einer Sitzung. P_S wird Berichten zufolge den Abfrageprofiler ersetzen.

Um Ihr Problem zu diagnostizieren, würde ich damit beginnen, ein TCP/IP-Problem zu bestätigen oder auszuschließen. Testen Sie beispielsweise das PHP-Skript, um zu sehen, ob es die gleiche intermittierende Latenz erhält, wenn es sich über den UNIX-Socket verbindet. Sie können dies tun, indem Sie sich mit verbinden localhost Das bedeutet, dass das PHP-Skript auf demselben Server wie die Datenbank ausgeführt werden muss. Wenn das Problem verschwindet, wenn Sie TCP/IP umgehen, bedeutet dies, dass die Hauptursache wahrscheinlich TCP/IP ist.

Wenn Sie sich in einer virtuellen Umgebung wie einem Cloud-Hosting befinden, können Sie leicht Leistungsschwankungen feststellen, da andere Benutzer derselben Cloud zeitweise die gesamte Bandbreite verbrauchen. Dies ist einer der Nachteile der Cloud.

Wenn Sie vermuten, dass es sich um ein TCP/IP-Problem handelt, können Sie die TCP/IP-Latenz unabhängig von PHP oder MySQL testen. Typische Tools, die leicht verfügbar sind, umfassen ping oder traceroute. Aber da sind viele andere. Du kannst auch Teste die Netzwerkgeschwindigkeit mit netcat. Verwenden Sie ein Tool, das im Laufe der Zeit wiederholt messen kann, da es so klingt, als hätten Sie die meiste Zeit eine gute Leistung mit gelegentlichen Störungen.

Eine andere Möglichkeit ist, dass der Fehler in PHP liegt. Sie können versuchen, PHP mit zu profilieren XHProf um herauszufinden, wo es seine Zeit verbringt.

Question 5

Versuchen Sie, das Problem einzugrenzen. Führen Sie ein kleines Skript wie folgt aus:

https://drive.google.com/file/d/0B0P3JM22IdYZYXY3Y0h5QUg2WUk/edit?usp=sharing

… um zu sehen, welche Schritte in der Kette spitzen. Wenn Sie haben ssh2 installiert, es wird auch zurückkehren ps axu unmittelbar nach der am längsten laufenden Testschleife, um zu sehen, was läuft.

Wenn ich auf meiner Heimentwicklungsbox gegen localhost laufe, sehen die Ergebnisse so aus:

Die ergebnisse von ps axu hier sind ziemlich nutzlos, weil ich mich mit localhost verbinde. Aber ich kann diesen Ergebnissen entnehmen, dass die DB-Verbindungslatenz gelegentlich ansteigt, ebenso wie die “Netzwerk”-Latenz (etwas TCP/IP-Puffer?).

Wenn ich Sie wäre, würde ich die Anzahl der Testzyklen auf 5000 oder 50000 erhöhen.

Question 6

Ich kann nur raten, aber da Sie die Serverlast eliminiert haben, und ich nehme an, Sie haben in den InnoDb-Stats nach roten Fahnen gesucht (phpmyadmin ist eine große Hilfe, obwohl es professionellere Tools gibt), was bleibt, ist eine inkonsistente Verwendung von Schlüsseln. Kann es sein, dass Ihre Abfrage leicht abweicht, und dass es da eine Konstellation gibt suboptimale Indizes werden verwendet?

Bitte fügen Sie eine hinzu FORCE INDEX PRIMARY oder wiederholen Sie Ihre Tests.

Question 7

Etwas, das ich bei der Diagnose von MySQL-Problemen in dieser Richtung als äußerst nützlich empfunden habe, ist mysqltuner. Es ist ein PERL-Skript, das sich Ihre MySQL-Instanz ansieht und verschiedene Optimierungsverbesserungen vorschlägt. Ehrlich gesagt wird es schwierig, den Überblick über all die Einstellungen zu behalten, die Sie vornehmen können, und dieses Skript ist großartig, um Ihnen eine Aufschlüsselung potenzieller Engpässe zu geben.

Etwas anderes, das Sie berücksichtigen sollten, ist die Funktionsweise von Linux selbst, was auch erklären könnte, warum Sie zufällig zurückbleiben. Beim Laden top Auf einer Linux-Box (jede Box, unabhängig von der Auslastung) werden Sie feststellen, dass Ihr Speicher fast vollständig belegt ist (es sei denn, Sie haben gerade neu gestartet). Dies ist kein Problem oder Überlastung Ihrer Box. Linux lädt so viel wie möglich in den Arbeitsspeicher, um Zeit zu sparen, und lagert selten verwendete Dinge in Ihre Auslagerungsdatei aus, genau wie alle modernen Betriebssysteme (genannt virtueller Arbeitsspeicher). Normalerweise keine große Sache, aber Sie verwenden wahrscheinlich InnoDB als Tabellentyp (der aktuelle Standard), der Dinge in den Arbeitsspeicher lädt, um ebenfalls Zeit zu sparen. Was passieren könnte, ist, dass Ihre Abfrage in den RAM geladen wurde (schnell), aber gerade lange genug im Leerlauf war, um in die Auslagerungsdatei ausgelagert zu werden (viel langsamer). Daher würden Sie einen kleinen Leistungseinbruch erleiden, während Linux es zurück in den RAM verschiebt (Auslagerungsdateien sind dabei effizienter, als MySQL es von der Festplatte verschieben würde). Weder MySQL noch InnoDB haben keine Möglichkeit, dies zu sagen, da es sich ihrer Meinung nach immer noch im RAM befindet. Das Problem ist ausführlich auf beschrieben dieses Blogwobei der relevante Teil ist

Normalerweise könnte ein kleiner Teil der Auslagerungsnutzung in Ordnung sein (wir sind wirklich besorgt über die Aktivität – Ein- und Auslagerungen), aber in vielen Fällen wird „echter“ nützlicher Speicher ausgelagert: hauptsächlich Teile des Pufferpools von InnoDB. Wenn es wieder benötigt wird, wird ein großer Leistungseinbruch vorgenommen, um es wieder einzulagern, was zu zufälligen Verzögerungen bei zufälligen Abfragen führt. Dies kann zu einer insgesamt unvorhersehbaren Leistung auf Produktionssystemen führen, und oft kann das System nach dem Beginn des Austauschs in eine Todesspirale der Leistung geraten.

Question 8

Wir haben herausgefunden, dass ein Problem mit der zugrunde liegenden Hardware dies verursacht hat. Wir haben den Server mit VMotion auf neue Hardware umgezogen und das Problem ist verschwunden. VMWare zeigte keine Warnungen oder Probleme mit der Hardware an. Ein Umzug von dieser Hardware hat das Problem jedoch behoben. Sehr sehr seltsam.