Hohe Last am Server - wie config optimieren

Post by **don** » 2006-11-20 15:27

Hi,

ich habe folgenden Server:
CPU: pIV 3.0 HT
RAM: 1 GB

Es ist ein vBulletin Forum mit ca 120 Usern im Durchschnitt online.

Und die Auslastung sieht im Durchschnitt so aus:

top - 15:19:00 up 26 days, 20:04, 1 user, load average: 14.46, 8.69, 7.04
Tasks: 160 total, 12 running, 148 sleeping, 0 stopped, 0 zombie
Cpu(s): 90.5% us, 9.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.2% hi, 0.3% si
Mem: 905352k total, 787852k used, 117500k free, 49512k buffers
Swap: 771112k total, 90496k used, 680616k free, 236444k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28602 mysql 15 0 146m 49m 4904 S 99.9 5.6 18:13.06 mysqld
28198 www-data 16 0 60080 17m 7712 S 14.2 1.9 0:13.25 apache2
31856 www-data 16 0 59900 16m 7344 S 8.4 1.9 0:04.49 apache2
27432 www-data 15 0 60308 16m 7160 S 3.2 1.9 0:13.32 apache2
25412 www-data 16 0 59804 18m 9252 R 2.9 2.1 0:23.71 apache2
31933 www-data 16 0 59984 15m 6568 R 2.9 1.8 0:03.06 apache2
32687 www-data 16 0 59668 15m 6660 R 2.9 1.8 0:01.98 apache2
1952 www-data 15 0 57216 11m 4924 S 2.9 1.3 0:00.14 apache2
27448 www-data 16 0 61020 19m 9488 R 2.6 2.2 0:12.50 apache2
31932 www-data 15 0 59996 15m 6544 S 2.6 1.8 0:03.18 apache2
32676 www-data 15 0 59884 15m 6024 S 2.6 1.7 0:01.54 apache2
1953 www-data 15 0 60376 14m 5276 S 2.6 1.7 0:01.05 apache2
26662 www-data 15 0 59636 17m 8556 S 2.3 2.0 0:15.75 apache2
31851 www-data 15 0 59996 16m 6796 S 2.3 1.8 0:04.44 apache2
28798 www-data 15 0 59984 18m 8888 S 1.6 2.1 0:08.90 apache2
32658 www-data 15 0 60192 16m 7416 S 1.6 1.9 0:04.60 apache2
1948 www-data 16 0 59492 13m 4940 R 1.6 1.5 0:00.15 apache2
29330 www-data 15 0 59888 17m 8096 S 1.3 2.0 0:09.15 apache2
32683 www-data 16 0 60332 16m 7024 R 1.3 1.9 0:02.94 apache2
27433 www-data 15 0 60324 18m 8584 S 1.0 2.1 0:15.00 apache2
27944 www-data 15 0 60212 17m 8480 S 1.0 2.0 0:11.11 apache2
31855 www-data 15 0 60160 16m 7360 S 1.0 1.9 0:03.66 apache2
1940 www-data 15 0 57180 10m 4352 S 1.0 1.2 0:00.03 apache2
1941 www-data 15 0 58596 13m 5884 S 1.0 1.6 0:00.44 apache2
1958 www-data 16 0 58840 14m 5792 R 1.0 1.6 0:00.42 apache2
27942 www-data 15 0 60340 17m 7676 S 0.6 2.0 0:08.68 apache2
32684 www-data 15 0 59864 15m 6044 S 0.6 1.7 0:01.66 apache2
1957 www-data 15 0 57056 11m 4924 S 0.6 1.3 0:00.09 apache2
26669 www-data 15 0 59764 19m 10m S 0.3 2.3 0:21.97 apache2
26675 www-data 15 0 60228 18m 8788 S 0.3 2.1 0:16.03 apache2
32054 www-data 15 0 60084 16m 6976 S 0.3 1.8 0:03.77 apache2

Hatte schon testweise mal die MySQL Conf genommen von CaptainCrunch. Aber da war es eher noch schlimmer.

Im Apachen habe ich folgendes eingestellt:

StartServers 5
MinSpareServers 20
MaxSpareServers 50
MaxClients 200
MaxRequestsPerChild 1000
ServerLimit 200

So wie ich das sehe ist die CPU zu lahm und der RAM sollte langen oder?
Oder kann ich da noch was optimieren?

Danke schon mal.

Grüße Don

Post by **metrax** » 2006-11-20 19:10

Also als erstes würde ich mal abchecken warum mysql so viel last macht.

Sehr gut eigenen tut sich dafür das tool "mytop", optional halt der Query "SHOW PROCESSLIST". Ansonsten halt auch die MySQL Option slow-query-log. Wenn du schuldige Queries gefunden hast, solltest du diese optimieren.

Wenn MySQL nicht mehr soviel Last macht, sollte die Reaktionszeit des Apaches auch zurückgehen. Falls nicht bräuchten wir mehr Informationen über die Konfiguration und die Installationsweise, ggf. PHP Version & Co.

