Page 3 of 6

Re: Slow queries on large website

Posted: Wed Feb 08, 2012 6:11 pm
by cuteschweinchen
Hi Gary,

any updates yet?

Re: Slow queries on large website

Posted: Thu Feb 09, 2012 10:34 am
by TBone
Hi guys,
We also had an issue with performance after upgrading with Jupgrade. Problem was the #_assets table. All parentid's were set to "0". Please check your assets table. If you see lots of parentid's with 0 value this could be the problem.

To solve this issue you have to run a piece of code to set the parentid's to it's right value.

Re: Slow queries on large website

Posted: Thu Feb 09, 2012 10:10 pm
by pawelp
TBone,

Can you share this code? Is it something very specific to your site? What is the correct value for parent id?

Re: Slow queries on large website

Posted: Fri Feb 10, 2012 5:20 am
by puneetjain87
Most of my parent ids are not 0 and are different, and I am still facing the issue.
It's because of slow queries, as told to me by my webhost.

Re: Slow queries on large website

Posted: Fri Feb 10, 2012 6:21 am
by Rik Brown
BBC2009 wrote:Does this patch require new install ?

http://joomlacode.org/gf/project/joomla ... m_id=27774

I just opened last 2.5.1 Joomla ZIP (from Joomla website) and i see that original joomla.sql in "installation" folder has 1676 lines. This one from patch has 6431 lines.
Did you run the patch? If so, what is the outcome?

Very anxious for a solution.

Thanks. -- Rik

Re: Slow queries on large website

Posted: Fri Feb 10, 2012 6:36 am
by puneetjain87
Gary, have you come up with any solution?

Re: Slow queries on large website

Posted: Fri Feb 10, 2012 8:01 am
by TBone
The query slowing things down in my case was this one:

Code: Select all

SELECT b.rules
FROM j17_assets AS a
LEFT JOIN j17_assets AS b ON b.lft <= a.lft
AND b.rgt >= a.rgt
WHERE (
  a.id =778
OR a.parent_id =0
  )
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lft
LIMIT 0 , 30;
Which is used on lot's of pages in front and backend.

The delay was caused as I mentioned earlier by wrong parentid values in #_assets table.

After searching I found a piece of code on http://joomlacode.org/gf/project/joomla ... m_id=25699

The piece of code is in the attachment.

Just execute the php script.
Make sure you make a backup of your #_assets table first!

You could also try the second piece of code in the post that fixes the lft and rgt records in this same table.

It worked for me.

Good luck.

Re: Slow queries on large website

Posted: Fri Feb 10, 2012 5:49 pm
by garyjaybrooks
I need to take inventory from everyone.

Who has a NEW install of Joomla 2.5 that is slow?

Who imported data from 1.5?

What tool did you use if you imported your data from 1.5?

Gary Brooks

Re: Slow queries on large website

Posted: Sat Feb 11, 2012 5:20 am
by puneetjain87
garyjaybrooks wrote:I need to take inventory from everyone.

Who has a NEW install of Joomla 2.5 that is slow?

Who imported data from 1.5?

What tool did you use if you imported your data from 1.5?

Gary Brooks
I migrated from 1.5 to 1.7 and then to 2.5

I did a fresh install of 1.7 and just imported my articles and users from 1.5 to 1.7 (I did not use jupgrade)

upgrading to 2.5 was pretty simple, I just hit the update button and it happened.

Let me know if you need any other details.


My webshost says that query "copy to tmp table" creates lots of load on server and they need to shut down sql services because of that.

Re: Slow queries on large website

Posted: Sat Feb 11, 2012 9:11 pm
by garyjaybrooks
@Puneetjain87,

What tool did you use to move your articles from 1.5 to 1.7 and how many articles do you have?

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 2:38 am
by BBC2009
garyjaybrooks wrote:I need to take inventory from everyone.

Who has a NEW install of Joomla 2.5 that is slow?

Who imported data from 1.5?

What tool did you use if you imported your data from 1.5?

Gary Brooks
I have 2.5 updated from 1.7. Same problem with both.

- Website is build mostly with K2. And i am not a begginer with K2, regarding perfomance and K2 settings.
- Some cca 100 Items, not so heavy website.
- Debug shows some 90-100 queries in Homepage and some 40-60 queries Item view.

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 2:39 am
by BBC2009
Rik Brown wrote:
BBC2009 wrote:Does this patch require new install ?

http://joomlacode.org/gf/project/joomla ... m_id=27774

I just opened last 2.5.1 Joomla ZIP (from Joomla website) and i see that original joomla.sql in "installation" folder has 1676 lines. This one from patch has 6431 lines.
Did you run the patch? If so, what is the outcome?

Very anxious for a solution.

Thanks. -- Rik
No, i don´t know how to use it without new install. And waiting for it to show in Admin - Update.

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 7:12 am
by puneetjain87
@garyjaybrooks

