Server issue xf_session_activity join performance

[Xon]

xf_session_activity is a Memory table. This means it has the same behaviour as a MyISAM table and uses full-table locks (ref).

Mixing MyISAM and InnoDB has some performance implications; forcing the query to wait for any full-table locks which occur against xf_session_activity. As xf_session_activity updates existing rows throughput the table (rather than append as a log), this table locks quite often.

This means, viewing any thread or conversation can potentially be required to wait for the xf_session_activity to clear it's full-table locks. And those same queries can block writes to xf_session_activity until the read completes.

 

[Mike]

Roughly, I would say that if you have configured InnoDB well, you could consider changing that table to InnoDB (and if you're not using a cache for sessions, the session tables as well). Plenty of hosts/servers are basically running the default InnoDB config which, aside from not giving InnoDB any significant memory, will have InnoDB flushing to disk and that is a potentially slow operation. (I have seen cases where a single update was slower than the entire rest of the page rendering.

So I while I may revisit it for some point in the future, this may be something for you to consider changing if it fits your needs.