[Due to server hardware (Hard disk) ?] DB Table crash due to hit counting

iamrobert
Topic Author
Offline
Platinum Member

9 years 4 months ago - 9 years 4 months ago #55902 by iamrobert

Replied by iamrobert on topic DB Table crash due to hit counting

Hi ggppdk,

It crashed again - according to the server company:

"The caching service on the Cloud was stalled. It appears that the memory on the server was exceeded for a short time and this resulted in the stop of the MySQL and caching services"

We have 3gb of RAM - so I'm looking through my slow mysql logs and these keep appearing - but I don't really understand it:

1.

Code:

SELECT  i.id  FROM xxx_flexicontent_items_tmp AS i JOIN xxx_flexicontent_items_ext AS ie on ie.item_id = i.id JOIN xxx_flexicontent_types AS ty on ie.type_id = ty.id JOIN xxx_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN xxx_categories AS  c ON  c.id = rel.catid JOIN xxx_categories AS mc ON mc.id = i.catid WHERE c.published = 1 AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2015-07-14 15:07:31' ) AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2015-07-14 15:07:31' ) AND ty.access IN (0,1,1,5) AND mc.access IN (0,1,1,5) AND  i.access IN (0,1,1,5) AND i.id = 596 AND i.state IN ( 1, -5 )  GROUP BY i.id ORDER BY i.created DESC, i.title ASC LIMIT 0, 5;

Code:

SELECT DISTINCT t.id, t.name, i.itemid, CASE WHEN CHAR_LENGTH(t.alias) THEN CONCAT_WS(':', t.id, t.alias) ELSE t.id END as slug FROM xxx_flexicontent_tags AS t JOIN xxx_flexicontent_tags_item_relations AS i ON i.tid = t.id WHERE i.itemid IN ('871') AND t.published = 1 ORDER BY t.name;

Code:

SELECT  i.*, ie.*, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', mc.id, mc.alias) ELSE mc.id END as categoryslug, GROUP_CONCAT(rel.catid SEPARATOR ",") as itemcats FROM xxx_content AS i JOIN xxx_flexicontent_items_ext AS ie on ie.item_id = i.id JOIN xxx_flexicontent_types AS ty on ie.type_id = ty.id JOIN xxx_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id JOIN xxx_categories AS  c ON  c.id = rel.catid JOIN xxx_categories AS mc ON mc.id = i.catid WHERE i.id IN (1029) GROUP BY i.id LIMIT 0, 5;

Any ideas?

Last edit: 9 years 4 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 4 months ago - 9 years 4 months ago #55904 by ggppdk

Replied by ggppdk on topic DB Table crash due to hit counting

Hello

these are normal

query 1 is backend and is cached 1 per login session , and happens in backend dashboard
-- (so it will not have any effect since it happens rarely)

query 3 is universal module and is using specific FLEXIContent item ID, using the same query with it, e.g. a Joomla module would be multiple times slower
- to call it slow please give some real numbers (milliseconds)

query 2 is also using ids
- same as above give some real numbers (milliseconds)

if they are the slowest queries, then they should be quite fast as they are quering specific IDs

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Last edit: 9 years 4 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 4 months ago - 9 years 4 months ago #55905 by ggppdk

Replied by ggppdk on topic DB Table crash due to hit counting

Hello

you could use a solution that offers same/better perfromance without sharing the software processes

unfortunately siteground is using virtualization method that is sharing mysql Database process with other users
try using a software that requires full ACCESS to the DB like the popular Toad for MySQL, e.g. to do a DB comparison and siteground will tell you that you cannot have proper access, and you cannot change some MySQL configuration either !

e.g. this website has 120,000-150,000 page views per month (including bots) with 5%-15% (average 5 mins) CPU load during day
(OVH) 2x3GHz CPUs, with less RAM 2 GBs

also if you try to replace FLEXIcontent universal module with other module that needs similar query functionality you will find them to be much slower
also using a DB table smaller that ppp_content to make queries faster ... ppp_flexicontent_items_tmp

finally you can enable conservative/progressive caching in frontend, in v3 it should work properly in all cases

Last edit: 9 years 4 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 4 months ago #55909 by ggppdk

Replied by ggppdk on topic DB Table crash due to hit counting

Hello

also if you do not enable cache Joomla will do more hits updates than FC, (same query), because FC by default will only count the page view once per user visit

Please Log in or Create an account to join the conversation.

iamrobert
Topic Author
Offline
Platinum Member

9 years 4 months ago #55955 by iamrobert

Replied by iamrobert on topic DB Table crash due to hit counting

Thanks ggppdk,

Now - I'm not saying its FLEXIcontent - as I have been using in J1.5 - and have built so many sites on it. SO - I am guessing it the server or something I have done.

Here is the latest crash stats. Could you please tell me what I should be looking for to stop this error?

Code:

Executed 716 min ago for 1 sec on Database --> .
SELECT DISTINCT c.id, c.title, rel.itemid, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as slug FROM xxx_categories AS c JOIN xxx_flexicontent_cats_item_relations AS rel ON rel.catid = c.id WHERE rel.itemid IN ('341
Executed 716 min ago for 2 sec on Database --> .
UPDATE xxx_content SET hits=hits+1 WHERE id = 876
Executed 716 min ago for 2 sec on Database --> .
UPDATE xxx_content SET hits=hits+1 WHERE id = 890

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 4 months ago #55959 by ggppdk

Replied by ggppdk on topic DB Table crash due to hit counting

Hello

that could be because the table was locked during the execution of it

get the query and execute it in phpmyadmin to see how much time it would normally take

Please Log in or Create an account to join the conversation.