Slow queries on large website

[aliens]

Hey,

I've been having some problems with a large website (50.000 articles), and, after a bit of debugging, I came across these issues:

1. Category Blog view

I built a menu item linking to a category blog display, with 10 articles per page (the category contains 4000 articles). On debug I get the following info:

Code: Select all

Profile Information
Application 0.000 seconds (+0.000); 1.17 MB (+1.167) - afterLoad
Application 0.118 seconds (+0.117); 7.35 MB (+6.179) - afterInitialise
Application 0.190 seconds (+0.072); 9.51 MB (+2.164) - afterRoute
Application 9.626 seconds (+9.437); 23.23 MB (+13.722) - afterDispatch
Application 9.633 seconds (+0.006); 23.47 MB (+0.239) - beforeRenderModule mod_menu (Categories)
Application 10.175 seconds (+0.543); 23.73 MB (+0.258) - afterRenderModule mod_menu (Categories)
Application 10.175 seconds (+0.000); 23.72 MB (-0.010) - beforeRenderModule mod_menu (Main Menu)
Application 10.180 seconds (+0.005); 23.72 MB (-0.004) - afterRenderModule mod_menu (Main Menu)
Application 11.423 seconds (+1.243); 23.83 MB (+0.112) - afterRender
Memory Usage
23.89 MB (25,046,384 Bytes)

So somewhere between afterRoute and afterDispatch there is a query that takes a long time to execute.

After playing around with the queries, I found these 2:

Code: Select all

