[Bug 34027] New: Add functionality to cleanup_database.pl to purge action logs in batches
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Bug ID: 34027 Summary: Add functionality to cleanup_database.pl to purge action logs in batches Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Command-line Utilities Assignee: koha-bugs@lists.koha-community.org Reporter: aleisha@catalyst.net.nz QA Contact: testopia@bugs.koha-community.org CC: robin@catalyst.net.nz 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Small patch -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #1 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 152394 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152394&action=edit 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 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #2 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 152395 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152395&action=edit Bug 34027: DO NOT PUSH!!!! Test data for KTD This is to add some test action logs to your database. DO NOT PUSH. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Liz Rea <wizzyrea@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wizzyrea@gmail.com --- Comment #3 from Liz Rea <wizzyrea@gmail.com> --- omg yes. I love this. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152394|0 |1 is obsolete| | --- Comment #4 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 167407 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=167407&action=edit 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152395|0 |1 is obsolete| | --- Comment #5 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 167408 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=167408&action=edit Bug 34027: DO NOT PUSH!!!! Test data for KTD This is to add some test action logs to your database. DO NOT PUSH. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com --- Comment #6 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Rebased -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #167407|0 |1 is obsolete| | --- Comment #7 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 167412 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=167412&action=edit 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 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #8 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 167413 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=167413&action=edit Bug 34027: (QA follow-up) Slight logic improvement This patch updates the loop to optimize the limit handling Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #167408|0 |1 is obsolete| | --- Comment #9 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 167414 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=167414&action=edit Bug 34027: DO NOT PUSH!!!! Test data for KTD This is to add some test action logs to your database. DO NOT PUSH. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off Assignee|koha-bugs@lists.koha-commun |aleisha@catalyst.net.nz |ity.org | --- Comment #10 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Signing off as it all appears to work as expected. I wonder slightly about the choice of sleep number.. do we really need to sleep for 3 seconds? -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #11 from Aleisha Amohia <aleisha@catalyst.net.nz> --- (In reply to Martin Renvoize from comment #10)
Signing off as it all appears to work as expected.
I wonder slightly about the choice of sleep number.. do we really need to sleep for 3 seconds?
I suppose not! It was a randomly chosen number as a precaution. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com QA Contact|testopia@bugs.koha-communit |kyle@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #12 from Kyle M Hall (khall) <kyle@bywatersolutions.com> ---
I wonder slightly about the choice of sleep number.. do we really need to sleep for 3 seconds?
I suppose not! It was a randomly chosen number as a precaution.
If the number is arbitrary, can you please make it configurable via another command line option? Thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #13 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 172506 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172506&action=edit Bug 34027: (follow-up) Make sleep seconds a command-line param Use --logs-sleep SECONDS to specify how many seconds Koha should wait between batches when deleting action logs. Defaults to 3 Sponsored-by: Auckland University of Technology Sponsored-by: Catalyst IT -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #167412|0 |1 is obsolete| | Attachment #167413|0 |1 is obsolete| | Attachment #167414|0 |1 is obsolete| | Attachment #172506|0 |1 is obsolete| | --- Comment #14 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 172543 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172543&action=edit 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 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #15 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 172544 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172544&action=edit Bug 34027: (QA follow-up) Slight logic improvement This patch updates the loop to optimize the limit handling Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #16 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 172545 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172545&action=edit Bug 34027: DO NOT PUSH!!!! Test data for KTD This is to add some test action logs to your database. DO NOT PUSH. Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #17 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 172546 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172546&action=edit Bug 34027: (follow-up) Make sleep seconds a command-line param Use --logs-sleep SECONDS to specify how many seconds Koha should wait between batches when deleting action logs. Defaults to 3 Sponsored-by: Auckland University of Technology Sponsored-by: Catalyst IT Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 --- Comment #18 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 172547 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=172547&action=edit Bug 34027: (QA follow-up) Tidy for qa script Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172547|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Patch doesn't apply -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Patch doesn't apply |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172543|0 |1 is obsolete| | --- Comment #19 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 186357 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=186357&action=edit 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 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172544|0 |1 is obsolete| | --- Comment #20 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 186358 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=186358&action=edit Bug 34027: (QA follow-up) Slight logic improvement This patch updates the loop to optimize the limit handling Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172545|0 |1 is obsolete| | --- Comment #21 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 186359 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=186359&action=edit Bug 34027: DO NOT PUSH!!!! Test data for KTD This is to add some test action logs to your database. DO NOT PUSH. Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #172546|0 |1 is obsolete| | --- Comment #22 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 186360 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=186360&action=edit Bug 34027: (follow-up) Make sleep seconds a command-line param Use --logs-sleep SECONDS to specify how many seconds Koha should wait between batches when deleting action logs. Defaults to 3 Sponsored-by: Auckland University of Technology Sponsored-by: Catalyst IT Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34027 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Patch doesn't apply --- Comment #23 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Patch doesn't apply, can you please re-base? Apply? [(y)es, (n)o, (i)nteractive] y Applying: Bug 34027: Add functionality to cleanup_database.pl to purge action logs in batches Applying: Bug 34027: (QA follow-up) Slight logic improvement Applying: Bug 34027: DO NOT PUSH!!!! Test data for KTD Applying: Bug 34027: (follow-up) Make sleep seconds a command-line param error: sha1 information is lacking or useless (misc/cronjobs/cleanup_database.pl). error: could not build fake ancestor Patch failed at 0001 Bug 34027: (follow-up) Make sleep seconds a command-line param hint: Use 'git am --show-current-patch=diff' to see the failed patch When you have resolved this problem run "git bz apply --continue". If you would prefer to skip this patch, instead run "git bz apply --skip". To restore the original branch and stop patching run "git bz apply --abort". Patch left in /tmp/Bug-34027-follow-up-Make-sleep-seconds-a-command-l-5tf7i29v.patch -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org