Slow queries on large website

Discussion regarding Joomla! 2.5 Performance issues.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
RedEye
Joomla! Ace
Joomla! Ace
Posts: 1460
Joined: Sat Jan 21, 2006 8:42 pm

Re: Slow queries on large website

Post by RedEye » Wed Feb 15, 2012 1:22 pm

Rik Brown wrote:"Most Read" module has been disabled some days ago, but I'm still getting this query executed. Something else must be triggering it.
In backend the module on Control Panel view triggers it, if you mean on frontend then pls look if it really is this one or if it is maybe the one for blog view, they look nearly identical. Or have you some other article module there?

>>I modified all the N's
'S'

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Wed Feb 15, 2012 5:03 pm

tappy52dog wrote:Has any cache extensions or settings helped you guys out at all?
Slightly, barely noticable. Not at all as with Joomla 1.5.

Joomla native cache and expire headers (pulling JS, CSS and images from own disk) don´t help much.

I have to repeat it, i compare perfomance of 1.7-2.5 with perfomance of Joomla 1.5. Nothing else.

In other words, i am not naive and don´t expect middle heavy websites to fly.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Wed Feb 15, 2012 8:27 pm

removed post that was associated with a file for fixing the assets table. - GB.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

Rik Brown
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 123
Joined: Wed Oct 15, 2008 9:30 pm
Location: St. Louis, Missouri, USA

Re: Slow queries on large website

Post by Rik Brown » Wed Feb 15, 2012 11:37 pm

Gary:

Executed assetfix.php but got the following error:

Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 79 bytes) in /home/www/html/subs/joomla/libraries/joomla/database/database/mysql.php on line 706

It didn't work, but it didn't mess up anything either. -- Rik

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Thu Feb 16, 2012 12:31 am

Rik,

Can you find me on skype? garyjaybrooks2000
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

scargonauts
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Sep 12, 2011 11:51 pm

Re: Slow queries on large website

Post by scargonauts » Thu Feb 16, 2012 2:30 pm

I am having the same problem with my site.

Running Queries:
*************************** 1. row ***************************
USER: chris_chris
DB: chris_j17
STATE: Copying to tmp table
TIME: 0
COMMAND: Query
INFO: SELECT b.rules
FROM ucdr7_assets AS a
LEFT JOIN ucdr7_assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgt
WHERE (a.name = 'com_content.article.24048' OR a.parent_id=0)
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lft

User avatar
TBone
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 138
Joined: Wed Apr 19, 2006 2:42 pm
Location: Beverwijk | The Netherlands
Contact:

Re: Slow queries on large website

Post by TBone » Thu Feb 16, 2012 6:33 pm

@scargonauts.
Solution is already in this thread (2 times)
Run the fix assets scripts to solve your problem.

John_Philip
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Thu Feb 16, 2012 6:32 pm

Re: Slow queries on large website

Post by John_Philip » Thu Feb 16, 2012 6:37 pm

Hi,

After installing Jreviews and I have over 40,000 listings, my server shut down sometime because of heavy SQL queries,
despite of cache activation for joomla and jreviews (query & views).
We found the SQL code which cause overload:

***** Manual signatures are NOT allowed *****__

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 pre_content AS a
LEFT JOIN pre_content_frontpage AS fp
ON fp.content_id = a.id
LEFT JOIN pre_categories AS c
ON c.id = a.catid
LEFT JOIN pre_users AS ua
ON ua.id = a.created_by
LEFT JOIN pre_users AS uam
ON uam.id = a.modified_by
LEFT JOIN pre_contact_details AS contact
on contact.user_id = a.created_by
LEFT JOIN pre_categories as parent
ON parent.id = c.parent_id
LEFT JOIN pre_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM pre_categories AS cat JOIN pre_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,2)
AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND a.catid IN (11)
AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2012-02-15 15:42:21')
AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2012-02-15 15:42:21')
GROUP BY a.id
ORDER BY a.title ASC

***** Manual signatures are NOT allowed *****__

This request is lauched on each pages even if there is no reviews or listings to display!

Is it a setting question, a bug or anything else?

Thank you for your time.

flashgordonweb
Joomla! Intern
Joomla! Intern
Posts: 74
Joined: Sat Mar 29, 2008 3:52 pm

Re: Slow queries on large website

Post by flashgordonweb » Fri Feb 17, 2012 12:45 am

The solution for the #_assets table works, but it does not solve the problem many of us are experiencing with large # of articles and 2.5.x. That is a slightly different problem, and is preventing me and others from being able to updated from 1.7.5 to 2.5.x. Even after applying the assets table solution, my site crashes the server in 2.5.x with only the Joomla! core installed (no extensions). I have 13,000 articles.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Feb 17, 2012 1:43 am

