Anfragen dauern, nach upgrade auf SuSe 9.3

[paintsplasher]

Hallo,

betreibe einen RootDS von Hosteurope! Auf ihm laufen ein vBulletin
Forum und ein wbb ! Sowie ein paar kleinere Sachen.

Er lief die ganze Zeit mit SuSe 9.0 und php 4.3.3. Nachdem ich den die neuen Packete mit SuSe 9.3, php 4.3.10 und mysql 4.1.10a fertig war
laufen die Foren sehr langsam. Man klickt, dann passiert erstmal 30s garnix, dennoch passiert es auch das es mal 2min ganz schnell läuft
wie gewohnt !

Das neue Packet wurde frisch von HE aufgespielt, der Server, FTP
und Plesk laufen sehr schnell, keine Probleme, nur die Foren hängen oft. Habe auch das Gefühl das es nachts schneller geht !

Jemand ne Idee?

[daemotron]

Kann mehrere Ursachen haben - war vorher auch Plesk drauf? Dafür muss nämlich noch mal eine komplette Apache-Instanz laufen... Was sagt top? Ansonsten gibt's ein paar Optimierungsansätze - sowohl für den Plesk-Indianer (http://www.rootforum.org/wiki/howto/all ... rpleskroot) als auch für Apache/PHP (Stichwort: PECL-APC u. a.) und MySQL (siehe http://www.rootforum.org/forum/viewtopic.php?t=36343) - und beim Apachen natürlich nur die Module laufen lassen, die unbedingt benötigt werden...

[pennywize]

Es laufen IMHO zu viele Instanzen für den Apachen. Plesk alleine benötigt schon so gut wie 15% der gesamten Ressourcen. An deiner Stelle hätte ich komplett auf Plesk verzichtet, den Server mit all seinen Domänen und Subdomänen manuell eingerichtet, PHP so kompiliert wie du es brauchst...
Hast du viele Besucher auf deinem Board?

[paintsplasher]

Hi,

also vorhher mit suse 9.0 lief ja alles genauso schnell und stabil !

Serverload liegt bei 7%, auf den Foren bewegen sich um die 100 user
gleichzeitig. http://www.fiesta-ka-forum.de

Mir kommt es vor als würden Anfragen in die Warteschlange gesteckt,
weil es sporatisch schnell, und dann die nächste schon wieder langsam
ist/kann.

[pennywize]

Mal eine ganz andere Frage, wie hast du das upgrade durchgeführt?
Einfach 9.3 über die alte als upgrade rübergebügelt oder komplett neu installiert und Daten von Backup eingespielt?
Steht was in den Errorlogs?
Kann aber auch sein das die SQL Abfragen stocken!?

[paintsplasher]

Hi,

also HE bietet ein komplettes Imagepacket an, sprich
wurde komplett neu installiert, und die Daten mit nem
Backup eingespielt, und nur die Pfade mussten geändert werden.
/home/httpd -> /var/www/

Das Errorlog sagt nur folgendes, das Files nicht existieren, also
nur das ein paar Bilder fehlen, mehr nicht.

[sledge0303]

Dann schau dir mal die Datenbank genauer an, wie der User oben angeraten hat. Sind die Abfragen verzögert kann natürlich der Indianer auch nicht schneller mit der Ausgabe sein.

[paintsplasher]

Also macht die Datenbank erst ne pause bevor sie weiter arbeitet,
oder wie darf ich das verstehn, wie stellt ihr fest das die
abfragen dort verzögert werden ?

[daemotron]

Füge folgendes in deine my.cnf ein, in der Sektion [mysqld]:

Code: Select all

[mysqld]
...
log-slow-queries=/var/log/mysqld/mysql-slow.log
long-query-time=2
...

Darauf achten, dass mysql in /var/log/mysqld schreiben darf. Danach MySQL neu starten und Logfile beobachten...

[paintsplasher]

Ja, ne log hab ich schon für sql hab ich das jetzt nicht eingetragen,
nur da steht nichts drin, so wie es ja sein soll;)

Code: Select all

060723 22:30:11  mysqld started
060723 22:30:11  InnoDB: Started; log sequence number 0 1168662
/usr/sbin/mysqld: ready for connections.
Version: '4.1.10a'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  SUSE MySQL RPM