Post by **Joe User** » 2006-11-20 19:55

Nutzt vB noch immer persistant_connections per Default? -> In der php.ini deaktivieren.

Post by **don** » 2006-11-20 20:16

Moin,

Die persistente Verbindungen sind auch bereits in der php.ini deaktiviert.

mysql.allow_persistent = Off

Installiert ist der Apache 2 und php als mod_php.
Das sagt mytop:

MySQL on localhost (5.0.26-Debian_0.dotdeb.1-log) up 0+05:12:10 [20:12:28]
Queries: 1.7M qps: 97 Slow: 0.0 Se/In/Up/De(%): 54/00/00/00
qps now: 71 Slow qps: 0.0 Threads: 10 ( 5/ 0) 49/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.0/ 6.8 Now in/out: 3.6/588.8

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
77086 root localhost test 0 Query show full processlist
77355 sam1 localhost usr_sam1_2 0 Query SELECT postid FROM mob_post_thanks WHERE (postid=19959) AND (userid=18251)
77369 sam1 localhost usr_sam1_2 0 Query SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.
77370 sam1 localhost usr_sam1_2 0 Query SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.
77371 sam1 localhost usr_sam1_2 0 Query SELECT mob_thread.featured, threadid, firstpostid, mob_thread.title, MAX(mob_attachment.attachmentid)AS at
77372 sam1 localhost usr_sam1_2 0 Query SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_at
77338 sam1 localhost usr_sam1_2 3 Query SELECT postid FROM mob_post_thanks WHERE (postid=47680) AND (userid=5988)
74246 gld localhost greylistin 739 Sleep

Zur Zeit sind ca 160 User online.

Dachte ja eigentlich das die slow_queries nicht so wild sind, aber sind doch ein paar:

/usr/sbin/mysqld, Version: 5.0.26-Debian_0.dotdeb.1-log. started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 061120 15:16:56
# User@Host: sam1[sam1] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
use usr_sam1_2;
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164032204
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 15:17:43
# User@Host: sam1[sam1] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164032251
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 16:46:17
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164037564
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:09:29
# User@Host: sam1[sam1] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164038957
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:09:30
# User@Host: sam1[sam1] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164038958
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164038957
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:22:40
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 70535
SELECT postid FROM mob_post_thanks WHERE (postid=20588) AND (userid=1733);
# Time: 061120 17:37:59
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164040666
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:38:19
# User@Host: sam1[sam1] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 5 Rows_examined: 3267
SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline, mob_thread.views FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164040682 ORDER BY mob_thread.views DESC LIMIT 5;
# Time: 061120 17:40:47
# User@Host: sam1[sam1] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 5 Rows_examined: 10615
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164040835
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:49:25
# User@Host: sam1[sam1] @ localhost []
# Query_time: 15 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164041350
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:50:10
# User@Host: sam1[sam1] @ localhost []
# Query_time: 23 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164041387
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:50:23
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 3268
SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline, mob_thread.views FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164041410 ORDER BY mob_thread.views DESC LIMIT 5;
# Time: 061120 17:55:16
# User@Host: sam1[sam1] @ localhost []
# Query_time: 15 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_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 mob_user AS user
LEFT JOIN mob_userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN mob_usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN mob_language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))

WHERE user.userid = 2;
# Time: 061120 17:56:40
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164041789
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 17:58:40
# User@Host: sam1[sam1] @ localhost []
# Query_time: 14 Lock_time: 0 Rows_sent: 0 Rows_examined: 70552
SELECT postid FROM mob_post_thanks WHERE (postid=18) AND (userid=1809);
# Time: 061120 18:01:06
# User@Host: sam1[sam1] @ localhost []
# Query_time: 18 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164042048
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:01:16
# User@Host: sam1[sam1] @ localhost []
# Query_time: 15 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164042061
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:08:42
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 8251
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1'AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164042508 ORDER BY RAND() LIMIT 5;
# Time: 061120 18:18:37
# User@Host: sam1[sam1] @ localhost []
# Query_time: 14 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043103
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:24:23
# User@Host: sam1[sam1] @ localhost []
# Query_time: 20 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043443
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:27:09
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043616
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:27:18
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043625
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:27:19
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043626
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 1 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043626
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:27:25
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043634
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 18:27:38
# User@Host: sam1[sam1] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 5 Rows_examined: 3268
SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline, mob_thread.views FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164043641 ORDER BY mob_thread.views DESC LIMIT 5;
# Time: 061120 18:43:23
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 10618
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164044592
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 19:14:50
# User@Host: sam1[sam1] @ localhost []
# Query_time: 19 Lock_time: 0 Rows_sent: 5 Rows_examined: 3268
SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline, mob_thread.views FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164046471 ORDER BY mob_thread.views DESC LIMIT 5;
# Time: 061120 19:15:42
# User@Host: sam1[sam1] @ localhost []
# Query_time: 15 Lock_time: 0 Rows_sent: 5 Rows_examined: 10621
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164046527
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 19:15:48
# User@Host: sam1[sam1] @ localhost []
# Query_time: 11 Lock_time: 0 Rows_sent: 5 Rows_examined: 10621
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164046537
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 19:51:43
# User@Host: sam1[sam1] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 5 Rows_examined: 3270
SELECT gallery,mob_thread.featured, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline, mob_thread.views FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164048690 ORDER BY mob_thread.views DESC LIMIT 5;
# Time: 061120 19:54:35
# User@Host: sam1[sam1] @ localhost []
# Query_time: 26 Lock_time: 0 Rows_sent: 5 Rows_examined: 10624
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164048849
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;
# Time: 061120 20:06:30
# User@Host: sam1[sam1] @ localhost []
# Query_time: 15 Lock_time: 0 Rows_sent: 5 Rows_examined: 10624
SELECT gallery, threadid, firstpostid, mob_thread.title, attachmentid, mob_attachment.dateline FROM mob_attachment, mob_thread, mob_forum
WHERE gallery='1' AND mob_thread.featured!='1' AND mob_thread.forumid=mob_forum.forumid AND mob_thread.firstpostid = mob_attachment.postid AND mob_attachment.dateline < 1164049575
GROUP BY mob_thread.threadid
ORDER BY mob_attachment.attachmentid DESC LIMIT 5;

