Bug 34027 - Add functionality to cleanup_database.pl to purge action logs in batches
Summary: Add functionality to cleanup_database.pl to purge action logs in batches
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Command-line Utilities (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-06-15 22:22 UTC by Aleisha Amohia
Modified: 2023-06-16 21:16 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 34027: Add functionality to cleanup_database.pl to purge action logs in batches (4.79 KB, patch)
2023-06-16 02:12 UTC, Aleisha Amohia
Details | Diff | Splinter Review
Bug 34027: DO NOT PUSH!!!! Test data for KTD (1.04 KB, patch)
2023-06-16 02:12 UTC, Aleisha Amohia
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Aleisha Amohia 2023-06-15 22:22:52 UTC
Some of our libraries did not have the --logs argument in place for cleanup_database.pl and their action logs tables have grown very large (millions of rows) and taking up lots of space on the disk.

We need to be able to clear these logs incrementally (in batches), as the sheer size of the delete query has overloaded the server.
Comment 1 Aleisha Amohia 2023-06-16 02:12:42 UTC
Created attachment 152394 [details] [review]
Bug 34027: Add functionality to cleanup_database.pl to purge action logs in batches

This enhancement gives libraries the option to purge action logs 'slowly', in batches. This is useful for when there are many, many rows to purge (i.e. millions).

To test, set up some sample logs (you'll need to change the timestamp in the database so they are picked up by the cronjob), run the cleanup_database.pl script with the --logs parameter and confirm it behaves as expected.

For example:

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Test cases:

1. logs argument, no logs-batch argument. Cleanup should run as normal, in one query.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --verbose

2. logs argument, logs-batch argument with no ROWS specified. logs-batch should default to 1000, deletions done in batches of 1000 rows at a time.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch --verbose

3. logs argument, logs-batch argument with ROWS specified. Deletions should be done in batches of the specified ROWS

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Sponsored-by: Auckland University of Technology
Comment 2 Aleisha Amohia 2023-06-16 02:12:45 UTC
Created attachment 152395 [details] [review]
Bug 34027: DO NOT PUSH!!!! Test data for KTD

This is to add some test action logs to your database. DO NOT PUSH.
Comment 3 Liz Rea 2023-06-16 21:16:35 UTC
omg yes. I love this.