XF 1.1 Problems with the Members tab

[AzzidReign]

I tried going through support when we first moved to xf, got no help. Then months later when Jake joined, I tried to get help again...no help. I was really hoping someone else might have ran into this problem already and found the answer (I mean, hell, digitalpoint is using xf and not having this problem).

I find it weird that my site is the only site that is having problems with the members tab and the "list all members" in the ACP. When I click on either, the site hangs for a while and then I get a db error. This happened before any mod was installed.

I got this error when trying to load the list all users in the acp with debug mode enabled:

Failed to get controller response and reroute to error handler (XenForo_ControllerAdmin_User::actionlist)
An exception occurred: Too many connections in /home/****/public_html/forums/library/Zend/Db/Adapter/Mysqli.php on line 333

Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 554
XenForo_Application->loadDb()
call_user_func_array() in XenForo/Application.php at line 781
XenForo_Application->lazyLoad() in XenForo/Application.php at line 811
XenForo_Application::get() in XenForo/Application.php at line 1100
XenForo_Application::getDb() in XenForo/Session.php at line 166
XenForo_Session->__construct() in XenForo/Session.php at line 292
XenForo_Session::startAdminSession() in XenForo/ControllerAdmin/Abstract.php at line 48
XenForo_ControllerAdmin_Abstract->_setupSession() in XenForo/Controller.php at line 298
XenForo_Controller->preDispatch() in XenForo/FrontController.php at line 309
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
XenForo_FrontController->run() in /home/****/public_html/forums/admin.php at line 13

This is from the forums members tab:

An exception occurred: Too many connections in /home/****/public_html/forums/library/Zend/Db/Adapter/Mysqli.php on line 333

Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 554
XenForo_Application->loadDb()
call_user_func_array() in XenForo/Application.php at line 781
XenForo_Application->lazyLoad() in XenForo/Application.php at line 811
XenForo_Application::get() in XenForo/Application.php at line 1100
XenForo_Application::getDb() in XenForo/Error.php at line 47
XenForo_Error::unexpectedException() in XenForo/Application.php at line 296
XenForo_Application::handleException()

This is my server specs:

This is my forum specs:

We typically have 1,000-1,700 users online at once.

 

[HWS]

This is easy: Raise the "max_connections" settings at your MySQL database.

 

[AzzidReign]

Our max is at 150 and we are typically at 2-3. Is 150 not enough?

 

[Jake Bunce]

Is 150 not enough?

Obviously not. You should increase it.

 

[AzzidReign]

Obviously not. You should increase it.

What to? Anyone with a large site have a recommendation?

 

[AzzidReign]

And why would the members tab and the list all members take soooooo many connections?

 

[HWS]

Why is another question.
We use 800 (and have 64 GB RAM in each DB server).

You should look at your "max_used_connections" gauge in MySQL and raise the setting accordingly in reasonable steps until the error disappears.

 

[AzzidReign]

I use admingeekz and when I asked about increasing the connections, this was their response:

It doesn't quite work that way. The number of maximum connections is determined by how much memory the system has available and how much mySQL will use with your dataset. All increasing connections does is when there is a spike (like if you get one to even hit 150) it will just continue and rather than hit the maximum connections it will use all your server resources.

Are you getting maximum connection errors at points? If so we can investigate to see why, this is almost always caused by some sort of query. The last time this happened was due to a DDoS as the GET flood was requiring the database to write lots of data into the session table.

 

[Mouth]

I use admingeekz and when I asked about increasing the connections, this was their response:

Ignore them, that is a generalised response that means nothing without analysis.
Unless you have a wayward sql occurring (unlikely, since as you say no-one else is experiencing it), linux can routinely handle at least 500 connections on mysql with only a few Gb RAM.

Use SHOW PROCESSLIST on your mysql console at the time, if you really want to see what is going on at that time.

Bump it, and your problem is solved.

 

[AzzidReign]

We increased it to 350 which agz said is the most we can do with the memory we have. We just tested out the "list all users" in the acp and this is what agz said:

The queries this page are executing are joining the user, session and post tables which just isn't going to work especially when the larger joins won't use full indexing so that would need some query optimization or even reworking to work with cached data (Such as dumping the data you want from the user fields in offpeak and then allowing it to search that table via the interface, just means the data would be alittle stale but you simply cannot be joining the larger tables while still actively using them).

|
| 41829291 | ***| localhost | ****| Execute | 56 | Sending data | SELECT COUNT(*)
FROM xf_user AS user
LEFT JOIN xf_session_activity AS user_online_status ON (user_online_status.user_id = user.user_id)
WHERE 1=1

| 41829242 | ****| localhost | ****| Execute | 56 | Locked | SELECT user.*
, user_online_status.view_date AS user_online
FROM xf_user AS user
LEFT JOIN xf_session_activity AS user_online_status ON (user_online_status.user_id = user.user_id)
WHERE user.username = 'Roos'

 

[HWS]

With 24 GB RAM you can easily raise until 700 if your buffer sizes are reasonable.

 

[AzzidReign]

They said to make changes (note above post). They further noted on it:
It's application layer so it's something you'd need to discuss with your bulletin board. Just having alook at the query you could likely adjust it to force the indexes it uses, as it looks like it doesn't even use the primary key index on the xf_user table so guiding the optimizer will likely help.

Is this something we can do to optimize these server intensive pages?? Why would it be querying ALL members...shouldn't it just query for the number of users on the first page, and then the 2nd page, query again, etc.?