Slow queries on large website

[breto33]

@garyjaybrooks we have had to revert back to 1.5.25, the solution you post here: http://docs.joomla.org/Fixing_the_assets_table might work fine for a site with 20 articles and 3 categories, but we have a couple hundred nested categories and a few thousand articles. There is no way i can manually do all that. This is a huge problem that in my opinion Joomla needs to provide an automated solution to.

[puneetjain87]

@doktorrakev

There is "N" many times in my query.

Thanks

[ikajaste]

Here's my information on the problem, in case it helps someone.

My situation

I host over 40 Joomla sites that I've been upgrading from 1.0.15 via 1.5 to 2.5.1. After last week migrating the sites with >1000 articles, my server got really overtaxed because of this query:

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;

For few sites the execution of that single query took over a minute, with MySQL process "Copying to tmp table". The query is apparently caused by a recursive query in libraries/joomla/access.php around line 187.

Though the problem doesn't seem to appear very consistently, I did manage to find conditions that replicate it.

It's worth nothing that logging in as the Super User does not trigger the problem (assumedly because since super user has full access Joomla skips the acl check), but logging in as a user with lesser priviledges can trigger it. Accessing user control from the admin interface also seems to trigger it. I am not sure if this is only in my conditions or if is more general behaviour.

Fixing the problem

While tracing the problem I noticed the assets table seems broken, and ended up writing a script that recreates the tree structure as best as it understands, and then reassigns new parent_id,lft,rgt,level values for all the rows.

At least my script fixes the problem. You can get it from here:

GitHub / ikajaste / Joomla-2.5-Asset-Weaver

(Use the ZIP link at left side to download the code, and run it as command line php. Also read the README.)

I would guess the instructions at Fixing the assets table also fix the problem, but it is pretty tiresome to execute on site with 92 categories, not to mention a system with over 40 sites, each having at least over 6 categories...

The fix_assets.php might also fix the problem, I haven't tried it yet. Will update this article if I get around to trying it. First, I have those 40 sites to fix.

Note that the even on a fixed database the query still takes the same time, but the apparently fixing the database causes the query not to be executed by the Joomla code without more sepefic limitations to it.

... but as to why any sane migration process would let something as crappy as a half-broken assets table through, I cannot say...

[btomczak]

I would be very interested in the script you've created. I'm in a similar situation and was planning to write a script very much like what you describe. Would you be willing to share it with me so I can test?

[ikajaste]

I would be very interested in the script you've created. I'm in a similar situation and was planning to write a script very much like what you describe. Would you be willing to share it with me so I can test?

Yes, of course. However, I did just find with testing that it still seems to have one bug (categories do not get assigned with the asset com_content as parent). I'll be fixing it shortly.

[ikajaste]

