phpBB langsam? oder Wie Mysql Slow Queries auslesen?

[Shocker]

hi,

Ich habe ein phpBB Board das aus unerklärlichen Gründen saulangsam beim Posten ist. Die Seite braucht dann immer ewig zum Laden, und ich versteh einfach nicht wieso...

MySQL loggt ja netterweise die langsamen Queries mit, also die die eine gewisse Grenze bei der Ausführungszeit überschreiten, nur blöderweise habe ich auf dem Server keinen Zugriff auf die Logfiles.

\s ist wenigstens so freundlich und sagt mir dass seit dem Serverstart 350 queries langsam waren. (seit ~2 1/2 tagen, weil das letzte mal wie ich die phpbb_search_wordmatch backuppen wollte (1GB) ist er wohl gecrasht)

Threads: 1 Questions: 6834410 Slow queries: 350 Opens: 310244 Flush tables: 1 Open tables: 256 Queries per second avg: 31.326

tja, jetzt wollte ich fragen ob es 1) eine möglichkeit gibt beim phpBB was zu verändern bzw. herauszufinden warum das so langsam beim posten ist oder 2) wie ich als eingeloggter mysql user die slow queries herausfinden kann...auf /var/logs/ habe ich keinen zugriff

[Zyancali]

Hi!

Jetzt stelle ich mir die Frage, wie viele und welche Mods du
eingebaut hast?!

MfG, Zyancali

[Shocker]

Keinen einzigen, simple standard phpBB 2.0.21 version.

[Gumfuzi]

ev. helfen Dir meine Snippets:
dieser:
http://www.phpbb.de/viewtopic.php?p=692686#692686
(loggt alle SQL-Abfragen, die eine einstellbare Sekundenanzahl überschreibten - bei allen Usern)
oder der hier:
http://www.phpbb.de/viewtopic.php?p=650594#650594
(listet die Queries im Footer auf. Man sieht genau, welcher Querie von welcher Datei wie lange gebraucht hat - wird nur Admins angezeigt; ist auch ein und ausschaltbar)

Diese Teile haben mir dazumals gut geholfen, ev. Übeltäter zu finden...

[Shocker]

Hui, sehr fein, danke

Wie vermutet liegt es an u.a. der phpbb_search_wordmatch und interessanterweise auch phpbb_posts.

######################### Anfang SQL-Log User: *** (IP: ***) ##############################
### aufgerufene Seite: /phpBB2/posting.php
### gekommen von: ***
### Browser: ***
################## Zeitpunkt: 24.09.2006-05:56
### Datei: includes/functions_search.php
### Zeile: 273
### Zeit: 8.18702
### Abfrage: SELECT COUNT(post_id) AS total_posts
FROM phpbb_posts
################## Ende Zeitpunkt ###


################## Zeitpunkt: 24.09.2006-05:56
### Datei: includes/functions_search.php
### Zeile: 307
### Zeit: 60.57256
### Abfrage: SELECT m.word_id
FROM phpbb_search_wordmatch m, phpbb_search_wordlist w
WHERE w.word_text IN ('alles', 'anfangen', 'auch', 'barlow', 'das', 'dass', 'dennoch', 'der', 'die', 'eigenständiges', 'fürs', 'ganz', 'gar', 'geld', 'gibt', 'halfordpiepmatz', 'hatte', 'ich', 'ichs', 'ist', 'klar', 'klasse', 'konnte', 'machen', 'mir', 'mit', 'musik', 'musiker', 'neue', 'nicht', 'nichts', 'passte', 'perfekt', 'schon', 'seiner', 'stimme', 'stimmt', 'zur')
AND m.word_id = w.word_id
GROUP BY m.word_id
HAVING COUNT(m.word_id) > 9929
################## Ende Zeitpunkt ###

######################### Ende SQL-Log User: *** ##############################



######################### Anfang SQL-Log User: *** (IP: ***) ##############################
### aufgerufene Seite: /phpBB2/posting.php
### gekommen von: ***
### Browser: ***

################## Zeitpunkt: 24.09.2006-05:57
### Datei: includes/functions_search.php
### Zeile: 307
### Zeit: 14.40027
### Abfrage: SELECT m.word_id
FROM phpbb_search_wordmatch m, phpbb_search_wordlist w
WHERE w.word_text IN ('auch', 'dauerts', 'doch', 'hier', 'lang')
AND m.word_id = w.word_id
GROUP BY m.word_id
HAVING COUNT(m.word_id) > 9930
################## Ende Zeitpunkt ###

######################### Ende SQL-Log User: *** ##############################



######################### Anfang SQL-Log User: *** (IP: ***) ##############################
### aufgerufene Seite: /phpBB2/posting.php
### gekommen von: ***
### Browser: ***

################## Zeitpunkt: 24.09.2006-05:58
### Datei: includes/functions_search.php
### Zeile: 273
### Zeit: 8.00239
### Abfrage: SELECT COUNT(post_id) AS total_posts
FROM phpbb_posts
################## Ende Zeitpunkt ###


################## Zeitpunkt: 24.09.2006-05:58
### Datei: includes/functions_search.php
### Zeile: 307
### Zeit: 75.97377
### Abfrage: SELECT m.word_id
FROM phpbb_search_wordmatch m, phpbb_search_wordlist w
WHERE w.word_text IN ('allem', 'anderen', 'aufgefallen', 'das', 'dass', 'dauert', 'dauerte', 'den', 'der', 'deutsch', 'die', 'doch', 'einen', 'eines', 'eröffnet', 'extrem', 'fall', 'hab', 'heute', 'ich', 'ist', 'lange', 'minuten', 'mir', 'neuen', 'nicht', 'offtopic', 'thread', 'topics', 'unbedingt', 'vor')
AND m.word_id = w.word_id
GROUP BY m.word_id
HAVING COUNT(m.word_id) > 9929
################## Ende Zeitpunkt ###

