xf_session marked as crashed -- happening daily/crashing. MYSQL Optimize Results within

[demonkey]

Hi everyone,
My site has been troublesome since migrating from VB4 in May. Recently it's got a lot worse. Currently averaging 60-80 users online at a time (mostly "guests"/bots lately), forum site is 350,000 posts /28,000 threads.

Running latest version of XF and disabled all plugins, yet almost nightly (when nobody's online really) the server crashes for reasons I can't figure out. Usually I can fix it in the morning by REPAIR TABLE XF_SESSION

Here's what I spotted in admin panel the last couple of nights:

Server Error

Mysqli prepare error: Table './mydb/xf_session' is marked as crashed and should be repaired

Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 825
Zend_Db_Adapter_Abstract->fetchOne() in XenForo/Session.php at line 625
XenForo_Session->getSessionFromSource() in XenForo/Session.php at line 360
XenForo_Session->_setup() in XenForo/Session.php at line 344
XenForo_Session->start() in XenForo/Session.php at line 246
XenForo_Session::getPublicSession() in XenForo/ControllerAdmin/Login.php at line 7
XenForo_ControllerAdmin_Login->actionForm() in XenForo/FrontController.php at line 310
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
XenForo_FrontController->run() in /home/mypath/mydomain/admin.php at line 13

I ran the MySQL Optimize script and here's what the output said, maybe someone can help with this:

[--] Up for: 2h 54m 1s (77K q [7.430 qps], 7K conn, TX: 4B, RX: 23M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (44% of installed RAM)
[OK] Slow queries: 0% (2/77K)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/372.0M
[OK] Key buffer hit rate: 99.6% (817K cached / 3K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 44% (110 on disk / 249 total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 10% (64 open / 638 opened)
[OK] Open file limit used: 0% (10/1K)
[OK] Table locks acquired immediately: 99% (79K immediate / 79K locks)
[!!] InnoDB data size / buffer pool: 768.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

Should I make the changes it says? I'm new to most of this and have been trying hard to make sense of this. Searching got me this far and I've been able to fix it each time, just running out of patience.

 

[MattW]

Are you on a dedicated server? I had this issue with my forum when it was on phpBB3, and it was a dying HDD causing various tables to crash randomly.

 

[demonkey]

Hi Matt,
It's a VPS and there are a few other sites on this server that have had no issues to date. I'll check the HDD though!

 

[MattW]

Are you able to post a copy of your my.cnf file? There looks to be quite a few things you can improve on based on the results of mysqltuner.

 

[demonkey]

Here's my.cnf:

mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#added kbm
#max_connections=200
#table_open_cache=16384
#table_definition_cache=16384
#tmp_table_size=64M
#join_buffer_size=512k

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The commented bits were from previous troubleshooting...

 

[CTXMedia]

AFAIK (and someone with more experience may be able to confirm/deny this) - the session table data is a current list of login sessions and can be truncated without any damage to the site itself (the worst outcome being people have to log back in again because their 'session' data is lost).

With that in mind you could try changing it to a (HEAP) MEMORY table to see if that stops it crashing? (with the data in memory it won't survive a server reboot, but that's okay, people can just log back in.)

If you have phpMyAdmin, select the table, select the Operations tab - then change the Storage Engine to MEMORY - and click Go. I'm not sure of the SQL command to do this, perhaps someone else could chip in with that?

Cheers,
Shaun

 

[MattW]

Here's my.cnf:


The commented bits were from previous troubleshooting...

So that's not got any adjustments in it then

How much RAM do you have on the VPS, just so we can set the innodb buffer pool?

 

[demonkey]

AFAIK (and someone with more experience may be able to confirm/deny this) - the session table data is a current list of login sessions and can be truncated without any damage to the site itself (the worst outcome being people have to log back in again because their 'session' data is lost).

With that in mind you could try changing it to a (HEAP) MEMORY table to see if that stops it crashing? (with the data in memory it won't survive a server reboot, but that's okay, people can just log back in.)

If you have phpMyAdmin, select the table, select the Operations tab - then change the Storage Engine to MEMORY - and click Go. I'm not sure of the SQL command to do this, perhaps someone else could chip in with that?

Cheers,
Shaun

Thanks Shaun I can give this a shot as well.

 

[demonkey]

So that's not got any adjustments in it then

How much RAM do you have on the VPS, just so we can set the innodb buffer pool?

Thanks for the help Matt. It should be 1 GB

 

[MattW]

So under the [mysqld] part add:

innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=768M
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=0
innodb_flush_method=O_DIRECT
thread_concurrency=16
sort_buffer_size=2M
max_connections=80
query_cache_size=64M
join_buffer_size=4M
key_buffer_size=64M
query_cache_limit=4M
skip-external-locking
query-cache-type=1
long_query_time=5
wait_timeout=300
interactive_timeout=300
tmp_table_size=32M
max_heap_table_size=32M

That should at least get you started.