[Roger Wilco]

Ja, ne log hab ich schon für sql

Aber kein Slow Query Log... :roll:

[paintsplasher]

Hi,

also hab es nun wie gesagt angelegt und folgendes Ergebnis:

Code: Select all

/usr/sbin/mysqld, Version: 4.1.10a-log. started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 060723 23:52:05
# User@Host: admin[admin] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use psa;
delete from sessions;
# Time: 060724  0:05:32
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 1  Rows_sent: 0  Rows_examined: 2452004
use fofi_forum;
SELECT p.pagerid FROM new_forum_pagertext AS p 
		LEFT JOIN new_forum_pagerreceipt AS r  
		USING (pagerid) WHERE r.pagerid is NULL;

# Time: 060724  1:00:30
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use fofi_forum;
UPDATE new_forum_user
						SET lastactivity = 1153695626
						WHERE userid = 4613;
# Time: 060724  1:01:06
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 16  Lock_time: 0  Rows_sent: 43  Rows_examined: 43
SELECT wordid, title FROM new_forum_word WHERE title IN ('','','fertig','http://img205.imageshack.us/img205/4913/foto565dv1.jpg','hast','du','hier','die','matten','die','das','große','loch','unten','in','der','tür','abdecken','mit','etwas','hinterbaut','wenn','nicht','hast','du','bei','praller','sommerhitze','ein','problem','da','ich','davon','ausgehe','dass','sich','die','matten','lösen','werden','da','sie','nur','eine','geringe','klebefläche','haben','das','würde','ich','nochmals','überarbeiten','und','natürlich','die','freien','blechstellen','noch','bekleben');

# Time: 060724  1:01:09
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
REPLACE INTO new_forum_postindex
				(wordid, postid, score, intitle)
				VALUES
				 (482, 328228, 1, 0), (3860, 328228, 2, 0), (125, 328228, 1, 0), (4628, 328228, 1, 0), (195385, 328228, 1, 0), (46377, 328228, 1, 0), (32189, 328228, 1, 0), (181464, 328228, 1, 0), (431, 328228, 1, 0), (22257, 328228, 1, 0), (3853, 328228, 1, 0), (29434, 328228, 1, 0), (48500, 328228, 1, 0), (202, 328228, 1, 0), (2702, 328228, 1, 0), (60561, 328228, 1, 0), (5323, 328228, 1, 0), (221710, 328228, 1, 0), (8913, 328228, 1, 0);
# Time: 060724  1:01:47
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 2  Rows_examined: 60
SELECT COUNT(*) AS total, folderid
		        FROM new_forum_pm AS pm
		        LEFT JOIN new_forum_pmtext AS pmtext USING(pmtextid)
		        WHERE userid = 2904
		        GROUP BY folderid;
# Time: 060724  1:05:22
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 5  Lock_time: 1  Rows_sent: 33  Rows_examined: 33
SELECT wordid, title FROM new_forum_word WHERE title IN ('der','muss','dafür','haften','gehe','auf','jeden','fall','zum','anwalt','und','melde','den','bei','ebay','die','kosten','vom','anwalt','wird','im','endeffekt','er','übernehmen','müssen','das','wollte','ich','hören','erst','zur','polizei','und','dann','zum','anwalt','oder','andersrum','oder','überhaupt');

# Time: 060724  1:05:23
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 8  Lock_time: 0  Rows_sent: 38  Rows_examined: 38
SELECT pmtextid, title, dateline
				FROM new_forum_pmtext AS pmtext
				WHERE fromuserid = 2050
				ORDER BY dateline DESC;
# Time: 060724  1:05:29
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 7  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
DELETE FROM new_forum_postindex WHERE wordid IN (0,7416,4932,195133,199494,165792,230933,202243,230946,196071,197957,1586,2224,230959,23,3630,14571,4848,196377,231023,274,6920,230978,33297,45,3538,201127,204129,231027,231282,6865,6131,230975,231018) AND postid = 298430;

# Time: 060724  1:05:36
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
DELETE FROM new_forum_postindex WHERE wordid IN (0,101535,22,67,71,3245,7783,30544,195133,231854,202243,230946,196071,197957,231311,939,1586,179824,231050,230959,129165,231677,1601,231305,231666,231074,194765,231201,196377,230915,198584,230948,45622,217078,3160,230943,230980,188064,195528,225076,134579,23954,230930,23636,331449,20022,1172,231123,16156,4614,199078,230997,231183,10830,196127,24826,201127,3670,207883,225423,3104,2971,36055) AND postid = 298783;

