Im gleichen Projekts, über das ich letzte Woche geschrieben habe, hatte ich die Aufgabe bekommen, die Leistung der Website zu optimieren. Ein Hauptproblem war die sehr langsame Anmeldung ins Backend. Jedes Mal, wenn ich mich eingeloggt habe, musste ich etwa eine Minute warten, bevor ich das Dashboard sehen konnte. Aber was stimmte mit der Website nicht? Warum war sie so langsam?
Eine riesige Datenbank
Um die Website analysieren zu können, musste ich sie erst einmal lokal einrichten. Allein dafür habe ich mehrere Tage gebraucht! Die Datenbank war so groß, dass ich buchstäblich die SSD meines Laptops aufrüsten musste, bevor ich überhaupt daran denken konnte, sie zu importieren. Der Datenbank-Dump war „nur“ etwa 40 GB groß, aber nach dem Import hat sie etwa 100 GB auf meiner SSD verbraucht. Für die Optimierung der Datenbank habe ich Backups erstellt. Dazu habe ich die gesamte Datenbank schließlich zweimal dupliziert, da ich auch eine HPOS-Migration durchführen wollte. Nachdem ich also Stunden damit verbracht hatte, eine neue SSD zu installieren, mein gesamtes System zu kopieren (ein Windows/Linux-Dual-Boot), einen defekten Bootloader zu reparieren und den Datenbankimport durchzuführen, war ich endlich bereit, mit der Analyse der Seite zu beginnen.
Das Performance-Problem für die Anmeldung finden
Nach meinem ersten Login hat mir das Query Monitor Plugin zwei Abfragen angezeigt, die extrem lange gedauert haben. Die sahen wie folgt aus:
SELECT COUNT(*)
FROM wp_comments
WHERE user_id = 123456
AND comment_approved = 1
Zwei dieser Abfragen dauerten jeweils etwa 30 Sekunden. Das hat die Anmeldezeit um eine ganze Minute verlängert. Der Query Monitor gab hat mir auch gesagt, welcher „Caller“ diese Abfragen ausgeführt hat. Es war die Akismet::get_user_comments_approved Funktion.
Der Grund für diese Abfragen
Der Shop verwendet Akismet zur Bekämpfung von Spam-Kommentaren. Im Widget „Auf einen Blick“ auf dem Dashboard wird die Anzahl der Kommentare in der Spam-Warteschlange und die Anzahl der Kommentare in der Moderation angezeigt. Im Widget „Aktivität“ werden außerdem „Neueste Kommentare“ angezeigt. Für jeden Kommentar, der in dieser Liste steht und der von einem User/Customer der Website verfasst wurde, wird die Anzahl der genehmigten Kommentare ermittelt. Der Witz ist aber, dass dieser Wert zwar dem Dashboard-Widget hinzugefügt, dann aber mit einem display:none Inline-Style versteckt wird. Er wird also nicht einmal angezeigt. Die Abfragen laufen trotzdem. Zum Glück waren es nur zwei Customer und nicht die möglichen fünf, die aufgelistet wurden, sonst wäre der Login um zweieinhalb Minuten verlangsamt worden.
Analysieren des Problems
Da wir nun wissen, welche Abfrage das Leistungsproblem verursacht und warum diese Abfrage ausgeführt wird, stellt sich die Frage, wie wir das Problem beheben können. Werfen wir einen Blick darauf, warum diese Abfrage so langsam ist.
Der Shop war eine Multisite-WordPress-Installation. In einem der Shops hatten wir etwa 76.600 Kunden (der andere Shop hatte fast doppelt so viele) mit mehr als 83.000 Abonnements, was zu mehr als 1.442.000 Bestellungen führte. Ja, das sind weit mehr als eine Million Bestellungen, und das ist nur für einen der Shops. Man kann also sagen, dass es sich um einen ziemlich großen Shop handelt. Jede Bestellung hat einige „Meta-Informationen“. Eine dieser Informationen sind „Bestellnotizen“ (Englisch „order notes“), und diese werden als Kommentare in derselben Tabelle wie die normalen Kommentare gespeichert. In der Kommentartabelle hatten wir insgesamt 15.348.283 Kommentare! Das waren die Kommentar-Typen:
| Einträge | comment_type |
|---|---|
| 677 | comment |
| 13876120 | order_note |
| 859541 | user_membership_note |
Wenn wir uns nun die Abfrage ansehen, die diese langsame Anmeldung verursacht, können wir erkennen, dass sie die Kommentare nach den Spalten user_id und comment_approved filtert. Sehen wir uns die Struktur der Tabelle, dann haben wir einen Index comment_approved_date_gmt, der für die Spalte comment_approved verwendet werden kann, aber wir haben keinen Index für die Spalte user_id. Das bedeutet, dass MySQL alle mehr als 15 Millionen Kommentare durchsuchen muss, um diejenigen zu finden, für die der Wert von user_id aus der Abfrage passt. Und das wiederholt sich für jede einzelne user_id Wert, den wir haben. Das kann eine Weile dauern.
Behebung des Problems
Da wir nun den Grund für die langsame Abfrage kennen, können wir versuchen, eine Lösung für das Problem zu finden. Es gibt drei Lösungen, die funktionieren könnten:
Lösung 1: Löschen der Kommentare
Das ist vielleicht ein bisschen zu radikal, aber bei diesem Shop würde es sogar funktionieren. Interessant ist, dass die beiden Kommentare, welche die Website verlangsamt haben, auf der „Checkout“ Seite gemacht wurden. Das ist keine Seite, auf der man normalerweise Kommentare zulassen würden. Das Löschen dieser Kommentare (oder zumindest das Verschieben in den Papierkorb) löste das Anmeldeproblem, da sie nicht mehr im Widget unter „Neueste Kommentare“ angezeigt wurden.
Lösung 2: Kommentar-Typen ausschließen
Bei der Vorbereitung dieses Blogbeitrags habe ich einen Filter gefunden, der zur Verbesserung der Abfrage verwendet werden kann. Für die Abfrage zum Zählen der Kommentare für eine user_id kann man diesen Filter verwenden, um einige Kommentar-Typen auszuschließen:
function my_excluded_comment_types($comment_types) {
$comment_types[] = 'order_note';
$comment_types[] = 'user_membership_note';
return $comment_types;
}
add_filter( 'akismet_excluded_comment_types', 'my_excluded_comment_types' );
Da wir die Typen order_note und user_membership_note nicht wirklich brauchen, sollten wir sie ausschließen. WooCommerce fügt einen Index für die Spalte comment_type in der Tabelle wp_comments hinzu, womit MySQL einige Kommentar-Typen sehr effektiv herausfiltern kann.
Das kann unsere die 30-Sekunden-Abfrage auf nur 0,015-0,03 Sekunden beschleunigen. Das ist schon eine ziemliche Verbesserung. Wir müssen nur darauf achten, dass wir alle Kommentar-Typen ausschließen, die wir nicht benötigen. Je nachdem, welche Plugins ihr verwendet, gibt es vielleicht noch andere Kommentar-Typen, die ihr ausschließen möchten.
Lösung 3: Hinzufügen eines Index für die user_id Spalte
Wie bereits erwähnt, besteht das Problem bei dieser Abfrage darin, dass wir versuchen, nach user_id zu filtern, diese Spalte aber keinen Index hat. Das Hinzufügen eines Index kann das Problem ebenfalls beheben. Um einen Index hinzuzufügen, müsst ihr diese Abfrage in der Datenbank ausführen (z.B. mit phpMyAdmin, Adminer, der WP-CLI oder einem anderen Tool):
ALTER TABLE wp_comments ADD INDEX `my_idx_user_id` (`user_id`);
Wenn ihr einen anderen „Präfix“ als „wp_“ verwendet, müsst ihr in der Abfrage entsprechend anpassen. Beachten aber bitte, dass das Hinzufügen eines Index zu einer Tabelle eine Weile dauern kann. Bei diesen 15 Millionen Einträgen hat es bei mir 30-35 Sekunden gedauert den Index zu erstellen. In dieser Zeit kann das Schreiben in die Tabelle langsamer sein oder sogar blockiert werden. Es ist also besser, die Abfrage zu einer Zeit auszuführen, in der weniger Aktivität im Shop herrscht.
Nach dem Hinzufügen eines Index dauert die anfängliche 30-Sekunden-Abfrage jetzt nur noch 0,001 Sekunden (oder sogar noch weniger, da dies die niedrigste Zahl ist, die ich bei der Analyse von Abfragen erhalte), d. h. sie ist sogar mehr als zehnmal schneller als bei der vorherigen Lösung.
Fazit
Die Performance-Probleme sind bei großen Websites und Shops oft ganz andere als bei kleinen Websites. Zumindest mein Blog ist weit davon entfernt, Millionen von Kommentaren zu erreichen. 😁
Die Ursache eines Leistungsproblems zu finden und es zu beheben, ist auch nicht immer eine einfache und unkomplizierte Aufgabe. Wenn ihr dabei Tools wie das Query Monitor Plugin verwendet, hilft das in der Regel sehr dabei, solche Probleme zu finden. Aber gute Lösungen zu finden, kann schwierig sein. Vor allem, wenn die Komponente, die das Problem verursacht, keine Action oder keinen Filter anbietet, um es zu lösen, oder wenn ihr nicht in der Lage seid, es anders zu beheben, wie z. B. durch das Hinzufügen eines Index. Für den in diesem Blogbeitrag erwähnten Shop habe ich am Ende die Lösungen 1 und 3 verwendet, wobei ich einen benutzerdefinierten WP-CLI-Befehl verwenden musste, um den Index zur Datenbank hinzuzufügen, da ich keinen Schreibzugriff mit Tools wie phpMyAdmin oder Adminer hatte.
Habt ihr auch einige „interessante“ Performance-Probleme mit großen Websites oder Shops erlebt? Dann teilt sie doch bitte in einem Kommentar mit uns. Oder vielleicht zwei, aber bitte nicht eine Million Kommentare. 😂
