We build. You grow.

Get best community software here

Start a social network, a fan-site, an education project with oxwall - free opensource community software

big database only 600 users.... | Forum

Jordi
Jordi Mar 4 '16
Hi guys what is a normal size for the ow_newsfeed_action table?? minie is 48 mb for a site with 600 profiles.... I think its huge..

and my site is really slow!


SO tell me is this normal 


thanks guys

ross Team
ross Mar 4 '16
It is slow due to this query: 


SELECT b.`id` FROM ( SELECT action.`id`, action.`entityId`, action.`entityType`, action.`pluginKey`, action.`data`, activity.timeStamp FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `cactivity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` LEFT JOIN `ow_base_user_disapprove` base_user_approve_table_alias ON base_user_approve_table_alias.`userId` = `base_user_table_alias`.`id` INNER JOIN ow_newsfeed_action_feed action_feed ON activity.id=action_feed.activityId LEFT JOIN ow_newsfeed_follow follow ON action_feed.feedId = follow.feedId AND action_feed.feedType = follow.feedType WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_approve_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = :u AND activity.status=:s AND activity.timeStamp<:st AND ( ( follow.userId=:u AND activity.visibility & :vf ) ) UNION SELECT action.`id`, action.`entityId`, action.`entityType`, action.`pluginKey`, action.`data`, activity.timeStamp FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `cactivity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` LEFT JOIN `ow_base_user_disapprove` base_user_approve_table_alias ON base_user_approve_table_alias.`userId` = `base_user_table_alias`.`id` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_approve_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = :u AND activity.status=:s AND activity.timeStamp<:st AND ( ( activity.userId=:u AND activity.visibility & :va ) ) UNION SELECT action.`id`, action.`entityId`, action.`entityType`, action.`pluginKey`, action.`data`, activity.timeStamp FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `cactivity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` LEFT JOIN `ow_base_user_disapprove` base_user_approve_table_alias ON base_user_approve_table_alias.`userId` = `base_user_table_alias`.`id` INNER JOIN ow_newsfeed_action_feed action_feed ON activity.id=action_feed.activityId WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_approve_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = :u AND activity.status=:s AND activity.timeStamp<:st AND ( ( action_feed.feedId=:u AND action_feed.feedType="user" AND activity.visibility & :vfeed ) ) UNION SELECT action.`id`, action.`entityId`, action.`entityType`, action.`pluginKey`, action.`data`, activity.timeStamp FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `cactivity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` LEFT JOIN `ow_base_user_disapprove` base_user_approve_table_alias ON base_user_approve_table_alias.`userId` = `base_user_table_alias`.`id` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType=:as AND subscribe.userId=:u WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_approve_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = :u AND activity.status=:s AND activity.timeStamp<:st ) b GROUP BY b.`id` ORDER BY MAX(b.timeStamp) DESC LIMIT 0, 5


this issue has been already reported to our devs, but the actions necessary to fix that requires  refactoring of the newsfeed code and it's querys it's a lot of work, please be patient, it will be fixed in the nearest future. 


The Forum post is edited by ross Mar 4 '16
Jordi
Jordi Mar 4 '16
Ross, Does that mean i cant do anything about it?

And is this related to 1.8.1 or also to 1.8 ?

ross Team
ross Mar 4 '16
This is related to older version as well, even 1.7.2 and older. 

You can try more powerful server for now, that's all I can recommend you.

Jordi
Jordi Mar 4 '16
I am moving this website to a more powerfull vps this weekend. I hope this is solved very quick.. 

Thanks ross.

Anitaku
Anitaku Mar 4 '16
Any estimated date of patch release? 
Will it be a fix to newsfeed plug in or core update? 
ross Team
ross Mar 4 '16
No estimated date for now. 
Jordi
Jordi Mar 6 '16
Ross my site is gerrting useless at the moment. I cant open the idex page with the newsfeed on it without a database error.. Can you plz have a look what's wrong? 
ross Team
ross Mar 6 '16
What database error you get? 
Jordi
Jordi Mar 6 '16
Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, webmaster@theotherbook.nl and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request.

Apache/2.2.31 (Unix) mod_ssl/2.2.31 OpenSSL/1.0.1e-fips mod_bwlimited/1.4 mod_fcgid/2.3.9 Server at theotherbook.com Port 443
ross Team
ross Mar 6 '16
This is server related error, contact your hosting provider to resolve it
Jordi
Jordi Mar 6 '16
no its not because when i open the site with account with no friends it open normal
Jordi
Jordi Mar 6 '16
pls test ot for your self


ross Team
ross Mar 7 '16
What do you mean it's not, the error clearly states that? 
Jordi
Jordi Mar 7 '16
when i login with  a other account <with just a few friends  the site opens normal. It has to do something with the database
ross Team
ross Mar 7 '16
Yes, I already told you, that's account is slow on dashboard and main newsfeed due to the query (See above)

but the error you get is related to the server, you need to contact your hosting provider to resolve it. 

Jordi
Jordi Mar 7 '16
ok but the i saw ysterday that the cron that deletes expired items runned in november for the last time. i think i removed the plugin that bloked the cron. Can we check this and can i manual run that cron to delete old items?
Jordi
Jordi Mar 7 '16
i think thats the reason for the big table
ross Team
ross Mar 7 '16
Jordi, I just checked your phpmyadmin ow_base_cron_jobs, 


your cron works fine, the job NEWSFEED_Cron::deleteActionSet ran today Mon, 07 Mar 2016 06:15:02 GMT

Jordi
Jordi Mar 7 '16
yes because i removed the blocking plugin. but can i also see if there are items that has to be deleted?
Pages: 1 2 3 4 5 »