# Time: 060724  1:05:47
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 2452004
SELECT p.pagerid FROM new_forum_pagertext AS p 
		LEFT JOIN new_forum_pagerreceipt AS r  
		USING (pagerid) WHERE r.pagerid is NULL;

# Time: 060724  1:07:28
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 2  Rows_examined: 540
SELECT COUNT(*) AS total, folderid
		        FROM new_forum_pm AS pm
		        LEFT JOIN new_forum_pmtext AS pmtext USING(pmtextid)
		        WHERE userid = 115
		        GROUP BY folderid;
# Time: 060724  1:07:32
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 50  Rows_examined: 437
SELECT pm.*, pmtext.*
				, icon.title AS icontitle, icon.iconpath
			FROM new_forum_pm AS pm
			LEFT JOIN new_forum_pmtext AS pmtext ON(pmtext.pmtextid = pm.pmtextid)
			LEFT JOIN new_forum_icon AS icon ON(icon.iconid = pmtext.iconid)
			WHERE pm.userid=115 AND pm.folderid=0
			ORDER BY pmtext.dateline DESC
			LIMIT 0, 50;
# Time: 060724  1:08:32
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 920  Rows_examined: 920
SELECT pmtextid, title, dateline
				FROM new_forum_pmtext AS pmtext
				WHERE fromuserid = 115
				ORDER BY dateline DESC;
# Time: 060724  2:05:15
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 2452004
SELECT p.pagerid FROM new_forum_pagertext AS p 
		LEFT JOIN new_forum_pagerreceipt AS r  
		USING (pagerid) WHERE r.pagerid is NULL;

# Time: 060724  3:12:05
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 7  Lock_time: 2  Rows_sent: 11  Rows_examined: 11
use fofi_forum;
SELECT title, data
			FROM new_forum_datastore
			WHERE title IN ('','options','bitfields','forumcache','usergroupcache','stylecache','languagecache','products','pluginlist','cron','iconcache','mailqueue');

# Time: 060724  3:12:23
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 18  Lock_time: 9  Rows_sent: 1  Rows_examined: 0
SELECT 
			userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
			IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
			language.phrasegroup_forumdisplay AS phrasegroup_forumdisplay,
			language.phrasegroup_inlinemod AS phrasegroup_inlinemod,
			language.phrasegroup_global AS phrasegroup_global,
			language.options AS lang_options,
			language.languagecode AS lang_code,
			language.charset AS lang_charset,
			language.locale AS lang_locale,
			language.imagesoverride AS lang_imagesoverride,
			language.dateoverride AS lang_dateoverride,
			language.timeoverride AS lang_timeoverride,
			language.registereddateoverride AS lang_registereddateoverride,
			language.calformat1override AS lang_calformat1override,
			language.calformat2override AS lang_calformat2override,
			language.logdateoverride AS lang_logdateoverride,
			language.decimalsep AS lang_decimalsep,
			language.thousandsep AS lang_thousandsep
			
		FROM new_forum_user AS user
		LEFT JOIN new_forum_userfield AS userfield ON (user.userid = userfield.userid)
		LEFT JOIN new_forum_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN new_forum_language AS language ON (language.languageid = IF(user.languageid = 0, 2, user.languageid)) 
		
		WHERE user.userid = 4567;
# Time: 060724  3:12:33
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 8  Lock_time: 2  Rows_sent: 43  Rows_examined: 43
SELECT title, template
			FROM new_forum_template
			WHERE templateid IN (1235,1246,3147,1236,1240,2950,2952,1238,1239,2956,2958,2944,2955,2945,2946,2957,2959,3179,3135,3475,1247,1791,3054,1241,3021,3178,3181,3189,3040,3041,3042,3043,3149,3194,3193,3195,3198,1252,3427,2249,2250,3428,3434);
# Time: 060724  3:13:12
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 31  Lock_time: 1  Rows_sent: 1  Rows_examined: 1715
SELECT COUNT(*) AS threads,
		SUM(IF(thread.lastpost >= 1152493917 AND open <> 10, 1, 0)) AS newthread
		
		FROM new_forum_thread AS thread
		
		WHERE forumid = 25
			AND sticky = 0
			 AND visible IN (1,2);
