XF 1.5 Site unresponsive due to table requests

[SuperCotilla]

Hello, I run a very busy community and the site have been unresponsive for a good part of the weekend. No specific error, just "Unexpected database error" message. You sometimes can enter to the site but as soon as you make a request you'll get the mentioned error. I'll give you the last Hostgator answer in hopes to find a solution together. Version 1.5.8. Thanks in advance.

www.cotilleando.com



Hello there,

This is due to requests to the cotille_xenforofinal and more specifically the xf_post table:

Minutes before and during the most recently reported issue:

16:00 1.44
16:01 1.75
16:02 2.08
16:03 1.89
16:04 1.79
16:05 1.54
16:06 1.94
16:07 1.66
16:08 4.44
16:09 16.33
16:10 16.02
16:11 22.18

Requests to that table with no load:

root@host [~]# for m in {00..06} ; do echo "16:${m} :: $(sys-snap -m 16:${m} | grep -c xf_post)" ; done | column -t
16:00 :: 2
16:01 :: 0
16:02 :: 0
16:03 :: 0
16:04 :: 1
16:05 :: 0
16:06 :: 0

Requests to that table with moderate to high load:

root@host [~]# for m in {07..11} ; do echo "16:${m} :: $(sys-snap -m 16:${m} | grep -c xf_post)" ; done | column -t
16:07 :: 0
16:08 :: 1
16:09 :: 18
16:10 :: 24
16:11 :: 24

As you can see, the table is very large and we recommend contacting a developer about caching to flat HTML files where possible and perhaps even archiving older posts:

root@host [/home/cotille]# mysql -e "SELECT COUNT(*) FROM xf_post" cotille_xenforofinal
+----------+
| COUNT(*) |
+----------+
| 3569155 |
+----------+

Please let us know if you experience any issues or have any questions or concerns.

Regards,

Matthew M.
GNU/Linux Systems Administrator II

 

[SuperCotilla]

Help anyone?

 

[Mike]

I don't really understand what the numbers in their comment is representing. Is that a load snapshot initially?

If so, it's generally indicative of load issues. It may indicate a traffic spike or a (D)DoS. It's difficult to say as that level of analysis isn't deep enough. It's not necessarily definitive that the DB is the cause, though it may well be.

I assume this is a dedicated server (or a VPS), so have you configured MySQL at all via my.cnf? Specifically, have you configured InnoDB? If not, this is almost certainly the root cause.

If that has been optimized, then showing a full process list from MySQL while the issue is happening would help.

For reference, your site errored for me with "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)", which would likely indicate that MySQL isn't running (or it isn't responding to requests). It was quite slow to trigger this, so that again points to a high load.

 

[SuperCotilla]

Thank you so much Mike. I'll let Hostgator know. They tend to blame the other side although had been very helpful customer service wise so far. I'll comeback with an answer, maybe between all parties we can pin point the issue.

BTW. Yes, is a dedicated. They'd offered me to purchase more memory thought.

 

[SuperCotilla]

Hello, I have reviewed the issues on your server and have some suggestions on things you can change to allow this to work better with the hardware you have available on your server. Reviewing the access logs for your site I see there were 597,120 requests all to the same page: root@host [/home/cotille/public_html]# grep "POST /index.php?liveupdate" ../access-logs/cotilleando.com -c 597120 These requests all look like this but with different referrers: 75.174.55.145 - - [16/Jul/2016:13:52:02 -0500] "POST /index.php?liveupdate HTTP/1.1" 200 107 "http://cotilleando.com/forums/casa-real-de-suecia-the-swedish-royal-family.35/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36" When doing a Google search for this URL, it brings up something about a Live Update plugin for XenForo to notify users of alerts and inbox messages without having to refresh the page. If you are indeed using this plugin, I would suggest disabling it completely as requests are being made multiple times a second and is overloading the server. If disabling this plugin is not an option then you would want to set the delay for updates to be as high as possible to prevent it from overloading the server. I would put a minimum of 5 minutes for the update delay. Since you receive very heavy traffic on your site, I would also recommend setting up PHP-FPM which will perform better in handling the heavy traffic you are receiving. We can install PHP-FPM for a one time fee of $35 for you. Please let us know how you'd like to proceed. Best regards, Mitch B. Linux Systems Administrator II