SELECT 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, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,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 ) 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 k1fhu_content AS a 
  LEFT JOIN k1fhu_content_frontpage AS fp 
  ON fp.content_id = a.id 
  LEFT JOIN k1fhu_categories AS c 
  ON c.id = a.catid 
  LEFT JOIN k1fhu_users AS ua 
  ON ua.id = a.created_by 
  LEFT JOIN k1fhu_users AS uam 
  ON uam.id = a.modified_by 
  LEFT JOIN k1fhu_contact_details AS contact 
  on contact.user_id = a.created_by 
  LEFT JOIN k1fhu_categories as parent 
  ON parent.id = c.parent_id 
  LEFT JOIN k1fhu_content_rating AS v 
  ON a.id = v.content_id 
  LEFT 
  OUTER JOIN (SELECT cat.id as id 
  FROM k1fhu_categories AS cat JOIN k1fhu_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 a.access IN (1,1) 
  AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 
  AND a.catid = 33 
  AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-12-06 13:34:17') 
  AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-12-06 13:34:17') 
  GROUP BY a.id 
  ORDER BY c.lft, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created 
  LIMIT 0, 10
SELECT 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, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,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 ) 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 k1fhu_content AS a 
  LEFT JOIN k1fhu_content_frontpage AS fp 
  ON fp.content_id = a.id 
  LEFT JOIN k1fhu_categories AS c 
  ON c.id = a.catid 
  LEFT JOIN k1fhu_users AS ua 
  ON ua.id = a.created_by 
  LEFT JOIN k1fhu_users AS uam 
  ON uam.id = a.modified_by 
  LEFT JOIN k1fhu_contact_details AS contact 
  on contact.user_id = a.created_by 
  LEFT JOIN k1fhu_categories as parent 
  ON parent.id = c.parent_id 
  LEFT JOIN k1fhu_content_rating AS v 
  ON a.id = v.content_id 
  LEFT 
  OUTER JOIN (SELECT cat.id as id 
  FROM k1fhu_categories AS cat JOIN k1fhu_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 a.access IN (1,1) 
  AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 
  AND a.catid = 33 
  AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-12-06 13:34:17') 
  AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-12-06 13:34:17') 
  GROUP BY a.id 
  ORDER BY c.lft, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created

The first one is rather obvious, getting the articles to display on the page, with LIMIT 0, 10 to only display the first 10 results.

The second one is weird, as far as I can see it is the same as the first one, only it doesn't have LIMIT, so it actually grabs the whole 4000 articles from that category. I don't see any reason why this should happen (there are no modules of any sort on the page).

2. Modules (Latest/Most Read Articles)

On the main page I've got 2 modules that seem to take forever to load. One is Latest Articles, the other one is for Popular Articles (sorted by hits).

Now, this isn't the same issue as the first one (otherwise it would kill my website), but still, a query like this takes over 10 seconds to execute:

Code: Select all

SELECT 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, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,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 ) 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 k1fhu_content AS a 
  LEFT JOIN k1fhu_content_frontpage AS fp 
  ON fp.content_id = a.id 
  LEFT JOIN k1fhu_categories AS c 
  ON c.id = a.catid 
  LEFT JOIN k1fhu_users AS ua 
  ON ua.id = a.created_by 
  LEFT JOIN k1fhu_users AS uam 
  ON uam.id = a.modified_by 
  LEFT JOIN k1fhu_contact_details AS contact 
  on contact.user_id = a.created_by 
  LEFT JOIN k1fhu_categories as parent 
  ON parent.id = c.parent_id 
  LEFT JOIN k1fhu_content_rating AS v 
  ON a.id = v.content_id 
  LEFT 
  OUTER JOIN (SELECT cat.id as id 
  FROM k1fhu_categories AS cat JOIN k1fhu_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 a.access IN (1,1) 
  AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 
  AND a.catid IN (8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,58) 
  AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-12-06 13:37:36') 
  AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-12-06 13:37:36') 
  GROUP BY a.id 
  ORDER BY a.hits DESC 
  LIMIT 0, 5

I'm guessing some mysqld optimization could help, but I wonder how much.

P.S.: Note that I am using SEBLOD 2.0 GA cck and AceSEF 1.7.6.

Cheers!

[aliens]

Seems that the main problem lied with these modules that I wrote about above. They were only published on the main homepage of the site, yet if 3-4 people accessed that page at the same time, they would bring the whole server down.

This is the fortunate case, where it's just 1 or 2 people loading the page at the same time:

Code: Select all

Application 0.001 seconds (+0.001); 1.17 MB (+1.165) - afterLoad
Application 0.096 seconds (+0.095); 7.34 MB (+6.179) - afterInitialise
Application 0.146 seconds (+0.050); 9.50 MB (+2.154) - afterRoute
Application 0.359 seconds (+0.213); 21.42 MB (+11.925) - afterDispatch
Application 0.365 seconds (+0.006); 21.66 MB (+0.238) - beforeRenderModule mod_menu (Categories)
Application 0.832 seconds (+0.468); 21.92 MB (+0.263) - afterRenderModule mod_menu (Categories)
Application 0.833 seconds (+0.000); 21.91 MB (-0.010) - beforeRenderModule mod_menu (Main Menu)
Application 0.837 seconds (+0.004); 21.91 MB (-0.003) - afterRenderModule mod_menu (Main Menu)
Application 0.922 seconds (+0.085); 22.06 MB (+0.146) - beforeRenderModule mod_articles_latest (Latest Recipes)
Application 11.073 seconds (+10.151); 22.32 MB (+0.261) - afterRenderModule mod_articles_latest (Latest Recipes)
Application 11.074 seconds (+0.001); 22.15 MB (-0.168) - beforeRenderModule mod_articles_popular (Popular Recipes)
Application 21.377 seconds (+10.303); 22.39 MB (+0.238) - afterRenderModule mod_articles_popular (Popular Recipes)
Application 21.381 seconds (+0.004); 22.15 MB (-0.234) - afterRender

Another 2 people and the page load gets to 80-100 seconds. I could enable cache of course, but even when that cache expires, there's a small chance a few people will access the page before new cache is created, which would cripple my site.

From the mysql slow query log:

Code: Select all

# Query_time: 77.375093  Lock_time: 3.956384 Rows_sent: 5  Rows_examined: 265405
use freecook_fcrseblod;
SET timestamp=1323263696;
SELECT 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, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,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 ) 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 k1fhu_content AS a
LEFT JOIN k1fhu_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN k1fhu_categories AS c ON c.id = a.catid
LEFT JOIN k1fhu_users AS ua ON ua.id = a.created_by
LEFT JOIN k1fhu_users AS uam ON uam.id = a.modified_by
LEFT JOIN k1fhu_contact_details AS contact on contact.user_id = a.created_by
LEFT JOIN k1fhu_categories as parent ON parent.id = c.parent_id
LEFT JOIN k1fhu_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM k1fhu_categories AS cat JOIN k1fhu_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 a.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND a.featured = 0 AND a.catid IN (8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,58) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2011-12-07 13:13:38') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2011-12-07 13:13:38')
GROUP BY a.id
ORDER BY a.publish_up DESC LIMIT 0, 5;

What is also weird is the number of rows examined. I only have about 50.000 articles, yet it examines more than 5 times that number.

[btomczak]

I'm having the same problem on a site with only 12,000 articles.

