Aujourd'hui j'ai recu le mail suivant de mon hebegeur :
Je me permets de vous contacter à la demande de mes collègues sysadmins
s'occupant de nos serveurs SQL.
Ces derniers ont constatés après plusieurs emails de clients signifiants
des lenteurs que votre base se trouve parmi les bases responsables de
ces lenteurs.
Voici un log relevé par mes collègues :
user count query_time lock_time rows_sent rows_examined
SELECT ms.id AS sid, ms.type AS stype, mc.id AS cid,
mc.type AS ctype, i.id as sectionid, i.id As catid, ms.published AS
spub, mc.published AS cpub FROM jos_content AS i LEFT JOIN jos_sections
AS s ON i.sectionid = %% LEFT JOIN jos_menu AS ms ON ms.componentid = %%
LEFT JOIN jos_categories AS c ON i.catid = %% LEFT JOIN jos_menu AS mc
ON mc.componentid = %% WHERE ( ms.type IN %% OR mc.type IN %% ) AND i.id
= %% ORDER BY ms.type DESC, mc.type DESC, ms.id, mc.id;
SELECT a.id, a.title, a.sectionid, a.catid FROM
jos_content AS a LEFT JOIN jos_content_frontpage AS f ON f.content_id =
%% INNER JOIN jos_categories AS cc ON cc.id = %% INNER JOIN jos_sections
AS s ON s.id = %% WHERE ( a.state = %% AND a.sectionid > %% ) AND (
a.publish_up = %% OR a.publish_up <= %% ) AND ( a.publish_down = %% OR
a.publish_down >= %% ) AND a.access <= %% AND cc.access <= %% AND
s.access <= %% AND s.published = %% AND cc.published = %% LIMIT %%;
dbo206426297 8 DELETE FROM jos_session WHERE ( ( time < %% ) AND guest =
%% AND gid > %% ) OR ( ( time < %% ) AND guest = %% AND userid = %% );
SELECT a.id, a.title, a.title_alias, a.introtext,
a.sectionid, a.state, a.catid, a.created, a.created_by,
a.created_by_alias, a.modified, a.modified_by, a.checked_out,
a.checked_out_time, a.publish_up, a.publish_down, a.images, a.urls,
a.ordering, a.metakey, a.metadesc, a.access, CHAR_LENGTH( a.fulltext )
AS readmore, s.published AS sec_pub, cc.published AS sec_pub, u.name AS
author, u.usertype, s.name AS section, cc.name AS category, g.name AS
groups , ROUND( v.rating_sum / v.rating_count ) AS rating,
v.rating_count FROM jos_content AS a LEFT JOIN jos_categories AS cc ON
cc.id = %% LEFT JOIN jos_users AS u ON u.id = %% LEFT JOIN jos_sections
AS s ON a.sectionid = %% LEFT JOIN jos_groups AS g ON a.access = %% LEFT
JOIN jos_content_rating AS v ON a.id = %% WHERE a.state = %% AND (
a.publish_up = %% OR a.publish_up <= %% ) AND ( a.publish_down = %% OR
a.publish_down >= %% ) AND ( a.catid=%% ) AND s.published = %% AND
cc.published = %% AND a.access <= %% AND s.access <= %% AND cc.access <=
%% LIMIT %%;
SELECT ROUND( v.rating_sum / v.rating_count ) AS rating,
v.rating_count FROM jos_content AS a LEFT JOIN jos_content_rating AS v
ON a.id = %% WHERE a.id = %%;
INSERT INTO jos_session (
`session_id`,`time`,`username`,`gid`,`guest` ) VALUES %%;
SELECT guest, usertype FROM jos_session;
Une telle utilisation se trouve trop importante pour un serveur SQL
mutualisé.
Cependant, mes collègues n'ont pas encore effectué la suspension de
votre base, comptant sur votre collaboration afin de résoudre le
problème à l'aide des logs ci-dessus.
Une mise à jour des scripts et une indexation de la base ont très
souvent raisons de ces lenteurs.
Que faire ?
Je pensais que joomla etait bien optimisé.
Voyez vous quelques choses qui cloche dans ces requetes SQL ?
Je me permets de vous contacter à la demande de mes collègues sysadmins
s'occupant de nos serveurs SQL.
Ces derniers ont constatés après plusieurs emails de clients signifiants
des lenteurs que votre base se trouve parmi les bases responsables de
ces lenteurs.
Voici un log relevé par mes collègues :
user count query_time lock_time rows_sent rows_examined
SELECT ms.id AS sid, ms.type AS stype, mc.id AS cid,
mc.type AS ctype, i.id as sectionid, i.id As catid, ms.published AS
spub, mc.published AS cpub FROM jos_content AS i LEFT JOIN jos_sections
AS s ON i.sectionid = %% LEFT JOIN jos_menu AS ms ON ms.componentid = %%
LEFT JOIN jos_categories AS c ON i.catid = %% LEFT JOIN jos_menu AS mc
ON mc.componentid = %% WHERE ( ms.type IN %% OR mc.type IN %% ) AND i.id
= %% ORDER BY ms.type DESC, mc.type DESC, ms.id, mc.id;
SELECT a.id, a.title, a.sectionid, a.catid FROM
jos_content AS a LEFT JOIN jos_content_frontpage AS f ON f.content_id =
%% INNER JOIN jos_categories AS cc ON cc.id = %% INNER JOIN jos_sections
AS s ON s.id = %% WHERE ( a.state = %% AND a.sectionid > %% ) AND (
a.publish_up = %% OR a.publish_up <= %% ) AND ( a.publish_down = %% OR
a.publish_down >= %% ) AND a.access <= %% AND cc.access <= %% AND
s.access <= %% AND s.published = %% AND cc.published = %% LIMIT %%;
dbo206426297 8 DELETE FROM jos_session WHERE ( ( time < %% ) AND guest =
%% AND gid > %% ) OR ( ( time < %% ) AND guest = %% AND userid = %% );
SELECT a.id, a.title, a.title_alias, a.introtext,
a.sectionid, a.state, a.catid, a.created, a.created_by,
a.created_by_alias, a.modified, a.modified_by, a.checked_out,
a.checked_out_time, a.publish_up, a.publish_down, a.images, a.urls,
a.ordering, a.metakey, a.metadesc, a.access, CHAR_LENGTH( a.fulltext )
AS readmore, s.published AS sec_pub, cc.published AS sec_pub, u.name AS
author, u.usertype, s.name AS section, cc.name AS category, g.name AS
groups , ROUND( v.rating_sum / v.rating_count ) AS rating,
v.rating_count FROM jos_content AS a LEFT JOIN jos_categories AS cc ON
cc.id = %% LEFT JOIN jos_users AS u ON u.id = %% LEFT JOIN jos_sections
AS s ON a.sectionid = %% LEFT JOIN jos_groups AS g ON a.access = %% LEFT
JOIN jos_content_rating AS v ON a.id = %% WHERE a.state = %% AND (
a.publish_up = %% OR a.publish_up <= %% ) AND ( a.publish_down = %% OR
a.publish_down >= %% ) AND ( a.catid=%% ) AND s.published = %% AND
cc.published = %% AND a.access <= %% AND s.access <= %% AND cc.access <=
%% LIMIT %%;
SELECT ROUND( v.rating_sum / v.rating_count ) AS rating,
v.rating_count FROM jos_content AS a LEFT JOIN jos_content_rating AS v
ON a.id = %% WHERE a.id = %%;
INSERT INTO jos_session (
`session_id`,`time`,`username`,`gid`,`guest` ) VALUES %%;
SELECT guest, usertype FROM jos_session;
Une telle utilisation se trouve trop importante pour un serveur SQL
mutualisé.
Cependant, mes collègues n'ont pas encore effectué la suspension de
votre base, comptant sur votre collaboration afin de résoudre le
problème à l'aide des logs ci-dessus.
Une mise à jour des scripts et une indexation de la base ont très
souvent raisons de ces lenteurs.
Que faire ?
Je pensais que joomla etait bien optimisé.
Voyez vous quelques choses qui cloche dans ces requetes SQL ?
