Site after migration extreme slowly / server down

[HDsportsAT]

Hey guys,
i had following problem.
After migration my joomla 1.5 to 3.3 my site was extreme slowly and the last days my server was down.
Now the hoster (i have a managed server) found the reason:
Server was overloaded (Out of memory), because the load on the server was maked by SQL-querys. There are permanent these querys:

Code: Select all

| 2 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 4 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 15 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 17 | dbo376405720 | localhost | db376405720 | Query | 0 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 23 | dbo376405720 | localhost | db376405720 | Query | 0 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 28 | dbo376405720 | localhost | db376405720 | Query | 0 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 53 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 60 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 63 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 66 | dbo376405720 | localhost | db376405720 | Query | 0 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 73 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 85 | dbo376405720 | localhost | db376405720 | Query | 0 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m |
| 91 | dbo376405720 | localhost | db376405720 | Query | 1 | Sending data | SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m
INNER JOIN `mh0to_tags` AS t ON `m | 

After Migration Joomla 1.5 to 3.3 i upgradet the component FJ Related.
FJ Related has a tool to convert all old keywords to the new keywords. I think i had more than 10.000 keywords in 4.000 articles. But 100 articles bevor finishing the conversion, the process cancelled.
So i think that the process run's now permanent in the background, because it was not finished. Is this the reason?

What can i do, to stop the process? At the moment i have no access to server/db (Out of memory). Must the hoster stop the process?

Thanks for your help.
Best regards!

[cmshelplive]

In case the process is still running, your host admin should be able to get the information from host tracer logs. If that is the case, you can request hosting staff to terminate the process.

[HDsportsAT]

i have entry to the MySQL Logs. Today i deinstalled the component FJ Related, but these makes no effect to solve the problem.
In mySQL.log the queries runs already:

Code: Select all

FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		  170 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		   90 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		  217 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		   53 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		   99 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
		   16 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8190 AND `t`.`published` = 1 AND t.access IN (1,1,5)
141030 13:21:05	  261 Connect	dbo376405720@localhost on 
141030 13:21:16	  262 Connect	dbo376405720@localhost on 
141030 13:21:35	  261 Query	SET @@SESSION.sql_mode = ''
141030 13:21:37	  263 Connect	dbo376405720@localhost on 
141030 13:21:48	  262 Query	SET @@SESSION.sql_mode = ''
141030 13:21:53	  264 Connect	dbo376405720@localhost on 
141030 13:22:11	  265 Connect	dbo376405720@localhost on 
141030 13:22:18	  263 Query	SET @@SESSION.sql_mode = ''
141030 13:22:44	  267 Connect	dbo376405720@localhost on 
141030 13:22:46	  265 Query	SET @@SESSION.sql_mode = ''
		  264 Query	SET @@SESSION.sql_mode = ''
141030 13:23:02	  261 Init DB	db376405720
141030 13:23:09	  268 Connect	dbo376405720@localhost on 
141030 13:23:14	  262 Init DB	db376405720
141030 13:23:16	  267 Query	SET @@SESSION.sql_mode = ''
141030 13:23:20	  269 Connect	dbo376405720@localhost on 
141030 13:23:31	  261 Query	SET NAMES utf8
141030 13:23:39	  270 Connect	dbo376405720@localhost on 
141030 13:23:44	  268 Query	SET @@SESSION.sql_mode = ''
141030 13:23:47	  263 Init DB	db376405720
141030 13:23:55	  271 Connect	dbo376405720@localhost on 
		  262 Query	SET NAMES utf8
141030 13:24:04	  269 Query	SET @@SESSION.sql_mode = ''
141030 13:24:27	  270 Query	SET @@SESSION.sql_mode = ''
141030 13:24:29	  265 Init DB	db376405720
141030 13:24:49	  264 Init DB	db376405720
141030 13:24:56	  271 Query	SET @@SESSION.sql_mode = ''
141030 13:24:59	  263 Query	SET NAMES utf8
141030 13:25:24	  274 Connect	dbo376405720@localhost on 
141030 13:25:38	  267 Init DB	db376405720
141030 13:26:02	  274 Query	SET @@SESSION.sql_mode = ''
141030 13:26:07	  265 Query	SET NAMES utf8
141030 13:26:33	  268 Init DB	db376405720
141030 13:26:40	  276 Connect	dbo376405720@localhost on 
141030 13:27:09	  264 Query	SET NAMES utf8
141030 13:27:26	  276 Query	SET @@SESSION.sql_mode = ''
141030 13:27:29	  278 Connect	dbo376405720@localhost on 
141030 13:27:54	  269 Init DB	db376405720
141030 13:28:17	  270 Init DB	db376405720
141030 13:28:23	  279 Connect	dbo376405720@localhost on 
141030 13:28:33	  267 Query	SET NAMES utf8
141030 13:28:35	  278 Query	SET @@SESSION.sql_mode = ''
141030 13:28:46	  280 Connect	dbo376405720@localhost on 
141030 13:29:12	  279 Query	SET @@SESSION.sql_mode = ''
141030 13:29:14	  263 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8189 AND `t`.`published` = 1 AND t.access IN (1,1,5)
141030 13:29:41	  280 Query	SET @@SESSION.sql_mode = ''
141030 13:29:46	  271 Init DB	db376405720
141030 13:30:08	  268 Query	SET NAMES utf8
141030 13:30:43	  265 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8189 AND `t`.`published` = 1 AND t.access IN (1,1,5)
141030 13:31:16	  274 Init DB	db376405720
141030 13:31:28	  269 Query	SET NAMES utf8
141030 13:32:09	  264 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8189 AND `t`.`published` = 1 AND t.access IN (1,1,5)
141030 13:32:12	  270 Query	SET NAMES utf8
141030 13:34:11	  276 Init DB	db376405720
141030 13:35:55	  267 Query	SELECT `m`.`tag_id`,`t`.*
FROM `mh0to_contentitem_tag_map` AS m 
INNER JOIN `mh0to_tags` AS t  ON `m`.`tag_id` = `t`.`id`
WHERE `m`.`type_alias` = 'com_content.article' AND `m`.`content_item_id` = 8189 AND `t`.`published` = 1 AND t.access IN (1,1,5)
141030 13:36:30	  271 Query	SET NAMES utf8

Hoster cannot solve the problem. I dont know from which file comes these permanent SQL queries... (

[leolam]

If your host cannot change host. Your host can see active processes and kill this specific process

Leo

[HDsportsAT]

Yes but when i stop the process, a new process starts automatically directly after i kill one of this process. Only when i change my db-configuration to false parameter it stops - but this is not a good solution.

But FJ Related is not responsible for that problem.
After i empty the two taples: mh0to_contentitem_tag_map, mh0to_tags
the site runs much faster

But now i have a new problem:
When i save an article an set "main article" to "yes", than the site loads extremely long (but when i look in new tab, if the article is saved, they is already saved, but the site loads and loads in the other tab)

And in phpmyAdmin i see these permanent process starting and not stopping
UPDATE mh0to_content_frontpage SET ordering = 789 WHERE `content_id` = '8997'

And few moments later the old processes start again:
SELECT `m`.`tag_id`,`t`.* FROM `mh0to_contentitem_tag_map` AS m INNER JOIN `mh0to_tags` AS t ON `m