I need confirmation for one thing: Is the root category (#_categories WHERE title="ROOT") supposed to have asset_id = 0 (note that there is not supposed to be any #_assets WHERE id = 0). That is, is it supposed to not have any asset associated with it?

[cuteschweinchen]

ikajaste that's some great news you posted. I also would be very interested in the script you've created.

[ikajaste]

The code that fixed this issue for me, without problems, on 40+ sites, is now available at GitHub, ikajaste / Joomla-2.5-Asset-Weaver

[cuteschweinchen]

ikajaste: can I use that script on Joomla 1.7? I downgraded a couple days from Joomla 2.5 again

[ikajaste]

ikajaste: can I use that script on Joomla 1.7? I downgraded a couple days from Joomla 2.5 again

I expect you can, AFAIK there aren't much changes from 1.7 to 2.5 on the asset database structure. The tool is written to only change the relevant parts of the database, so even if there are structural differences in the tables, it should still run ok as long as the relevant parts are the same.

But I haven't looked into it myself. You should at least create a clean 1.7 install, and run the tool against that. If it reports errors on a clean install, you probably can't use to fix 1.7.

[mcsmom]

@breto33
We've used the technique in the wiki docs on a site wot 7000 articles and it worked fine, I do't know why you would say it wouldn't work on a site of a few hundred articles.

1.7 to 2.5 had no changes in the asset table.

@ikajaste

It would be great if you would update your script to use the Joomla api for rebuilding.
Yes root should not have any actual assets associated with it.

[ikajaste]

@ikajaste

It would be great if you would update your script to use the Joomla api for rebuilding.
Yes root should not have any actual assets associated with it.

You mean so it would use the Joomla platform environment to get database access?

Yes, I agree it would be the best approach. But it would require further research on my part to get things buzzing. I defaulted to anything that produces results quickly.

I'll create an issue about it to github. But since the current approach works, I've already fixed my sites, and command line joomla seems like a bit of a weird beast to me, I probably won't be doing much work on it. Sorry. If someone else codes the change, happy to take the pull request.

[btomczak]

@ikajaste -- I really can't thank you enough for this script. Great job. I've got my problem site up and running on 2.5.1 pretty much okay.

Although this fixes the assets table problem, I believe there is still an issue with the original query that started this thread. I am still unable to use the latest news module. It doesn't take the server down the way the assets table problem did, but still so slow as to be unusable on a site with 12,000 articles.

[nzimas]

@ikajaste
For some strange reason, your script syas that the assets table is not present, although it actually is.

root@rtvnweb:~/ikajaste-Joomla-2.5-Asset-Weaver-6a0ce5f# php assetweaver.php /path/to/configuration.php
PHP Deprecated: Comments starting with '#' are deprecated in /etc/php5/cli/conf.d/mcrypt.ini on line 1 in Unknown on line 0
Reading configuration from /path/to/configuration.php
Connecting to <database_name> as root to read tables: prefix_assets
Table '<database_name>.prefix_assets' doesn't exist

Mysql tells a different tale, however:

mysql> use <database_name>;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------------------+
| Tables_in_rtvnde |
+-------------------------------------+
| jupgrade_categories |
| jupgrade_menus |
| jupgrade_modules |
| jupgrade_steps |
| prefix_admintools_acl |
| prefix_admintools_adminiplist |
| prefix_admintools_badwords |
| prefix_admintools_customperms |
| prefix_admintools_filescache |
| prefix_admintools_ipautoban |
| prefix_admintools_ipblock |
| prefix_admintools_log |
| prefix_admintools_redirects |
| prefix_admintools_scanalerts |
| prefix_admintools_scans |
| prefix_admintools_storage |
| prefix_admintools_wafexceptions |
| prefix_ak_acl |
| prefix_ak_profiles |
| prefix_ak_stats |
| prefix_ak_storage |
| prefix_assets |

Must be noted that MySQL runs on a remote machine, although that should not be an issue since all info on cofiguration.php is correct.


[EDIT]
After reading through assetweaver.php, I am under the impression that it completely bypasses the dbhost parameter and may just assume localhost.

[breto33]

@mcsmom obviously you only had a few categories on your site and not a few dozen or a few hundred. It would takes weeks of careful clicking to implement the fix specified here: http://docs.joomla.org/Fixing_the_assets_table

[miracle3736]

Is there any official reply from the developers? My site is in big trouble since a few weeks, getting my server down about once a day. All of a sudden the number of processes explodes and the system hangs. I see many MySQL queries that are taking way too long time and are processing way to many rows. It's a website with about 13.000 articles.

Looking around at this forum (and elsewhere on the internet) it seems this Joomla version is very unstable in certain setups (big sites?) and many people seem to have this problem. So... developers please respond and help us out, don't leave us alone....

[breto33]

@miracle3736 we had to revert back to Joomla 1.5 as our site was unusable. And for the ones that want to say it is a server issue I will let them know our site resides on servers maintained by the state that have pretty much limitless resources applied to them. This is really disappointing for us because our state just passed a requirement that only allows for Drupal or dotnetnuke as cms systems for agencies to use.

Because we have been on Joomla for years (since 1.0) we were grandfathered in, but every time we do an upgrade or patch, we need to do an analysis of time to do it vs time to migrate, and if it is anywhere close, we will be forced to Drupal.

What is going on now with the manual fix that Joomla has proposed, sadly looks to me like if there is not an automated solution soon, we will have to switch to Drupal as our enforcement people will say that there is enough time spent in the upgrade to justify spending the extra time to convert to Drupal.

[cuteschweinchen]

miracle3736

I am having the same problem with my radio /news site with over 15.000 articles - after trying lots of things over the weeks nothing solved the problem.

Early this week I had enough and installed a clean Joomla 2.5 (new folder), added my template and used sp-transfer to move my articles to the clean version. So far the site is faster & performance improved. It is a lot of work and I am still working on it, but at least (hopefully) the site will work properly.

I think all the upgrades over the years has messed up the database.

[miracle3736]

I work with a rather new site which I transferred to an old home made CMS to Joomla 1.7 (and upgraded to 2.5). So messing up the database is not the problem I think. I think the problem is in the newest version 2.5.x. There's something wrong which is only a big problem with big sites. Calculating 13.000 articles or 130 articles is a big difference. My other 2.5 sites have no problem, but those are small site (10 to 20 articles).

[jservices]

So....after taking the time to read this rather lengthy thread I see there is still no REAL solution. The arguments for throwing more hardware at this is just silly in my opinion. I have a client with his own server, with a properly configured MySQL, 8-core Xeon, 8 gigs ram and scsi drives and he is hosting one single solitary site and yet when upgrading to 2.5.2 it causes the exact same issues others experience on a shared host. It crashes the mysql server with 800% cpu usage running the following query:

Code: Select all

Copying to tmp table | SELECT b.rules
FROM j17_assets AS a
LEFT JOIN j17_assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgt

The problem occurs every time I try to go to version 2.5.x from 1.7.5 (which works fine btw). I tried the fix_assets.php script and the assetweaver.php but neither resolved the issue for me.

The problem is definitely around the _assets table as has been articulated many times already. It seems pretty clear where the problem lies given all that has been reported and posted.0

Is there a Joomla developer looking into this?

[miracle3736]

Good to hear more people have the problem, sad to discover Joomla developers don't seem to care :-( Can anybody tell me how difficult it is to downgrade to 1.7?

[miracle3736]

Yesterday I thought I found the problem. I thought it had something to do with paging. All the slow queries were returning all my articles (about 30 Mb / 10.000 articles). Rows sent was always about 9387 rows. I disabled paging and set all the blog pages to show 15 intro text and 40 links. Then most of the slow queries changed into limit 0,55 which was good. Strange thing was there were still more than 200.000 rows examined. Also there were still queries that sent 9387 rows. I can't find where that's coming from.

An hour ago my server went down again (as it did about once or twice every day the last three weeks :-<). I looked at the log. There were 4421 queries in that time. I thought most of them should be in the last hour where the problems started. But that wasn't. Every hour (also in the middle of the night) there were about 400 queries run.

So.... I'm pretty sure visitors of the site have nothing to do with it. But I can't find what else could do these queries... If anyone would like to inspect my site, it's at www.fan.tv (it's Dutch).

