xf session activity is full - a clear answer

[Liam C.]

Hi there

I've seen this reported as an issue numerous amount of times, and it's a bit difficult to get a clear answer on the solution

I've seen people suggest the following

1. Create a PHP script to clear the xf_session_activity table whenever it is full
   • Would this be an issue? I mean it is clearly a temporary fix, but from what I've read, the data in there is purely temporary and wouldn't be an issue if lost?
2. Delete useless robots that may hog performance
   • I've started work on this but heard that bots such as Bytespider do not listen to robots.txt, I heard we can consider blocking IPs but assuming we do that in ranges, couldn't that affect genuine users?
   • For any modification I make, I'd like to ensure the traffic of the site, as well as users, are not affected in any shape or form
3. Limit heavy performance routes to a need-to-access basis (dont need access = dont give access)
   • Don't think I have any additional questions for this, I've started to restrict unregistered users from accessing subforums where we use the Multi Prefix addon as I believe it uses additional queries for each prefix change you do (seen a lot of queries of different prefixes being applied and it does not make sense for our site)

I'd really love some advice, we've been experiencing the issue for a little while now and we're looking to try and use our Forums site more and more often however this is a major issue that we're looking to solve.

Thank you

 

[FUX Gaming]

This happens for me from time to time, too.
At some point (and I don't know why), my crons stop working or hang and I have to disable an re-enable all of them one by one. After that, the xf_session_activity table gets cleared and everything works again. Is that maybe the same case for you?

 

[Liam C.]

Is there something related to cronjobs about it? Ours do get cleared after some time, so if there is, it's working as intended. None has been disabled, I'd ask your Admins if I were you.

But it still causes an issue for the bots that come to our Forums site. I think we have about 400 bots during one of our downtimes

 

[akok]

You can add steps.
1. Increase database data limits.
2. Convert the table xf_session_activity to innodb. This is a pretty bad solution, but it works.

 

[estranged]

1) Convert the table to innodb. It is not a bad solution. May be it was 10 years ago.
2) Use a caching solution to cache sessions.

I prefer the second option.

 

[Jja]

2. Convert the table xf_session_activity to innodb. This is a pretty bad solution, but it works.

Explain? Don't know form which exact version, but Xenforo already using InnoDB for this table when you install it.
So it is false statement!

 

[Liam C.]

Increase database data limits

I've heard people suggest this but it doesn't work for long as it means it'll just take longer to fill up? So surely that is a temporary solution?

Convert the table to innodb. It is not a bad solution. May be it was 10 years ago

I'm not that familiar with innodb, any clue why it may be considered a bad idea by some?

Use a caching solution to cache sessions

Is this something commonly used? I would've expected Xenforo to cache sessions if it was known to be a fix to this issue

 

[akok]

Explain? Don't know form which exact version, but Xenforo already using InnoDB for this table when you install it.
So it is false statement!

I originally installed XenForo 1 and have only applied updates since then, and I may have missed the point when the xf_session_activity table became standard in InnoDB.
This table is active: it is filled with short-lived data and cleared using XenForo’s hourly cleanup.
The following is strictly my opinion. First, it’s worth configuring database limits, as such errors usually occur on large, active forums or due to improper server configuration.
At a minimum, you should start by checking the results from mysqltuner.

 

[ES Dev Team]

I've posted the answer many times now in different threads.

You problem: you have a buttload of web crawlers and they are maxing out a table whose max size is too conservatively set.
The immediate answer: expand size of mysql sessions table by editing config file and restarting mysql.
The long term answer: use cloudflare or fail2ban to defend your website against bots ( fail2ban is working way better than us )

Default is 16mb which is not enough.

Snippet to put in your msyqld.conf:

#xenforo uses in-memory tables; default is 16M and these tend to fill up quick, so we need to boost their size. - DS
tmp_table_size = 64M
max_heap_table_size = 64M

 

[Liam C.]

I've posted the answer many times now in different threads.

You problem: you have a buttload of web crawlers and they are maxing out a table whose max size is too conservatively set.
The immediate answer: expand size of mysql sessions table by editing config file and restarting mysql.
The long term answer: use cloudflare or fail2ban to defend your website against bots ( fail2ban is working way better than us )

Default is 16mb which is not enough.

Snippet to put in your msyqld.conf:

#xenforo uses in-memory tables; default is 16M and these tend to fill up quick, so we need to boost their size. - DS
tmp_table_size = 64M
max_heap_table_size = 64M

Thank you. I apologise if this question has came up many times, and I've looked through many of those threads to try and find a clear and definite answer. However, I found a lot of threads with contradicting each other or providing some methods that can have some poor implications.

I was concerned that though this may be the same issue though? By updating the value, doesn't this mean that there is still a reasonable chance it will get filled up? Just may require more bots

 

[ES Dev Team]

I've seen a lot of bad advice on here too. That's why i post, to try to help.

I run this limit on our website, https://endless-sphere.com
And i never ran into the table full issue even with 12000 "guests" aka, mostly bots, before we implemented our fail2ban defense system.
You could jack this up to 256gb if you need to.