Post by **metrax** » 2006-11-20 20:19

die ausgabe von mytop bringt uns nix ... du musst halt beobachten welche querys mehrere sekunden drinnen hängen bleibeb z.b.

läuft auf dem rehcner nur das board?

Post by **don** » 2006-11-20 20:22

Jupp auf dem Rechner läuft nur das Board.

Post by **metrax** » 2006-11-20 20:47

mhhh, dann zeig mal die my.cnf her

Post by **don** » 2006-11-20 20:54

Kannst du haben:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/serve ... ables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking

#
# For compatibility to other Debian packages that still use
# libmysqlclient10 and libmysqlclient12.
#old_passwords = 1
#
# Instead of skip-networking you can listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
skip-networking
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
max_connections=250
#
# * Query Cache Configuration
#
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql.log
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
log-slow-queries = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size = 104857600
#binlog-do-db = include_database_name
#binlog-ignore-db = include_database_name
#
# * BerkeleyDB
#
# According to an MySQL employee the use of BerkeleyDB is now discouraged
# and support for it will probably cease in the next versions.
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the ndbd storage daemons,
# not from the ndb_mgmd management daemon.
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1

Mit der config läuft die Kiste aber performanter als mit der Congig aus dem MySQL Forum hier.

P.S. Hast du die slow_querys gesehen? Kann es an denen liegen? Ich wüsste nur nicht wie man die noch optimieren könnte.

Post by **Joe User** » 2006-11-20 21:16

Ich habe die my.cnf im gepinnten Thread gerade etwas überarbeitet, falls Du sie nochmal testen möchtest, Feedback ist durchaus erwünscht ;)

Post by **don** » 2006-11-21 06:56

Moin,

die geänderte conf teste ich gerne heute.
Allerdings habe ich beim letzten austesten der conf nur Probleme gehabt, weil du da alles auf urf-8 gestellt hast und dann alle Sonderzeichn im Forum (auch wenn ich die Zeichenkodierung passend eingestellt habe im Header der Seite) etwas verrückt gespielt hatten.
Und ich konnte anfangs keine DBs mehr in phpmyadmin sehen.

Post by **Joe User** » 2006-11-21 11:56

Du kannst utf8 durch latin1 ersetzen, dann funktioniert es auch mit älteren Datenbanken.

Post by **don** » 2006-11-21 12:18

Aber brauche ich denn überhaupt diese Zeichnsatzeinstellung?
Ich meine über kurz oder lang wird eh utf-8 der Standard sein (denke ich mal).

Post by **don** » 2007-06-07 11:10

So nun bin ich endlich mal dazu gekommen, die mysql Einstellungen zu testen ;) Bin mal gespanbt, wie die sich entwickeln.
Mittlerweile sind es aber ein paar mehr User :D

Post by **EdRoxter** » 2007-06-07 13:59

Ich würde mal die MaxRequestsPerChild runterschrauben, die haben mir auch schonmal einige Freezes beschert.

Post by **oxygen** » 2007-06-07 15:49

@EdRoxter: 1000 ist eigentlich schon sehr wenig. Normal wäre eigentlich 15000 oder 20000.

Du kannst mal das MySQL Tuning Script probieren: http://www.day32.com/MySQL/
damit sollten sich die MySQL Parameter leicht verbessern lassen.

Post by **don** » 2007-06-07 18:59

Hmm und was genau treibt das Script?
Ich habe eh gerade auf den Lighttpd umgestellt, da ist mir die Apache Config nicht mehr so wichtig.

RootForum Community

Hohe Last am Server - wie config optimieren

Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren

Re: Hohe Last am Server - wie config optimieren