Now i must change already my configuration-datas to kill these processes completely, but this is no long-term solution

[leolam]

I reacted on what you wrote and not on the actual error.

Uninstall and delete the database tables of FJ Related. I do not know this component but it does for sure kills your MySql process it seems. Otherwise contact Mark Dexter first for advise (!)

Leo

[cmshelplive]

Try optimizing your database as well. If emptying the tables results in better performance, then the background operations on db might consuming too much of processing power. Though I believe optimizing also may not suffice, it can definitely provide improvements.

To start with, go to phpMyAdmin, select all tables, and click “optimize”. Next follow this link for good advise:

http://kb.siteground.com/How_to_optimiz ... _database/

[HDsportsAT]

Hey guys!

I reacted on what you wrote and not on the actual error.

Uninstall and delete the database tables of FJ Related. I do not know this component but it does for sure kills your MySql process it seems. Otherwise contact Mark Dexter first for advise (!)

Leo

i had uninstalled FJ Related and saw the this component wasn't responsible about my problem.
After i empty die tables _tags and contintentitem_tag_map the site runs faster.

But this solved not the problem with the content, when i set an article to "main article". In Backend after saving the article, the site load and load, but the article saved succesfully after few seconds (when i look in another tab to the article-survey). But i dont know, why the site load so long ??

