MySQL-Performance nach Update mindestens Faktor 10 langsamer

[Klaus1]

Hallo,

ich habe letzte Woche einen Linux-Webserver aktualisiert. Betriebssystem von Suse SLES 12 SP1 auf SP5, PHP von 7.2 auf 7.4.6, und MySQL von 5.x auf 8.0.26.

Seit dem dauert jede Query mindestens 10 Mal so lange. Die meisten Tabellen laufen noch als MyISAM und nicht InnoDB, aber das war ja vorher auch nicht das Problem. Es haben sich auch weder die Anzahl der Daten noch die Struktur oder die Indexes geändert.

Der Server selber hat 8GB Ram (MySQL nutzt ca. 10%) und 4 CPU-(Kerne).

Hat jemand eine Idee, was ich wo einstellen könnte oder muss, damit die Performance nicht schlechter ist als vorher?

LG Klaus

[Rolf B]

Hallo Klaus1,

Es haben sich auch weder die Anzahl der Daten noch die Struktur oder die Indexes geändert.

MYSQL 5 nach 8 ist ein großer Schritt - verwendet das neue MySQL die Indexe noch? Oder musst Du sie neu aufbauen? Das könnte man mit EXPLAINs feststellen.

Sind andere Programme, oder der Start von Programmen, auch langsamer geworden?

Rolf

[Klaus1]

Hallo Rolf,

soweit ich das erkennen kann, sind nur die Datenbank-Abfragen so langsam geworden.

Eine einfache Abfrage: EXPLAIN SELECT count(*) as wert2 FROM aufgaben WHERE erfasstam like '2021-07-%' AND erfasser='1234' group by erfasser;

liefert:

id 	select_type 	table 	partitions 	type 	possible_keys 	key 	key_len 	ref 	rows 	filtered 	Extra 	
1 	SIMPLE 	aufgaben 	NULL 	ALL 	NULL 	NULL 	NULL 	NULL 	12086 	1.11 	Using where

Reparieren bzw. Optimieren hatte keinen Effekt.

Die Abfrage oben dauerte 0.0848 Sekunden, was jetzt nicht lang erscheint, allerdings werden im Programm insgesamt 120 dieser Abfragen losgeschickt.

LG Klaus

[Mitleser 2.0]

EXPLAIN SELECT count(*) as wert2 FROM aufgaben WHERE erfasstam like '2021-07-%' AND erfasser='1234' group by erfasser;

Welchen Datentyp hat das Feld "erfasser"?

[Klaus1]

Hallo,

das Feld "erfasser" ist vom Typ Varchar(15) und kann auch alphanumerische Zeichen enthalten. Aktuell gibt es aber keine Inhalte länger als 6 Zeichen. Das könnte ich kürzen.

LG Klaus

[Raketenwissenschaftler]

Gegenprobe: (rechts gekürzt):

+------+-------------+-------+-------+---------------+------+
| id   | select_type | table | type  | possible_keys | key  |
+------+-------------+-------+-------+---------------+------+
|    1 | SIMPLE      | gn250 | const | NNID          | NNID |
+------+-------------+-------+-------+---------------+------+

Ich sehe bei Dir im Gegensatz keinen „possible_key“. (Da steht bei Dir NUL.)

Ist für die Spalte erfasser der Index verloren gegangen?

[Klaus1]

Für "Erfasser" existiert bisher kein Index. Nachdem ich den Index erstellt habe, läuft diese Abfrage jetzt deutlich schneller.

Damit habe ich jetzt zwar in etwa die alte Performance wieder erreicht, aber warum die alte Version so viel schneller war, dass diese ohne Index genauso schnell war, wie jetzt die neue mit Index, ist damit nicht geklärt.

[Raketenwissenschaftler]

Womöglich gab es bei den früheren Test nicht genug Datensätze in der Tabelle. Soll heißen, es ist bis zum Update nur nicht aufgefallen, dass da Verbesserungspotential besteht

Unterhalb einer gewissen Datenmenge bringen Indexe wenig bis gar nichts.

[Raketenwissenschaftler]

Eine weitere Möglichkeit für einen nur scheinbaren Performanceverlust kann sein, dass bei den Tests vor dem Update die Daten aus den Abfragecache kamen.

Der wurde in MySQL 8.0 entfernt, weil er in größeren Umgebungen (Galera, Replikation, Multicores) oder bei hoher Arbeitslast schlicht kontraproduktiv war.

Um sich sicher zu sein müsste man also mit beiden Versionen unter wirklich gleichen Bedingungen testen.

Möglicherweise willst Du die Datenbanken auf InnoDB umstellen.

In MariaDB hingegen kann man den Query-Cache unter Umständen noch nutzen.