I used J2XML http://extensions.joomla.org/extensions ... I7fQ%3D%3D to export my files from joomla 1.5

and used

J2XML Importer http://extensions.joomla.org/extensions ... I7fQ%3D%3D to import into Joomla 1.7

The number of articles was above 2000

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 12:21 pm
by BBC2009
TBone wrote:The query slowing things down in my case was this one:

Code: Select all

SELECT b.rules
FROM j17_assets AS a
LEFT JOIN j17_assets AS b ON b.lft <= a.lft
AND b.rgt >= a.rgt
WHERE (
  a.id =778
OR a.parent_id =0
  )
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lft
LIMIT 0 , 30;
Which is used on lot's of pages in front and backend.

The delay was caused as I mentioned earlier by wrong parentid values in #_assets table.

After searching I found a piece of code on http://joomlacode.org/gf/project/joomla ... m_id=25699

The piece of code is in the attachment.

Just execute the php script.
Make sure you make a backup of your #_assets table first!

You could also try the second piece of code in the post that fixes the lft and rgt records in this same table.

It worked for me.

Good luck.
This is what i get when i run this PHP file:

Code: Select all

827-8 328-3 729-7 1930-19 2531-25 834-8 835-8 840-8 841-8 842-8 843-8 844-8 45-51 850-8 851-8 852-8 853-8 854-8 55-51 56-51 57-51 58-51 59-51 360-3 864-8 383-3 JLIB_DATABASE_ERROR_REBUILD_FAILED

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 2:25 pm
by BBC2009
Anyway, i don´´t think it will work in my case. I don´t use Joomla native Sections/Categories and Joomla Articles at any of my websites.

Re: Slow queries on large website

Posted: Sun Feb 12, 2012 4:28 pm
by Rik Brown
garyjaybrooks wrote:I need to take inventory from everyone.

Who has a NEW install of Joomla 2.5 that is slow?

Who imported data from 1.5?

What tool did you use if you imported your data from 1.5?

Gary Brooks
Gary:

I found Joomla 2.5 an improvement somewhat over 1.7.3. However, we have queries that can run a minute (when the query is triggered multiple times by different users) on a quite fast server with SSD drives and 12 GB of RAM. We do have a lot of articles and the JOIN queries produce huge tmp tables that can max out our ramdisk. I really don't understand the usage of those queries since the information is not displayed to anyone. I wish I could just turn it off.

I'd be happy to forward my mysql slow-query-log. There is one specific query which is really bogging down our site the most and it appears to be from the Joomla core. If the query doesn't run, the site delivers our pages at a very decent clip.

Thanks. -- Rik

PS: This is such a problem that I've been looking into using a mysql "kill" utility like mt-kill (now pt-kill) to kill any query running more than "x" seconds.

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 12:24 am
by flashgordonweb
Hi Rik, can you tell us what query is the one really slowing down your site? I have a site with 13,000 articles that is currently sitting in 1.7.5 because of this issue. Have you looked at the query associated with ContentModelArticles::getListQuery() ? Or is your query problem something else? Thanks.

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 12:55 am
by tappy52dog
So it seems like after Joomla 1.5, site speed has only gotten worse with 1.7 and even worse yet with 2.5? Joomla was already slow with 1.5, I can't imagine 2.5 being even a slower CMS. I hope they fix this as I really don't want to push clients back to Drupal and Wordpress if not needed.

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 6:55 am
by Rik Brown
flashgordonweb wrote:Hi Rik, can you tell us what query is the one really slowing down your site? I have a site with 13,000 articles that is currently sitting in 1.7.5 because of this issue. Have you looked at the query associated with ContentModelArticles::getListQuery() ? Or is your query problem something else? Thanks.
EDIT: CODE PREVIOUSLY HERE WAS INCOMPLETE. SEE THE COMPLETE CODE HERE.

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 8:04 am
by TBone
On what type of page is this query fired?

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 1:37 pm
by Rik Brown
TBone wrote:On what type of page is this query fired?
I get this moving around the site in both the front end and back end. Its hard to tell exactly which pages fire it since it seems to show up all over the place. I know that I can kill it (via a mysql utility) and it doesn't seem to make any difference on the front end.

But the query also definitely triggers when I log into the backend and if I kill the query then, I get a 500 server error. So I let it run when I log into the backend. But I'm thinking of using mt-kill (now pt-kill) to kill the query on the front end unless it can get "fixed."

-- Rik

Re: Slow queries on large website

Posted: Mon Feb 13, 2012 4:10 pm
by BBC2009
tappy52dog wrote:So it seems like after Joomla 1.5, site speed has only gotten worse with 1.7 and even worse yet with 2.5? Joomla was already slow with 1.5, I can't imagine 2.5 being even a slower CMS. I hope they fix this as I really don't want to push clients back to Drupal and Wordpress if not needed.
Believe me i am thinking about Drupal too. A lot of work to make same website in Drupal, but what to do.
Other solution is to convert to Joomla 1.5, some way or other.

