XF 1.5 Server Error Logs

click here

Active member
So out of the blue I am now getting a ton of these errors, any ideas?

Mysqli statement execute error : The total number of locks exceeds the lock table size
Yesterday at 8:19 PM - library/Zend/Db/Statement/Mysqli.php:214
 

Brogan

XenForo moderator
Staff member
Can you paste the full stack trace from a couple of the entries.
That may help to narrow down what is causing it.

Also, can you check what innodb_buffer_pool_size is set to in your MySQL configuration.
 

click here

Active member
Code:
Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : The total number of locks exceeds the lock table size - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, Yesterday at 8:19 PM
Stack Trace

#0 /home/username/mysite.com/forums/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/username/mysite.com/forums/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/username/mysite.com/forums/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /home/username/mysite.com/forums/library/XenForo/Model/Search.php(77): Zend_Db_Adapter_Abstract->insert('xf_search', Array)
#4 /home/username/mysite.com/forums/library/XenForo/ControllerPublic/FindNew.php(206): XenForo_Model_Search->insertSearch(Array, 'recent-posts', '', Array, 'date', false)
#5 /home/username/mysite.com/forums/library/XenForo/ControllerPublic/FindNew.php(35): XenForo_ControllerPublic_FindNew->findNewPosts()
#6 /home/username/mysite.com/forums/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_FindNew->actionPosts()
#7 /home/username/mysite.com/forums/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#8 /home/username/mysite.com/forums/index.php(13): XenForo_FrontController->run()
#9 {main}

Request State

array(3) {
  ["url"] => string(44) "http://mysite.com/forums/find-new/2695410/posts"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}
 

click here

Active member
Code:
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : The total number of locks exceeds the lock table size - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, Yesterday at 8:19 PM
Stack Trace

#0 /home/username/mysite.com/forums/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/username/mysite.com/forums/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/username/mysite.com/forums/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /home/username/mysite.com/forums/library/XenForo/Model/Search.php(77): Zend_Db_Adapter_Abstract->insert('xf_search', Array)
#4 /home/username/mysite.com/forums/library/XenGallery/ControllerPublic/FindNew.php(171): XenForo_Model_Search->insertSearch(Array, 'xengallery_medi...', '', Array, 'date', false)
#5 /home/username/mysite.com/forums/library/XenGallery/ControllerPublic/FindNew.php(41): XenGallery_ControllerPublic_FindNew->findNewMedia()
#6 /home/username/mysite.com/forums/library/XenForo/FrontController.php(351): XenGallery_ControllerPublic_FindNew->actionMedia()
#7 /home/username/mysite.com/forums/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#8 /home/username/mysite.com/forums/index.php(13): XenForo_FrontController->run()
#9 {main}

Request State

array(3) {
  ["url"] => string(44) "http://mysite.com/forums/find-new/3337020/media"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}
 

Brogan

XenForo moderator
Staff member
If this is for the site registered against your license, you have in excess of 3.5 million posts and are using standard MySQL search.

You are likely hitting the limits so will likely have to carry out some server tuning, although that will only mitigate the problem to some extent as MySQL search doesn't scale particularly well.

Most large sites tend to use an alternative to default MySQL search, such as the Enhanced Search add-on.
 

click here

Active member
If this is for the site registered against your license, you have in excess of 3.5 million posts and are using standard MySQL search.

You are likely hitting the limits so will likely have to carry out some server tuning, although that will only mitigate the problem to some extent as MySQL search doesn't scale particularly well.

Most large sites tend to use an alternative to default MySQL search, such as the Enhanced Search add-on.
Well crap: https://xenforo.com/community/threads/can-enhanced-search-be-installed-on-my-server.110918/#post-1026546
 

click here

Active member
I just performed all the searches and didn't receive any errors on the user side of things... I will keep an eye on it as it's about the only option I have right now.
 

Mike

XenForo developer
Staff member
There seems to be an implication that this could be related to innodb_buffer_pool_size: https://major.io/2010/02/16/mysql-the-total-number-of-locks-exceeds-the-lock-table-size-2/ Check to ensure that you've configured that as expected for the amount of data you have.

If you have, try truncating the xf_search table. It's a table that gets a lot of writes and deletes, so depending on your config, the table may be very fragmented (mentioned towards the end of http://bugs.mysql.com/bug.php?id=15667).
 

Mike

XenForo developer
Staff member
innodb_buffer_pool_size is a InnoDB configuration setting that is changed in /etc/my.cnf. The default is (or has been) horrendously small. Do you see a value for this in that file?

Otherwise, you can run:
Code:
TRUNCATE TABLE xf_search;
Which may resolve it. (I would backup that table just in case, though it's only temporary data anyway.)
 

Tracy Perry

Well-known member
@Mike I don't have any .cnf files in my etc directory.
They can either be in /etc or it could be in /etc/mysql.
I believe Debian puts them in /etc/mysql, whereas my CentOS install has it in the /etc directory.
Type in (while root or using sudo)
Code:
updatedb
locate my.cnf
The updatedb command make take a bit to run. Once it has ran and you run the other command you should see similar to this (but probably not in as many locations)

Screen Shot 2016-01-27 at 8.49.45 PM.png

If not, then the file doesn't exist at all and to find where it's reading from you can use this
Code:
mysql --help | grep Default -A 1
and you should see similar to this
Screen Shot 2016-01-27 at 8.53.56 PM.png

If you notice, the /etc/my.cnf is where it reads from first on my system.
 

click here

Active member
innodb_buffer_pool_size is a InnoDB configuration setting that is changed in /etc/my.cnf. The default is (or has been) horrendously small. Do you see a value for this in that file?

@Mike
Code:
innodb_buffer_pool_size=7M
innodb_log_buffer_size=2M
innodb_log_file_size=5M
innodb_open_files=1000
 

Mike

XenForo developer
Staff member
It's hard to say, as I don't know what other elements are on your machine and how memory is being used. This is where a sysadmin would help to analyze this. That said, if it's really 7 megabytes, almost anything would be better. With 4G of RAM and not knowing what else is on there, I'd probably start at 1G and see how that goes.
 
Top