XF 1.4 MYSQL Errors (Need Optimization?)

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_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) {
}
}

PHP: 
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) {
}
}
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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)
 
Upvote 0 Downvote
[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?
 
Upvote 0 Downvote
Sheratan said:
[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?
Click to expand...
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.
 
Upvote 0 Downvote
I also use the option in ACP "Cache BB Code output"

Could that add massively to database size? :o

Or is such a database size normal for a forum like mine.
 
Upvote 0 Downvote
Divvens said:
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.
Click to expand...
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
Click to expand...

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)
Click to expand...

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.
 
Upvote 0 Downvote
Sheratan said:
Ouch. 1GB RAM + 1GB Swap. That's from SolusVM right? How about from ssh? Run this script: free -m or from WHM > Server Information.
Click to expand...
Memory.webp
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
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

jb9826
  • Question Question
XF 1.5 An unexpected error occurred. "The memcache extension must be loaded for using this backend"
Replies
3
Views
319
Paul B
Paul B
jb9826
  • Question Question
XF 1.5 Batch Update Threads Timeout
Replies
0
Views
319
jb9826
jb9826
jb9826
  • Question Question
XF 1.5 Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
Replies
0
Views
534
jb9826
jb9826
bloop
  • Question Question
Xenforo Enhanced Search 1.1.4 - Error Message
Replies
1
Views
800
Overscan
Overscan
K
  • Question Question
XF 1.5 Email Transport Method SMTP errors (connection timed out, invalid auth)
Replies
11
Views
1K
TPerry
TPerry
Top Bottom