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

XF 1.4 MYSQL Errors (Need Optimization?)

Discussion in 'Troubleshooting and Problems' started by Divvens, Apr 23, 2015.

  1. Divvens

    Divvens Well-Known Member

    A couple of mysql errors came up last night, I am on a managed VPS server, should I be telling my host anything specific or are there any specifics to make mysql optimization better?

    Errors below:
    PHP:
    Zend_Db_Statement_Mysqli_ExceptionMysqli statement execute error Lock wait timeout exceeded; try restarting transaction -library/Zend/Db/Statement/Mysqli.php:214
    Generated By
    :XXXToday at 5:47 AM
    [B]Stack Trace[/B]
    #0 /home/XXX/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
    #1 /home/XXX/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
    #2 /home/XXX/public_html/library/Zend/Db/Adapter/Abstract.php(632): Zend_Db_Adapter_Abstract->query('UPDATE `xf_user...', Array)
    #3 /home/XXX/public_html/library/XenForo/Model/Alert.php(365): Zend_Db_Adapter_Abstract->update('xf_user', Array, 'user_id = 3418')
    #4 /home/XXX/public_html/library/XenForo/Model/Alert.php(352): XenForo_Model_Alert->resetUnreadAlertsCounter(3418)
    #5 /home/XXX/public_html/library/XenForo/ControllerPublic/Account.php(41): XenForo_Model_Alert->markAllAlertsReadForUser(3418)
    #6 /home/XXX/public_html/library/XenForo/FrontController.php(347): XenForo_ControllerPublic_Account->actionAlerts()
    #7 /home/XXX/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #8 /home/XXX/public_html/index.php(13): XenForo_FrontController->run()
    #9 {main}
    [B]Request State[/B]
    array(
    3) {
    [
    "url"] => string(36"http://orojackson.com/account/alerts"
    ["_GET"] => array(0) {
    }
    [
    "_POST"] => array(0) {
    }
    }
    PHP:
    Zend_Db_Adapter_Mysqli_ExceptionUser database_name already has more than 'max_user_connections' active connections library/Zend/Db/Adapter/Mysqli.php:333
    Generated By
    Unknown AccountToday at 5:48 AM
    Stack Trace
    #0 /home/XXX/public_html/library/Zend/Db/Adapter/Abstract.php(315): Zend_Db_Adapter_Mysqli->_connect()
    #1 /home/XXX/public_html/library/XenForo/Application.php(727): Zend_Db_Adapter_Abstract->getConnection()
    #2 [internal function]: XenForo_Application->loadDb(Object(Zend_Config))
    #3 /home/XXX/public_html/library/XenForo/Application.php(970): call_user_func_array(Array, Array)
    #4 /home/XXX/public_html/library/XenForo/Application.php(1001): XenForo_Application->lazyLoad('db', NULL)
    #5 /home/XXX/public_html/library/XenForo/Application.php(1571): XenForo_Application::get('db')
    #6 /home/XXX/public_html/library/XenForo/Session.php(236): XenForo_Application::getDb()
    #7 /home/XXX/public_html/library/XenForo/Session.php(323): XenForo_Session->__construct()
    #8 /home/XXX/public_html/library/XenForo/Session.php(257): XenForo_Session::getPublicSession(Object(Zend_Controller_Request_Http))
    #9 /home/XXX/public_html/library/XenForo/Controller.php(293): XenForo_Session::startPublicSession(Object(Zend_Controller_Request_Http))
    #10 /home/XXX/public_html/library/XenForo/Controller.php(306): XenForo_Controller->_setupSession('Alerts')
    #11 /home/XXX/public_html/library/XenForo/FrontController.php(346): XenForo_Controller->preDispatch('Alerts', 'XenForo_Control...')
    #12 /home/XXX/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #13 /home/XXX/public_html/index.php(13): XenForo_FrontController->run()
    #14 {main}
    Request State
    array(3) {
    [
    "url"] => string(36"http://orojackson.com/account/alerts"
    ["_GET"] => array(0) {
    }
    [
    "_POST"] => array(0) {
    }
    }
     
  2. Mike

    Mike XenForo Developer Staff Member

    It's hard to give any specific comments. The first query sat waiting for a lock to become available and then gave up. The latter indicates that a connection was prevented because there were too many open already.

    These would likely point to a performance/load issue. It could be a one off because of a spike in traffic/unlucky circumstances, or it could be because of something that is happening at that time (such as performing a backup). If it happens at that time consistently, you may want to look at what the server is doing then.
     
    Divvens likes this.
  3. Divvens

    Divvens Well-Known Member

    Thanks for your input @Mike. I'll have a word with my host to see if they have any logs of around that time that could help find out why we had those errors occurred, as reaching max connections is out of possibility as we have it set to 100.

    For anyone if they want to provide input, below is MySQL Tuners report:
    Code:
     >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.42-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 453M (Tables: 21)
    [--] Data in InnoDB tables: 1G (Tables: 287)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 3)
    [!!] Total fragmented tables: 175
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 47m 49s (953K q [55.195 qps], 91K conn, TX: 9B, RX: 365M)
    [--] Reads / Writes: 56% / 44%
    [--] Total buffers: 336.0M global + 3.5M per thread (100 max threads)
    [OK] Maximum possible memory usage: 686.0M (66% of installed RAM)
    [OK] Slow queries: 0% (24/953K)
    [OK] Highest usage of available connections: 15% (15/100)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/352.1M
    [OK] Key buffer hit rate: 99.5% (3M cached / 16K reads)
    [OK] Query cache efficiency: 54.2% (241K cached / 445K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 26K sorts)
    [!!] Temporary tables created on disk: 83% (2K on disk / 2K total)
    [OK] Thread cache hit rate: 99% (15 created / 91K connections)
    [OK] Table cache hit rate: 70% (428 open / 604 opened)
    [OK] Open file limit used: 0% (96/200K)
    [OK] Table locks acquired immediately: 99% (869K immediate / 869K locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/1.2G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        tmp_table_size (> 64M)
        max_heap_table_size (> 64M)
        innodb_buffer_pool_size (>= 1G)
     
  4. Divvens

    Divvens Well-Known Member

    @Sheratan thank you for your support on my other thread (where you had initially suggested me to run MySQL Tuner and show the results to my host). I've posted the results above if you want to take a look at it! :)
     
  5. Sheratan

    Sheratan Well-Known Member

    [OK] Key buffer size / total MyISAM indexes: 64.0M/352.1M
    [!!] InnoDB buffer pool / data size: 128.0M/1.2G

    You need to increase key_buffer_size and innodb_buffer_pool. Your database is seems so big. How much free RAM do you have?
     
    Divvens likes this.
  6. Divvens

    Divvens Well-Known Member

    Memory Usage - 45% | 456.3 MB of 1 GB Used / 567.7 MB Free
    VSwap Usage - 21% | 211.55 MB of 1 GB Used / 812.45 MB Free

    That is on an average, when traffic increases the memory usage also increases a bit.
     
  7. Divvens

    Divvens Well-Known Member

    I also use the option in ACP "Cache BB Code output"

    Could that add massively to database size? :eek:

    Or is such a database size normal for a forum like mine.
     
  8. Sheratan

    Sheratan Well-Known Member

    Ouch. 1GB RAM + 1GB Swap. That's from SolusVM right? How about from ssh? Run this script: free -m or from WHM > Server Information.

    And based on this:

    You need "roughly" allocating at least 1.3GB RAM for optimal performance for InnoDB. And Also another 360MB for MyISAM. So you will need about ~1.5GB RAM for optimal database performance

    Not to mention another ~100MB for tmp_table_size and max_heap_table_size.

    You need to watch out for this too:

    During peak hour when Oda release new chapter ;) , you may experience 50-70 connection.

    tl;dr for optimal database experience you "may" need more RAM. I say "may" because I'm not a MySQL experts.
     
    Divvens likes this.
  9. Divvens

    Divvens Well-Known Member

    Memory.png
    Via SSH
    Code:
                 total       used       free     shared    buffers     cached
    Mem:          1024        970         53         13          0        555
    -/+ buffers/cache:        415        608
    Swap:         1024        235        788
    
    I'll check with my host if we can get additional ram as an add-on :D
     

Share This Page