Log settings - data not deleted on sites with large # of old logs

[ankush]

Log Settings

Email Queue, records from eleven months ago

Originally posted by @muchai in #16118 (comment)

---

Email queue cleanup - #16973 should fix it for old sites
Activity log cleanup - drop on v14; v13 requires a separate fix (Should we do it or just stop deleting it?)

[muchai]

In reply to: Can you check "scheduled job log" for job type "log_settings.run_log_clean_up` ?

[ankush]

@muchai as mentioned on this comment: #16118 (comment)

on v13 it's only supposed to clear 0 priority items from queue but this issue exist on v14 too. (just checked on few old sites)

Basically, the background job starts, attempts to delete huge amount of data, times out in 300 seconds and doesn't do anything. This goes on everyday.

[muchai]

Any harm in deleting non 0 priority items? Just wondering.

The problem is really the huge amount of space consumed by the email queue data.

[ankush]

Any harm in deleting non 0 priority items? Just wondering.

Not really, it's technically a "breaking change" as the behaviour was to only delete items that are priority=0, so pushing this in v13 might upset some users / break some workflows.

TBH I don't know any valid use of old email queues.

[ankush]

@muchai you're free to delete items from your site with two SQL queries ;)

DELETE FROM `tabEmail Queue` WHERE `modified`<NOW()-INTERVAL '30' DAY;
DELETE FROM `tabEmail Queue Recipient` WHERE `modified`<NOW()-INTERVAL '30' DAY;

[muchai]

Any harm in deleting non 0 priority items? Just wondering.

Not really, it's technically a "breaking change" as the behaviour was to only delete items that are priority=0, so pushing this in v13 might upset some users / break some workflows.

TBH I don't know any valid use of old email queues.

Especially old email queues, from workflows. Those are the most in our case.

[muchai]

@muchai you're free to delete items from your site with two SQL queries ;)

DELETE FROM `tabEmail Queue` WHERE `modified`<NOW()-INTERVAL '30' DAY;
DELETE FROM `tabEmail Queue Recipient` WHERE `modified`<NOW()-INTERVAL '30' DAY;

Sure, thank you. Been using this approach. We've recently moved most of our sites/customers to FC though (could move most critical to private bench, for now they're on public bench on FC). So will wait till it's fixed for now. Hopefully v14 is around the corner :)

[ankush]

I think we will just have to disable auto-deletion on very large tables and write a util to manually clean up old data

Tested this suggestion by mariadb https://mariadb.com/kb/en/big-deletes/#deleting-more-than-half-a-table

Works perfectly and in reasonable time (even over YUGE databases)

CREATE TABLE `tabActivity Log clean` LIKE `tabActivity Log`;

INSERT INTO `tabActivity Log clean`
  SELECT * FROM `tabActivity Log`
     WHERE `tabActivity Log`.`modified` > NOW()-INTERVAL '90' DAY;

RENAME TABLE `tabActivity Log` TO `tabActivity Log backup`, `tabActivity Log clean` TO `tabActivity Log`;

DROP TABLE `tabActivity Log backup`;

[ankush]

added a patch here: #17159

Whenever site migrates to v14 old stuck log deletion will be performed in one swoop during mgiration. Couldn't think of any other better way to handle this on v13 gracefully.