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

1.7.3 Slow Newsfeed MySQL Queries | Forum

Webster Molaudi
Webster Molaudi Apr 17 '15
Hi Oxwallers,

1.7.3 Newsfeed seems to have some issues i contacted my host as my site kept on crushing and being slow and they said they noticed that multiple slow MySQL queries are being executed on my database. They provided me with the most recent and the complete mysql-slow.log (it is over 500 000 lines long) file on my server saved Logs :





------ THE CULPRIT ----

Slow queries were found:
        Executed 47 min ago for 1 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = 38 AND activity.status='active' AND activity.timeStamp<1429245531 AND ( ( follow.userId='38' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '38' AND activity.status='active' AND activity.timeStamp<'1429245531' AND ( ( activity.userId='38' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '38' AND activity.status='active' AND activity.timeStamp<'1429245531' AND ( ( action_feed.feedId='38' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='38' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '38' AND activity.status='active' AND activity.timeStamp<'1429245531' ) a                                                                                                  
        Executed 47 min ago for 1 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT action.id) FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId INNER JOIN ow_newsfeed_activity cactivity ON action.id = cactivity.actionId INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `activity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_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_table_alias`.`emailVerify` = 1) AND activity.status='active' AND activity.timeStamp<1429245531 AND activity.privacy='everybody' AND action_feed.feedType='user' AND action_feed.feedId='1' AND activity.visibility & 8 AND cactivity.status='active' AND cactivity.activityType='create' AND cactivity.privacy='everybody' AND cactivity.visibility & '8                            
        Executed 47 min ago for 1 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = 1 AND activity.status='active' AND activity.timeStamp<1429245533 AND ( ( follow.userId='1' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429245533' AND ( ( activity.userId='1' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429245533' AND ( ( action_feed.feedId='1' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='1' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429245533' ) a                                                                                                          
        Executed 47 min ago for 1 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = 3067 AND activity.status='active' AND activity.timeStamp<1429245535 AND ( ( follow.userId='3067' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '3067' AND activity.status='active' AND activity.timeStamp<'1429245535' AND ( ( activity.userId='3067' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '3067' AND activity.status='active' AND activity.timeStamp<'1429245535' AND ( ( action_feed.feedId='3067' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='3067' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '3067' AND activity.status='active' AND activity.timeStamp<'1429245535' ) a                                                                                  
        Executed 46 min ago for 5 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT action.id) FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId LEFT JOIN ow_newsfeed_activity pactivity ON activity.actionId = pactivity.actionId AND (pactivity.status='active' AND pactivity.activityType='create' AND pactivity.privacy!='everybody' AND pactivity.visibility & 1) INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `activity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND pactivity.id IS NULL AND activity.status='active' AND activity.activityType='create' AND activity.privacy='everybody' AND activity.visibility & '1                                                                                                                                        
        Executed 46 min ago for 6 sec on Database --> .                                                                                                                                             
        SELECT COUNT(DISTINCT action.id) FROM ow_newsfeed_action action INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId LEFT JOIN ow_newsfeed_activity pactivity ON activity.actionId = pactivity.actionId AND (pactivity.status='active' AND pactivity.activityType='create' AND pactivity.privacy!='everybody' AND pactivity.visibility & 1) INNER JOIN `ow_base_user` base_user_table_alias ON base_user_table_alias.`id` = `activity`.`userId` LEFT JOIN `ow_base_user_suspend` base_user_suspend_table_alias ON base_user_suspend_table_alias.`userId` = `base_user_table_alias`.`id` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND pactivity.id IS NULL AND activity.status='active' AND activity.activityType='create' AND activity.privacy='everybody' AND activity.visibility & '1                                                                                                                                        
        Executed 40 min ago for 1 sec on Database --> myvirgo3_aw.                                                                                                                                  
        SELECT * FROM ow_oaseo_item WHERE `hash` = 'df2d60e9b855d2cfaa88296bbef19855' LIMIT 0, 1                                                                                                    
        Executed 26 min ago for 1 sec on Database --> myvirgo3_aw.                                                                                                                                  
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<1429246773 AND ( ( follow.userId='1' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429246773' AND ( ( activity.userId='1' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429246773' AND ( ( action_feed.feedId='1' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='1' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '1' AND activity.status='active' AND activity.timeStamp<'1429246773' ) a                                                                                                        
        Executed 26 min ago for 1 sec on Database --> myvirgo3_aw.                                                                                                                                  
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '227' AND activity.status='active' AND activity.timeStamp<1429246801 AND ( ( follow.userId='227' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '227' AND activity.status='active' AND activity.timeStamp<'1429246801' AND ( ( activity.userId='227' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '227' AND activity.status='active' AND activity.timeStamp<'1429246801' AND ( ( action_feed.feedId='227' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='227' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '227' AND activity.status='active' AND activity.timeStamp<'1429246801' ) a                                                                                        
        Executed 26 min ago for 2 sec on Database --> myvirgo3_aw.                                                                                                                                  
        SELECT COUNT(DISTINCT `id`) FROM ( SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '228' AND activity.status='active' AND activity.timeStamp<1429246802 AND ( ( follow.userId='228' AND activity.visibility & 2 ) ) UNION SELECT action.`id` 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` WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '228' AND activity.status='active' AND activity.timeStamp<'1429246802' AND ( ( activity.userId='228' AND activity.visibility & 4 ) ) UNION SELECT action.`id` 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` 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_table_alias`.`emailVerify` = 1) AND cactivity.userId = '228' AND activity.status='active' AND activity.timeStamp<'1429246802' AND ( ( action_feed.feedId='228' AND action_feed.feedType="user" AND activity.visibility & 8 ) ) UNION SELECT action.`id` 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` INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe' AND subscribe.userId='228' WHERE (`base_user_suspend_table_alias`.`id` IS NULL) AND (`base_user_table_alias`.`emailVerify` = 1) AND cactivity.userId = '228' AND activity.status='active' AND activity.timeStamp<'1429246802' ) a
        Executed 13 min ago for 1 sec on Database --> myvirgo3_aw.
        SELECT * FROM `ow_oaseo_slug` WHERE `entityType` = 'event-view' AND `entityId` = '--PLACEHOLDER--' AND `active` = 1
        Executed 2 min ago for 1 sec on Database --> myvirgo3_aw.
        SELECT * FROM ow_forum_read_topic WHERE `topicId` = 5041 AND `userId` = 3067 LIMIT 0, 1


-----

So how do i fix this ?

Here are my server details :

Server Basics:

1 x 3.0 GHz CPU Cores, CentOS, 2GB RAM, 50GB SSD, cPanel, Cloud Storage, Management Services


The Forum post is edited by Webster Molaudi Apr 17 '15
ross Team
ross Apr 20 '15
How many users do you have?

How many active users

How many concurrent online users?

Webster Molaudi
Webster Molaudi Apr 20 '15
Hi Ross,

How many users do you have? - = +/- 10,000

How many active users = +/- 34,000 a Month visit but not logged in

How many concurrent online users? = +/- 3 a Day logged in


I have an Auto Purge plugin that delete inactive users after 365 days without login in.....!!!


ross Team
ross Apr 23 '15
Webster, I see you have on your website almost all plugins from the store:). Please disable them all and try again, as to the sql queries, yes these are the heaviest query of all, as they gather the info for the newsfeed, but if you have crushing of the server, that means that it can handle the load anymore, you need to upgrade it. 


Also I see you post some news like every 2 hours, obviously you have friends and followers which also get notifications and their dashboard newsfeed get updated. I hope you see what I mean. 

If the server no longer can handle this feature you obviously have to upgrade it, especially with so many users. 

Webster Molaudi
Webster Molaudi Apr 23 '15
Hi Ross,

Yes i bought / downloaded almost half if not all of the plugins instore that seems to be what i need to complete my Oxwall site i did upgrade to cloud....What i need to know is :

1. Do i need to increase RAM
2. Do i need to increase CPU's
3. Do i need to increase Both

So the site functions to perfection ?
The Forum post is edited by Webster Molaudi Apr 23 '15
dave Leader
dave Apr 24 '15
I would say both, first its more economical when you do one you might as well do the other. Also with that many members you are not only processing alot of data in the backend but your also holding alot of data current in the fontend so yes i would do both. 


i have 50GB space and 4GB of ram and my site is only about 5GB in space and i struggle sometimes so i would even try to go 8GB + if you can afford it and quad core if you can.  Which many of the quad cores will have 16GB already so if you went that way you would see a dramatic improvement. 

ross Team
ross Apr 27 '15
Dave is right, you will benefit from increasing both.