XF 2.1 `xf_session_activity` table is point of failure on large sites

[Jawsh]

xf_session_activity is a MyISAM MEMORY table which, unlike InnoDB, locks during a select. Post entities are usually pulled full and, when the postbit 'user is online' badge is enabled, this means every xf_post select is also an xf_session_activity select.

       LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_5`
              ON ( `xf_session_activity_Activity_5`.`user_id` =
                   `xf_user_User_1`.`user_id`
                   AND `xf_session_activity_Activity_5`.`unique_key` = Concat(
                       `xf_user_User_1`.`user_id`, '') )

                function()
                {
                    if (\XF::options()->showMessageOnlineStatus)
                    {
                        return 'User.Activity';
                    }

                    return null;
                },

This, in my database, is now causing problems. The select lock in certain instances can cause the entire site to freeze. It's a very busy table as every user action triggers an update and every full post query triggers a select. If xf_session_activity locks, the entire site is unavailable.

 

[Jeremy P]

xf_session_activity is a MyISAM table

The xf_session_activity table should be a MEMORY table unless you've manually changed the engine. It looks as though it's still a table-level lock but it shouldn't be I/O bound at least.

 

[Jawsh]

The xf_session_activity table should be a MEMORY table unless you've manually changed the engine. It looks as though it's still a table-level lock but it shouldn't be I/O bound at least.

You're correct. It is MEMORY, not MyISAM, but as you said MySQL does table-level locking with MEMORY.

 

[Jeremy P]

I know @Xon had recommended converting the table to InnoDB for high-traffic websites in one of his XF1 add-ons targeted at larger forums. It looks like Sufficient Velocity has online statuses in postbits enabled, I wonder if it required any special tuning or configuration to make it viable?

 

[Jawsh]

I'd convert it to InnoDB but I get the feeling it's MEMORY for a reason, particularly for indexing IP columns or something similar.

 

[Jawsh]

As an update: turning this one feature off has improved performance by a shocking amount. The entire site is snappier. I'm running SHOW PROCESSLIST every so often and despite how big the site is, rarely do I catch any query hanging. If I did this before it would always show something.

 

[rdn]

Already implemented this?
https://xenforo.com/xf2-docs/manual/cache/#session-caching

 

[Jeremy P]

Session caching eliminates reads/writes from xf_session, but apparently not xf_session_activity.

 

[Jawsh]

Already implemented this?
https://xenforo.com/xf2-docs/manual/cache/#session-caching

$config['cache']['enabled'] = true;
$config['cache']['provider'] = 'Redis';
$config['cache']['config'] = $redisCfg;
$config['cache']['sessions'] = true;

I've gone through all the traditional routes of optimization already. I've got all caching turned on.

 

[djbaxter]

As an update: turning this one feature off has improved performance by a shocking amount. The entire site is snappier. I'm running SHOW PROCESSLIST every so often and despite how big the site is, rarely do I catch any query hanging. If I did this before it would always show something.

Turning which feature off? Do you mean changing xf_session_activity to InnoDB instead of MEMORY?

 

[Jawsh]

Turning which feature off? Do you mean changing xf_session_activity to InnoDB instead of MEMORY?

Disabling showMessageOnlineStatus and by extension not selecting from xf_session_activity when doing entity work involving posts.

 

[Xon]

The worst part about xf_session_activity is probably the single most "busy" table, as it is hit with writes on nearly every page view and reads on practically every page view where a user's profile care may be displayed.

I know @Xon had recommended converting the table to InnoDB for high-traffic websites in one of his XF1 add-ons targeted at larger forums. It looks like Sufficient Velocity has online statuses in postbits enabled, I wonder if it required any special tuning or configuration to make it viable?

I've converted all the tables from MEMORY to InnoDB on a couple of very busy sites, it helps massively.

It is also basically required if you want to use MariaDB galera clustering.

 

[Jawsh]

MariaDB galera clustering

Do you just convert everything in the db to InnoDB for this? I figured they were using MyISAM and MEMORY for specific reasons. I'd love to just convert everything over and set up Galera.

 

[Jeremy P]

I could be wrong, but I imagine they're using MEMORY because it has better performance characteristics under lighter workloads where locking isn't as much of an issue, and the data is ephemeral anyways.

 

[HWS]

Do you just convert everything in the db to InnoDB for this? I figured they were using MyISAM and MEMORY for specific reasons. I'd love to just convert everything over and set up Galera.

You need to change all MEMORY tables to INNODB and also give them a unique id column to use Galera. This is what we have done. MEMORY tables don't replicate in HA environment.

 

[Xon]

I could be wrong, but I imagine they're using MEMORY because it has better performance characteristics under lighter workloads where locking isn't as much of an issue, and the data is ephemeral anyways.

It probably made a lot more sense back when XF was first designed as disk IO was precious, and it was a simple way for a pure in memory store. Now days you can just throw NVMe SSDs at the problem and forget about it.

Joining between InnoDB and Memory/MyIASM tables is painful once you get contention.

Do you just convert everything in the db to InnoDB for this? I figured they were using MyISAM and MEMORY for specific reasons. I'd love to just convert everything over and set up Galera.

Yes, I converted everything to InnoDB. Even the xf_search_index table, as full-text search is actually supported with very latest version of MySQL/MariaDB even if I don't use it.

You need to change all MEMORY tables to INNODB and also give them a unique id column to use Galera. This is what we have done. MEMORY tables don't replicate in HA environment.

With XF2, every table has a primary key (multi-column primary keys is OK). Just a matter of hitting all the tables which use non-Innodb tables.

This query finds all non-innodb tables and generates SQL statements to convert them;

select concat('alter table `',table_schema,'`.`',`table_name`,'` engine=InnoDB;') 
from information_schema.`tables` 
where `engine` <> 'innodb' AND `table_schema` = '<mydatabase>';

Note; you need to set a <mydatabase>, as there are a pile of MySQL internal tables which you can't change the table type for without hosing your install!

 

[Jawsh]

Can confirm that everything neatly changes over to InnoDB. If you're using ES instead of MySQL fulltext searching, I'd advise doing a truncate on xf_search_index before converting it so that legacy data is pruned. Otherwise (in my instance) I was spending hours converting a table that isn't even used.

 

[Masetrix]

can just throw NVMe SSDs at the problem and forget about it.

Can confirm that everything neatly changes over to InnoDB.

Confirm +1
much better performance after that changes...