XF 1.4 MYSQL Errors (Need Optimization?)

[Divvens]

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:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction -library/Zend/Db/Statement/Mysqli.php:214
Generated By:XXX, Today 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) {
}
}

Zend_Db_Adapter_Mysqli_Exception: User database_name already has more than 'max_user_connections' active connections - library/Zend/Db/Adapter/Mysqli.php:333
Generated By: Unknown Account, Today 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) {
}
}

 

[Mike]

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]

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:

 >>  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)

 

[Divvens]

@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!

 

[Sheratan]

[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]

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

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.

 

[Divvens]

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

Could that add massively to database size?

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

 

[Sheratan]

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.

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:

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

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:

Highest usage of available connections: 15% (15/100)

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]

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

Via SSH

             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