xf_session_activity can end up with too many records (can't be pruned)

digitalpoint

digitalpoint

Well-known member
Affected version
2.3
So this was a byproduct of the cron job record going missing (not sure why it went missing, but it did).

By the time I noticed cron jobs weren't running, the xf_session_activity had grown to 2.1M records since it wasn't being pruned. All cron jobs seemed to recover when the record was restored except for the pruning of that table. The table had grown so large that trying to delete 2M+ records in a single query would fail. Ended up doing it manually by deleting 100,000 records at a time and got the table back to normal (and handleable by cron again).

SQL: 
mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (19.87 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (20.54 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (21.35 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (21.70 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (19.83 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (20.90 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (20.62 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (19.82 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (20.10 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (21.14 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (21.55 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (23.62 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (21.79 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (20.37 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 100000 rows affected (22.79 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 19058 rows affected (6.09 sec)

mysql> DELETE FROM xf_session_activity WHERE view_date < 1761577861 LIMIT 100000;
Query OK, 0 rows affected (0.00 sec)

It probably would be ideal to have any of the prune functions loop with a limit of xx records (better for table locking slowing down real users), but xf_session_activity in particular is a little more problematic because of the storage engine used and also because if you have a server that can sustain the load, a malicious actor could fairly easily inject millions of records before it's hourly prune job.

Even as a quick hack (without doing looping code), a LIMIT of 100000 would be useful and at least allow it to catch up as hours pass if it was overloaded (and not have a failed DELETE).
 
Various clustering software has functional restrictions on the transaction size and worse as the transaction size grows so does the risk of deadlocks.

My Alert Improvements spent a while rewriting alert pruning to reduce the transaction size as otherwise I was seeing deadlocks and also elevated query times.
 
It's much faster to just truncate/empty the table and a forum can afford to lose that data.
This is a bit of an edge case issue, but yeah, there is not much overhead for if you have a freak accident like this.

I don't run anything on cron so that there's less to potentially break
 
Back
Top Bottom