Bonjour
Je gère un forum smf depuis pres de 3 ans et je viens de recevoir un mail du support de mon hebergeur 1&1 m'informant que ma base de donnée provoquait des lenteurs
Voici la log que j'ai reçu
rdb272:~# zgrep dbo182763563 /var/log/anna.log*
/var/log/anna.log:dbo182763563@infong162: 9157 Queries: 6899 Selects,
471 Ins, 1413 Upd, 329 Del, 841 Connects
/var/log/anna.log.0:dbo182763563@infong162: 9895 Queries: 7475 Selects,
531 Ins, 1527 Upd, 328 Del, 949 Connects
/var/log/anna.log.1.gz:dbo182763563@infong162: 7012 Queries: 5164
Selects, 525 Ins, 915 Upd, 319 Del, 668 Connects
/var/log/anna.log.2.gz:dbo182763563@infong162: 6532 Queries: 4915
Selects, 277 Ins, 924 Upd, 265 Del, 577 Connects
/var/log/anna.log.3.gz:dbo182763563@infong162: 8731 Queries: 6623
Selects, 488 Ins, 1258 Upd, 313 Del, 789 Connects
/var/log/anna.log.4.gz:dbo182763563@infong162: 9087 Queries: 7130
Selects, 415 Ins, 1207 Upd, 257 Del, 882 Connects
/var/log/anna.log.5.gz:dbo182763563@infong162: 5353 Queries: 4160
Selects, 283 Ins, 677 Upd, 186 Del, 503 Connects
/var/log/anna.log.6.gz:dbo182763563@infong162: 8382 Queries: 6416
Selects, 436 Ins, 1142 Upd, 358 Del, 714 Connects
rdb272:~# myslowana /db/logs/mysql.slowlog.?.gz -e 'user dbo182763563 '
-s | head -n 20
user count query_time lock_time rows_sent rows_examined
dbo182763563 15 108[14](7.20) 0[0](0.00) 487[286](32.47)
19669[2466](1311.27)
dbo182763563 4 SELECT m.subject, t.numReplies, t.ID_BOARD, t.ID_TOPIC,
b.name FROM (smf_topics AS t, smf_messages AS m, smf_boards AS b) WHERE
m.ID_MSG = %% AND FIND_IN_SET(-%%, b.memberGroups) AND t.ID_BOARD = %%
LIMIT %%;
dbo182763563 3 SELECT HOUR(FROM_UNIXTIME(posterTime + %%)) AS hour,
COUNT(*) AS postCount FROM smf_messages WHERE ID_MEMBER = %% GROUP BY
hour;
dbo182763563 2 SELECT ID_MSG, ID_MEMBER FROM smf_messages WHERE ID_TOPIC
= %% LIMIT %%;
dbo182763563 1 SELECT t.ID_TOPIC, t.numReplies, t.locked, t.numViews,
t.isSticky, t.ID_POLL, %% AS new_from, t.ID_LAST_MSG, ml.posterTime AS
lastPosterTime, ml.ID_MSG_MODIFIED, ml.subject AS lastSubject, ml.icon
AS lastIcon, ml.posterName AS lastMemberName, ml.ID_MEMBER AS
lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.subject AS
firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS
firstDisplayName, LEFT(ml.body, %%) AS lastBody, LEFT(mf.body, %%) AS
firstBody, ml.smileysEnabled AS lastSmileys, mf.smileysEnabled AS
firstSmileys FROM (smf_topics AS t, smf_messages AS ml, smf_messages AS
mf) LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = %%) LEFT JOIN
smf_members AS memf ON (memf.ID_MEMBER = %%) WHERE t.ID_BOARD = %% AND
ml.ID_MSG = %% AND mf.ID_MSG = %% ORDER BY isSticky,
IFNULL(memf.realName, mf.posterName) DESC LIMIT %%;
dbo182763563 1 SELECT t.ID_TOPIC FROM (smf_topics AS t, smf_messages AS
mf) LEFT JOIN smf_members AS memf ON (memf.ID_MEMBER = %%) WHERE
t.ID_BOARD = %% AND mf.ID_MSG = %% ORDER BY isSticky DESC,
IFNULL(memf.realName, mf.posterName) DESC LIMIT %%;
dbo182763563 1 SELECT subject, posterTime, body, IFNULL(mem.realName,
posterName) AS posterName FROM smf_messages AS m LEFT JOIN smf_members
AS mem ON (mem.ID_MEMBER = %%) WHERE ID_TOPIC = %% ORDER BY ID_MSG;
dbo182763563 1 SELECT t.ID_TOPIC, t.numReplies, t.locked, t.numViews,
t.isSticky, t.ID_POLL, %% AS new_from, t.ID_LAST_MSG, ml.posterTime AS
lastPosterTime, ml.ID_MSG_MODIFIED, ml.subject AS lastSubject, ml.icon
AS lastIcon, ml.posterName AS lastMemberName, ml.ID_MEMBER AS
lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.subject AS
firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS
firstDisplayName, LEFT(ml.body, %%) AS lastBody, LEFT(mf.body, %%) AS
firstBody, ml.smileysEnabled AS lastSmileys, mf.smileysEnabled AS
firstSmileys FROM (smf_topics AS t, smf_messages AS ml, smf_messages AS
mf) LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = %%) LEFT JOIN
smf_members AS memf ON (memf.ID_MEMBER = %%) WHERE t.ID_BOARD = %% AND
ml.ID_MSG = %% AND mf.ID_MSG = %% ORDER BY isSticky DESC, ID_LAST_MSG
DESC LIMIT %%;
dbo182763563 1 SELECT t.ID_TOPIC FROM (smf_topics AS t, smf_messages AS
mf) LEFT JOIN smf_members AS memf ON (memf.ID_MEMBER = %%) WHERE
t.ID_BOARD = %% AND mf.ID_MSG = %% ORDER BY isSticky DESC,
IFNULL(memf.realName, mf.posterName) LIMIT %%;
dbo182763563 1 SELECT t.ID_TOPIC, t.numReplies, t.locked, t.numViews,
t.isSticky, t.ID_POLL, %% AS new_from, t.ID_LAST_MSG, ml.posterTime AS
lastPosterTime, ml.ID_MSG_MODIFIED, ml.subject AS lastSubject, ml.icon
AS lastIcon, ml.posterName AS lastMemberName, ml.ID_MEMBER AS
lastID_MEMBER, IFNULL(meml.realName, ml.posterName) AS lastDisplayName,
t.ID_FIRST_MSG, mf.posterTime AS firstPosterTime, mf.subject AS
firstSubject, mf.icon AS firstIcon, mf.posterName AS firstMemberName,
mf.ID_MEMBER AS firstID_MEMBER, IFNULL(memf.realName, mf.posterName) AS
firstDisplayName, LEFT(ml.body, %%) AS lastBody, LEFT(mf.body, %%) AS
firstBody, ml.smileysEnabled AS lastSmileys, mf.smileysEnabled AS
firstSmileys FROM (smf_topics AS t, smf_messages AS ml, smf_messages AS
mf) LEFT JOIN smf_members AS meml ON (meml.ID_MEMBER = %%) LEFT JOIN
smf_members AS memf ON (memf.ID_MEMBER = %%) WHERE t.ID_BOARD = %% AND
ml.ID_MSG = %% AND mf.ID_MSG = %% ORDER BY isSticky, mf.subject DESC
LIMIT %%;
rdb272:~# mylogana -U dbo182763563 /db/logs/mysql.log.1.gz |cut -d\ -f
7- |grep -E '(UPDATE|INSERT|DELETE|update|insert|delete)'| head -n 20
UPDATE galerie_images SET
UPDATE galerie_categories SET categorie_hits = categorie_hits + 1
WHERE categorie_id = "1" OR categorie_id = "61" OR categorie_id = "49"
OR categorie_id = "45"
DELETE FROM smf_log_online WHERE logTime < NOW() - INTERVAL 900 SECOND
INSERT IGNORE INTO smf_log_online (session, ID_MEMBER, logTime, ip,
url) VALUES ('ip193.47.80.47', 0, NOW(),
IFNULL(INET_ATON('193.47.80.47'), 0),
'a:4:{s:5:\"topic\";s:0:\"\";s:5:\"board\";s:0:\"\";s:6:\"action\";s:0:\
"\";s:10:\"USER_AGENT\";s:69:\"Mozilla/5.0 (compatible; Exabot/3.0;
+http://www.exabot.com/go/robot)\";}')
UPDATE smf_sessions SET data =
'rand_code|s:32:\"5583de1d28274b18ed0ba5e9ac94847e\";log_time|i:12329604
30;timeOnlineUpdated|i:1232960430;login_url|s:57:\"
http://www.allezsochaux.com/forum/index.php?topic=3000.30\";old_url|s:57:\"
http://www.allezsochaux.com/forum/index.php?topic=3000.30\";USER_AGENT|s:69:\"Mozilla/5.0
(compatible; Exabot/3.0; +http://www.exabot.com/go/robot)\";',
last_update = 1232960430 WHERE session_id =
'9745e95de0d32a2ab06a356db85bb7f3' LIMIT 1
INSERT IGNORE INTO smf_sessions (session_id, data, last_update) VALUES
('9745e95de0d32a2ab06a356db85bb7f3',
'rand_code|s:32:\"5583de1d28274b18ed0ba5e9ac94847e\";log_time|i:12329604
30;timeOnlineUpdated|i:1232960430;login_url|s:57:\"
http://www.allezsochaux.com/forum/index.php?topic=3000.30\";old_url|s:57:\"
http://www.allezsochaux.com/forum/index.php?topic=3000.30\";USER_AGENT|s:69:\"Mozilla/5.0
(compatible; Exabot/3.0; +http://www.exabot.com/go/robot)\";',
1232960430)
UPDATE galerie_images SET image_hits = image_hits + 1 WHERE image_id
= "3996"
UPDATE galerie_categories SET categorie_hits = categorie_hits + 1
WHERE categorie_id = "1" OR categorie_id = "83"
DELETE FROM smf_log_online WHERE logTime < NOW() - INTERVAL 900 SECOND
INSERT IGNORE INTO smf_log_online (session, ID_MEMBER, logTime, ip,
url) VALUES ('ip193.47.80.47', 0, NOW(),
IFNULL(INET_ATON('193.47.80.47'), 0),
'a:3:{s:5:\"topic\";i:3466;s:5:\"board\";i:27;s:10:\"USER_AGENT\";s:69:\
"Mozilla/5.0 (compatible; Exabot/3.0;
+http://www.exabot.com/go/robot)\";}')
UPDATE smf_topics SET numViews = numViews + 1 WHERE ID_TOPIC = 3466
LIMIT 1
UPDATE smf_sessions SET data =
'rand_code|s:32:\"e1db8655fd591c08dfa8945ce61d3057\";ban|a:5:{s:12:\"las
t_checked\";i:1232960443;s:9:\"ID_MEMBER\";i:0;s:2:\"ip\";s:12:\"193.47.
80.47\";s:3:\"ip2\";s:12:\"193.47.80.47\";s:5:\"email\";s:0:\"\";}last_t
opic_id|i:3466;log_time|i:1232960443;timeOnlineUpdated|i:1232960443;last
_read_topic|i:3466;old_url|s:63:\"
http://www.allezsochaux.com/forum/index.php?topic=3466.msg92053\";USER_AGENT|s:69:\"Mozilla/5.0 (compatible;
Exabot/3.0; +http://www.exabot.com/go/robot)\";', last_update =
1232960444 WHERE session_id = '94553f7c607a0eb276cdf1925b023f22' LIMIT
1
INSERT IGNORE INTO smf_sessions (session_id, data, last_update) VALUES
('94553f7c607a0eb276cdf1925b023f22',
'rand_code|s:32:\"e1db8655fd591c08dfa8945ce61d3057\";ban|a:5:{s:12:\"las
t_checked\";i:1232960443;s:9:\"ID_MEMBER\";i:0;s:2:\"ip\";s:12:\"193.47.
80.47\";s:3:\"ip2\";s:12:\"193.47.80.47\";s:5:\"email\";s:0:\"\";}last_t
opic_id|i:3466;log_time|i:1232960443;timeOnlineUpdated|i:1232960443;last
_read_topic|i:3466;old_url|s:63:\"
http://www.allezsochaux.com/forum/index.php?topic=3466.msg92053\";USER_AGENT|s:69:\"Mozilla/5.0 (compatible;
Exabot/3.0; +http://www.exabot.com/go/robot)\";', 1232960444)
UPDATE galerie_images SET image_hits = image_hits + 1 WHERE image_id
= "1993"
UPDATE galerie_categories SET categorie_hits = categorie_hits + 1
WHERE categorie_id = "1" OR categorie_id = "61" OR categorie_id = "62"
OR categorie_id = "46"
UPDATE galerie_images SET image_hits = image_hits + 1 WHERE image_id
= "948"
UPDATE galerie_categories SET categorie_hits = categorie_hits + 1
WHERE categorie_id = "1" OR categorie_id = "61" OR categorie_id = "26"
OR categorie_id = "30"
DELETE FROM smf_log_online WHERE logTime < NOW() - INTERVAL 900 SECOND
INSERT IGNORE INTO smf_log_online (session, ID_MEMBER, logTime, ip,
url) VALUES ('ip72.30.81.157', 0, NOW(),
IFNULL(INET_ATON('72.30.81.157'), 0),
'a:3:{s:5:\"topic\";i:2803;s:5:\"board\";i:18;s:10:\"USER_AGENT\";s:83:\
"Mozilla/5.0 (compatible; Yahoo! Slurp;
http://help.yahoo.com/help/us/ysearch/slurp)\";}')
UPDATE smf_topics SET numViews = numViews + 1 WHERE ID_TOPIC = 2803
LIMIT 1
Voici ce que me conseil mon support :
Une mise à jour des scripts et une indexation de la base ont très
souvent raisons de ces lenteurs.
Nous comptons sur vous pour la régularisation rapide de votre problème
Je suis totalement perdu et ne sait que faire pour corriger ce problème.