[garyjaybrooks]

Joomla developers care very much! but this is from bad import and nothing to do with Joomla! itself or the teams that built Joomla. These teams do not have time or are the paid to come and fix bad import issues to new version of Joomla. I can tell you that the Joomla teams are working on a better migration tool we can give free to the community that works. Please rest assured we do care.

The issue I see stems from people who have used the Jupgrade or done a really bad import and the asset table are all messed up.

We have something new in motion on our end but its not a easy problem to solve, everyone has a different issue and to fix it auto is hard in logic. I have been walking around meeting people and trying to look at each installation but they are all different. Best bet is built a tool to detect the issue and then give it out so that those that have the infection can at least know that its the assets table.

If you are in a rush and can not wait, I suggest you do the import again on a new database with a different import tool. Here it he tool I suggest.

http://extensions.joomla.org/extensions ... tion/15609

I have heard good things about this tool

I do have some contractor friends that I can connect you with if you want to pay to have someone look at it. Sorry this is bad option for most since free is best best.

I'll be back if I can push out the infection detection tool.

[breto33]

@garyjaybrooks Actually it is an issue with Joomla itself. You guys are the ones discontinuing support for Joomla 1.5 in April. Some people have huge websites, and simply starting over with a fresh install is not an option. If you are ending support for a version, you had better provide an easy solution for your customers to upgrade that version to a new one and not rely on a 3rd party that obviously did it half a55. I dont really see any reason to stick with Joomla after seeing your new release cycle and strategy after this fiasco, its just going to happen all over again when support for 2.5 stops.

Some of the customers on Joomla are very high traffic websites with a ton of content. We have been in Joomla since 1.0, and have went through 1 nightmare migration already. If we have to go through a manual process to get this fixed, we will not be able to stay with Joomla as our CMS, we will have to switch to Drupal or dotnetnuke as we have new organizational standards that only allow the use of those 2 CMS products.

At the end of the day it is your (Joomlas) responsiblity to provide a seamless upgrade, or, to continue to support your current version if you want to keep customers. If customer retention is not your goal, you are on the right path.

[jservices]

Please correct me if I am wrong here but isn't the upgrade tool found in Joomla 1.7.x build by Joomla developers???? It does not look like a third party tool, I did not install it and yet it is there when installing Joomla 1.7. Please clarify.

Or, are you saying this problem precedes Joomla 1.7 and was a remnant of an upgrade from 1.5 to 1.7?

[btomczak]

@jservices - correct. This is a problem in upgrading from 1.5 to 1.6/1.7/2.5. Once you successfully get to 1.7 (and this seems to remain a difficult and sketchy process) upgrades are theoretically built-in.

But all of this makes me wonder what happens when 3.0 comes out. There are no guarantees of backward compatibility that I've seen and we may find our sites getting blown up yet a third time.

[jservices]

Even though the site works perfectly fine with no performance issues in Joomla 1.7?

[garyjaybrooks]

You can continue to use Joomla 1.5 forever. I know many sites that are still running in Mambo and Joomla 1.0. Nothing says you have to upgrade or select a tool that does bad migrations.

We are lucky we found the issue on this thread. Now everyone has to go back and work on it.

This thread is not made to complain to leadership. If you want to complain try the groups message board and post something with logic and planning.

Lets all be positive! - Thank you for very.

[breto33]

@garyjaybrooks your solution at this point is to have me use a commercial upgrade for an open source CMS to move my site into the "long term" release (1 year) or to stay on a version that will no longer be supported and will be highly vulnerable to hackers?

What a great choice.

[garyjaybrooks]

Bret,

You could also consider asking the Jupgrade people if they have fixed the issue and you can try again. If you have a big enough site that you care about paying a few dollars for a extension should not be an issue.

Another option is to move your data over manually using a SQL tool.

Gary Brooks