mysql: Slowquery untersuchen

[Pit]

Hallo Forum,

um meine Webanwendung nicht durch Engpässe auszubremsen, logge ich Scripte und Queries mit, die zu langsam sind. Leider habe ich bei meinem Provider keinen Slowquery-Log zur Verfügung, daher behelfe ich mir mit

  $start = microtime(true);
  $result_query=mysqli_query($verbindung,$query_string);
  $end = microtime(true);

Nun fällt hier ab und an eine Query in den Log, die bis zu 20 Sekunden dauert. Immer dieselbe query…

Führe ich diese dann aber im phpmyadmin aus, benötigt sie immer unterhalb 0,2 Sek.

Darüber hinaus ist die Query eine ganz simple Abfrage ala

select a,b FROM Table where Art="Z" AND ID="60829" AND B_Art="FF" ORDER BY ID DESC LIMIT 0, 1

Ein EXPLAIN bringt für mich auch keine wesentlichen Erkenntnisse:

id 	select_type 	table 	type    possible_keys 	key 	    key_len 	ref 	rows 	Extra
1 	SIMPLE 	      Table 	index 	NULL           	PRIMARY 	4        	NULL	1 	  Using where

Diese Abfrage wird zwar innerhalb einer Schleife bis zu 30 mal aufgerufen, aber das sollte doch machbar sein…

Warum taucht sie so oft als Slowquery bei mir auf oder wie gehe ich das Problem an?

Sie läuft auf einem Managed Server, aber im shared webhosting Bereich.

Pit

[Rolf B]

Hallo Pit,

ein ORDER BY ID ist sinnlos, wenn Du nach genau einer ID suchst.

Ich habe deinen Explain mal spaltengerecht verteilt. Was auffällt, ist das NULL bei "possible keys". Offenbar meint er, er hätte keinen passenden Index für Dich? Dann besteht immer die Gefahr, dass er einen Table Scan machen muss, und wenn sein Cache gerade anderweit belegt ist, dauert das lange.

Unter "keys" steht dann PRIMARY. Welche Spalte ist der Primary Key der Table? Welche Indexe hast Du noch? Wieviele Rows hat die Table?

Die 20s können übrigens auch daher rühren, dass eine andere Query parallel die Table festhält. Das ist mit handgemachtem Logging kaum herauszubekommen, es sei denn, du loggst JEDEN SQL Zugriff und prüfst, ob während der slow-query andere Queries auf der Tabelle zeitgleich aktiv sind.

Rolf

[Pit]

Hallo Rolf,

ein ORDER BY ID ist sinnlos, wenn Du nach genau einer ID suchst.

klar, das stimmt.

Ich habe deinen Explain mal spaltengerecht verteilt. Was auffällt, ist das NULL bei "possible keys". Offenbar meint er, er hätte keinen passenden Index für Dich? Dann besteht immer die Gefahr, dass er einen Table Scan machen muss, und wenn sein Cache gerade anderweit belegt ist, dauert das lange.

"AND ID="60829" ID ist eine int-Spalte (übrigens ein Weglassen der Quotes ändert im Explain-Ergebnis nichts, das habe ich ausprobiert)

Bringt ein Index auf ID etwas?

Unter "keys" steht dann PRIMARY. Welche Spalte ist der Primary Key der Table? Welche Indexe hast Du noch? Wieviele Rows hat die Table?

Primary = B_ID (in der Query komplett unberücksichtigt) Key = R_ID (in der Query auch komplett unberücksichtigt) Rows sind etwas unter 100.000 Auffällig, dass die Anzahl der Rows durchaus Auswirkung auf die Slowqueryzeit zu haben scheint. In einem anderen System mit knapp über 40.000 Rows gehen die Sowqueryzeiten "nur" bis ca. 6-7 Sekunden hoch.

Die 20s können übrigens auch daher rühren, dass eine andere Query parallel die Table festhält. Das ist mit handgemachtem Logging kaum herauszubekommen, es sei denn, du loggst JEDEN SQL Zugriff und prüfst, ob während der slow-query andere Queries auf der Tabelle zeitgleich aktiv sind.

Hm, irgendwie befürchte ich, dass die Query selber wirklich nicht das Problem ist... denn sonst müßte sie immer geloggt werden, nicht nur ab und zu. Zudem läuft sie ja im phpmyadmin schnell.

Pit

