XF 1.5 Server Error Logs

[click here]

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

 

[Paul B]

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]

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]

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

 

[Paul B]

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]

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

 

[click here]

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]

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

 

[click here]

@Mike thanks but I have no idea what your even talking about as that's above my knowledge level at the moment.

 

[Mike]

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:

TRUNCATE TABLE xf_search;

Which may resolve it. (I would backup that table just in case, though it's only temporary data anyway.)

 

[click here]

@Mike I don't have any .cnf files in my etc directory.

 

[TPerry]

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

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)



If not, then the file doesn't exist at all and to find where it's reading from you can use this

mysql --help | grep Default -A 1

and you should see similar to this


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

 

[click here]

Okay, finally got access figured out.

 

[click here]

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

innodb_buffer_pool_size=7M
innodb_log_buffer_size=2M
innodb_log_file_size=5M
innodb_open_files=1000

 

[Mike]

Is innodb_buffer_pool_size really 7 megabytes? That's really off and will likely be causing major performance issues. Please see this for some InnoDB tuning guidance: https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/

 

[click here]

@Mike what do you suggest I change it to? I have a 4 core 4 gig RAM dedicated server and my forums are pushing 3.6M messages.
TIA

 

[Mike]

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.

 

[click here]

Cool, thanks for your input.