The Joomla! Forum ™

I've been following en contributing to the thread 'slow queries on large website'. Most of the people there have problems concerning the #_assets table. For as fas as I can see my #_assets table is correct. As it should be, because I didn't use Jupgrade.

History: I started with an empty 1.7 installation and used freakedout Content Uploader to get the content of my old sites (about 10K of articles) imported. This worked well. After some thorough testing we put the site live around the 20st of December 2011. Joomla 2.5 was released around January 24th 2012. So our site was up and running without any big problems for already a month. Some days after the release I upgraded to 2.5 and some days later all the trouble started.

To make everything clear: our starting point was an old ASP-coded website with an 'home made' CMS. I hired a developer to convert my MSSQL tables to MYSQL tables and to create XLSfiles with all the content. The XLS files where used to import in to Joomla 1.7 with 'freakedout Content Uploader'.

http://extensions.joomla.org/extensions/migration-a-conversion/data-import-a-export/10735

Send me a PM for the live site URL

Can you share the live URL here?

What is it that you see is slow?

Do you have a dedicated server so we can see resources?

If you do have a server, what resources are being used most?

Can you please provide me a full screen shot or video of the assets table?

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

Live URL is www.fan.tv

Site is pretty slow, but that ain't the biggest problem. About once or twice a day the VPS will hang itself, site becomes unreachable and (for me) rebooting the VPS is the only option. Hosting provider says he sees very many MySQL queries that run very long and seem to be very inefficient in using indexes (not using them). I can sent you mysql-slow.log via wetransfer.com

I have a dedicated VPS with 6Gb of RAM and 3 cores. In normal state I have about 200-300 disk I/O per second, when the problem occures it can easily get to 50.000 disk I/O per second. Memory is always completely free (10-15% used). Roundtrip time is usually around 1ms. I can mail you screens hots of the statistics screen.

I can mail you an export of the assets table if you want.

Would you be willing to give me a copy of the whole site so I can build a replicated copy to test?

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

What exactly would you need? A Akeeba backup?

Akeeba backup would be great. Find me on skype chat.

garyjaybrooks2000

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

Is this issue of your resolved now. I have similar issue. I have posted about it here viewtopic.php?f=616&t=708415

I am still looking for a possible solution and while searching I landed on this https://groups.google.com/forum/#!topic ... -9HOB7zEN4
It's seems to be a Joomla issue for sure. It's there on Joomlacode as well, but it seems there are very less people who are bothered to get it working.

PS: my jos_content is a heavy one. Over 4000 articles and its size is around 18MB

i am also facing same problem after upgraded from 1.7 to Joomla! 2.5.4

server details
VPS
6GHZ
4GB Ram

support team have increase the caching buffers so the server up and running now

root@admin [~]# mysqlreport
MySQL 5.0.95-community- uptime 0 11:46:5 Sat Apr 14 07:19:27 2012

__ Key ***** Manual signatures are NOT allowed *****
Buffer used 1.54M of 64.00M %Used: 2.41
Current 12.65M %Usage: 19.76
Write hit 99.57%
Read hit 100.00%

__ Questions ***** Manual signatures are NOT allowed *****
Total 1.06M 25.1/s
Com_ 645.16k 15.2/s %Total: 60.78
-Unknown 518.93k 12.2/s 48.89
QC Hits 446.86k 10.5/s 42.10
DMS 436.47k 10.3/s 41.12
COM_QUIT 51.84k 1.2/s 4.88
Slow 10 s 47 0.0/s 0.00 %DMS: 0.01 Log: ON
DMS 436.47k 10.3/s 41.12
SELECT 331.22k 7.8/s 31.21 75.89
DELETE 64.82k 1.5/s 6.11 14.85
INSERT 22.47k 0.5/s 2.12 5.15
UPDATE 17.41k 0.4/s 1.64 3.99
REPLACE 550 0.0/s 0.05 0.13
Com_ 645.16k 15.2/s 60.78
stmt_prepar 173.01k 4.1/s 16.30
stmt_close 173.01k 4.1/s 16.30
stmt_execut 173.01k 4.1/s 16.30

__ SELECT and Sort ***** Manual signatures are NOT allowed *****
Scan 21.08k 0.5/s %SELECT: 6.36
Range 100.57k 2.4/s 30.36
Full join 104.94k 2.5/s 31.68
Range check 65.81k 1.6/s 19.87
Full rng join 0 0/s 0.00
Sort scan 187.44k 4.4/s
Sort range 68.41k 1.6/s
Sort mrg pass 60 0.0/s

__ Query Cache ***** Manual signatures are NOT allowed *****
Memory usage 55.64M of 64.00M %Used: 86.94
Block Fragmnt 14.25%
Hits 446.86k 10.5/s
Inserts 201.44k 4.8/s
Insrt:Prune 10.28:1 4.3/s
Hit:Insert 2.22:1

__ Table Locks ***** Manual signatures are NOT allowed *****
Waited 1.29k 0.0/s %Total: 0.10
Immediate 1.26M 29.7/s

__ Tables ***** Manual signatures are NOT allowed *****
Open 349 of 512 %Cache: 68.16
Opened 1.00k 0.0/s

__ Connections ***** Manual signatures are NOT allowed *****
Max used 61 of 1000 %Max: 6.10
Total 51.85k 1.2/s

__ Created Temp ***** Manual signatures are NOT allowed *****
Disk table 55.32k 1.3/s
Table 312.39k 7.4/s Size: 16.0M
File 120 0.0/s

__ Threads ***** Manual signatures are NOT allowed *****
Running 3 of 5
Cached 4 of 8 %Hit: 99.85
Created 78 0.0/s
Slow 0 0/s