# Time: 060724  3:13:28
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 16  Lock_time: 9  Rows_sent: 32  Rows_examined: 69
SELECT 
			IF(votenum >= 1, votenum, 0) AS votenum,
			IF(votenum >= 1 AND votenum > 0, votetotal / votenum, 0) AS voteavg,
		 
			thread.threadid, thread.title AS threadtitle, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
			thread.lastpost, lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach,
			hiddencount
			, NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
			, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason, threadread.readtime AS threadread
			
		FROM new_forum_thread AS thread
			LEFT JOIN new_forum_deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
			 LEFT JOIN new_forum_subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 4567 AND canview = 1)
			 LEFT JOIN new_forum_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 4567)
			
			
			
		WHERE thread.threadid IN (0,26292,14263,23476,26248,25879,26176,19221,26267,26203,5200,26252,26107,26210,26238,25853,268,24638,26199,26181,25968,7523,26205,25938,26070,26084,26115,26072,26066,26157,26089,10877,23980) 
		ORDER BY sticky DESC, thread.lastpost DESC;
# Time: 060724  3:13:39
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 8  Lock_time: 2  Rows_sent: 1  Rows_examined: 0
SELECT user.avatarid, user.avatarrevision, avatarpath, NOT ISNULL(customavatar.userid) AS hascustom, customavatar.dateline,
			customavatar.width, customavatar.height
		FROM new_forum_user AS user
		LEFT JOIN new_forum_avatar AS avatar ON avatar.avatarid = user.avatarid
		LEFT JOIN new_forum_customavatar AS customavatar ON customavatar.userid = user.userid
		WHERE user.userid = 1;
# Time: 060724  3:13:48
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 9  Lock_time: 0  Rows_sent: 1  Rows_examined: 19
SELECT COUNT(*) AS count
					FROM new_forum_post AS post
					LEFT JOIN new_forum_threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 1)
					WHERE dateline >= 1153691759 AND dateline > IF(threadread.readtime IS NULL, 1152494000, threadread.readtime);
# Time: 060724  3:13:52
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 24  Lock_time: 2  Rows_sent: 0  Rows_examined: 0
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
			FROM new_forum_post AS post
			WHERE post.userid = 4567 AND
			post.visible = 1 AND
			post.threadid IN (0,26292,14263,23476,26248,25879,26176,19221,26267,26203,5200,26252,26107,26210,26238,25853,268,24638,26199,26181,25968,7523,26205,25938,26070,26084,26115,26072,26066,26157,26089,10877,23980)
			GROUP BY threadid;
# Time: 060724  3:13:57
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE new_forum_user
						SET
							lastvisit = lastactivity,
							lastactivity = 1153703517
						WHERE userid = 4567;
# Time: 060724  3:14:00
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 7  Lock_time: 4  Rows_sent: 2  Rows_examined: 13
SELECT IF(votenum >= 1, votenum, 0) AS numbvote, IF(votenum >= 1 AND votenum != 0, votetotal / votenum, 0) AS voteavg,
				thread.threadid, post.title, thread.replycount, postusername, postuserid, thread.dateline AS postdateline, thread.lastposter, thread.lastpost, IF(views<=thread.replycount, thread.replycount+1, views) AS views, thread.forumid, post.postid, pagetext, allowsmilie
				
				,thread.iconid AS threadiconid, iconpath AS threadiconpath
				, avatarpath, NOT ISNULL(filedata) AS hascustom, customavatar.dateline AS avatardateline, avatarrevision
				, NOT ISNULL(subscribethread.subscribethreadid) AS subscribed 
				FROM new_forum_thread AS thread
				LEFT JOIN new_forum_post AS post ON (post.postid = thread.firstpostid)

				LEFT JOIN new_forum_post_parsed AS post_parsed ON (post_parsed.postid = post.postid AND post_parsed.styleid_code = -1 AND post_parsed.styleid_html = -1 AND post_parsed.styleid_php = -1 AND post_parsed.styleid_quote = -1)

				LEFT JOIN new_forum_icon AS icon ON (icon.iconid = thread.iconid)
				LEFT JOIN new_forum_user AS user ON (user.userid = post.userid)
				
				
				
					LEFT JOIN new_forum_avatar as avatar ON (avatar.avatarid = user.avatarid)
					LEFT JOIN new_forum_customavatar as customavatar ON (customavatar.userid = user.userid)
				
				 LEFT JOIN new_forum_subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = '1')
				WHERE thread.threadid IN(25201,25850)
				
				
				GROUP BY post.postid
				ORDER BY sticky DESC,thread.dateline DESC
				LIMIT 3;
