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.

 

[rdn]

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

More info: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

 

[Moshe1010]

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

But then what about other resources? Wouldn't that cause problems in terms of RAM to, let's say, Elastic search, xCache, and others?

 

[TheBigK]

Yeah - if 80% of RAM is being offered to database (whenever it needs it); my other resources would definitely take a hit (I guess).

 

[MattW]

But then what about other resources? Wouldn't that cause problems in terms of RAM to, let's say, Elastic search, xCache, and others?

Which is why you need to figure out what is the most suitable setting to use in your own personal situation.

From the MySQL docs link I posted earlier

The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds.

 

[estranged]

sessions table does not use innodb. It uses myisam so it has nothing to do with innodb optimization. I suggest using xcache (or another cache) and set cache sessions to true in config.php

$config['cache']['cacheSessions'] = true;

Sessions table tends to crash a lot with heavy traffic and this fixes that problem.

 

[Mouth]

This is what I'd suggest starting off with

Plus also ...

innodb-flush-method            = O_DIRECT
innodb_file_io_threads          = 4

https://tools.percona.com/wizard is a great resource for starting with a fairly optimised config.

 

[CTXMedia]

sessions table does not use innodb. It uses myisam so it has nothing to do with innodb optimization. I suggest using xcache (or another cache) and set cache sessions to true in config.php

$config['cache']['cacheSessions'] = true;

Sessions table tends to crash a lot with heavy traffic and this fixes that problem.

Is the sessions table used for all visitors (guests and bots included), or just the forum members who are logged in?

 

[estranged]

It is used for all visitors.

 

[CTXMedia]

It is used for all visitors.

Interesting - thanks; I'd always assume a "session" was a logged-in user session.