XF 2.1 xf_session_activity getting occasionally locked & freezing site

[ddrager]

Hello, We just migrated our large forum over to XenForo 2.1. Loving it so far! Occasionally, about every 2 hours, it seems like the xf_session_activity gets locked for writing and everything comes to a halt for about 2 minutes. APM shows it is this query that is locked and unable to complete:

INSERT INTO xf_session_activity ( user_id, unique_key, ip, controller_name, controller_action, view_state, params, view_date, robot_key ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE ip = VALUES ( ip ), controller_name = VALUES ( controller_name ), controller_action = VALUES ( controller_action ), view_state = VALUES ( view_state ), params = VALUES ( params ), view_date = VALUES ( view_date ), robot_key = VALUES ( robot_key )

I read about converting this table to InnoDB from MEMORY which I've done, but the problem remains. Any ideas on how to reduce writing to xf_session_activity?

 

[Mike]

If the lock happens, you may need to see if you can catch it to see what query is locking it initially (likely the oldest query that shows up as running). I'd probably guess the hourly cleanup, though the comment that it's once after 2 hours rather than hourly makes me wonder. If there isn't a specific query holding it, then there might be a step lower in MySQL that may need to be investigating.

If you convert the table to InnoDB, you do potentially get some advantages, though you are now persisting the results to disk and the purpose of the table is pretty much to be written to on every page view, so it is a very write heavy table. I'm sure your MySQL settings are optimized for writes and transaction disk flushing, but that's something to check. In theory, you could take this down to only being written to for registered members, though you'll lose proper tracking of online guest totals for example. (If you use guest page caching, there is an option to turn off writes to this table when serving a cached page with that same caveat.) There isn't anything built in that would skip these writes for guests though, so some custom development would likely be involved.

 

[ddrager]

If the lock happens, you may need to see if you can catch it to see what query is locking it initially (likely the oldest query that shows up as running). I'd probably guess the hourly cleanup, though the comment that it's once after 2 hours rather than hourly makes me wonder. If there isn't a specific query holding it, then there might be a step lower in MySQL that may need to be investigating.

If you convert the table to InnoDB, you do potentially get some advantages, though you are now persisting the results to disk and the purpose of the table is pretty much to be written to on every page view, so it is a very write heavy table. I'm sure your MySQL settings are optimized for writes and transaction disk flushing, but that's something to check. In theory, you could take this down to only being written to for registered members, though you'll lose proper tracking of online guest totals for example. (If you use guest page caching, there is an option to turn off writes to this table when serving a cached page with that same caveat.) There isn't anything built in that would skip these writes for guests though, so some custom development would likely be involved.

Thanks for the reply Mike! We already are losing some of the total users count since we full page cache with nginx in front of PHP, and eventually I would like to push this out to Cloudflare as well as it can really save on page download times for international logged out visitors.

I know our use case is edgy but it would be great if there was a separate lightweight php hit that can be used for visitor tracking - perhaps stored in memcache/redis instead of db - then written to db via cron occasionally, but this not really essential at the end of the day.

We have tweaked innodb, specifically innodb_flush_log_at_trx_commit, so that not every write is written to disk, but instead written in a batch every second.

Usually you are right, looking at the

show processlist

will show an offending transaction, but in this case I can't really see anything standing out. Would it be helpful to send over those results in a ticket?

 

[Mike]

I know our use case is edgy but it would be great if there was a separate lightweight php hit that can be used for visitor tracking - perhaps stored in memcache/redis instead of db - then written to db via cron occasionally, but this not really essential at the end of the day.

So FWIW, that's sort of the exact point of this table (well, one of the points). Aside from giving people online user numbers, it's also used to periodically update the user last activity times stored in the xf_user table, rather than pushing a write to that on every request. Hence I wouldn't recommend dropping the table's usage entirely, but skipping it for guests shouldn't cause any issues.

I take the point about doing it elsewhere as an option though.

We have tweaked innodb, specifically innodb_flush_log_at_trx_commit, so that not every write is written to disk, but instead written in a batch every second.

Yeah that's what I was referring to, though given the forum we're talking about, I assumed that was already done.

Would it be helpful to send over those results in a ticket?

Yeah, if you could send that via a ticket or a conversation, that'd help. That might give me some more ideas.