# Time: 060724  3:14:09
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 9  Rows_examined: 9
SELECT title, data
			FROM new_forum_datastore
			WHERE title IN ('','options','bitfields','forumcache','usergroupcache','stylecache','languagecache','products','pluginlist','cron');
# Time: 060724  3:14:18
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 7  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE new_forum_thread
		SET views = views +
		CASE
			 WHEN threadid = 7 THEN 4 WHEN threadid = 448 THEN 3 WHEN threadid = 1988 THEN 2 WHEN threadid = 5209 THEN 6 WHEN threadid = 7202 THEN 1 WHEN threadid = 7282 THEN 1 WHEN threadid = 10089 THEN 2 WHEN threadid = 12739 THEN 1 WHEN threadid = 13660 THEN 1 WHEN threadid = 16482 THEN 1 WHEN threadid = 16883 THEN 1 WHEN threadid = 19148 THEN 1 WHEN threadid = 19275 THEN 1 WHEN threadid = 19471 THEN 1 WHEN threadid = 21453 THEN 1 WHEN threadid = 21697 THEN 1 WHEN threadid = 22564 THEN 1 WHEN threadid = 23915 THEN 1 WHEN threadid = 24004 THEN 3 WHEN threadid = 24245 THEN 1 WHEN threadid = 24318 THEN 1 WHEN threadid = 24579 THEN 1 WHEN threadid = 25173 THEN 1 WHEN threadid = 25201 THEN 1 WHEN threadid = 25525 THEN 2 WHEN threadid = 25535 THEN 3 WHEN threadid = 25861 THEN 4 WHEN threadid = 25938 THEN 1 WHEN threadid = 25947 THEN 1 WHEN threadid = 26051 THEN 1 WHEN threadid = 26052 THEN 1 WHEN threadid = 26085 THEN 1 WHEN threadid = 26131 THEN 1 WHEN threadid = 26138 THEN 4 WHEN threadid = 26152 THEN 4 WHEN threadid = 26176 THEN 1 WHEN threadid = 26196 THEN 1 WHEN threadid = 26208 THEN 3 WHEN threadid = 26230 THEN 1 WHEN threadid = 26235 THEN 1 WHEN threadid = 26246 THEN 2 WHEN threadid = 26256 THEN 2 WHEN threadid = 26267 THEN 1 WHEN threadid = 26275 THEN 1 WHEN threadid = 26276 THEN 1 WHEN threadid = 26286 THEN 1 WHEN threadid = 26295 THEN 1 WHEN threadid = 26302 THEN 1 WHEN threadid = 26310 THEN 1
			ELSE 0
		END
		WHERE threadid IN (0,7,448,1988,5209,7202,7282,10089,12739,13660,16482,16883,19148,19275,19471,21453,21697,22564,23915,24004,24245,24318,24579,25173,25201,25525,25535,25861,25938,25947,26051,26052,26085,26131,26138,26152,26176,26196,26208,26230,26235,26246,26256,26267,26275,26276,26286,26295,26302,26310);
# Time: 060724  3:14:43
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 15  Lock_time: 0  Rows_sent: 12  Rows_examined: 1739
SELECT thread.threadid
			FROM new_forum_thread AS thread
			
			LEFT JOIN new_forum_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
			INNER JOIN new_forum_forum AS forum ON (forum.forumid = thread.forumid)
			LEFT JOIN new_forum_forumread AS forumread ON (forumread.forumid = forum.forumid AND forumread.userid = 1)
		
			WHERE thread.forumid IN(9, 14, 37, 3, 22, 23, 24, 25, 12, 11, 20, 17, 32, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 7, 16, 30, 35, 34, 4, 13, 15, 33, 21, 19, 36, 18, 5, 26, 29, 6, 27, 28)
				
			AND thread.lastpost > IF(threadread.readtime IS NULL, 1152494059, threadread.readtime)
			AND thread.lastpost > IF(forumread.readtime IS NULL, 1152494059, forumread.readtime)
			AND thread.lastpost > 1152494059
		
				AND thread.visible IN (0,1,2)
				AND thread.sticky IN (0,1)
				AND thread.open <> 10
			ORDER BY thread.lastpost DESC
			LIMIT 500;