######################### Ende SQL-Log User: *** ##############################



######################### Anfang SQL-Log User: *** (IP: ***) ##############################
### aufgerufene Seite: /phpBB2/posting.php
### gekommen von: ***
### Browser: ***

################## Zeitpunkt: 24.09.2006-06:43
### Datei: includes/functions_search.php
### Zeile: 249
### Zeit: 6.60421
### Abfrage: INSERT INTO phpbb_search_wordmatch (post_id, word_id, title_match)
SELECT 25103, word_id, 0
FROM phpbb_search_wordlist
WHERE word_text IN ('das', 'ist', 'deine', 'rein', 'subjektive', 'meinung', 'ich', 'radi', 'beschissen', 'muß', 'danach', 'immer', 'ewig', 'aufstoßen', 'bääh', 'außerdem', 'geht', 'ein', 'solcher', 'vergleich', 'mal', 'gar', 'nicht', 'eine', 'flüssigwürze', 'mit', 'einem', 'gemüse', 'vergleichen', 'mercedes', 'auch', 'besser', 'als', 'milka', 'lol')
################## Ende Zeitpunkt ###


################## Zeitpunkt: 24.09.2006-06:43
### Datei: includes/functions_search.php
### Zeile: 273
### Zeit: 8.38262
### Abfrage: SELECT COUNT(post_id) AS total_posts
FROM phpbb_posts
################## Ende Zeitpunkt ###


################## Zeitpunkt: 24.09.2006-06:43
### Datei: includes/functions_search.php
### Zeile: 307
### Zeit: 52.54613
### Abfrage: SELECT m.word_id
FROM phpbb_search_wordmatch m, phpbb_search_wordlist w
WHERE w.word_text IN ('als', 'auch', 'aufstoßen', 'außerdem', 'beschissen', 'besser', 'bääh', 'danach', 'das', 'deine', 'ein', 'eine', 'einem', 'ewig', 'flüssigwürze', 'gar', 'geht', 'gemüse', 'ich', 'immer', 'ist', 'lol', 'mal', 'meinung', 'mercedes', 'milka', 'mit', 'muß', 'nicht', 'radi', 'rein', 'solcher', 'subjektive', 'vergleich', 'vergleichen')
AND m.word_id = w.word_id
GROUP BY m.word_id
HAVING COUNT(m.word_id) > 9930
################## Ende Zeitpunkt ###

######################### Ende SQL-Log User: *** ##############################

Eine Ahnung was man da machen könnte? Mehr Indizes?

phpbb_search_wordmatch hat einen Index auf word_id und post_id.
phpbb_search_wordlist hat einen Index auf word_id.

[Gumfuzi]

noch was:
nur wenn einmal das drinsteht, ist es nicht zwangsläufig diese Datei, kann ja auch an einer vorübergehenden Serverüberlast liegen...

[Shocker]

Naja, von 23 Postings seit der Installation des Log-Mods stehen 18 Queries im Logfile

Dann müsste der DB-Server schon ziemlich überlastet sein...


Wie es scheint hängt es also an solchen Queries:

Mittlerweile bin ich draufgekommen dass er bei folgendem Query hängt:

Code: Alles auswählen

SELECT m.word_id 
	FROM phpbb_search_wordmatch m, phpbb_search_wordlist w 
	WHERE w.word_text IN ([i][wörter aus post][/i])  
		AND m.word_id = w.word_id 
	GROUP BY m.word_id 
	HAVING COUNT(m.word_id) > 9930

~ 60 sek,
mit EXPLAIN bekomme ich folgendes:

Code: Alles auswählen

 id  	 select_type  	 table  	 type  	 possible_keys  	 key  	 key_len  	 ref  	 rows  	 Extra
1 	SIMPLE 	w 	range 	PRIMARY,word_text 	word_text 	50 	NULL 	21488 	Using where; Using temporary; Using filesort
1 	SIMPLE 	m 	ref 	word_id 	word_id 	3 	wm79112a_edenbridge.w.word_id 	246 	Using index

Vorher hatte ich noch keinen Index auf w.world_text gesetzt, da kam bei type 'ALL' statt 'range', aber eine zeitliche Verbesserung hat es auch nicht gebracht.
Langsam bin ich mit meinem Latein am Ende...Änderung auf INNER JOIN nutzt auch nichts...

Würde eine Umstellung von MyISAM auf InnoDB etwas bringen?

btw:
phpbb_search_wordlist | 263,782 Zeilen | 9.1 MB
phpbb_search_wordmatch | 65,416,501 Zeilen | 921.6 MB

[Gumfuzi]

nur die wordmatch ist 921 MB groß- wow!

in welcher Datei kommt der Querie vor? standard-phpBB oder ist das von einem Mod?

[Shocker]

der query kommt in includes/functions_search.php vor (Zeile 307), und wird aus der posting.php aufgerufen, wenn man einen neuen beitrag erstellt.

afaik keine Mods wie ich das Forum übernommen habe. (2.0.19)
Habe dann auf 2.0.21 upgedated, aber die functions_search.php ist anscheinend noch die alte.

muss dann zuhause ausprobieren ob sie mit der von der version 2.0.21 eh übereinstimmt, aber nachdem sie das gleiche änderungsdatum wie die anderen dateien hat, wird wohl nichts daran geändert worden sein -> standarddatei...

[miccom]

phpbb_search_wordlist | 263,782 Zeilen | 9.1 MB
phpbb_search_wordmatch | 65,416,501 Zeilen | 921.6 MB

Stimmt das? Kommt mir nur irgendwie etwas groß vor im Verhältnis zur wordlist.