XF 1.5 Forum unresponsive out of the blue and many errors

[snowman]

Seemingly out of the blue and over the past few days my forum has become increasingly slow to the point where it is not often unresponsive. I also suddenly have been getting a lot of errors in e-mail from the server. I have a modest dedicated server and have run XenForo since 1.0 (and before that vBulletin) at the same host. (Intel Atom D510 Dual-Core 1.6 with HT and 2GB memory).

Several dozen server errors have been logged of this type over the last few days:

"Zend_Db_Adapter_Mysqli_Exception: User snowman_xenforo already has more than 'max_user_connections' active connections - library/Zend/Db/Adapter/Mysqli.php:333 Generated By:
Unknown Account, Today at 10:20 AM"

Also getting this error at times:

Zend_Db_Adapter_Mysqli_Exception: Lost connection to MySQL server at 'sending authentication information', system error: 32 - library/Zend/Db/Adapter/Mysqli.php:333
Generated By: Unknown Account, Yesterday at 9:00 PM

I've received several HANG errors during this time (very rare prior to this).

Also, earlier today, FAILED error message - The service “mysql” appears to be down.

Typically, there are about 4 members online, 30 guests and 30 robots. There are 140,000 messages. I am running XF 1.50. No changes have been made recently except removal of a Holiday theme and going back to the default theme and color scheme. This traffic is about the same the entire time.

So far the hosting company has been rather unhelpful. Here's their latest reply:

Looking into activity on your server, it appears that the slowdown is due to excessive activity on your [ snowman_community ] database for the forums:

| 4463 | DELAYED | localhost | snowman_community | Delayed insert | 9 | Waiting for INSERT |
|
| 4537 | snowman_xenforo | localhost | snowman_community | Execute | 2 | query end | INSERT INTO xf_bb_code_parse_cache
(content_type, content_id, parse_tree, cache_version, ca |
| 4551 | snowman_xenforo | localhost | snowman_community | Execute | 9 | query end | INSERT INTO xf_bb_code_parse_cache
(content_type, content_id, parse_tree, cache_version, ca |
| 4564 | snowman_xenforo | localhost | snowman_community | Execute | 33 | update | INSERT INTO xf_bb_code_parse_cache
(content_type, content_id, parse_tree, cache_version, ca |
| 4577 | snowman_xenforo | localhost | snowman_community | Execute | 9 | update | INSERT INTO xf_bb_code_parse_cache
(content_type, content_id, parse_tree, cache_version, ca |
| 4579 | snowman_xenforo | localhost | snowman_community | Execute | 1 | Sending data | SELECT title, template_compiled
-----------------------------------------------------------------------------------------------------------------------

MySQL is being overloaded with queries to insert entries to the 'xf_bb_code_parse_cache' table. I have checked this table in PHPMyAdmin for the 'snowman' cPanel account and found it contains roughly 4000 pages worth of entries. Since this appears to be a table for holding cached information, the issue could be due to your site's MySQL code and queries not being set to remove or properly handle this cache information. I highly recommend that you contact your web developer or the developer of the xenforo tool this table appears to be related to for further assistance, please not that code management is beyond the scope of our technical support.
​

They seem to be blaming XenForo software although I have run it for 5 years without this issue. On it's own, the situation has improved a little bit tonight, but for unknown reasons. There is still quite a bit of lag time when navigating and it is slow to display database items such as photos in the media gallery.

Any help and or suggestions would be appreciated. Thanks!

 

[Mike]

You can disable the BB code parse cache to stop writes into that table.

But otherwise, the errors are generally consistent with a server that's being overloaded. The queries shown really shouldn't be executing for that long, so presumably there's further load on the system and/or MySQL isn't optimized. Have you done any InnoDB-specific tuning in my.cnf?

 

[snowman]

Thanks for your response Mike. I have not done anything to optimize the MySQL or InnoDB. However, it seems odd that the forum ran OK like this five years with the same amount of traffic. This is supposed to be a managed server. What should I tell them the MySQL and InnoDB should be set at? I'm not sure if they are on the right track. They "tuned it" this morning and now the forum isn't working at all. I'm getting the "An unexpected database error occurred. Please try again later" error.

The tech wrote, "After a thorough review, it appears that these issues have been stemming from Apache and MySQL both being configured too liberally for the amount of resources available on this server." They suggested:

1. Update PHP to 5.6. Your current version of PHP (5.5) is currently supported, but upgrading to 5.6 will bring some much-needed optimizations to your PHP performance.

2. Switch to the DSO PHP handler with mod_ruid2. This combination switches from SuPHP to mod_php/DSO for the PHP handler, allowing Apache to use its native handler which tends to be speedier and have lower overhead. mod_ruid2 allows this to work without requiring permissions to be modified. We can also use this PHP handler change to enable OpCache (which is not compatible with SuPHP), which will further improve PHP performance and lower its CPU overhead, which should have a significant effect on your server's performance, as PHP processing appears to be the most taxing process on your CPU at this time.

3. Update Apache to 2.4. Your current version (2.2) is still currently supported, but upgrading to 2.4 will also help with optimization. We also recommend allowing us to patch Apache for [better security] protection.​

They don't seem very focused on MySQL. Am I wasting time here and need to find another host?

With regard to "You can disable the BB code parse cache to stop writes into that table" ... I will look into this. I'm not sure how to do it.

Thank you!

 

[TPerry]

With regard to "You can disable the BB code parse cache to stop writes into that table" ... I will look into this. I'm not sure how to do it.

PHP.. If your system supports it, then it's a good idea to go ahead and upgrade.
Apache upgrade - you will get a more optimized experience with the newer code (I use nginx personally)
Other aspect of mod_ruid2 vs DSO, see my reference in the parenthesis above. <grin>
Actually I've heard that mod_ruid2 is better for performance, but an Apache user (I got off it 3 years ago) would be more capable in answering.

yoursite.com/admin.php?options/list/performance#_bbCodeCacheTrimDays
will get you in the area for this... in the future for reference, the ACP search feature is very handy for stuff like this

 

[snowman]

PHP.. If your system supports it, then it's a good idea to go ahead and upgrade.

Thanks for your thoughts and assistance Tracy.

 

[MoonRiver]

We were having the same issues and did the optimize as instructed by tech support. It appeared to be working, but here we are again with the same issues. We didn't have any connection problems at all until we upgraded to Pro Web Hosting. Thoughts?