Slow SQL Queries after Upgrading from 1.5 to 3.x

Discussion regarding Joomla! 3.x 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.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Locked
HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Thu Apr 09, 2015 1:53 am

hey, have anyone a solution for this problem.
since migration from joomla 1.5 to 3 i have the problem, that in my database run lot of processes. i have a network transfer from more than 3 GiB at hour. Thats not normal.

On slowmysql.log from my hoster i see lot of these processes:

Code: Select all

# Time: 150408 19:35:15
# User@Host: dbo376405720[dbo376405720] @ localhost []
# Query_time: 2.358831  Lock_time: 0.000273 Rows_sent: 5293  Rows_examined: 174411
SET timestamp=1428514515;
SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = '0000-00-00 00:00:00' 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, a.language,  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,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 ngie_content AS a
LEFT JOIN ngie_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN ngie_categories AS c ON c.id = a.catid
LEFT JOIN ngie_users AS ua ON ua.id = a.created_by
LEFT JOIN ngie_users AS uam ON uam.id = a.modified_by
LEFT JOIN ngie_categories as parent ON parent.id = c.parent_id
LEFT JOIN ngie_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM ngie_categories AS cat JOIN ngie_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,5) AND c.access IN (1,1,5) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2015-04-08 17:35:12') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2015-04-08 17:35:12')
ORDER BY  c.lft,   CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END  DESC ,  a.created;
Have anyone an idea how a can stop these processes? I think thats the problem, why my site is so slowly
Last edited by imanickam on Thu Apr 09, 2015 3:33 pm, edited 1 time in total.
Reason: Split from the post http://forum.joomla.org/viewtopic.php?f=616&t=709666. In the future, for your issues/questions create topics of your own.
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Fri Apr 10, 2015 11:23 am

in attachemend you find the mysqlslow.log from my hoster.
it hopes it help...
You do not have the required permissions to view the files attached to this post.
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Fri Apr 10, 2015 11:06 pm

Suggest you find out what is running that query, it runs without limit. How many entries do you have in #__content_frontpage ?
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Sat Apr 11, 2015 8:20 am

The "Rows_sent: 5293" is exactly the number of entries in #_content
In #__content_frontpage i have actually 188 entries

i actually dont know where these query run.
on article-page i see in phpmyadmin these query:

Code: Select all

SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-00 00:00:00'
THEN a.created
ELSE a.modified
END AS modified, a.modified_by, uam.name AS modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00'
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, a.language, 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, 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 ngie_content AS a
LEFT JOIN ngie_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN ngie_categories AS c ON c.id = a.catid
LEFT JOIN ngie_users AS ua ON ua.id = a.created_by
LEFT JOIN ngie_users AS uam ON uam.id = a.modified_by
LEFT JOIN ngie_categories AS parent ON parent.id = c.parent_id
LEFT JOIN ngie_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (

SELECT cat.id AS id
FROM ngie_categories AS cat
JOIN ngie_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, 5 )
AND c.access
IN ( 1, 1, 5 )
AND CASE WHEN badcats.id IS NULL
THEN a.state
ELSE 0
END =1
AND a.featured =1
AND (
a.publish_up = '0000-00-00 00:00:00'
OR a.publish_up <= '2015-04-11 08:05:17'
)
AND (
a.publish_down = '0000-00-00 00:00:00'
OR a.publish_down >= '2015-04-11 08:05:17'
)
ORDER BY a.publish_up DESC
LIMIT 0 , 1
Its only 1 row. So i think this is correct


On frontpage i see these querie:

Code: Select all

SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.images, a.hits, a.catid AS category_id, c.title AS category_name, c.alias AS category_alias, u.id AS user_id, u.name AS user_name, a.created_by_alias AS user_alias, ROUND( AVG( r.rating_sum / r.rating_count ) , 2 ) AS rating_average, r.rating_count, a.created AS date, COUNT( cm.id ) AS comments_count, MAX( cm.date ) AS comments_date
FROM ngie_content AS a
LEFT JOIN ngie_categories AS c ON c.id = a.catid
LEFT JOIN ngie_users AS u ON u.id = a.created_by
LEFT JOIN ngie_content_rating AS r ON r.content_id = a.id
LEFT JOIN ngie_jcomments AS cm ON cm.object_id = a.id
AND cm.object_group = "com_content"
AND cm.published =1
WHERE a.id NOT
IN ( 10351, 10352, 10353, 10354 )
AND a.state =1
AND c.published =1
AND a.access
IN ( 1, 1, 5 )
AND c.access
IN ( 1, 1, 5 )
AND a.publish_up <= "2015-04-11 08:10:59"
GROUP BY a.id
ORDER BY date DESC
LIMIT 0 , 12
it shows the 12 newest entries. so this is correct