[Raketenbackfisch]

Eine weitere Möglichkeit für einen nur scheinbaren Performanceverlust kann sein, dass bei den Tests vor dem Update die Daten aus den Abfragecache kamen.

Der wurde in MySQL 8.0 entfernt, weil er in größeren Umgebungen (Galera, Replikation, Multicores) oder bei hoher Arbeitslast schlicht kontraproduktiv war.

Guter Punkt. Das könnte die Lösung sein. Wenn mit Cache gegen „ohne Query-Cache“ getestet wurde, kann Klaus ihm der Query-Cache tatsächlich einfach ein falsches Ergebnis geliefert haben, weil er vor der Messung im Altsystem kein „FLUSH QUERY CACHE“ gefeuert hat.

Möglicherweise willst Du die Datenbanken auf InnoDB umstellen.

Das dürfte in den meisten Fällen eine gute Idee sein. Einen Zusammenhang bzgl. Performance unter Laborbedingungen wage ich allerdings zu bezweifeln, weil dann ja vermutlich keine Locks in Betracht gezogen werden.

[Raketenwissenschaftler]

Für "Erfasser" existiert bisher kein Index. Nachdem ich den Index erstellt habe, läuft diese Abfrage jetzt deutlich schneller.

Ich hoffe, das hat Dich auf die Idee gebracht, alle Spalten, welche in Deinem Projekt hinter "WHERE" oder "SORT" "GROUP BY" auftauchen, indexieren zu lassen…

Ausnahmen wären allenfalls Abfragen, die nur sehr selten stattfinden (z.B. in Admin-Bereichen des Projektes) - Aber selbst bei diesen müsstest Du bei wirklich großen Datenbeständen damit rechnen, dass ohne Indexierung eventuell Ausführungslimits (z.B. von PHP) „gerissen“ werden.

Und warten ist nie schön…

Etwas Nützliches noch:

Unter Unix/Linux liefert

~> grep -Rni WHERE *

rekursiv (-R) alle nicht versteckten Dateien mit Zeilennummern (-n) im aktuellen Verzeichnis mit dem Vorkommen von where- egal ob groß oder klein.

[Rolf B]

Hallo Klaus1,

84ms für einen COUNT(*) klingt nach viel. War das die erste Ausführung? Oder hast Du die Abfrage mehrfach gemacht (wegen Caching)? Aber man weiß auch nicht, wie groß die Table ist und wieviele Spalten sie hat. Sind die 12086 im Explain alle Rows oder die gefundenen Rows?

Ich habe hier MariaDB 10.5.9 am Start, und habe durch Einsatz von RAND() eine Testtabelle mit knapp einer halben Million Zeilen erzeugt. Wenn der Index verwendet wird, sind die Query-Zeiten unmessbar klein. Ohne Index liegen sie bei 200ms.

Hast Du Messwerte für die gleiche Query aus der Zeit vor der Umstellung?

Die Frage von Mitleser ist auch ganz interessant: Ist erfasstam ein String oder ein Date? LIKE-Abfragen gegen ein DATE könnten in MYSQL tatsächlich möglich sein, diese DB ist dafür berüchtigt, einfach blindlings zu konvertieren statt aufwändige Konvertierungen explizit zu verlangen.

LIKE Abfragen neigen aber auch zu Table Scans, bei einer Wenn es ein DATE ist, könnte erfasstam BETWEEN '2021-07-01' AND '2021-07-31' deutlich fixer sein. Wenn es ein DATETIME ist, musst Du beim End-Datum natürlich '2021-07-31 23:59:59' oder gleich '2021-08-01' schreiben.

Und dann die Frage: Warum COUNT(*)? Geht es Dir um die Anzahl? Oder um die Frage, ob es Sätze gibt? Für die zweite Frage ist ein EXISTS (SELECT * ...) viel schneller, und wenn Du einen festen Satz von solchen Prüfungen machen musst, kannst Du sie auch in einem SELECT zusammenfassen:

SELECT EXISTS(SELECT * FROM ...) AS testa,
       EXISTS(SELECT * FROM ...) AS testb,
       EXISTS(SELECT * FROM ...) AS testc,
       EXISTS(SELECT * FROM ...) AS testd,
       EXISTS(SELECT * FROM ...) AS teste

Die größte Performancebremse ist oft nicht der Server, sondern sitzt vor der Tastatur 😉

insgesamt 120 dieser Abfragen

Ja. Hm. Wetten, dass das eleganter geht? Ich weiß zwar nicht wie, weil ich deine Aufgabenstellung nicht kenne (oder seit einer früheren Frage von Dir wieder vergessen habe), aber bei 120 COUNT(*) Abfragen muss es eine bessere Lösung geben.