[ursus contionabundo]

WHERE Art="Z" AND ID="60829" AND B_Art="FF"

Bringt ein Index auf ID etwas?

Wird Nach nach ID gefragt? Ja? Dann: Ja!

Primary = B_ID (in der Query komplett unberücksichtigt) Key = R_ID (in der Query auch komplett unberücksichtigt)

Du brauchst für die obige Abfrage einen Index auf Art, auf ID und auf B_Art. Für andere Abfragen eventuell weitere.

Einfache Regel: Sobald eine Spalte in einem Vergleich hinter einem WHERE, AND, OR oder hinter ORDER auftaucht sollte diese indexiert sein. Es sei denn die Tabelle hat nur wenige Zeilen.

[Rolf B]

Hallo ursus,

in einem System mit vielen Life-Updates kann ein Index auch kontraproduktiv sein, das muss man fallweise betrachten.

Aber eine Query ohne Index auf eine 100K Tabelle, ja, das ist auf jeden Fall Käse.

Das Tempo der Query dürfte mit der Belastung der DB oder des Servers schwanken. Ein Share-Hoster wird da nicht unbedingt reichlich Ressourcen spendieren. Ist wenig los, liegt die Table komplett im Cache. Oder der Server kann zumindest ungestört durch den Tablespace schmökern. Ist viel los, gibt's Ärger um die freien Cache-Plätze sowie um den Lesekopf der Festplatte, und er muss den Tablespace von der Platte lesen, ggf. noch in Konkurrenz mit dem Apache. Wenn die Festplatte keine SSD ist, dann wird das ein lustiger Tanz mit dem Schreib-/Lesekopf.

Hinzu kommt der ORDER BY. Wenn die WHERE-Bedingung mehr als eine Row treffen kann, führt er vermutlich eine Sortierung von X Zeilen mit gleichem Key durch. Keine Ahnung, ob MYSQL an Hand der WHERE Bedingung merkt, dass der ORDER BY effektlos ist. Und wenn viel in der DB los ist, wird er diese Sortierung nicht unbedingt im RAM ausführen, sondern auf Platte.

Rolf

[ursus contionabundo]

Ein Share-Hoster wird da nicht unbedingt reichlich Ressourcen spendieren.

Meiner tut GENAU das…

[Pit]

Ein Share-Hoster wird da nicht unbedingt reichlich Ressourcen spendieren.

Meiner tut GENAU das…

SSD DBs bieten inzwischen viele Hoster an, meiner auch… Aber die besagte DB liegt auf einer Nicht-SSD. Ich könnte die auch mal testhalber auf eine SSD kopieren und mir die Unterschiede im Verhalten anschauen.

Pit

[Pit]

Hallo Rolf,

Das Tempo der Query dürfte mit der Belastung der DB oder des Servers schwanken. Ein Share-Hoster wird da nicht unbedingt reichlich Ressourcen spendieren. Ist wenig los, liegt die Table komplett im Cache. Oder der Server kann zumindest ungestört durch den Tablespace schmökern. Ist viel los, gibt's Ärger um die freien Cache-Plätze sowie um den Lesekopf der Festplatte, und er muss den Tablespace von der Platte lesen, ggf. noch in Konkurrenz mit dem Apache. Wenn die Festplatte keine SSD ist, dann wird das ein lustiger Tanz mit dem Schreib-/Lesekopf.

Ja, genau so ein Szenario kann ich mir gut vorstellen...

Hinzu kommt der ORDER BY. Wenn die WHERE-Bedingung mehr als eine Row treffen kann, führt er vermutlich eine Sortierung von X Zeilen mit gleichem Key durch. Keine Ahnung, ob MYSQL an Hand der WHERE Bedingung merkt, dass der ORDER BY effektlos ist. Und wenn viel in der DB los ist, wird er diese Sortierung nicht unbedingt im RAM ausführen, sondern auf Platte.

Weiß auch nicht, wie mysql das handhabt. Aber hier kann ich ja reagieren. Ich muß nochmal genau prüfen, ob der ORDER BY wirklich effektlos ist, denn die betreffende ID kann ist in dieser Tabelle ein Fremdschlüssel, sie ist also nicht unique. Aber ich bin der Meinung, dass sie bereits über B_Art selektiert wird und dann müßte sie doch wieder unique sein. Wie gesagt, ich schau mir das morgen genauer an und werfe den Order by dann ggf. raus.