We worked on this all day today with a site that was having speed issues. The site was killing a VPS and eating all CPU and the IO. We moved the site to the CloudAccess.net servers and we were fine keeping the server online but it still was slow and the problem had to be solved. We are 100% confident that at CloudAccess.net servers are super fast and we should not have this issue. We hooked up our server tools and minds the the site and we found out that this site was upgraded using jupgrade and had a series of extensions that were also migrated over to the 1.7 site. The issue was in the assets table and how the data was moved.

Everyone please read carefully the link below created by Elin Waring who helped all day with this issue, follow the details and your site is still slow you *could* be left with starting new again and importing all your data properly or manually so you do not have the pain of mismatched information in the database that causes troubles in the assets table.

http://docs.joomla.org/Fixing_the_assets_table

If you used jupgrade to update your site I might suggest you talk to the developers and ask them if the have a fix for the assets table. We are confident that we have seen this slow site issue with several sites that have used this extension. I do not have a confident answer to tell you that every site that used jupgrade has had the issues, but the ones we looked at manually used this tool. (Every site we have looked at with this issue used this extension)

It is possible the issues with the assets table *could* come from manual upgrades or even other upgrade tools not related to jupgrade. We are confident that this is note core Joomla issue and that it is related imports from 1.5 to >>> 1.6/1.7/2.5. I would suggest to those that are not able to fix the issue to consider starting new with 2.5 and moving your data in properly.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Fri Feb 17, 2012 1:17 pm

@Garyjaybrooks

Any solutions for those who are facing this issue but did not use jupgrade to migrate to 1.7 or 2.5.
In one of the earlier threads, I mentioned what I used and did to migrate.

cuteschweinchen
Joomla! Apprentice
Joomla! Apprentice
Posts: 12
Joined: Fri Sep 09, 2011 1:10 pm

Re: Slow queries on large website

Post by cuteschweinchen » Fri Feb 17, 2012 1:51 pm

Gary,

I executed assetfix.php - but no change at all. I slowly give up with Joomla 2.5 . Downgraded to 1.7 and the site works perfect!

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Feb 17, 2012 3:26 pm

I'm doubting that the difference is 1.7 to 2.5. The issue on this thread was between 1.5 and >> 1.6/1.7/2.5. Would you mind finding me on skype so I can take a look? garyjaybrooks2000

http://docs.joomla.org/Fixing_the_assets_table <<< did you look at this?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Fri Feb 17, 2012 7:23 pm

cuteschweinchen wrote:Gary,

I executed assetfix.php - but no change at all. I slowly give up with Joomla 2.5 . Downgraded to 1.7 and the site works perfect!
Same for me. Blank page when i execute assetfix.php. Error reporting on max and no errors.

I will check this link above.

I checked. Don´t think it is my problem. I am not using Joomla native Categories and Articles at all. All deleted, none in Trash.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Fri Feb 17, 2012 9:15 pm

You are supposed to get the white page.

@BBC2009 < contact me on skype so I can see your issue live.
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

User avatar
BBC2009
Joomla! Intern
Joomla! Intern
Posts: 50
Joined: Sun Feb 08, 2009 4:41 pm

Re: Slow queries on large website

Post by BBC2009 » Fri Feb 17, 2012 9:30 pm

I did it. You will see message there.

btomczak
Joomla! Apprentice
Joomla! Apprentice
Posts: 34
Joined: Wed Dec 14, 2005 5:38 pm
Location: Portland OR, USA

Re: Slow queries on large website

Post by btomczak » Sat Feb 18, 2012 2:42 am

@gary -- what *doesn't* your assetfix program do that might get done going through Elin's procedure. Doing what that doc describes is going to be a long and tedious process. Before I go there I want to make sure it isn't a wasted effort.

Also, is the fix procedure described in the docs something that can be done before upgrading to 2.5?

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Sat Feb 18, 2012 9:33 am

@garyjaybrooks,
I am still waiting for you to reply my last question.

Thanks.

Schlimmer
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Sun May 13, 2007 4:35 pm

Re: Slow queries on large website

Post by Schlimmer » Sat Feb 18, 2012 11:04 pm

I had to revert a 2.5.0 installation back to a 1.7.5 installation because parts of my customer's website were too slow. Yes, I was using jupgrade before, to convert a 1.5 website to then 1.6. After that the structure of a part of the webiste was changed to a nested category structure. No performance problems until upgrade from 1.7(.3) to 2.5.0.

Now I tried the following on my development machine:
1. Upgraded from 1.7.5 to 2.5.1, let the database repair feature of 2.5.1 run
2. Let assetfix.php run

Now the performance issue with 2.5.1, after running assetfix.php, is not so drastic then before. But the 1.7.5 installation was running a lot faster. So my result is: assetfix.php works (a subversion compare showed me the database differences). But 2.5.1 is far from being as fast as 1.7.x was. The worst performance loss is still when using a menu of type "List All Categories", navigating to a list of sub-categories and then navigating to the leaf level where a list of articles has to be rendered.

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Mon Feb 20, 2012 8:32 pm

For sites that are still having slow site issues. I need you to carefully read this document. http://docs.joomla.org/Fixing_the_assets_table