Rolf

[Klaus1]

Die Tabelle aufgaben beinhaltet ein sehr einfaches Ticketsystem. Dort ist erfasst, wer wann welche Aufgaben erhalten und erledigt hat.

Darüber gibt es dann eine grafische Aufarbeitung in Form eines Balkendiagramms, je Person und Monat, jeweils für die letzten 12 Monate. D.h. es geht nur um die Anzahl. Also bei 5 Personen, immer 2 Abfragen je Monat, 12 Monate = 120 Abfragen.

Das Feld erfasstam ist ein String-Feld, weil aus einer alten Anwendung gefüllt, die nur Strings kennt.

Und natürlich habe ich die Abfragen mehrfach hintereinander durchgeführt. 😉

Und ja, die gesamte Tabelle umfasst aktuell die 12086 Rows. Vor der Umstellung dauerte der Aufruf aller 120 Queries weniger als 1 Sekunde. Vor dem Setzen des Index dauerte das ca. 12 Sekunden. Jetzt mit dem Index ca. 1 Sekunde. Insgesamt sind alle Seiten mit Datenbankzugriff merklich langsamer geworden. Eine (extreme) Seite mit vielen Daten (1.321.814 Datensätze) von Sensoren dauerte nach der Umstellung zum vollständigen Laden fast 3 Minuten. Vorher vielleicht 5 Sekunden. Nachdem ich auch hier einen Index erstellt habe, immerhin nur noch knapp 20 Sekunden. Aber immernoch deutlich zu lang.

LG Klaus

[Rolf B]

Hallo Klaus1,

je Person und Monat, jeweils für die letzten 12 Monate. D.h. es geht nur um die Anzahl. Also bei 5 Personen, immer 2 Abfragen je Monat, 12 Monate = 120 Abfragen.

Siehste, wusste ich es doch. Das geht in einem Statement. Mit GROUP BY.

SELECT Erfasser, SUBSTR(erstelltAm, 1, 7) as Monat, COUNT(*) as Anzahl
FROM aufgaben
WHERE erstelltAm >= '2020-07-01' erstelltAm < '2021-08-01'
GROUP BY person, monat
ORDER BY person, monat

liefert pro Erfasser und Monat die Anzahl der letzten vollständigen 12 Monate. Ich hab's mit >= und < gemacht statt mit BETWEEN, weil ich so keine Datumsarithmetik für den Monatsletzten brauche (die zwar keine Raketenwissenschaft ist, aber trotzdem Arbeit).

Irgendwas fehlt jetzt noch, du schriebst "2 Abfragen pro Monat", d.h. da fehlt noch irgend ein Merkmal. Aber das kenne ich nicht, davon steht hier nichts. Es ist darum Hausaufgabe für Dich 😉

Wenn erstelltAm ein Datumsfeld wäre, würde ich mit EXTRACT(YEAR_MONTH, erstelltAm) Jahr und Monat rausholen und damit gruppieren (hier stand vorher DATEPART, aber das gibt's bei MySQL und MariaDB nicht).

Alles in ein Array, je Erfasser einen Kuchen draus backen und genießen...

Rolf

[Raketenwissenschaftler]

MySQL von 5.x auf 8.0.26.

Frage: Hast Du das „Umarbeiten“ der Daten MySQL vollständig überlassen („inplace Upgrade“) oder die Datenbanken aus einem Dump neu erstellt („logical upgrade“)?

Frage: Was genau meint „5.x“? - Leseaufgabe:

1. https://dev.mysql.com/doc/refman/8.0/en/upgrading.html
2. https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

Da steht dann auch:

Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.

Hast Du

~> mysqlcheck -u root -p --all-databases --check-upgrade

ausgeführt?

MySQL 8.0 kennt neue/andere Einstellungsparameter. Du splltest also schauen, welche Tuningmaßnahmen getätigt wurden und jetzt neu gemacht werden müssen.

Etwas Performance (aber nicht wie von Dir beschrieben) geht verloren, weil MySQL 8 auf UTF8mb4 als Default-Charset setzt.)

[Klaus1]

Hallo,

es war vorher Version 5.7. Welche genaue Version, weiß ich leider nicht mehr. Ich bin da wohl ziemlich blauäugig herangegangen, hatte nur vorher gelesen, dass der Upgrade-Pfad supported wird und dass die Migration der Daten durch beim Upgrade automatisch durchgeführt werden. Also "Inplace Upgrade". Mir ist sogar aufgefallen, dass ein neues Reserved Word hinzugekommen ist: "system", dass in zwei Tabellen verwendet wurde und ich damit vorher umstellen musste.

Das Kommando mysqlcheck hatte ich nicht ausgeführt.

LG Klaus