__ Aborted ***** Manual signatures are NOT allowed *****
Clients 7 0.0/s
Connects 0 0/s

__ Bytes ***** Manual signatures are NOT allowed *****
Sent 6.65G 156.9k/s

Received 365.38M 8.6k/s

with this can i assume that server is running normal?

What is the URL of your site?

Did you import the site from 1.5?

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

thanks for taking time to check the problem,

i have import it from 1.7 ...URLs pm messages send

When you say import? you mean few click upgrade? 1.5 to 2.5 is not a import it should be a few clicks upgrade.

Correct me if Im wrong unless you did something different other the the 1 click upgrade in the admin section of Joomla.

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

let me explain it

i have install fresh Joomla 1.7 in the server and then

all other updates done by admin section using the joomla update until 2.5.4.

i use the j2xml importer to transfer few articles from 1.5.22

So this site does have imported data from 1.5 ? Correct?

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

yes of course..

i can provide more details if u want by email or pm

Can you find me on skype? I want to look at your assets table to see if its messed up.
garyjaybrooks2000 < ID

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

i have added you in Skype

@Gary
I appreciate the help which you are offering in many Joomla threads. But you are yet to answer any of the issues raised by me in this thread and the slow query thread which is now closed.

Though, the point you are raising might be valid and helping many but the fact is that for many of us that is not at all working.

Please have a look at this thread https://groups.google.com/forum/#!topic ... -9HOB7zEN4 Isn't it a Joomla Problem?

Just to add, Joomla puts heavy load on server when the cache is being rebuild after being cleared or deleted. It never used to happen in Joomla 1.5 but it happened to me on 1.7 and happens on 2.5.

A query "Copying to tmp table" puts heavy load on the server.

Also, the first byte time of Joomla 1.7 and Joomla 2.5 is very high when compared to 1.5. When I was on 1.5 it was less than 500ms and now it is over 1.2 seconds every time, sometimes even 4 seconds. To find whether the first byte issue is with my website only or is it happening with Joomla.org I did some speed test and was amazed to find that Joomla.org is still on version 1.5. Anyways this is a separate issue.

I hope I will get a response this time.

#_assets table have 4,300 rows and some example of it

Title - Root Asset
primary key - 1
parent_id - 0
Level - 0
Lft - 1
Rgt -8684

Com_content
primary key - 8
parent_id - 1
Level - 1
Lft - 80
Rgt -8573

com_content.category.149
primary key - 8
parent_id - 113
Level - 8
Lft - 4704
Rgt -113

so the #_assets will messed up with articles transfer from 1.5 in any other way?
lft and rgt also have to fixed in above example in #_assets table?

how ever parent id and levels are not messed up with
root
components
categories
Subcategories

If this helps, I just had a talk with my webhost and they say _categories table puts load on server.

We have a tool we can hookup to the database server to look at server. I can hookup this tool if you can give us remote access to your mysql database. You would have to find me on skype. I would love to solve this problem live with you on the forum but its not possible. We have to look around the node, isolate the site in some cases, and even run xdebug. I also need a history of the site, what extensions you have installed, plus I need to review the assets table.

I would prefer if you great a Google doc with the history of your site and all access information so I can do a study. I want to find that its core Joomla issues but to date I have found many bad imports from 1.5 due to bad migrations.

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

I have already added you on skype. You are yet to accept it.

I accepted everyone.

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

I have the exact same problem. I upgraded from j1.5.25 to j1.6 and up to j 1.7.5. No issues. I used jupgrade from 1.5.25 to j1.6.

Last week after reading that j 1.7.5 will be end of support soon, I took the step to do the builtin update in Joomla 1.7.5.
After doing the upgrade the website began to slowdown, seeing tons of this one:
333 root localhost ervikasdb Query 110 Copying to tmp table SELECT b.rules FROM 17_assets AS a LEFT JOIN j17_assets AS b ON b.lft <= a.lft AND b.rgt >= a.rgtW

Please help out, as this is really a big issue!

ervik,

What date did you do this work?

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

Gary,

I did it on the 9th of April, to be exact

When did you download the jupgrade.

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

Oh, I used jupgrade back in august 2011 or something like that... so it's a good 9 or more months ago

Jupgrade has issue where it caused issues with importing the data in a strange way that makes things strange. I *think* this is issue with that. I would need access to look at phpmyadmin to tell you if you have assets table issue.

If you could also try to please upload this assets table check - I would like to see the output. Could you do this?

-unzip
-put in root of site /httpdocs or /htdocs

Give me URL on private message. Make sure you reference link to this forum post in private message.

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

Hi,

It's a turnkeylinux joomla 1.6 vm, so I don't think phpmyadmin is allowed from the outside... or maybe but then I need to get my network guy to open up the port... I could dump the j17_assets table and attach it here if you like...
I upload it to the root of the site and here is the output:

Asset Diagnostic

Is there any other asset with parent_id of 0? If no good if yes bad

Asset Table: yes
Category Table: yes

Is there any category without an asset in the asset table? if yes bad, if no good

com_banners: 0
com_contact: 0
com_content: 0
com_newsfeeds: 0
com_rokcandy: 0
com_weblinks: 0

Is there any article without an asset in the asset table? if yes bad, if no good
yes

Is there any category with an asset level of < 2? If yes, bad, if no good

com_banners: no
com_contact: no
com_content: yes
com_newsfeeds: no
com_rokcandy: yes
com_weblinks: no

Is there any article that has an asset level of < 3? If yes bad, if no good

yes

Is there any article that has a parent_id that does not correspond to a category? if yes bad, if no good
no