Vielen Dank jedenfalls an Dich und Ursus für die Hilfe und die Anregungen zu diesem Thema.

Pit

[MudGuard]

Hi,

Hinzu kommt der ORDER BY. Wenn die WHERE-Bedingung mehr als eine > Ich muß nochmal genau prüfen, ob der ORDER BY wirklich effektlos ist, denn die betreffende ID kann ist in dieser Tabelle ein Fremdschlüssel, sie ist also nicht unique.

Du machst im Prinzip

select * where ID = 42 order by ID

Egal wie viele Zeilen das ergibt, alle haben die ID 42, und nach etwas zu sortieren, das in allen Zeilen gleich ist, ist ziemlich sinnlos. Eine Sortierung nach einem anderen Kriterium (bei dem verschiedene Werte auftreten können) wäre was anderes.

cu,
Andreas a/k/a MudGuard

[Pit]

Hallo Andreas,

Egal wie viele Zeilen das ergibt, alle haben die ID 42, und nach etwas zu sortieren, das in allen Zeilen gleich ist, ist ziemlich sinnlos. Eine Sortierung nach einem anderen Kriterium (bei dem verschiedene Werte auftreten können) wäre was anderes.

Danke nochmal für die Aufklärung. Es ist tatsächlich so, dass nach BID sortiert wird. Mir ist beim Übertrag ins Posting hier ein kleiner Fehler unterlaufen. Trotzdem kann es sein, dass die Sortierung unnötig ist, da die ID durch die Vorabselektion (die restlichen Bedinungen der Where) inzwischen unique geworden sein kann. Ich muß es, wie gesagt, mal prüfen.

Pit

[Rolf B]

Hallo Pit,

eine Sortierung nach B_ID sollte keine Laufzeit kosten. Du hast geschrieben, dass das der Primärschlüssel ist. Dazu gehört normalerweise ein clustering index, d.h. der Tablespace ist nach B_ID sortiert gespeichert.

Rolf

[Pit]

Hallo Rolf,

eine Sortierung nach B_ID sollte keine Laufzeit kosten. Du hast geschrieben, dass das der Primärschlüssel ist. Dazu gehört normalerweise ein clustering index, d.h. der Tablespace ist nach B_ID sortiert gespeichert.

Stimmt auch wieder 😀

Also bleibt unterm Strich, dass ich jetzt mal den Produktivbetrieb abwarten muß, um zu sehen, ob die Maßnahmen etwas gebracht haben.

Bin sehr gespannt.

Pit

[Rolf B]

Hallo Pit,

ich auch, und wenn es das nicht war, dann guck mal hierhin, falls noch nicht geschehen.

Rolf

[Pit]

Hallo Ursus,

Du brauchst für die obige Abfrage einen Index auf Art, auf ID und auf B_Art. Für andere Abfragen eventuell weitere.

Das habe ich nun mal gemacht. Jetzt muß ich den Live-Betrieb abwarten, um zu sehen, wieviel das bringen wird.

Der erneute Explain sagt auf jeden Fall nun:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	      table 	ref 	B_Art,Art,ID 	  ID 	     4     const 	 2   Using where; Using filesort

Heißt also, der Index wird genutzt…

Pit

[Rolf B]

Hallo Pit,

hast Du 3 Indexe erzeugt? Je einen mit B_ART, ART und ID? Das ist nicht unbedingt die beste Lösung. Er kann nur einen davon verwenden und muss, wenn er den ID Index verwendet, innerhalb der ID einen indexsequenziellen Zugriff machen, d.h. die Record-IDs aller Rows aus dem ID Index lesen, die einen bestimmten ID Wert aufweisen, und dann diese Rows aus dem Tablespace lesen, um die beiden übrigen Spalten zu vergleichen. D.h. wenn Du bspw. 20 Sätze mit ID=4711 hast, muss er auf durchschnittlich 10 verschiedene Stellen der Table zugreifen.

Wenn Du einen Index konkret für die Optimierung dieses Zugriffs erzeugen willst, muss er alle 3 Spalten enthalten. Dann kann MYSQL über einen reinen Index-Seek direkt die Record-ID der richtigen Row finden. Bzw. wenn es mehrere gibt, die Record-ID einer richtigen Row. EIne reicht Dir ja. Oder ist die Kombination (B_Art, Art, ID) UNIQUE? Wenn ja, könntest Du den Index auch als UNIQUE anlegen, er erzeugt dann einen INSERT Fehler, wenn Du irrtümlich eine NON-UNIQUE Kombination erzeugst.