Even though there is a limit to the articles you want to display, when determining the total number of records (for pagination for example) Joomla models will run the query without the limit. Ideally, I'd love to see that totaling query run by replacing the entire field list with count(*). Something I've done occasionally when I couldn't tolerate the inefficiency.

the mod_articles_latest module uses the article model and though I don't think it looks for the total count, you are right about the need to optimize that massive query. It falls apart pretty quickly. I've played with it a bit by trying out some additional indexes but there's no help there. The large number of fields being returned is certainly part of the problem.

-bill

[aliens]

Regarding the blog queries, surprisingly it's the limit query that takes longer.

Regarding the module queries, I decided to remove them entirely to avoid crashing my site, they really need to be optimized in a future release.

[drjonz]

I don't mean to cross post, but I think I have the same issue:

http://forum.joomla.org/viewtopic.php?f ... 3#p2701503

And I think it's because my menus are category lists which go over every article to see if its category matches. I'll know by the end of the day if hiding the menus fixes the issue.

[rojer_31]

Has this been posted in the bug tracker yet? Retrieving all the articles just for the sake of getting a count is unbelievable. We're in the process of migrating 5 J1.5 sites to 1.7 & 2.5 and are getting a bit apprehensive looking at this.

@aliens, could you post this as a bug and put in the link in this thread so that we can watch it? Thanks!

[btomczak]

The method of getting a full count has not changed from 1.5 to 1.6/1.7/2.5. So if it is a bug, it's a bug since the beginning. It wasn't much of an issue on 1.5 even on sites with a large number of articles. The 12,000 article site I recently migrated was working fine on 1.5.

From what I can tell, the advent of nested categories and maybe the new ACL has made for more complicated queries that bog down much sooner. If your sites contain on the order of hundreds of articles (rather than the multiple thousands some of us are using) then there should be no problem.

It's worth noting that the 12,000 article site I just migrated is working fine. I just can't use the latest articles module because in this particular instance, that is when the problem shows up. We got lucky that the structure of the site doesn't exercise this problem and the client is happy.

[Per Yngve Berg]

To get the number of rows without the limit use the SQL_CALC_FOUND_ROWS without re-running the query.

Code: Select all

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

[rojer_31]

@Berg Didn't know that!

Found something that could help any dev implementing this in the core:
http://stackoverflow.com/questions/6740 ... ork-in-php

Even if this has been on joomla since 1.5 this obviously is a very expensive way to get count & as a sysadmin, I would request that this needs to be fixed please.

[CyberCr33p]

The same issue affects 3 websites that I host. One is on 1.5 , one on 1.6 and one on 1.7. The one at 1.5 is ok (the issue exist there too), but to other two have serious issues with the queries. The site that is 1.7 had 36.000 articles and when I remove most of them (now has 8.000) is ok again. Any idea if we can index any fields in database tables to make the queries run faster?

[drjonz]

My hosting company came back after a week and is saying they're going to take my site down. Last week, I disabled my menu system thinking that might be causing the issue (since the page loads very fast, but menu lists did not). I'm at almost 17K articles. I was thinking about archiving 1/2 of them. Seems like I'm just out of options.

I seriously do wonder if my hosting company is just trolling for people to force onto expensive VPS plans. From what little I can see from these slow queries, they don't seem to happen often enough to warrant pulling the site.

[btomczak]

@CyberCr33p -- I've played a bit with adding indexes and didn't find any help there. Rearranging the table selections was another thought I had but haven't had much chance to play with that. So far, the easiest and largest performance improvement I was able to find was cutting back on the number of fields selected. And when called for, an alternative to determining the total number of records to be returned. @Berg describes an excellent alternative. The problem is any solution will require changes to core code. It would be great to see something like @Berg's totals solution implemented generally in the list model. But the query itself still needs careful review for optimization.

@drjonz -- Sorry to hear it wasn't the menus. It may seem like the hosting company is being too picky, and maybe they are. But in a shared hosting environment, they do have to watch their processing cycles. As @aliens found, a query like this has the potential to take down a whole server. They have to watch out for everyone sharing the server.

[drjonz]

@btomczak - they sure do have to look out for people slowing each other down. But they refuse to tell me how often this happens (shared log is edited before shown to me).

Going back to @aliens 2nd query that has no limit.

The second one is weird, as far as I can see it is the same as the first one, only it doesn't have LIMIT, so it actually grabs the whole 4000 articles from that category. I don't see any reason why this should happen (there are no modules of any sort on the page).

Why does it have no limit, what generates it, and where can we fix it? Why 2?

[btomczak]

