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.
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
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.
omg yes. I love this.
Created attachment 167407 [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
Created attachment 167408 [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.
Rebased
Created attachment 167412 [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 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 167413 [details] [review] 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>
Created attachment 167414 [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.
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?
(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.
> > 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!
Created attachment 172506 [details] [review] 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
Created attachment 172543 [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 Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 172544 [details] [review] 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>
Created attachment 172545 [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. Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Created attachment 172546 [details] [review] 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>
Created attachment 172547 [details] [review] Bug 34027: (QA follow-up) Tidy for qa script Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>