XF 1.3 table xf_session keeps on crashing

[squid]

Hi all,

my Xenforo forum runs since 6 weeks now with a vBulletin import. I really like the software but the table xf_session keeps on crashing.

How can this be fixed?

 

[Mike]

If it's recurring, then it's almost certainly an issue on your server -- either a hardware issue or something causing MySQL to be stopped unexpectedly. You may need to talk to your host.

 

[squid]

Ok, I'll talk with my host.

 

[squid]

This problem keeps coming up :-(

How about changing xf_session to InnoDB?

 

[Paul B]

Did your host offer any explanation?

Under normal circumstances the session table shouldn't be crashing.

 

[squid]

They checked it and don't blame it on the server.

Maybe this helps:

mysql> show processlist;
+-------+------------------+-----------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 70596 | mydbuser | localhost | mydb | Execute | 3143 | updating | DELETE FROM `xf_session` WHERE (expiry_date < 1423084245) |
| 70599 | mydbuser | localhost | mydb | Execute | 3143 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70605 | mydbuser | localhost | mydb | Execute | 3138 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70606 | mydbuser | localhost | mydb | Execute | 3137 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70611 | mydbuser | localhost | mydb | Execute | 3133 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70613 | mydbuser | localhost | mydb | Execute | 3132 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70619 | mydbuser | localhost | mydb | Execute | 3128 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70623 | mydbuser | localhost | mydb | Execute | 3124 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70628 | mydbuser | localhost | mydb | Execute | 3120 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70630 | mydbuser | localhost | mydb | Execute | 3119 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70634 | mydbuser | localhost | mydb | Execute | 3118 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70637 | mydbuser | localhost | mydb | Execute | 3117 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = '198a8c46935a31a686b749795a1c0fd6'
| 70641 | mydbuser | localhost | mydb | Execute | 3115 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70645 | mydbuser | localhost | mydb | Execute | 3112 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70648 | mydbuser | localhost | mydb | Execute | 3110 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70651 | mydbuser | localhost | mydb | Execute | 3107 | Waiting for table level lock | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 70652 | mydbuser | localhost | mydb | Execute | 3105 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70659 | mydbuser | localhost | mydb | Execute | 3101 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'a3894f1984bcba3a5732911fe25b3c70'
| 70660 | mydbuser | localhost | mydb | Execute | 3099 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'e7e9cb4b1dcf5faa2348940e223e4e71'
| 70661 | mydbuser | localhost | mydb | Execute | 3098 | Waiting for table level lock | SELECT session_data
FROM xf_session
| WHERE session_id = 'e7e9cb4b1dcf5faa2348940e223e4e71'
| 70664 | mydbuser | localhost | mydb | Execute | 3097 | Waiting for table level lock | SELECT session_data

 

[Mike]

Any query running for 3100 seconds is not a sign of a healthy server. I'd suggest that the server is overloaded.

You can convert the xf_session table to InnoDB if you wish and if InnoDB has a decent configuration (it is often unconfigured which uses the default settings which are really no use if you have any real data).