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

What would cause a db crash with xf_session's table?

Live Free

Active member
#1
Several days ago my Xenforo database crashed, users got a "cannot connect to database" error, some with mobile variants of the same error but still showing the forum theme.

My web host restarted the MySQL server, which fixed the issue for 6-9 hours, saying "MySQL was hanging and refusing to accept new connections". It then re-occurred. I restarted the MySQL server again, but the issue remained, at which point I ran the WHM database repair tool. This seemed to fix the issue. However, a day later the issue reoccured and I had to run the database repair tool yet again.

Here's the XF error logs:

Code:
Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Table './database_name/xf_session' is marked as crashed and should be repaired - library/Zend/Db/Statement/Mysqli.php:77
Generated By: User1, Jun 12, 2017
Stack Trace
#0 /home/admin/public_html/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('INSERT INTO `xf...')
#1 /home/admin/public_html/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'INSERT INTO `xf...')
#2 /home/admin/public_html/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('INSERT INTO `xf...')
#3 /home/admin/public_html/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#4 /home/admin/public_html/library/XenForo/Session.php(856): Zend_Db_Adapter_Abstract->insert('xf_session', Array)
#5 /home/admin/public_html/library/XenForo/Session.php(574): XenForo_Session->saveSessionToSource('7e48518aa51b803...', false)
#6 /home/admin/public_html/library/XenForo/Controller.php(414): XenForo_Session->save()
#7 /home/admin/public_html/library/XenForo/Controller.php(358): XenForo_Controller->updateSession(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#8 /home/admin/public_html/library/XenForo/FrontController.php(358): XenForo_Controller->postDispatch(Object(XenForo_ControllerResponse_View), 'XenForo_Control...', 'Index')
#9 /home/admin/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#10 /home/admin/public_html/index.php(13): XenForo_FrontController->run()
#11 {main}
Request State
array(3) {
  ["url"] => string(108) "https://www.mywebsite.org/threads/thread-title.144421/page-115"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(0) {
  }
}
I have not encountered the problem for about four days, but it was strongly reoccurring for the first 3-4 days. Also worth noting is my test board crash during this period as well, with the same/similar error message about the xf_session table.

I have not had this problem for a few days but I'm posting here because I'm trying to determine the cause, and I'd like to prevent data corruption. Possible causes I've found in searching involve faulty server hardware and poorly configured MySQL settings.

When I asked if it could my MySQL configuration, he said it could be resource usage causing issues with MySQL. Regarding said configuration, my host had this to say:

I have taken a look at the server's MySQL configuration and I have found some information relating to MySQL that I would like to provide you. To get an idea of what a good direction to move in as far as MySQL optimization goes, we need to know exactly how much MySQL data your server is using. I've located that usage and am providing it for you below.


+----------------+----------------------+----------------------+----------------------+| Storage Engine | Data Size | Index Size | Table Size |+----------------+----------------------+----------------------+----------------------+| MEMORY | 0.001 GB | 0.001 GB | 0.002 GB || MyISAM | 3.735 GB | 2.563 GB | 6.298 GB || InnoDB | 50.066 GB | 1.276 GB | 51.342 GB || Total | 53.801 GB | 3.840 GB | 57.642 GB |+----------------+----------------------+----------------------+----------------------+4 rows in set, 6 warnings (1.14 sec)


I would definitely recommend you consult with a developer or ideally a database administrator to work making sure things are being indexed wherever possible because that can help improve performance. We can also help with altering your MySQL configuration file so that once things are properly indexed, MySQL can operate more efficiently with configuration changes that make better use of available system resources.

Below is the current MySQL configuration file. I would recommend increasing the **innodb_buffer_pool_size** value from 512MB to at least 2GB. However it could be increased more, but the concern I have with that is you only have 8GB of memory on the server and we don't want to exhaust all of that on MySQL.

[mysqld]open_files_limit = 50000default-storage-engine=innodbinnodb_file_per_table=1performance-schema=0innodb_buffer_pool_size=512Minnodb_log_file_size=128Mmax_connections=500query_cache_type=ON#skip_name_resolveperformance_schema=offkey_buffer_size = 512Mmax_allowed_packet=268435456



**[root@host ~]# free -m**

total used free shared buff/cache availableMem: 6987 2990 307 240 3689 3440Swap: 1999 671 1328
Thoughts?
 

AndyB

Well-known member
#2
I have a similar sized forum 1.5M posts, I'm also on a dedicated server. I just checked and my server has 16GB memory, so the first thing I suggest is asking your web host to increase the memory to 16GB.

Then use this tool to optimize your MySQL memory.

Code:
https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl