Schdule _delete_old_items using wp-cron

[lucasRolff]

trafficstars

Currently aryo-activity-log, for every insert it does, it also performs a DELETE query to MySQL, something like:

DELETE FROM `wp_aryo_activity_log` WHERE `hist_time` < ?

Now, this is not an issue on not so busy sites, however - let's say you run something like WP All Import that can do thousands of changes in a few minutes, it means you issue 1000 DELETE queries to MySQL, all doing a table scan (because you also fail to put an index on hist_time, this is pretty useless in terms of MySQL performance, specially if you have a few million rows in the table like we do.

My suggestion is to schedule the _delete_old_items() function using the wp-cron instead, and pick it up every X hours - after all the history is based on days, so doing a table scan for every insert (aka every action in WP), is extremely overkill.

[tlhackque]

+1 There is no reason to prune the log more often than daily. In the worst case, a user would see one extra day of history. If this bothers you, you can qualify the SELECTs used for display with and hist_time < ? - in which case, indexing hist_time would be sensible. This would retain the precision, and only add cost to displaying the log - which is a lot less frequent than site changes. I don't think that this degree of precision is necessary, but it certainly can be attained at MUCH lower cost.

I/Os are expensive - not just in performance, but on hosted installations, there are quotas and/or charges for I/O.

[jschrab-lc]

If one DID want this sort of behavior (continuous deletes), then minimally hist_time should be an indexed field. This would slow down inserts/"writes" but I think even for a cron task, it is necessary.

I have a site with 177,480 rows in the wp_aryo_activity_log table. Full table scans of that have become a problem.

[hadronix]

i had some performance issues with a simple wp_insert_post, it tooks almost 1sec. i tested the problem through different setups and found the problem in the activity log table, it has ~550k entries. So i tried two different things:

1. remove the duplicate check: this isn't necessary if you use more indexes on the table
2. remove the _delete_old_items: this is really necessary. Except for performance, it makes more sense to run a cronjob to delete old items (e.g. daily).

If you don't like to do this, it would be nice to use filters/actions to prevent this behaviour.