We have successfully solved the slow site issue and it does not relate to core Joomla. The mistake comes from bad imported data and how it relates to the asset table.

I removed the file I had posted earlier. The file was not for everyone case. We figured this out after testing with alternate sites.

We had a site that was eating up huge amounts of CPU and killing a service. After the fix everything has been fast as ever. This picture shows how much the CPU usage improved after fixing the issues in the database.
http://quick-markup.com/image/4f42ad121c853

Be careful with your imports, and be sure to follow the instructions on fixing your assets table.
http://docs.joomla.org/Fixing_the_assets_table

Gary Brooks
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Tue Feb 21, 2012 6:17 am

@garyjaybrooks

I read entire http://docs.joomla.org/Fixing_the_assets_table and my asset tables are not broken, still I am having the issue

User avatar
garyjaybrooks
Joomla! Guru
Joomla! Guru
Posts: 556
Joined: Wed Feb 20, 2008 4:34 am
Location: Michigan
Contact:

Re: Slow queries on large website

Post by garyjaybrooks » Tue Feb 21, 2012 2:42 pm

@Puneetjain87 what is the URL of your site? Did you upgrade your site from 1.5? Give me a small story of how your site got slow?
Gary Brooks - Joomla Hosting with Phone Support http://www.CloudAccess.net Official host of http://demo.joomla.org

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Tue Feb 21, 2012 2:48 pm

@garyjaybrooks

I have replied earlier, please check the below links

http://forum.joomla.org/viewtopic.php?f ... 0#p2741705

http://forum.joomla.org/viewtopic.php?f ... 0#p2742376

Adding to above links, according to my webhost mysql eats up CPU and there are lots of slow queries causing this.

PS: Check PM for URL

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Fri Feb 24, 2012 5:53 pm

@GaryJayBrooks

I am waiting for you to revert back.

doktorrakev
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Feb 27, 2012 7:31 am

Re: Slow queries on large website

Post by doktorrakev » Mon Feb 27, 2012 7:47 am

Hi,

first sorry for my english. I had the same problem. Lot of articles and the same query fired twice (once without limit). I find temporary solution.

In components/com_content/models/articles.php in function getListQuery

change this line:

Code: Select all

a.id, a.title, a.alias, a.title_alias, a.introtext
to this:

Code: Select all

SQL_CALC_FOUND_ROWS a.id,a.id, a.title, a.alias, a.title_alias, a.introtext
than open file libraries/joomla/application/component/modellist.php

before constructor put this line:

Code: Select all

protected $total = null;
in function getItems put this:

Code: Select all

// Load the list items.
		$query	= $this->_getListQuery();
                
                /*ADDED*/
                $qArt = trim($query ->__toString());
                $selectTotal = false;
                if(strpos($qArt,'SQL_CALC_FOUND_ROWS a.id') !== false){                 
                        $selectTotal = true;                                  
                }
                /*ADDED END*/
                

		$items	= $this->_getList($query, $this->getStart(), $this->getState('list.limit'));
                
                
                 /*ADDED*/
                if($selectTotal){
                    $this->_db-> setQuery('SELECT FOUND_ROWS()');
                    $count = $this->_db->loadResult();
                    $this->total = $count;
                }
                /*ADDED END*/

finaly add this code to the function: getTotal

Code: Select all

// Try to load the data from internal storage.
		if (!empty($this->cache[$store])) {
			return $this->cache[$store];
		}
                /*ADDED*/
                if($this->total != null){
                    $this->cache[$store] = $this->total;
                    return $this->total;
                }
                /*ADDED END*/
		// Load the total.
		$query = $this->_getListQuery();
Hope this helps someone. This should reduce the second slow query, instead of we call just SELECT FOUND_ROWS(). This should work in category, frontpage, in modules news and latest.

Please note, that after update could be updated files rewritten.

Tested on Joomla 1.7.3

Doktor

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Mon Feb 27, 2012 7:59 am

I have slow query log, if any body wants to see I can share.

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Mon Feb 27, 2012 8:24 am

Hi doktorrakev
Will it work on 2.5.1

doktorrakev
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Feb 27, 2012 7:31 am

Re: Slow queries on large website

Post by doktorrakev » Mon Feb 27, 2012 8:34 am

I dont know, but the code structure seems to be the same as in 1.7 so it should work

puneetjain87
Joomla! Explorer
Joomla! Explorer
Posts: 337
Joined: Mon Feb 16, 2009 4:38 am

Re: Slow queries on large website

Post by puneetjain87 » Mon Feb 27, 2012 8:38 am

@Doktorrakev
Should I share my slow query log with you.

doktorrakev
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Feb 27, 2012 7:31 am

Re: Slow queries on large website

Post by doktorrakev » Mon Feb 27, 2012 8:47 am

The solution i posted should solve problem with this 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_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
so if you see in log another one, i cant help


Locked

Return to “Performance - Joomla! 2.5”