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

[Live Free]

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:

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]

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.

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

 

[Kintaro]

I got a very similar problem today in two xenforo DBs at the same time in the same server.

is your xf_session InnoDB or MyISAM?

One of the two installations got a huge xf_session and after repairing it with:

#service mysql stop
#myisamchk -r /var/lib/mysql/DATABASE_NAME/xf_session
#service mysql start

and then TRUNCATE the huge xf_session table

the problem was solved (I hope definitely) on the other DB too, without doing nothing more.

 

[Paul B]

Server instability and/or MySQL restarting unexpectedly is the most likely cause.

Ask your host to investigate.

 

[Bryan]

I got a very similar problem today in two xenforo DBs at the same time in the same server.

is your xf_session InnoDB or MyISAM?

One of the two installations got a huge xf_session and after repairing it with:

#service mysql stop
#myisamchk -r /var/lib/mysql/DATABASE_NAME/xf_session
#service mysql start

and then TRUNCATE the huge xf_session table

the problem was solved (I hope definitely) on the other DB too, without doing nothing more.

This thread was from June last year, I seriously hope he is sorted by now

 

[Live Free]

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.

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

The first thing we did was change the innodb_buffer_pool_size from 512MB to 2048MB and set the InnoDB buffer pool instances to 2, with each share the total size of the InnoDB buffer pool. Still on the 8GB memory, any suggestions on confirming this is the cause before upgrading to 16GB?

I got a very similar problem today in two xenforo DBs at the same time in the same server.

is your xf_session InnoDB or MyISAM?

One of the two installations got a huge xf_session and after repairing it with:

I'm not sure the difference.

Did your problem reocur? I have a test install as well as an inactive installation of my second license. What is a normal xf_session size?

This thread was from June last year, I seriously hope he is sorted by now

It actually is still occurring, but not as frequently as it was. Serious issue and my current priority. Sometimes it'll go a month without a problem, and then crash.

Server instability and/or MySQL restarting unexpectedly is the most likely cause.

Ask your host to investigate.

I have, they were unable to find the cause so far. Multiple tickets.

FIRST TICKET
He said MySQL seized up, there was no error log and he assumed something didn't write correctly causing MySQL to panic and shut down. He suggested looking at the forum software, as the forum's database was the only database having issues and it appeared to be only tables connected to the forum software.

SECOND TICKET
When the issue first started, they said there was a possibility of resources causing the issue and that we need to know how much MySQL data server is using. He recommended I consult with a developer or database administrator to make sure things are being indexed when possible to improve performance. We increase innodb_buffer_pool_size from 512MB to 2GB, set the amount of InnoDB buffer pool instances to 2 with each share the total size of the InnoDB buffer pool, and reduced other data in InnoDB.

THIRD - MOST RECENT - TICKET
The issue continued. Two weeks ago we got a little bit further in investigating. The first support guy discovered that there were at least a few instances of MySQL error logs showing "mysql got signal 11." He wasn't able to determine the cause and handed it off to a different technician, who also could not determine the cause but came up with additional clues.

He said that just before the ticket was opened he could see in the logs that MySQL received a kill command. Found that the xf_sessions table was crashed and repaired during the next MySQL restart. He said that to move forward we need to see the issue with MySQL and investigate it before MySQL is restarted to get more information. Also may need to get an idea of the MySQL queries running when the issue starts. He said there is also a chance that the table xf_session was waiting on table level locks, causing MySQL to have to wait for a write to complete before starting another one. He said if this is the case conversions from MyISAM to InnoDB would be in order.

Does any of this help from? I guess my only option is to re-open the ticket the next time this happens, get the MySQL logs prior to restart, and let them investigate.

 

[HD-Sam]

Adding RAM alone in this situation may not help. @Live Free you're on the right path. You'll need to tweak your my.cnf settings. Running a tool like mysqltuner.pl will assist in generating stats such as your innodb usage and provide a basic guideline of how you need to tweak your my.cnf settings.

You can run mysqltuner.pl via SSH with:
wget -O mysqltuner.pl mysqltuner.pl
perl mysqltuner.pl

 

[Live Free]

So I managed to capture the logs my host asked for when the MySQL database/server became unresponsive, but prior to the reboot/full crash. After sharing it with them, they think they found the cause. It hasn't crashed it a week, but I'd love the opinion of someone more knowledgeable than me as to whether you think their explanation is sufficient, and whether I should consider this case closed (pending another crash, of course).

They said that on my server, query_cache_type=ON" was set in /etc/my.cnf to enable query cache. For some workflows, caching queries can provide a performance benefit, but for the Xenforo session (and search) tables this was creating a bottleneck that was holding up queries from executing efficiently under some circumstances. Below is a sample of a few lines of the process list showing the effects:

[XXX@XXX]# mysqladmin processlist status

+--------+------------------+-----------+---------------------+----------------+------+------------------------------+------------------------------------------------------------------------------------------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+------------------+-----------+---------------------+----------------+------+------------------------------+------------------------------------------------------------------------------------------------------+

| 376726 | account_admin | localhost | account_database | Execute | 2880 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

| 376727 | account_admin | localhost | account_database | Execute | 2880 | update | INSERT INTO xf_session_activity

(user_id, unique_key, ip, controller_name, controller_action, v |

| 376728 | account_admin | localhost | account_database | Execute | 2878 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

| 376729 | account_admin | localhost | account_database | Execute | 2878 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

| 376730 | account_admin | localhost | account_database | Execute | 2878 | Waiting for query cache lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |

| 376731 | account_admin | localhost | account_database | Execute | 2877 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

| 376732 | account_admin | localhost | account_database | Execute | 2877 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |

| 376733 | account_admin | localhost | account_database | Execute | 2876 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

| 376734 | account_admin | localhost | account_database | Execute | 2874 | Waiting for query cache lock | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |

... etc ... (166 threads total when I checked)

They said there were other minor issues, and they made the following changes:

• commented out "query_cache_type=ON" in /etc/my.cnf to disable query cache
• 
  
• disabled delayed inserts by setting "max_delayed_threads=0" in /etc/my.cnf
• 
  
• increased the MySQL file limit by setting "LimitNOFILE=65535" in /xx/xxx/xxx/mysql.service.d/limits.conf
• 
  
• repaired xenforo databases using mysqlcheck -r

The support technician said that by having the query cache disabled, he believes it should directly address the problem leading to non-responsiveness (and MySQL crashing). The issue hasn't reappeared yet, but there were multiple technicians trading the ticket. Any thoughts?

 

[AndyB]

I just checked my dedicated server and the query_cache_type is set to OFF.

 

[markus68]

Hi,
I also have query-cache disabled. This seems to be default now and should be disabled if you have multiple cores available (wich is normal these days): https://dba.stackexchange.com/questions/136790/when-to-disable-mysql-query-cache

Based on your forum size, you could think of using Elasticsearch. This offloads searches from the DB.

Regarding Memory-size for the DB: do a check how large your xenforo DB acutally is. I would size mysql so that this DB fits into memory and add some more room just to be safe. Allocating all 16GB (if your server only has 16GB) exclusively to mysql is not a good idea (esp. in the DB itself is only about 4 or 5 GB itself in size).

As mentioned mysqltuner is definately a help in getting the inital db-conf right.

HTH,
-Markus