No matter what i do pageload is relatively slow with only me as visitor. (or Live server with few visitors at the same time)
What would it look like when number of visitors increase significantly.

Joomla 1.5 with expire headers is space rocket. Joomla 1.7-2.5 with expire headers, almost as there are no expire headers set.

Re: Slow queries on large website

Posted: Tue Feb 14, 2012 8:45 am
by TBone

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 = N THEN a.created ELSE a$
  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 = N
  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 = 'S' AND parent.published != N $
  WHERE a.access IN (N) AND CASE WHEN badcats.id is null THEN a.state ELSE N END = N AND (a.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
  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.na$
  ORDER BY  c.lft,  a.ordering,  a.created LIMIT N, N
I tried to run this query on our 2.5.1 installation since I haven't got a 1.7 available right now. But it gives me several syntax errors. Could it be some things are missing. For example an 'END' after the 'ELSE a$'.

Re: Slow queries on large website

Posted: Tue Feb 14, 2012 2:01 pm
by RedEye
Has someone tested if a STRAIGHT_JOIN helps with those queries?

Re: Slow queries on large website

Posted: Tue Feb 14, 2012 4:27 pm
by tappy52dog
Has any cache extensions or settings helped you guys out at all?

Re: Slow queries on large website

Posted: Tue Feb 14, 2012 10:18 pm
by Rik Brown
TBone:

I'm sorry. Wordwrap wasn't on when I cut that that query from my ssh client. Here is the entire code:

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 = N THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = N THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, 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 N 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 > 'S' 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, N) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE N 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 = N
  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 = 'S' AND parent.published != N GROUP BY cat.id ) AS badcats ON badcats.id = c.id
  WHERE a.access IN (N) AND CASE WHEN badcats.id is null THEN a.state ELSE N END = N AND (a.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
  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  c.lft,  a.ordering,  a.created LIMIT N, N
I'm so sorry for the confusion caused. -- Rik

Re: Slow queries on large website

Posted: Wed Feb 15, 2012 4:24 am
by RedEye
You not see all the N`s Rik? Also no need to repeat this query here ten times, because all suffer on the same queries, pls refer to page one of this thread, queries are there.
edit: Rik like I wrote in your other thread, if you not need latest/most read article module try to disable because the query you posting is from this.
Or you can try the STRAIGHT_JOIN I mentioned, it`s worth to give it try if it helps or not.

Re: Slow queries on large website

Posted: Wed Feb 15, 2012 7:39 am
by puneetjain87
tappy52dog wrote:Has any cache extensions or settings helped you guys out at all?
Nothing helped me.

Re: Slow queries on large website

Posted: Wed Feb 15, 2012 7:54 am
by Rik Brown
RedEye wrote:You not see all the N`s Rik? Also no need to repeat this query here ten times, because all suffer on the same queries, pls refer to page one of this thread, queries are there.
edit: Rik like I wrote in your other thread, if you not need latest/most read article module try to disable because the query you posting is from this.
Or you can try the STRAIGHT_JOIN I mentioned, it`s worth to give it try if it helps or not.
RedEye:

"Most Read" module has been disabled some days ago, but I'm still getting this query executed. Something else must be triggering it.

-- Rik

Re: Slow queries on large website

Posted: Wed Feb 15, 2012 8:04 am
by TBone
Ah ok.
I tried running the query again in phpmyadmin
I modified all the N's

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.images, a.urls, 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 > 'S' 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 j17_content AS a
  LEFT JOIN j17_content_frontpage AS fp ON fp.content_id = a.id
  LEFT JOIN j17_categories AS c ON c.id = a.catid
  LEFT JOIN j17_users AS ua ON ua.id = a.created_by
  LEFT JOIN j17_users AS uam ON uam.id = a.modified_by
  LEFT JOIN (
  SELECT contact.user_id, MAX(contact.id) AS id, contact.language
  FROM j17_contact_details AS contact
  WHERE contact.published = 0
  GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
  LEFT JOIN j17_categories as parent ON parent.id = c.parent_id
  LEFT JOIN j17_content_rating AS v ON a.id = v.content_id
  LEFT 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 = 'S' AND parent.published != 0 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.publish_up = 'S' OR a.publish_up <= 'S') AND (a.publish_down = 'S' OR a.publish_down >= 'S')
  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  c.lft,  a.ordering,  a.created LIMIT 0, 1
The query takes 0,27 seconds on NOT one of the most fast servers.
The database holds 1100 articles.

How many records does your jos_categories table contain? The most suspected part for slowing things down seems to be the LEFT OUTER JOIN on jos_categories in this query. If parent en rgt and lft where migrated incorrectly this maybe could be a problem like it did in my case on jos_assets.