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

[TheBigK]

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.

 

[MattW]

You need to look in your MySQL error logs to see what it's happening

 

[CTXMedia]

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]

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]

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]

You really need to look into your mysql error log to anseer that question seriouly and find a solution.

Any specific information I should be looking for?

 

[HWS]

Any specific information I should be looking for?

Just the last lines before the crash to see what happened.

The error log is usually almost empty anyway.

 

[MattW]

It would be good to see a copy of your my.cnf file to see how MySQL is currently tuned.

 

[TheBigK]

It would be good to see a copy of your my.cnf file to see how MySQL is currently tuned.

Let me post the config file details. Gotta make my way to the file

 

[rdn]

Innodb must be optimize in your my.cnf
Most important is innodb_buffer_pool_size

 

[TheBigK]

@RoldanLT & @MattW here we go -

[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]

That's not very well optimized for InnoDB. How large is your database?

 

[TheBigK]

That's not very well optimized for InnoDB. How large is your database?

~1 GiB

 

[rdn]

innodb_buffer_pool_size = 256M
Should be changed to something like:
innodb_buffer_pool_size = 2G
Since you have 8GB or RAM right?
http://dba.stackexchange.com/questi...-impact-of-increasing-innodb-buffer-pool-size

 

[MattW]

This is what I'd suggest starting off with

#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

 

[TheBigK]

Yes, I do have 8GB RAM.

 

[TheBigK]

Could you guys quickly tell me how does increasing the innodb_buffer_pool_size help?

 

[MattW]

Could you guys quickly tell me how does increasing the innodb_buffer_pool_size help?

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html

 

[Moshe1010]

innodb_buffer_pool_size = 256M
Should be changed to something like:
innodb_buffer_pool_size = 2G
Since you have 8GB or RAM right?
http://dba.stackexchange.com/questi...-impact-of-increasing-innodb-buffer-pool-size

So how does it work in terms of RAM? What if you have 5GB RAM?

 

[MattW]

So how does it work in terms of RAM? What if you have 5GB RAM?

You can set the buffer pool to be approx 80% of the RAM available on the server if you wanted to.