Even though you may want only 10 items listed on a page, there are situations in which you need to know the total number of items the query *would* return without any limit. In order to build the page navigation links, how else to know how many pages there will be?

The way the total method in Joomla models works is by running the query as-is without the limit clause. Now I know that X items match the criteria and I know that I want to see Y per page = X/Y pages. Now I can build my page navigation.

@Berg's suggestion for getting that total would be by far the most efficient way to do this, though I'm not sure how simple it would be to incorporate into the workings of Joomla models.

[CyberCr33p]

If someone finds a solution about this let us know.

[rcristofrf]

Same here on a page with 3000+ articles. It is regularly exceeding our (decent) server CPU and memory, thus rendering the page (J1.7.3) dead. :-(

[drjonz]

After archiving a few thousand articles, my hosting company is officially leaving me alone for now. Ugliest work around of all time, but cheaper than the $80 a month they wanted.

[rcristofrf]

After archiving a few thousand articles, my hosting company is officially leaving me alone for now. Ugliest work around of all time, but cheaper than the $80 a month they wanted.

Hey, drjonz, you may find some of this tips useful.

[Neuromante]

Hi guys...

I am in the same club. My query:

Code: Select all

SELECT 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, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up, a.publish_down, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore ,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,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 ) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_publishedFROM j17_content AS aLEFT JOIN j17_content_frontpage AS fp ON fp.content_id = a.idLEFT JOIN j17_categories AS c ON c.id = a.catidLEFT JOIN j17_users AS ua ON ua.id = a.created_byLEFT JOIN j17_users AS uam ON uam.id = a.modified_byLEFT JOIN j17_contact_details AS contact on contact.user_id = a.created_byLEFT JOIN j17_categories as parent ON parent.id = c.parent_idLEFT JOIN j17_content_rating AS v ON a.id = v.content_idLEFT OUTER JOIN (SELECT cat.id as id FROM j17_categories AS cat JOIN j17_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.idWHERE a.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-01-09 12:41:23') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-01-09 12:41:23')GROUP BY a.idORDER BY c.lft, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC , a.created 

Should the archive solution avoid this problem? Is there any "real" fix? I don't want my site to be taken down permanently... and I am already under their notice...

Cheers,

N.

[CyberCr33p]

The only solution that "fix" the issue, is to enable tmpfs and write the mysql temporary tables to RAM. This way the I/O is low.

[Neuromante]

Uhm, any sugestion about how to do both things? Thanks...

[garyjaybrooks]

My suggestion is to find a host that runs raid 10,4 disk dedicated for MySQL, plus 15K SaS disk, the 7,200RPM disk from walmart are not going to cut it. Many host provide low cost disk with shared hosting or even dedicated servers. A setting you should have on is MySQL Query Cache. If your on a shared host make sure that they CPU limit is not throttled at a really low limit. Remember MySQL runs better at a higher frequency then a higher core count.

To many times I am seeing people try to host a large Joomla site on a shared host that only has 1 spindle and that's for both database and files with no partition, and 200 other customers. Ask your host if the have a LUN for MySQL or if they partition the database, what speed the disk are, what raid arrangement, and what the IO wait time is if you can not see it. For the demo.joomla.org site we have servers that have 24, 15K SaS disk for MySQL. We do this so we do not have issues with IOPS. We want to spit our Joomla sites out with power and speed.

Joomla sites that are large need some love under the hood before you think about fixing the code.
If your site is strictly content driven and nothing to do with the DB and you plan keep a host with a small disk system you might think to try this guys idea.

https://github.com/juliopontes/staticcontent

have fun scaling!

[kenmcd]

.
"Throwing hardware at it" is not the solution to this problem query being discussed in this thread.

Fixing the query, or telling users how to avoid it, or documenting specific server settings which help – those are practical solutions.

"My buy my hosting service" is not the solution for most users.
If you actually want to help rather than sell something, post some actual specific concrete detailed practical advice based on real-world experience.
That would be valuable.

.

[Neuromante]

Thanks, Kenmcd. That is exactly the point: Some users (I suspect too many) have this problem, and in my humble opinion, to tell what the hosting company should do, doesn't really help us at all. Besides, the query -and not the hardware of X company- is the one to blame.

Anyway, I have started to archive 96% of my articles. Most are old news that shouldn't be relevant anyway. I have already repaired/optimized tables, so I hope that this will do... for now.

Thanks guys.

N.

[garyjaybrooks]