on category-page:

Code: Select all

SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-00 00:00:00'
THEN a.created
ELSE a.modified
END AS modified, a.modified_by, uam.name AS modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00'
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, a.language, 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, 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 ngie_content AS a
LEFT JOIN ngie_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN ngie_categories AS c ON c.id = a.catid
LEFT JOIN ngie_users AS ua ON ua.id = a.created_by
LEFT JOIN ngie_users AS uam ON uam.id = a.modified_by
LEFT JOIN ngie_categories AS parent ON parent.id = c.parent_id
LEFT JOIN ngie_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (

SELECT cat.id AS id
FROM ngie_categories AS cat
JOIN ngie_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, 5 )
AND c.access
IN ( 1, 1, 5 )
AND CASE WHEN badcats.id IS NULL
THEN a.state
ELSE 0
END =1
AND a.catid =39
AND (
a.publish_up = '0000-00-00 00:00:00'
OR a.publish_up <= '2015-04-11 08:12:03'
)
AND (
a.publish_down = '0000-00-00 00:00:00'
OR a.publish_down >= '2015-04-11 08:12:03'
)
ORDER BY a.created DESC , a.created
LIMIT 0 , 12
shows the 12 newest articles in a category-page.
its also correct

but i dont know what produces these query with ALL of my articles.
I have no site where shown all articles at one page

