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

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

#1
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

Well-known member
#2
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.
 
#3
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

Well-known member
#4
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.
 
#5
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

Formerly CyclingTribe
#6
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 :D
 
#8
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 :D
Thanks Shaun I can give this a shot as well.
 

MattW

Well-known member
#10
So under the [mysqld] part add:

Code:
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.