In welcher Reihenfolge die 3 Spalten im Index stehen, hängt davon ab, ob der Index auch noch für andere Abfragen genutzt werden könnte. Wenn diese anderen Abfragen nur einen Teil dieser 3 Spalten nutzen, dann muss diese Teilmenge im Index nach vorn.

Rolf

[Pit]

Hallo Rolf,

Wenn Du einen Index konkret für die Optimierung dieses Zugriffs erzeugen willst, muss er alle 3 Spalten enthalten. Dann kann MYSQL über einen reinen Index-Seek direkt die Record-ID der richtigen Row finden. Bzw. wenn es mehrere gibt, die Record-ID einer richtigen Row. EIne reicht Dir ja.

Ok, das wußt ich nicht. Danke.

Oder ist die Kombination (B_Art, Art, ID) UNIQUE? Wenn ja, könntest Du den Index auch als UNIQUE anlegen, er erzeugt dann einen INSERT Fehler, wenn Du irrtümlich eine NON-UNIQUE Kombination erzeugst.

Nein, ich habe nochmal darüber nachgedacht. Die Kombi ist nicht UNIQUE. Daher auch kein UNIQUE Index und daher geht auch das ORDER BY in Ordnung. Ich hatte im Eingangspost da einen Fehler in der Beispielquery.

In welcher Reihenfolge die 3 Spalten im Index stehen, hängt davon ab, ob der Index auch noch für andere Abfragen genutzt werden könnte. Wenn diese anderen Abfragen nur einen Teil dieser 3 Spalten nutzen, dann muss diese Teilmenge im Index nach vorn.

Soweit ich das in Erinnerung habe, könnte ggf. mal nach B_Art gesucht werden. Die anderen Beiden Spalten machen aber nur in Kombination überhaupt Sinn. Deshalb nehme ich B_Art an den Anfang und packe die anderen beiden dazu.

Explain meint dazu:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	      table 	ref 	kombi 	        kombi 	10 	const,const,const 	1 	Using index condition; Using where; Using filesort

Ist das besser?

Pit

[Rolf B]

Hallo Pit,

der Index ist auf jeden Fall besser. Das "using filesort" gefällt mir noch nicht. Du könntest versuchen, die Spalte, nach der sortiert wird, als vierte Spalte in den Index zu hängen. Dann kann er die Sätze gleich in der richtigen Reihenfolge aus dem Index lesen.

Rolf

[Pit]

Hallo Pit,

der Index ist auf jeden Fall besser. Das "using filesort" gefällt mir noch nicht. Du könntest versuchen, die Spalte, nach der sortiert wird, als vierte Spalte in den Index zu hängen. Dann kann er die Sätze gleich in der richtigen Reihenfolge aus dem Index lesen.

Hallo Rolf,

habe ich gemacht.

Explain sagt dazu:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	          rows 	Extra
1 	SIMPLE 	      table 	ref 	kombi 	        kombi 	10 	   const,const,const 	1 	Using where

Gruß, Pit

[Rolf B]

Hallo Pit,

sehr schön. Die Performance dieser konkreten Query sollte damit durch die Decke schießen (äh, gut, durch den Boden, die Zeit soll ja klein sein...).

Nun bleibt zu hoffen, dass der Index sich nicht ungünstig auf Updates auswirkt und die Gesamtperformance herunterreißt. Da hilft nur beobachten.

Rolf

[Pit]

Hallo Rolf,

sehr schön. Die Performance dieser konkreten Query sollte damit durch die Decke schießen (äh, gut, durch den Boden, die Zeit soll ja klein sein...).

Haha.. 😀

Nun bleibt zu hoffen, dass der Index sich nicht ungünstig auf Updates auswirkt und die Gesamtperformance herunterreißt. Da hilft nur beobachten.

Na das mache ich ja ohnehin. Aber nahezu alle Slowlogs bezogen sich wirklich auf diese eine Abfrage, insofern ist es gut, eine Lösung gefunden zu haben und andererseits auch leicht, ggf. andere Probleme, die hierdurch entstehen könnten, zu sichten.

Danke für die Hilfe und viele Grüße,

Pit