so i am very confused :(
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Sat Apr 11, 2015 12:20 pm

So am I, the left join to #__content_frontpage should limit the returned rows!
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Sat Apr 11, 2015 3:10 pm

sovainfo wrote:So am I, the left join to #__content_frontpage should limit the returned rows!
i dont unterstand what you mean. what i should do??
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Sat Apr 11, 2015 7:22 pm

Find where this query is used!
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon Apr 13, 2015 8:42 am

thats what i do, but without success.
Dont no where these come...

Is there a tool, where i can see live the history of ala database-queries they are running in the last time? so i can test and find out, where on my site running these queries...
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Mon Apr 13, 2015 11:58 am

The thing that comes closest to what you want is https://github.com/joomla/joomla-cms/pull/6202.

Use com_patchtester to enable it. Configure/enable debug to log database queries. It will produce files with the queries to produce the output.
Search those files on the query without the limit. The name of the log file tells you which menu item it is for frontend. For backend it is by component. That should narrow it down.
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon Apr 13, 2015 3:20 pm

i have installed the patchtester in backend
an maked the changes from the github site: https://github.com/joomla/joomla-cms/pull/6202/files
and i set in plugins --> system --> debug " Log Executed Queries" to yes, but i dont find a new file in "logs/"

what i do false? must i restart the server or phpmyadmin now (i cant do it. only my hoster)?
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon Apr 13, 2015 4:10 pm

OK, now it works.
And the problem is defenitly my frontpage, i think.
the "Log Executed Queries" runs about 20 minutes.
the most sql-files had not more than 40 KB and had not the query that show the more than 5.000 articles.

But only "HDsports.at_home1443.sql" had troubles. Filesize 1MB inclusive they querie that makes the troubles.

And there are more of 5.000 (!!!) queries about this:

Code: Select all

SELECT m.tag_id,t.* FROM ngie_contentitem_tag_map AS m  INNER JOIN ngie_tags AS t  ON m.tag_id = t.id WHERE m.type_alias = 'com_content.article' AND m.content_item_id = 8463 AND t.published = 1 AND t.access IN (1,1,5);
The result in phpmyadmin is everytime 0, because "contentitem_tag_map" is empty (i do that after migration)

in attachmend i had uploaded the files.
Has anyone an idea, what i can do?

EDIT: Its so crazy.
at first moment my "HDsports.at_home1443.sql" is also so small (10KB) than the other files, but i refresh very often the ftp-files and sometimes the sql-file is more than 1MB big.
So, not every hit on my frontpage produces the lot of "contentitem_tag_map" and the slow-query.
So that makes it not easy, to find out, where the problem is...:(
You do not have the required permissions to view the files attached to this post.
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Mon Apr 13, 2015 6:55 pm

The > 5000 queries on contentitem_tag_map will be reduced as soon as the problem query without the limit is killed! The number should reflect the number of articles presented on the home page.

Looks like jcomments is causing that.

EDIT: Starts to look like you didn't migrate properly! What did you do?
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon Apr 13, 2015 7:44 pm

sovainfo wrote:The > 5000 queries on contentitem_tag_map will be reduced as soon as the problem query without the limit is killed! The number should reflect the number of articles presented on the home page.

Looks like jcomments is causing that.

EDIT: Starts to look like you didn't migrate properly! What did you do?
i migrated from 1.5 to 3.x with SPugrade (first attempt was with RedMigrator, but without success), and had many troubles with Migration, but finished.

jcomments is not the reason, i think. when i deactivate the module on frontpage, sometimes the "HDsports.at_home1443.sql" has 1 MB.

The most time these sql.-file has only 10-15 KB, but when i press 1 minute every second on update (F5) in filezilla 1 time the file has 1 MB and 59 times the file has 15 KB.

So for me its absolutely incomprehensible why these happens...?
My Joomla-site: www.HDsports.at

sovainfo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8808
Joined: Sat Oct 01, 2011 7:06 pm

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by sovainfo » Mon Apr 13, 2015 7:54 pm

You need to analyse your home page context. What menu type and which modules.

When pressing F5 every second for a minute, I think you have other problems to worry about!
Issue with migrating? Include logs/joomla_update.php in your report!
Blank screen? Verify pagesource for HTML code (javascript error)
Installation failing on populating database? Install with set_time_limit(0)
Document your customizations!

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon Apr 13, 2015 11:27 pm

now i looked on phpmyadmin which modules are on the front-page placed.
many modules was old or deactivated, so i deleted that via backend.
but that solved not really the problem.

sometimes the "HDsports.at_home1443.sql"-file has over 1MB.

so i deleted the "Home"-Button and maked a new one (Name "Start") with the same Modules than "Home". But that solved also not the problem. Now the "HDsports.at_start1823.sql"-file has "sometimes" over 1MB.
It's so crazy it makes absolutely no scence what here is going on:/ i dont not unterstand why these slow query runs only "sometimes"...

and one time in the last hours, i a saw two other .sql-file with a big size (0,6 MB): "HDsports.at_schmidatal1079". It's this site: http://www.hdsports.at/schmidataler-laufcup/schmidatal. There where also the slow-querie and many "ngie_contentitem_tag_map" AND lot of these queries (started at 1.270):

Code: Select all

SELECT count(*) FROM ngie_jcomments AS c WHERE c.object_id = 10344 AND c.object_group = 'com_content' AND c.published = 1;
SELECT * FROM ngie_content_rating WHERE content_id=10316;
SELECT * FROM ngie_content_extravote WHERE content_id=10316;
SELECT * FROM ngie_k2_rating WHERE itemID=10316;
and my impressum (http://www.hdsports.at/impressum): Has also the slow-query and lot of contentitem_tag_map.
both (schmidatal and impressum) have as menu-item component-->article-->one article.

only 1 module is placed on all of these sites (Home, Schmidatal, Impressum), but this is a menu-module, so it can make defenitly not the slow querie.

it's absolutely crazy

in attachemend i uploaded some log-files from "home/start" and "schmidatal", where you can see how the file looks when where the one Slow-Querie inclusive the thousand contentitem_tag_map-queries and when they look when not the slow-querie is in it.

what i also not understand on the SLOW-files, is that on the end:

Code: Select all

SELECT new_url,header,published FROM ngie_redirect_links WHERE old_url = 'http://www.hdsports.at/images/joomgallery/thumbnails/laufsport_2/x_cross_run_2014_224/5_km_lauf_startgruppe_1_nr_1_-_200_225/x-cross-run-2014-dsc_7668_20140524_1015599017.jpg' LIMIT 0, 1;
SELECT new_url,published FROM ngie_redirect_links WHERE old_url = '/images/joomgallery/thumbnails/laufsport_2/x_cross_run_2014_224/5_km_lauf_startgruppe_1_nr_1_-_200_225/x-cross-run-2014-dsc_7668_20140524_1015599017.jpg' LIMIT 0, 1;
SELECT id FROM ngie_redirect_links WHERE old_url = 'http://www.hdsports.at/images/joomgallery/thumbnails/laufsport_2/x_cross_run_2014_224/5_km_lauf_startgruppe_1_nr_1_-_200_225/x-cross-run-2014-dsc_7668_20140524_1015599017.jpg';
UPDATE ngie_redirect_links SET hits = hits + 1 WHERE id = 7622;
What means that old-ulr and redirect-links? is that the site where the user was, before he go to this site? this things are only shown in the SLOW-sql-files...

hope, anyone can make a look on these file...
Thanks;)
You do not have the required permissions to view the files attached to this post.
My Joomla-site: www.HDsports.at

emeyer
Joomla! Explorer
Joomla! Explorer
Posts: 352
Joined: Thu Sep 29, 2005 2:37 am

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by emeyer » Tue Apr 14, 2015 12:58 am

Did you try disabling jcomments?

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 14, 2015 1:17 am

i had only for a short moment deactivated the jcomment-module for the frontpage, but one time the sql-file for the frontpage was also 1MB.
so i think this is not the reason.
but actually i deactivated all plugins, modules from the component jcomment and i will look what happens.
but its not easy to find out whats the reason for these queries, because they are running so seldom...
My Joomla-site: www.HDsports.at

User avatar
NathanHawks
Joomla! Ace
Joomla! Ace
Posts: 1900
Joined: Wed Oct 02, 2013 6:17 pm
Location: Washington state, U.S.
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by NathanHawks » Tue Apr 14, 2015 4:11 am

Two thoughts come to mind:

Have you been turning on the Prepare Content option, on some modules? This option causes plugins to be run on the module; rampantly enabling it, cripples most robust sites.

and

In the final SQL you quoted:
SELECT new_url,header,published FROM __redirect_links WHERE old_url = 'http://www.you.tld/images/joomgallery/t ... n/shim.ext' LIMIT 0, 1;
The __redirect_links table is for the Redirects component; do those image URLs look familiar to you? It seems very suspicious to me, that this SQL seems to imply you have a variety of JoomGallery images programmed to trigger redirections... does that sound familiar vis-a-vis your site implementation?
Save time - hire me for your Joomla to-do list! http://nathanhawks.us/joomla

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 14, 2015 8:25 am

NathanHawks wrote:Two thoughts come to mind:

Have you been turning on the Prepare Content option, on some modules? This option causes plugins to be run on the module; rampantly enabling it, cripples most robust sites.

and

In the final SQL you quoted:
SELECT new_url,header,published FROM __redirect_links WHERE old_url = 'http://www.you.tld/images/joomgallery/t ... n/shim.ext' LIMIT 0, 1;
The __redirect_links table is for the Redirects component; do those image URLs look familiar to you? It seems very suspicious to me, that this SQL seems to imply you have a variety of JoomGallery images programmed to trigger redirections... does that sound familiar vis-a-vis your site implementation?
i dont found a "Prepare Content"-option at any modules. Which modules should have some option?


OOOOH, i think the redirecton is the malefactor.
the links are all false and redirect

When i enter these link "http://www.hdsports.at/impressum/images ... 309660.jpg"
than the site loads very, very long. and then it redirektcs to my "404 Page"

In the log file, there is a new "HDsports.at_impressum108.sql" with more than 1 MB
but when i deactivate the redirect-plugin it has no effect, the big file will comes again after using these link.

but actually its only these one link from the SLOW-Examples.

When i use the other links from my SLOW-Examples of the attachment:
http://www.hdsports.at/tag/veitsch
http://www.hdsports.at/images/joomgalle ... 760449.jpg
http://www.hdsports.at/images/joomgalle ... 647431.jpg'
http://www.hdsports.at/images/joomgalle ... 599017.jpg
http://www.hdsports.at/images/joomgalle ... 562628.jpg
I will redirect to frontpage, and it produces very small "start-files" without the slow queriess and without the redirct-queries.


The REason about the slow-queries (only 1 time) on the Schmidatal-Site, i dont know. there are no redirect-queries

when i looked in my redirection-manager all these false links from the rediret-queries (also this one, with produces the 1MB-File) are in the redirection-manager. All of them has no "destination URL".

So i am a little bit confused, why one of them examples are yet produces again the slow-queries, and all other does not.

But the good thing is, we know the reason, what makes the troubles: Dead Links that redirects... So "Bots" makes the big load :/
My Joomla-site: www.HDsports.at

User avatar
NathanHawks
Joomla! Ace
Joomla! Ace
Posts: 1900
Joined: Wed Oct 02, 2013 6:17 pm
Location: Washington state, U.S.
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by NathanHawks » Tue Apr 14, 2015 1:46 pm

Prepare Content is available on some module types, on the Options tab.

Try disabling or deleting the faulty redirections.
Save time - hire me for your Joomla to-do list! http://nathanhawks.us/joomla

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 14, 2015 2:01 pm

when i search in jos_modules in params to "prepare_content" there are only at some mod_custoums prepare_content-params, but all set to "0". so this is not the problem...

acutally i dont know how i delete that.
when i make an redirection from "http://www.hdsports.at/impressum/images ... 309660.jpg" to frontpage, the slow-query runs also in the impressum-sql file.

The strange thing: When i enter "http://www.hdsports.at/impressum/images/joomgallery/" (Impressum is a Module with one article) than the slow query runs.
But when i enter "http://www.hdsports.at/laufsport/images/joomgallery/" (laufsport is the joomla-component that shows the newest entries from a category) than not the slow-query runs.
Both redirect to 404-site. But i dont know why, one of them runs the many queries...

I dont unterstand that, it makes no scence :/
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 14, 2015 3:26 pm

ok, new information.
when i activated an dead link which produces the slow-query in the redirect-component and set new-url to my frontpage, than the slow-query disappears when i enter the dead link page on my browser and the dead-link-page redirect to frontpage.

But the problem: When i write for example "www.hdsports.at/impressum/images/joomga ... llsport_4/" than the slow-query comes again in the impressum-file and a new redirect is set in the backend. When i activate the redirect than the slow query goes away.

Now i know, how i solve the problem with the slow-query. but i think there will come new, when a bot or something go on my site to a new dead-link what is not in the redirect-list...
My Joomla-site: www.HDsports.at

User avatar
NathanHawks
Joomla! Ace
Joomla! Ace
Posts: 1900
Joined: Wed Oct 02, 2013 6:17 pm
Location: Washington state, U.S.
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by NathanHawks » Tue Apr 14, 2015 3:52 pm

You can add any URLs you want blocked from robot crawls, to robots.txt.

I would really question why these image URL redirects are even in your redirect registry if you didn't add them. There are many levels on which it makes no sense for them to even be in your redirects system, in the first place. Beyond merely solving the slowness symptom (which I'm glad we're doing) I suggest being more thorough and treating this as a continued problem until you understand what caused it.
Save time - hire me for your Joomla to-do list! http://nathanhawks.us/joomla

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 14, 2015 5:24 pm

Actually i dont know why this happens:
When i go to:
http://www.hdsports.at/impressum/images ... lsport_455
than slow queries and many queries. Redirect to 404-page. Automatically create Link in redirect-page. When i activate the link with redirect to frontpage, than when i go to the dead-link it redirects without slow query.

http://www.hdsports.at/impressum/tags/abcdef
same thing.

when i go to:
http://www.hdsports.at/images/joomgalle ... lsport_455
redirect to frontpage without the slow queries. Link doestn exist in redirect and dont create after redirect.

http://www.hdsports.at/tags/abcdef
same thing

So, when not the menuitem "impressum" is before, than there are no problems.
And the slow queries comes only when i go to a menu-point which 1 article is selected.
i testet many other menu-points (categories, components) and there was no slow-queries (for example: http://www.hdsports.at/fotos/tags/testesteste). they are all redirected without troubles.

so i have no idea what is the problem...
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Thu Apr 16, 2015 6:45 am

Actually there come no slow queries, when nobody goes on dead-links.

But i have since migration a second little problem. When i write an new article and saved it in an category with many articles, than i must wait more then one minute, because there are running lot of ORDERING-queries.
I absolutely dont know how i can solve these problem.
in attachemend a sql-file that shows the queries they are running, when i save an new article in an category with many articles...
You do not have the required permissions to view the files attached to this post.
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue Apr 21, 2015 9:48 am

Have nobody an idea to solve the problem with the ORDERING-queries, when i save an new article in a category with many entries?

Code: Select all

SELECT id, ordering FROM ngie_content WHERE ordering >= 0 AND catid = 159 AND state >= 0 ORDER BY ordering;
UPDATE ngie_content SET ordering = 1 WHERE id = '10306';
UPDATE ngie_content SET ordering = 2 WHERE id = '10292';
UPDATE ngie_content SET ordering = 3 WHERE id = '10285';
UPDATE ngie_content SET ordering = 4 WHERE id = '10286';
UPDATE ngie_content SET ordering = 5 WHERE id = '6154';
UPDATE ngie_content SET ordering = 6 WHERE id = '10238';
UPDATE ngie_content SET ordering = 7 WHERE id = '6010';
UPDATE ngie_content SET ordering = 8 WHERE id = '10237';
UPDATE ngie_content SET ordering = 9 WHERE id = '5906';
UPDATE ngie_content SET ordering = 10 WHERE id = '10219';
UPDATE ngie_content SET ordering = 11 WHERE id = '5904';
UPDATE ngie_content SET ordering = 12 WHERE id = '10216';
UPDATE ngie_content SET ordering = 13 WHERE id = '10213';
UPDATE ngie_content SET ordering = 14 WHERE id = '10210';
UPDATE ngie_content SET ordering = 15 WHERE id = '10200';
UPDATE ngie_content SET ordering = 16 WHERE id = '10199';
UPDATE ngie_content SET ordering = 17 WHERE id = '10193';
...
My Joomla-site: www.HDsports.at

User avatar
NathanHawks
Joomla! Ace
Joomla! Ace
Posts: 1900
Joined: Wed Oct 02, 2013 6:17 pm
Location: Washington state, U.S.
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by NathanHawks » Wed Apr 22, 2015 1:19 pm

The answer is probably to modify Joomla in such a way that will be undone and need redoing with every Joomla update. It is possible that your reordering queries are being caused by an extension, but I suspect it's standard behavior (not an extension).

That is why nobody is answering you. The standard reply to that, would be for you to say "okay so how do we do that" and then we volunteers say "you don't, but you can file a bug report which might see some action in the next 1-to-4 years."

I personally would divide a huge site like that up into multiple Joomla installations in order to avoid breaking my face running into scalability walls.
Save time - hire me for your Joomla to-do list! http://nathanhawks.us/joomla

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Tue May 05, 2015 5:44 pm

i dont understand why nobody other has these ordering-problem.

so i maked new joomla installation with example-datas.
When i have a category (id=64) with 3 entries, than the newest have "ordering=0", than the 2nd newest "1" and the oldest "2"
when i write an new article in these cateogry, so from every article the ordering-number jumps one number higher.

so its defenitly normal that there running 1.000 of these processes when i save an article in a category with 1.000 articles.

but i dont unterstand, why on my site the waiting time for these processes is 1-2 minutes...

When i enter in phpmyadmin some sql-command

Code: Select all

UPDATE ngie_content SET ordering = 1 WHERE id = '10403';
the time for the query is 0,11 seconds. when i have 1.000 articles, so the time is 100 seconds.
is 0,11 seconds so slowy??

Edit: Sometimes my site saved the articles without long waiting time.
so i compared the sqllog-files from one time when waiting time was long and one time waiting-time is short by saving an article in an category with about 200 articles
the first 15 rows are the same like thath:

Code: Select all

SELECT data FROM ngie_session WHERE session_id = 'e7f9d2d0ff57b22f40c6b8e58a6adb71';
SELECT folder AS type, element AS name, params FROM ngie_extensions WHERE enabled = 1 AND type ='plugin' AND state IN (0,1) AND access IN (1,1,2,3,6) ORDER BY ordering;
SELECT query FROM ngie_admintools_wafexceptions WHERE (option IS NULL OR option = '' OR option = 'com_content') AND (view IS NULL OR view = '') GROUP BY query ORDER BY query ASC;
SELECT extension_id AS id, element AS "option", params, enabled FROM ngie_extensions WHERE type = 'library' AND element = 'joomla';
UPDATE ngie_extensions SET params = '{\"mediaversion\":\"ea0199cce0d1945ef673bd77fbe4bc1d\"}' WHERE type = 'library' AND element = 'joomla';
SELECT extension_id AS id, element AS "option", params, enabled FROM ngie_extensions WHERE type = 'component';
SELECT template, s.params FROM ngie_template_styles as s LEFT JOIN ngie_extensions as e ON e.type='template' AND e.element=s.template AND e.client_id=s.client_id WHERE s.client_id = 1 AND home = '1' ORDER BY home;
SHOW FULL COLUMNS FROM ngie_content;
SELECT enabled FROM ngie_extensions WHERE type = 'plugin' AND folder = 'system' AND element = 'languagefilter';
SELECT id, home, template, s.params FROM ngie_template_styles as s LEFT JOIN ngie_extensions as e ON e.element=s.template AND e.type='template' AND e.client_id=s.client_id WHERE s.client_id = 0 AND s.home = 1 AND e.enabled = 1;
SELECT c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,    c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,    c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,    c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version, CASE WHEN CHAR_LENGTH(c.alias) != 0 THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as slug FROM ngie_categories as c LEFT JOIN ngie_categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt) LEFT JOIN  (SELECT cat.id as id FROM ngie_categories AS cat JOIN ngie_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 (c.extension='com_content' OR c.extension='system') AND c.access IN (1,1,2,3,6) AND c.published = 1 AND s.id=69 AND badcats.id is null GROUP BY c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,     c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,     c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,     c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version ORDER BY c.lft;
SELECT b.id FROM ngie_user_usergroup_map AS map LEFT JOIN ngie_usergroups AS a ON a.id = map.group_id LEFT JOIN ngie_usergroups AS b ON b.lft <= a.lft AND b.rgt >= a.rgt WHERE map.user_id = 845;
SELECT * FROM ngie_content WHERE alias = 'rrrrrrrrrrrrrr' AND catid = '69';
SELECT * FROM ngie_content WHERE alias = 'rrrrrrrrrrrrrr' AND catid = '69';
SELECT id, ordering FROM ngie_content WHERE ordering >= 0 AND catid = 69 AND state >= 0 ORDER BY ordering;
in one file than comes

Code: Select all

UPDATE ngie_content SET ordering = 1 WHERE id = '10403';
UPDATE ngie_content SET ordering = 2 WHERE id = '10293';
UPDATE ngie_content SET ordering = 3 WHERE id = '10202';
UPDATE ngie_content SET ordering = 4 WHERE id = '10194';
But in the other file, with short saving-time there are not these rows

i have uploaded the files. i dont unterstand why sometimes these Update-process runs und sometimes not... But most times (90 %) the ORDERING-Processes runs..
You do not have the required permissions to view the files attached to this post.
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Mon May 18, 2015 1:59 pm

ok nobody look at my file, thats fine:((

Acutally i installed joomla new an imported with j2xml all articles and categories from my existing joomla to the new joomla.
And than, when i write and save an article in a category with 1000 articles, than the saving-time is also 2 minutes, because there running the 1000 ordering-processes

will joomla kidding me? or why nobody have these problem?
i have a completely new joomla-site only with the importing articles and categories and i have the same problem. waaaaaah, thats not normal
My Joomla-site: www.HDsports.at

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 170
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: Slow SQL Queries after Upgrading from 1.5 to 3.x

Post by HDsportsAT » Sun May 24, 2015 11:00 am

thanks that nobody supported me :(
My Joomla-site: www.HDsports.at


Locked

Return to “Performance - Joomla! 3.x”