Prune old hits & sessions (includes OPTIMIZE)
This task will avoid that your sessions and hitlog tables grow indefinitely. It should typically run once a day (preferably at a low traffic time for your site).
The number of days of hit log and sessions to keep can be configured in Analytics > Settings. The default is 15 days.
The sessions expiration time can be configured in Users > Settings > Profiles. The default is 7 days. This value defines when a session expires and becomes available to be pruned.
Sessions will be pruned when they are BOTH expired (default: 7 days) and older than the hit log pruning delay (default: 15 days). The bigger you make these values, the bigger your sessions table will grow and the more time it will take to prune it daily. Don’t let this table grow too large.
Note: this task can take a very long time to execute, depending on the size of your session and hitlog tables.
If you have not run that task for a long time, your tables may be so large that MySQL will choke itself when trying to prune them. In such a case, it may be a good idea to TRUNCATE the tables using phpMyAdmin and then set this task to run on a daily basis.
If your site is receiving a lot of daily traffic and MySQL chokes very night when trying to prune, your only options are to upgrade your web server or to log less data. The configuration settings are in Analytics > Settings.
In order to let this job run properly, the parameter Auto pruning must be set to "With a scheduled job". For more details, please read this page: Hit Logging settings.
This code performs 4 main actions:
Pruning the Hitlog
Prune T_hitlog based on the number of days to keep.
Pruning Expired Sessions
Prune timed-out T_sessions older than number of days to keep. Note: old sessions that have not timed out yet will be kept.
The decision about which sessions are timed out or not is based on the value of this field:
sess_lastseen_ts on T_sessions; it stores the last timestamp for any activity on this session. All the sessions where
lastseen exceeds the sessions expiration time and also reach the hitlog keep number of days described above, will be pruned.
Note: if a custom session expiration delay is set for a user, it cannot be longer than the pruning expiration time, because at that point the session will be deleted from the DB.
Before pruning sessions, a BeforeSessionsDelete event is triggered to all the plugins in the site, and the behaviour of the job could be affected if at least one of those plugins takes some action over the sessions data. This could be your first target when detecting an issue with this job.
The section of the code that manages the process of sessions data deletion performs three tasks:
- Calculates the cutoff date. All sessions inactive beyond that date will be deleted.
- Triggers the event "BeforeSessionsDelete" to all plugins.
- Delete all the sessions beyond the date obtained in 1.
Prune T_basedomains that are not used by any hit/session
Optimizing the database
Created by • Last edit by on Jan 10, 2015