# Time: 060724  3:14:46
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 1
SELECT text, languageid
			FROM new_forum_phrase
			WHERE phrasetypeid = 2000
				AND varname = 'search' AND languageid IN (-1, 0, 2);
# Time: 060724  3:14:51
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 3  Lock_time: 0  Rows_sent: 12  Rows_examined: 28
SELECT 
				thread.threadid, thread.threadid AS postid, thread.title AS threadtitle, thread.iconid AS threadiconid, thread.dateline, thread.forumid,
				thread.replycount, IF(thread.views=0, thread.replycount+1, thread.views) as views, thread.sticky,
				thread.pollid, thread.open, thread.lastpost AS postdateline, thread.visible, thread.hiddencount,
				thread.lastpost, thread.lastposter, thread.attach, thread.postusername, thread.forumid,

				deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,

				user.userid AS postuserid
				, NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
				, threadread.readtime AS threadread
				
			FROM new_forum_thread AS thread
			LEFT JOIN new_forum_user AS user ON(user.userid = thread.postuserid)

			LEFT JOIN new_forum_deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
			 LEFT JOIN new_forum_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)

			 LEFT JOIN new_forum_subscribethread AS subscribethread
				ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 1 AND canview = 1)
			
			
			WHERE thread.threadid IN
		(26302, 26246, 26138, 20514, 26141, 26163, 26256, 26292, 26310, 26284, 26277, 26006);
# Time: 060724  3:15:07
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
DELETE FROM new_forum_threadread
	WHERE readtime < 1152494093;
# Time: 060724  3:15:23
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 6  Lock_time: 2  Rows_sent: 0  Rows_examined: 2454219
SELECT p.pagerid FROM new_forum_pagertext AS p 
		LEFT JOIN new_forum_pagerreceipt AS r  
		USING (pagerid) WHERE r.pagerid is NULL;
# Time: 060724  3:16:06
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 5  Lock_time: 2  Rows_sent: 1  Rows_examined: 2
SELECT IF(visible = 2, 1, 0) AS isdeleted,
			

			NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed, emailupdate, folderid,threadrate.vote,threadread.readtime AS threadread, forumread.readtime AS forumread,
			thread.*
			FROM new_forum_thread AS thread
			
			LEFT JOIN new_forum_subscribethread AS subscribethread ON (subscribethread.threadid = thread.threadid AND subscribethread.userid = 1 AND canview = 1)
			LEFT JOIN new_forum_threadrate AS threadrate ON (threadrate.threadid = thread.threadid AND threadrate.userid = 1)
			
				LEFT JOIN new_forum_threadread AS threadread ON (threadread.threadid = thread.threadid AND threadread.userid = 1)
				LEFT JOIN new_forum_forumread AS forumread ON (forumread.forumid = thread.forumid AND forumread.userid = 1)
			
			WHERE thread.threadid = 26246;
# Time: 060724  3:16:10
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 60  Rows_examined: 60
SELECT title, template
			FROM new_forum_template
			WHERE templateid IN (2944,3179,2970,2971,2973,2980,2979,1243,3069,3070,3072,3071,3073,0,3074,3075,2800,1244,3080,2882,2884,2886,2888,1245,3103,3106,3107,3104,3062,3061,3063,3067,3065,3134,2921,2922,2923,3475,1247,1791,3054,1241,3021,3178,3181,3189,3040,3041,3042,3043,3149,3194,3193,3195,3198,1252,3427,2249,2250,3428,3434);