I would not agree with either of you. Yes fixing a query that is slow is a decent idea or tuning it for your application is fine, you can ALWAYS fix the code more. Remember with PHP you can have 300 solutions for he same problem. If you believe this is a issue with the core of Joomla please post a detailed post on the developers mailing list on Google. My bet is that if you put this same code on a properly built node with the right gear under the hood you would feel that the code will work as if under normal conditions.

You can write code for slow small disk, but in the case of Joomla, it does use SQL database. This is a common practice that anyone for 50,000 anything in a DB would make sure they have good gear. After you put the site on decent gear then you can take a measure of the results of the application. We could continue down this war path of complaining Joomla is broke but I would complain that your budget is broken not to put a very large database on dedicated good gear. Why would Oracle themselves put MySQL on flash these days? Yes that's right, big database require IOPS.

By the way, I'm not selling anything. I have no need to come to the forums to sell. I spent the day replying to people yesterday because nobody was cleaning up the performance area. Rather I'm sharing what I do know and have tested scores of times. I might advise you not put words in others mouths. Go back and read my post, I never offered any of my services and never have on the forum. *happy grin*

If anyone wants to try to question the fact. We host well over 50,000 sites and much larger databases then you explain here. We have ZERO issue with such cases and guess what? Yes, we have customers with all kinds of crap code and long joins. It's because of a properly tuned MySQL database and good gear why we hardly ever talk about Joomla not working fast.

Gary Brooks

[Neuromante]

Garyjaybrooks,

... the ones here having this problem cannot afford better hosting plans. I wish I had HAL9000 here with me, but I don't have it. I wish I had a replicant (in fact, a Shakira replicant) but I cannot afford it neither.

Therefore, we are looking for a solution that means optimising our Joomla-based sites, not improving a server.

- Can we do anything in the database? Probably, keeping it repaired, optimised and under control.
- Can we fix some php file? I don't have a clue.
- Can we avoid it archiving articles? I have done it and I don't know yet if that helps.
- Can we avoid it erasing articles? Easy, but I don't want to do that yet.
- Some of the above, none of them? Perhaps the problem comes from somewhere else (a dodgy menu item, lost in translation? A slow template?)

Let's be realistic: I have a Ford [spam], 20 y.o., and I need to fix the fan. I am not asking for "which car would make an excellent replacement... Oh, a Ferrari, thanks, I will make a mental note of that".

Regards

[garyjaybrooks]

@Neuromante,

Do you have access to the server or are you on a shared host?

[Neuromante]

Hi

It is a share host, and I don't have access to the server.

My website get's around 1000 visits a day, with a Kunena forum and several scores of "own" content and several thousands of news stories I have just copied and paste from "general media". The topic of my website is Poland.

I don't really mind much archiving the stories, as the age quickly (although they are a good way of harvesting new users...).

Perhaps I should have explained that. I think (I think...) the problem exists since I migrated to 1.7. Probably it was there before, but probably was not so evident as it was last week. I remember accessing my website and finding an error 500 a few times. Then, after some couple of days, the website was suspended.

I wish I can fix that "just" doing my homework in the database, tidying it a bit, installing some extension for optimisation and so on...

Thanks!

[Neuromante]

The last resource I have: Removing articles.

My DB hosts some 2,800 articles, and although I think a server should be able to cope with that, it seems it doesn't. Apparently is not enough archiving them.

Of those having this issue, do you have a large amount of articles in your DB?

This is killing me... I just want to write about Poland!

[garyjaybrooks]

Neuromante,

Find a host with more juice. That's my advise. I you want to try to code Joomla better, you will need to start profiling the code and running load test on the application and your hosting setup. Running load test on a shared host will get you in trouble and removed from the service. You can use a tool like http://www.jetprofiler.com/ to watch the DB while you spin up the site using a service like http://loadimpact.com/. To read what PHP script is eating all the juice you will need a special admin and some special tools like xdebug.

If you were on a dedicated node I would volunteer my time to show you its your server that can cause a bottle neck. In any case, I don't think you have root access to the node to do all the testing.

What are you left with if you can not invest into profiling and fixing code? > or hunting down better hosting with much better gear? Try to reorganize the content and archive as you been doing. Shared host have limits and with huge DB you need to think about how to better serve your business scaling needs.

Other thoughts? Caching on the DB, make sure you have Query Cache turned on in the DB, make sure the DB is tuned perfect, and other good stuff that has to do with how you run a big database. I could go on and on about how to scale a Joomla site but unless you have access to the box your left with the settings that the shared hosting company gives you.

Best of luck.

Gary Brooks