1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.1 Problems with the Members tab

Discussion in 'Troubleshooting and Problems' started by AzzidReign, Feb 6, 2013.

  1. AzzidReign

    AzzidReign Well-Known Member

    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:
    Code:
    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:
    Code:
    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:
    [​IMG]

    This is my forum specs:
    [​IMG]

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

    HWS Well-Known Member

    This is easy: Raise the "max_connections" settings at your MySQL database.
     
    Jake Bunce likes this.
  3. AzzidReign

    AzzidReign Well-Known Member

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

    Jake Bunce XenForo Moderator Staff Member

    Obviously not. You should increase it.
     
  5. AzzidReign

    AzzidReign Well-Known Member

    What to? Anyone with a large site have a recommendation?
     
  6. AzzidReign

    AzzidReign Well-Known Member

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

    HWS Well-Known Member

    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 and Jake Bunce like this.
  8. AzzidReign

    AzzidReign Well-Known Member

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

    Mouth Well-Known Member

    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.
     
  10. AzzidReign

    AzzidReign Well-Known Member

    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'
     
  11. HWS

    HWS Well-Known Member

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

    AzzidReign Well-Known Member

    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.?
     

Share This Page