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

XF 1.2 Database Crashes Every Time Total Online Users Cross 6200+

TheBigK

Well-known member
#1
I've just observed that in the past ~2 hours, every time the total count of online users (guests) crosses about 6200; MySQL crashes. I then login to cPanel and restart mySQL to bring the site back up.

My host says they'll optimise MySQL. But could there be any other reason?

PS: My limited knowledge of things tells me it must be the xf_session table that is crashing.
 

CyclingTribe

Well-known member
#3
If some of those 6200 include multiple unwanted connections from bots and scrapers you should be able to mitigate it a little by blocking the ones you don't want (or won't benefit from) in robots.txt
 

TheBigK

Well-known member
#4
Not sure, but the traffic is continuously growing and I've restarted mysql for 5th time now. Why is it that the moment 'user' count grows beyond 6000; the server becomes unstable. I'm already using the latest 'cache' plugin from @xfrocks. I thought it'd be all fine and handle all the traffic without any problem. :(
 

HWS

Well-known member
#5
You really need to look into your mysql error log to answer that question seriouly and find a solution.

If you post the last 50 line of the log, I am sure someone can help you out.
 

TheBigK

Well-known member
#11
@RoldanLT & @MattW here we go -

Code:
[mysqld]
max_connections = 150
skip-external-locking
key_buffer = 256M
innodb_buffer_pool_size = 256M
open_files_limit = 8190
table_cache = 3184
myisam_sort_buffer_size = 64M
query_cache_size = 32M
max_heap_table_size = 64M
tmp_table_size = 64M
thread_cache_size = 16
wait_timeout = 45
interactive_timeout = 600
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log



[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 64M
read_buffer = 2M
write_buffer = 2M
 

MattW

Well-known member
#15
This is what I'd suggest starting off with

Code:
#InnoDB
innodb_file_per_table=1
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
Because you are changing the log file size, you will need to stop MySQL, delete ib_logfile0 and ib_logfile1 in /var/lib/mysql and then restart MySQL