Server issues: slowdowns and timeout. Need help on what is causing the problem.

[deslocotoco]

So,

I'm getting some nice quantity for my new Forum. I have about 150 users active and a lot by the day. Tops around 250 (members + guests), with 1500 registered members.

I trying to contact the host every time that i have a slowdown (seconds, some times 1, 2 minutes of no server response). I did upgrade the my virtual server to a "Level 2" plan, with 4GB of memory and two CPUs. I was thinking that is gonna be enough but it seems it not.

My host just send me some logs, with, in their opinion, are consuming more resources in the MySQL.

The first one was about proxy images. I disabled this function but did just a little improvement on my server performance.

Now, they send me this log, telling me that this kind of 'requests' on the server are consuming a lot of the resources on my server:

5995348 politz localhost forum 0 Execut SELECT thread.thread_id, post.post_id FROM xf_post AS post INNER JOIN xf_thread AS thread ON (post.thread_id = thread.thread_id AND thread.node_id IN (16, 3, 5, 7, 2, 21, 10, 9, 22, 20, 8, 19, 11, 12)) INNER JOIN xf_forum AS forum ON (forum.node_id = thread.node_id AND forum.find_new = 1) LEFT JOIN xf_thread_read AS thread_read ON (th
5995358 politz localhost forum 0 Execut SELECT thread.thread_id, post.post_id FROM xf_post AS post INNER JOIN xf_thread AS thread ON (post.thread_id = thread.thread_id AND thread.node_id IN (16, 3, 5, 7, 2, 21, 10, 9, 22, 20, 8, 19, 11, 12)) INNER JOIN xf_forum AS forum ON (forum.node_id = thread.node_id AND forum.find_new = 1) LEFT JOIN xf_thread_read AS thread_read ON (th
5995370 politz localhost forum 0 Execut INSERT INTO `xf_edit_history` (`content_type`, `content_id`, `edit_user_id`, `old_text`, `edit_history_id`, `edit_date`) VALUES (?, ?, ?, ?, ?, ?)
5995372 politz localhost forum 0 Execut SELECT COUNT(*) FROM `xf_post` WHERE (`xf_post`.`thread_id` = 7043) AND ((`xf_post`.`message_state` = 'moderated' AND `xf_post`.`user_id` = 1132) OR (`xf_post`.`message_state` IN ('visible'))) AND (`xf_post`.`post_date` > 1541373320) AND (`xf_post`.`user_id` NOT IN (764))
5995375 politz localhost forum 0 Execut SELECT `xf_user`.*, `xf_user_option_Option_1`.*, `xf_user_privacy_Privacy_2`.*, `xf_user_profile_Profile_3`.* FROM `xf_user` LEFT JOIN `xf_user_option` AS `xf_user_option_Option_1` ON (`xf_user_option_Option_1`.`user_id` = `xf_user`.`user_id`)LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`u
5995378 politz localhost forum 0 Execut SELECT `xf_thread`.*, `xf_post_LastPost_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_thread_read_Read_5`.*, `xf_forum_read_Read_6`.* FROM `xf_thread` LEFT JOIN `xf_post` AS `xf_post_LastPost_1` ON (`xf_post_LastPost_1`.`post_id` = `xf_thread`.`last_post_id`)LEFT JOIN `xf_forum` AS
5995379 politz localhost forum 0 Execut SELECT `xf_thread`.*, `xf_user_User_1`.*, `xf_thread_watch_Watch_2`.*, `xf_draft_DraftReplies_3`.*, `xf_thread_reply_ban_ReplyBans_4`.*, `xf_forum_Forum_5`.*, `xf_node_Node_6`.*, `xf_permission_cache_content_Permissions_7`.*, `xf_thread_read_Read_8`.*, `xf_forum_read_Read_9`.* FROM `xf_thread` LEFT JOIN `xf_user` AS `xf_user_User_1` O
5995380 politz localhost forum 0 Execut SELECT `xf_thread`.*, `xf_post_LastPost_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_thread_read_Read_5`.*, `xf_forum_read_Read_6`.* FROM `xf_thread` LEFT JOIN `xf_post` AS `xf_post_LastPost_1` ON (`xf_post_LastPost_1`.`post_id` = `xf_thread`.`last_post_id`)LEFT JOIN `xf_forum` AS
5995381 politz localhost forum 0 Execut SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_user_LastPoster_5`.* FROM `xf_thread` FORCE INDEX (`last_post_date`) LEFT JOIN `xf_forum` AS `xf_forum_Forum_1` ON (`xf_forum_Forum_1`.`node_id` = `xf_thread`.`node_id`)LEFT JOIN `xf_node` AS `xf_node_
5995384 politz localhost forum 0 Execut SELECT `xf_post`.*, `xf_thread_Thread_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.* FROM `xf_post` LEFT JOIN `xf_thread` AS `xf_thread_Thread_1` ON (`xf_thread_Thread_1`.`thread_id` = `xf_post`.`thread_id`)LEFT JOIN `xf_forum` AS `xf_forum_Forum_2` ON (`xf_forum_Forum_2`.`node_id` = `xf_
5995385 politz localhost site 0 Sleep
5995386 politz localhost forum 0 Execut SELECT `xf_user`.*, `xf_admin_Admin_1`.*, `xf_user_option_Option_2`.*, `xf_user_profile_Profile_3`.*, `xf_user_privacy_Privacy_4`.*, `xf_permission_combination_PermissionCombination_5`.* FROM `xf_user` LEFT JOIN `xf_admin` AS `xf_admin_Admin_1` ON (`xf_admin_Admin_1`.`user_id` = `xf_user`.`user_id`)LEFT JOIN `xf_user_option` AS `xf_us
5995387 politz localhost forum 0 Execut SELECT `xf_thread`.*, `xf_post_LastPost_1`.*, `xf_forum_Forum_2`.*, `xf_node_Node_3`.*, `xf_permission_cache_content_Permissions_4`.*, `xf_thread_read_Read_5`.*, `xf_forum_read_Read_6`.* FROM `xf_thread` LEFT JOIN `xf_post` AS `xf_post_LastPost_1` ON (`xf_post_LastPost_1`.`post_id` = `xf_thread`.`last_post_id`)LEFT JOIN `xf_forum` AS
5995388 politz localhost forum 0 Execut -- XFDB=fromWrite SELECT session_data FROM xf_session WHERE session_id = 'PIQiE_o8yzYrPRzscx7GT0kuBc46ZLpp' AND expiry_date >= '1541373579'

How i can "translate" this information to try to solve the problem?

Any tips on fixing? Do i need a new plan for my virtual server?

 

[WSWD]

What they gave you doesn't really do a whole lot...just says what MySQL processes are using the most resources. Is MySQL actually failing during the connection losses? Is Apache failing? What is top showing when the slowdowns take place? How about free -m?

 

[deslocotoco]

Well, is what they tell me when i ask what are consuming my resources.

When the site fail, first, he don't open when you click in anything, then, i get a Cloudflare error (most of then are timeouts 5xx erros). Happens when i get more than 180 active users in the same time.

 

[Walter]

What does top say? What is in my.cnf?

If you cant answer these questions: either you need a managed host or you need to learn how to manage a server.

 

[WSWD]

Yep, what @Walter said. What they are giving you helps with absolutely nothing. When things start going bad, you need to run the commands I listed, i.e. top, free -m, etc. What your host gave you is not useful at all.