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

XF 1.5 Server Error Logs

#1
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
#2
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.
 
#3
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) {
  }
}
 
#4
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
#5
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.
 
#6
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/threa...e-installed-on-my-server.110918/#post-1026546
 
#7
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
#8
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
#10
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
#12
@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.
 
#14
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
#17
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.