# Time: 060724  3:16:19
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 6  Lock_time: 0  Rows_sent: 149  Rows_examined: 29325
SELECT user.userid, user.options, user.username, user.usergroupid, user.displaygroupid, threadread.readtime, 
	groupa.opentag as opentaga, groupa.closetag as closetaga, groupb.opentag as opentagb, groupb.closetag as closetagb
	FROM new_forum_threadread as threadread
	LEFT JOIN new_forum_user as user ON user.userid = threadread.userid
	LEFT JOIN new_forum_usergroup as groupa ON user.usergroupid = groupa.usergroupid
	LEFT JOIN new_forum_usergroup as groupb ON user.displaygroupid = groupb.usergroupid
	WHERE threadread.threadid = 26246 ORDER BY username;
# Time: 060724  3:16:38
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 18  Lock_time: 0  Rows_sent: 7  Rows_examined: 41
SELECT
			post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
			user.*, userfield.*, usertextfield.*,
			icon.title as icontitle, icon.iconpath,
			avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,
			
			deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
			editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
			editlog.reason AS edit_reason,
			post_parsed.pagetext_html, post_parsed.hasimages,
			IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
			
			
		FROM new_forum_post AS post
		LEFT JOIN new_forum_user AS user ON(user.userid = post.userid)
		LEFT JOIN new_forum_userfield AS userfield ON(userfield.userid = user.userid)
		LEFT JOIN new_forum_usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
		LEFT JOIN new_forum_icon AS icon ON(icon.iconid = post.iconid)
		LEFT JOIN new_forum_avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN new_forum_customavatar AS customavatar ON(customavatar.userid = user.userid)
			LEFT JOIN new_forum_deletionlog AS deletionlog ON(post.postid = deletionlog.primaryid AND type = 'post')
		LEFT JOIN new_forum_editlog AS editlog ON(editlog.postid = post.postid)
		LEFT JOIN new_forum_post_parsed AS post_parsed ON(post_parsed.postid = post.postid AND post_parsed.styleid_code = -1 AND post_parsed.styleid_html = -1 AND post_parsed.styleid_php = -1 AND post_parsed.styleid_quote = -1)
			
		WHERE post.postid IN (0,328128,328130,328137,328140,328200,328202,328231)
		ORDER BY post.dateline;
# Time: 060724  3:17:47
# User@Host: fofi_forum_88[fofi_forum_88] @ localhost []
# Query_time: 4  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
SELECT 
			userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate,
			IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
			language.phrasegroup_holiday AS phrasegroup_holiday,
			language.phrasegroup_global AS phrasegroup_global,
			language.options AS lang_options,
			language.languagecode AS lang_code,
			language.charset AS lang_charset,
			language.locale AS lang_locale,
			language.imagesoverride AS lang_imagesoverride,
			language.dateoverride AS lang_dateoverride,
			language.timeoverride AS lang_timeoverride,
			language.registereddateoverride AS lang_registereddateoverride,
			language.calformat1override AS lang_calformat1override,
			language.calformat2override AS lang_calformat2override,
			language.logdateoverride AS lang_logdateoverride,
			language.decimalsep AS lang_decimalsep,
			language.thousandsep AS lang_thousandsep
			
		FROM new_forum_user AS user
		LEFT JOIN new_forum_userfield AS userfield ON (user.userid = userfield.userid)
		LEFT JOIN new_forum_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) INNER JOIN new_forum_language AS language ON (language.languageid = IF(user.languageid = 0, 2, user.languageid)) 
		
		WHERE user.userid = 3391;

[daemotron]

Autsch, ganz schön schnell vollgelaufen... Entweder geht deine Forensoftware saumäßig mit SQL um, oder Dein MySQL-Server bietet noch erhebliches Optimierungspotenzial. Wahrscheinlich beides...

Code: Select all

Query_time: 18  Lock_time: 9

Da hast Du Deine knapp 30 Sekunden...

Wenn Du nicht auf eine schlankere Foren-Software ausweichen kannst/willst, würde ich mir mal die my.cnf vornehmen. Schau Dir mal die Posts von Joe User und isotopp im DB-Forum dazu an. Eventuell musst Du die Caches ein wenig zurückschrauben, da der RootDS wahrscheinlich keine 2GB RAM hat...

[paintsplasher]

Hi,

das Problem war, dass die MaxClients beim SuSE 9.3-Template standardmaessig auf 10 gesetzt sind. Ich habe dies nun auf 50 erhoeht und den Apache neugestartet.

Bisher läuft es auch wirklich schnell, auch die SlowQuery Log
füllt sich bisher nicht !