Try optimizing your database as well. If emptying the tables results in better performance, then the background operations on db might consuming too much of processing power. Though I believe optimizing also may not suffice, it can definitely provide improvements.

To start with, go to phpMyAdmin, select all tables, and click “optimize”. Next follow this link for good advise:

http://kb.siteground.com/How_to_optimiz ... _database/

tables are optimized now, but solved not the problem with "main article"-setting.
indixes are already setted

[HDsportsAT]

i will push my post.
My site (http://www.HDsports.at) is soo slowly and i dont know the reason

When i write an article in the backend and i press save, than i must wait one minute, till the article will saved.
On the frontend, it takes many seconds (2-6 or longer) that my site will start loading.
I dont know what the problem is. I only know, that i have these problems since migration from 1.5 to 3.3

[Per Yngve Berg]

How did you migrate from 1.5 to 3.3?

Strange that you got problems with the tag component, as it is not present in 1.5.

Many migrations from 1.5 results in a bad Assets table.
https://docs.joomla.org/Fixing_the_assets_table

[HDsportsAT]

with SPupgrade.

yes i know the problem with the assets table.
but i cant to that step.
because when i MOVE for example 100 articles in the "temp-xx" category i must wait about 10 minutes, and than i become a "Gateaway-Time Out" Error.
I have 10.000 articles, and that will take soooo much time to move the articles to the temp-categories and back.

2nd opinion: i bought the "acl-manager" to solve the assets problem.
but when i startet to use the repair-tool, my site load already 10 minutes and than i see a "Gateaway: Time out" error.
And than when i go back to the backend and klick to the component acl-manager, i have following error:

Code: Select all

 Fatal error: Allowed memory size of 125829120 bytes exhausted (tried to allocate 41 bytes) in /homepages/2/d375415369/htdocs/neu/libraries/joomla/database/driver/mysqli.php on line 811

So, thats a little bit frustrated. i think the problem comes from the assets-table, but actually i cant use both opinions to solve the problem...

[HDsportsAT]

i also have a problem, when i write an article in the backend and save it:
i must wait 1 minute, till the article is saved.
when i look in phpmyadmin on the status on my server i see following process:

UPDATE xx_content SET ordering = 102 WHERE `id` = '9617'
whenn i press f5
i see another ordering-process
UPDATE xx_content SET ordering = 256 WHERE `id` = '8835'

i dont know what that means. but i think these ordering-process is the problem, for the long waiting-time when save an article

EDIT: sorry for double-post

[Per Yngve Berg]

Have you manually inspected the Assets table with Phpmyadmin and checked that there are no multiple roots?

[HDsportsAT]

Have you manually inspected the Assets table with Phpmyadmin and checked that there are no multiple roots?

yes, i had it inspected.
i have only one id on my root (=level 0)

[HDsportsAT]

can nobody help me:?
i have always the problem when i save an article, that my db make these fu**** ordering-processes
UPDATE xx_content SET ordering = 102 WHERE `id` = '9617'
UPDATE xx_content SET ordering = 256 WHERE `id` = '8835'
so i must wait one minute, when i save an article in a category with about 5.000 articles...
it's so frustrated

[Per Yngve Berg]

Is there an index on the id column? It's only 2 rows that get changed.

[HDsportsAT]

Is there an index on the id column? It's only 2 rows that get changed.

what you mean with index?
i maked a screenshot of the xx_assets datasets

i have 48.000 datasets in the xx_assets-table

[Per Yngve Berg]

The update is on the xx_content table, not the xx_assests.

If you enable debug n Global Configuration, you can see the sql queries and how long they will take at the debug information at the bottom of the page.

[HDsportsAT]

what you mean with index on the id column?
i am now in xx_content
the lowest id is 47 (because the articles before 47 are all deleted)
i also have a column named "asset_id"

[Per Yngve Berg]

The id column should be the primary key. "asset_id" is a foreign key and relates to the id column in the assets table.
An index on defined on the column makes the database able to look up a value without searching through the hole table. The Primary Key is an Index.
Did you enable Debug? Watch out for "an index could not be used on the able" messages.

[HDsportsAT]

yes the field, id has the primary key.
look at the screenshot:


when i activate debug-modus. I have many messages about index could not be used:
_admintools_wafexception
_categories
_mj_plugins
_mj_modules
_template_styles
_extensions
_jcomments_settings
_languages
_content
_rereplacer
_custom_properties
_assets
_viewlevels
_jcomments_mailq
_custom_properties_fields

But i dont see any query with a long time.

I have actually activated debug:
You can check it here for example:
http://www.hdsports.at/laufsport/events ... -und-stein

[HDsportsAT]

After activate debug-modus, my site is since hours down.
THANKS ((

[Per Yngve Berg]

Only query 29 stands out. It may help to put an index on the 'created' column in the content table.
Your frontend is not very slow. Do the same check with the backend.

[HDsportsAT]

MY SITE IS DOWN
i can go to files via ftp and can you phpmyadmin.
But i dont reach my site, since i activated the debug-modus

EDIT: Now site works again
i set "Index" to "created" in content. MYSQL says "No result"

[HDsportsAT]

At attachmend you see the results of the debug-modus of my site.
i hope anyone can helps me. i am so frustrated...

Results of debug-modus from an article of my site:
http://www.hdsports.at/laufsport/events ... -und-stein

File to download:
http://www.filedropper.com/query_1

[leolam]

The reasons for being slow are shown here: http://gtmetrix.com/reports/www.hdsports.at. Work on the issues, enable gzip, caching, install and configure JCH-plugin

Leo

[HDsportsAT]

The reasons for being slow are shown here: http://gtmetrix.com/reports/www.hdsports.at. Work on the issues, enable gzip, caching, install and configure JXH-plugin

Leo

GZIP is enabled.
what is JXH-Plugin. I dont found anything about a plugin named JXH

i used gmetrix already. but these helps not to solve the problem. its definitly a problem, that camed after migration to 3.x.
but i dont know, how to solve the problem.

the big problem is the the to the "first connect". a good site has a time from 10ms or something around. i have 2.000 or 3.000 ms...

[leolam]

Corrected the typo: JCH

Leo

[HDsportsAT]

i use already the jch optimize plugin

[HDsportsAT]

have anyone an idea to solve these problem:

i also have a problem, when i write an article in the backend and save it:
i must wait 1 minute, till the article is saved.
when i look in phpmyadmin on the status on my server i see following process during saving:

UPDATE xx_content SET ordering = 102 WHERE `id` = '9617'
whenn i press again on the status-button
i see another new ordering-process
UPDATE xx_content SET ordering = 256 WHERE `id` = '8835'

i dont know what that means. but i think these ordering-process is the problem, for the long waiting-time when save an article. the numbers 9617, 8835... going from the highest id in this category down to the lowest id.

So when i have 5.000 articles in one category, there are 5000 of these queries when i save an article...