The Joomla! Forum ™



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.



Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 168 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next
Author Message
PostPosted: Wed Feb 15, 2012 1:22 pm 
Joomla! Ace
Joomla! Ace

Joined: Sat Jan 21, 2006 8:42 pm
Posts: 1140
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'


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 5:03 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 8:27 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Wed Feb 15, 2012 11:37 pm 
Joomla! Intern
Joomla! Intern

Joined: Wed Oct 15, 2008 9:30 pm
Posts: 87
Location: St. Louis, Missouri, USA
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


Top
 Profile  
 
PostPosted: Thu Feb 16, 2012 12:31 am 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Thu Feb 16, 2012 2:30 pm 
Joomla! Fledgling
Joomla! Fledgling

Joined: Mon Sep 12, 2011 11:51 pm
Posts: 3
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


Top
 Profile  
 
PostPosted: Thu Feb 16, 2012 6:33 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Wed Apr 19, 2006 2:42 pm
Posts: 138
Location: Beverwijk | The Netherlands
@scargonauts.
Solution is already in this thread (2 times)
Run the fix assets scripts to solve your problem.


Top
 Profile  
 
PostPosted: Thu Feb 16, 2012 6:37 pm 
Joomla! Fledgling
Joomla! Fledgling

Joined: Thu Feb 16, 2012 6:32 pm
Posts: 1
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.


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 12:45 am 
Joomla! Intern
Joomla! Intern

Joined: Sat Mar 29, 2008 3:52 pm
Posts: 74
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.


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 1:43 am 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 1:17 pm 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@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.


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 1:51 pm 
Joomla! Apprentice
Joomla! Apprentice

Joined: Fri Sep 09, 2011 1:10 pm
Posts: 12
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!


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 3:26 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 7:23 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
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.


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 9:15 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Fri Feb 17, 2012 9:30 pm 
User avatar
Joomla! Intern
Joomla! Intern

Joined: Sun Feb 08, 2009 4:41 pm
Posts: 50
I did it. You will see message there.


Top
 Profile  
 
PostPosted: Sat Feb 18, 2012 2:42 am 
Joomla! Apprentice
Joomla! Apprentice

Joined: Wed Dec 14, 2005 5:38 pm
Posts: 34
Location: Portland OR, USA
@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?


Top
 Profile  
 
PostPosted: Sat Feb 18, 2012 9:33 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@garyjaybrooks,
I am still waiting for you to reply my last question.

Thanks.


Top
 Profile  
 
PostPosted: Sat Feb 18, 2012 11:04 pm 
Joomla! Fledgling
Joomla! Fledgling

Joined: Sun May 13, 2007 4:35 pm
Posts: 2
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.


Top
 Profile  
 
PostPosted: Mon Feb 20, 2012 8:32 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
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


Top
 Profile  
 
PostPosted: Tue Feb 21, 2012 6:17 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@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


Top
 Profile  
 
PostPosted: Tue Feb 21, 2012 2:42 pm 
User avatar
Joomla! Guru
Joomla! Guru

Joined: Wed Feb 20, 2008 4:34 am
Posts: 562
Location: Michigan
@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


Top
 Profile  
 
PostPosted: Tue Feb 21, 2012 2:48 pm 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@garyjaybrooks

I have replied earlier, please check the below links

viewtopic.php?f=616&t=679355&start=60#p2741705

viewtopic.php?f=616&t=679355&start=60#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


Top
 Profile  
 
PostPosted: Fri Feb 24, 2012 5:53 pm 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@GaryJayBrooks

I am waiting for you to revert back.


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 7:47 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Mon Feb 27, 2012 7:31 am
Posts: 3
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:
a.id, a.title, a.alias, a.title_alias, a.introtext


to this:

Code:
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:
protected $total = null;


in function getItems put this:

Code:
// 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:
// 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


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 7:59 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
I have slow query log, if any body wants to see I can share.


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 8:24 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
Hi doktorrakev
Will it work on 2.5.1


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 8:34 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Mon Feb 27, 2012 7:31 am
Posts: 3
I dont know, but the code structure seems to be the same as in 1.7 so it should work


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 8:38 am 
Joomla! Explorer
Joomla! Explorer

Joined: Mon Feb 16, 2009 4:38 am
Posts: 337
@Doktorrakev
Should I share my slow query log with you.


Top
 Profile  
 
PostPosted: Mon Feb 27, 2012 8:47 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Mon Feb 27, 2012 7:31 am
Posts: 3
The solution i posted should solve problem with this query:

Code:
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 168 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6  Next



Who is online

Users browsing this forum: No registered users and 20 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group