Slow Querys are killing my server

[solokco]

We run a massive traffic web page using Joomla 2.5.2 (lates version every time a new update comes out).

Since de start we get a CPU % warning because MySQL is using a lot of resources. We have one server only for MySQL and other for Apache and other apps.

We have try to narrow down the different modules that we believe might be causing so many calls to our DataBase, but now we get a Slow Query warning (over 50.000 every day)... Every day the log mysql-slow.log gets over 300MB of text information.

Running some test we get that some of the querys joins are not using indexes. We don't know if this comes from the core, templete or some module. We have try getting everything out, but the problem is not going away.

The main issues come from the tables jos_banners and jos_content ...

The url of the web page is www.elimpulso.com

Please help.

Here are the most common logs.

# User@Host: root[root] @ li104-7.members.linode.com [72.14.189.7]
# Query_time: 0.000534 Lock_time: 0.000140 Rows_sent: 0 Rows_examined: 17
SET timestamp=1332349660;
SELECT *
FROM jos_banners
WHERE '2012-03-21 17:07:37' >= `reset` AND `reset` != '0000-00-00 00:00:00' AND `reset`!=NULL AND (`checked_out` = 0 OR `checked_out` = 0);


# User@Host: root[root] @ li104-7.members.linode.com [72.14.189.7]
# Query_time: 0.025685 Lock_time: 0.000352 Rows_sent: 9 Rows_examined: 2917
SET timestamp=1332349660;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by, a.publish_up, a.publish_down, a.images, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.featured, ROUND(v.rating_sum / v.rating_count, 2) AS rating_value, LENGTH(a.fulltext) AS readmore,a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by, a.publish_up, a.publish_down, a.images, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.featured, ROUND(v.rating_sum / v.rating_count, 2) AS rating_value, LENGTH(a.fulltext) AS readmore,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN jos_categories AS c ON c.id = a.catid
LEFT JOIN jos_users AS ua ON ua.id = a.created_by
LEFT JOIN jos_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND a.featured = 0 AND a.catid IN (106,43,44,45,47,83) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-03-21 17:07:38') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-03-21 17:07:38') AND a.created >= DATE_SUB('2012-03-21 17:07:38', INTERVAL 2 DAY)
GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urls
ORDER BY a.created DESC LIMIT 0, 9;


# User@Host: root[root] @ li104-7.members.linode.com [72.14.189.7]
# Query_time: 0.002058 Lock_time: 0.000342 Rows_sent: 0 Rows_examined: 0
SET timestamp=1332349660;
SELECT a.id, a.asset_id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, CASE WHEN badcats.id is null THEN a.state ELSE 0 END AS state, a.mask, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.urls, a.attribs, a.version, a.parentid, a.ordering, a.metakey, a.metadesc, a.access, a.hits, a.metadata, a.featured, a.language, a.xreference,c.title AS category_title, c.alias AS category_alias, c.access AS category_access,u.name AS author,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count
FROM jos_content AS a
LEFT JOIN jos_categories AS c on c.id = a.catid
LEFT JOIN jos_users AS u on u.id = a.created_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM jos_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN jos_categories as parent ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM jos_categories AS cat JOIN jos_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published <= 0 GROUP BY cat.id) AS badcats ON badcats.id = c.id
WHERE a.id = 0 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-03-21 17:07:40') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-03-21 17:07:40') AND (a.state = 1 OR a.state =2);