Bug 31629 - Cleanup database needs a flag for removing elastic index entries from background_jobs table
Summary: Cleanup database needs a flag for removing elastic index entries from backgro...
Status: RESOLVED DUPLICATE of bug 31969
Alias: None
Product: Koha
Classification: Unclassified
Component: System Administration (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-09-26 18:51 UTC by Liz Rea
Modified: 2022-10-28 14:30 UTC (History)
3 users (show)

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


Attachments
Bug 31629: Add elasticqueue options to cleanup_database (5.07 KB, patch)
2022-10-13 18:42 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31629: Add elasticqueue options to cleanup_database (5.12 KB, patch)
2022-10-13 22:23 UTC, David Nind
Details | Diff | Splinter Review
Bug 31154: Avoid unecessary loop (1.53 KB, patch)
2022-10-24 12:07 UTC, Frédéric Demians
Details | Diff | Splinter Review
Bug 31629: Add elasticqueue options to cleanup_database (5.26 KB, patch)
2022-10-24 13:29 UTC, Frédéric Demians
Details | Diff | Splinter Review
Bug 31629: Add jobsqueue options to cleanup_database (4.29 KB, patch)
2022-10-24 13:29 UTC, Frédéric Demians
Details | Diff | Splinter Review
Bug 31629: Add elasticqueue options to cleanup_database (5.26 KB, patch)
2022-10-24 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31629: Add jobsqueue options to cleanup_database (4.34 KB, patch)
2022-10-24 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31629: (follow-up) Fix spelling of background (1.53 KB, patch)
2022-10-24 13:55 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Liz Rea 2022-09-26 18:51:55 UTC
Bug 27344 introduced using the background worker to handle elastic indexing, which means the background_jobs table can end up with a lot of entries for elastic search updates. Currently these are not cleaned up, we either need a job in cleanup_database cron to get rid of these, or they should be removed after processing.

If we have a cleanup database job, the default interval for deletions should be 1 day.

Cheers,
Liz
Comment 1 Nick Clemens 2022-10-13 18:42:48 UTC
Created attachment 141837 [details] [review]
Bug 31629: Add elasticqueue options to cleanup_database

This patch adds an elasticqueue option to cleanup_database.pl to allow
for purging completed reindexing jobs

To test:
 1 - Enable elastic search in Koha
 2 - perl misc/maintenance/touch_all_items.pl
 3 - Check db and note there are a bunch of elastic reindex jobs
 4 - Update to make them old
     UPDATE background_jobs SET ended_on = '2022-10-01 00:00:00' WHERE type = 'update_elastic_index'
 5 - perl misc/cronjobs/cleanup_database.pl
 6 - Note elasticqueue entry shows in help
 7 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v
 8 - Note that elasticqueue would have been cleared
 9 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v --confirm
10 - Note that number of entries deleted is reported
11 - Confirm in staff interface that jobs are gone:
     http://localhost:8081/cgi-bin/koha/admin/background_jobs.pl
     (Uncheck 'Current jobs only')
Comment 2 David Nind 2022-10-13 22:23:22 UTC
Created attachment 141850 [details] [review]
Bug 31629: Add elasticqueue options to cleanup_database

This patch adds an elasticqueue option to cleanup_database.pl to allow
for purging completed reindexing jobs

To test:
 1 - Enable elastic search in Koha
 2 - perl misc/maintenance/touch_all_items.pl
 3 - Check db and note there are a bunch of elastic reindex jobs
 4 - Update to make them old
     UPDATE background_jobs SET ended_on = '2022-10-01 00:00:00' WHERE type = 'update_elastic_index'
 5 - perl misc/cronjobs/cleanup_database.pl
 6 - Note elasticqueue entry shows in help
 7 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v
 8 - Note that elasticqueue would have been cleared
 9 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v --confirm
10 - Note that number of entries deleted is reported
11 - Confirm in staff interface that jobs are gone:
     http://localhost:8081/cgi-bin/koha/admin/background_jobs.pl
     (Uncheck 'Current jobs only')

Signed-off-by: David Nind <david@davidnind.com>
Comment 3 Katrin Fischer 2022-10-17 10:36:02 UTC
Hm... a week ago - I think it's cookie time! ;)
Comment 4 Frédéric Demians 2022-10-24 12:07:33 UTC
Created attachment 142492 [details] [review]
Bug 31154: Avoid unecessary loop

We don't need to loop over the subfield and enter this block if there is
no record.

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 5 Frédéric Demians 2022-10-24 12:25:48 UTC
Above, it was a false attachement, made by mistake. Sorry.
Comment 6 Frédéric Demians 2022-10-24 12:26:15 UTC
The default value for deletion is set to 30 in DEFAULT_ELASTICQ_PURGEDAYS. Not 1. Is it intended?

What do you think about another option for purging all other finished jobs? Something like --jobqueue.
Comment 7 Nick Clemens 2022-10-24 12:28:09 UTC
(In reply to Frédéric Demians from comment #6)
> The default value for deletion is set to 30 in DEFAULT_ELASTICQ_PURGEDAYS.
> Not 1. Is it intended?

Matching the default for zebraqueue

> What do you think about another option for purging all other finished jobs?
> Something like --jobqueue.

I think it is a great idea, please file a follow-up bug
Comment 8 Björn Nylén 2022-10-24 13:15:51 UTC
I made a more generalized addition to cleanup_database.pl before I saw this. (moved to 22.05 last week so wanted this quickly).
Can add it as a suggestion if people are interested?
Comment 9 Frédéric Demians 2022-10-24 13:29:24 UTC
Created attachment 142501 [details] [review]
Bug 31629: Add elasticqueue options to cleanup_database

This patch adds an elasticqueue option to cleanup_database.pl to allow
for purging completed reindexing jobs

To test:
 1 - Enable elastic search in Koha
 2 - perl misc/maintenance/touch_all_items.pl
 3 - Check db and note there are a bunch of elastic reindex jobs
 4 - Update to make them old
     UPDATE background_jobs SET ended_on = '2022-10-01 00:00:00' WHERE type = 'update_elastic_index'
 5 - perl misc/cronjobs/cleanup_database.pl
 6 - Note elasticqueue entry shows in help
 7 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v
 8 - Note that elasticqueue would have been cleared
 9 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v --confirm
10 - Note that number of entries deleted is reported
11 - Confirm in staff interface that jobs are gone:
     http://localhost:8081/cgi-bin/koha/admin/background_jobs.pl
     (Uncheck 'Current jobs only')

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Frédéric Demians <f.demians@tamil.fr>
Comment 10 Frédéric Demians 2022-10-24 13:29:32 UTC
Created attachment 142502 [details] [review]
Bug 31629: Add jobsqueue options to cleanup_database

This patch adds an jobsqueue option to cleanup_database.pl to allow
for purging background jobs queue

There is also a fix for --elasticqueue default value

To test:

1. On a DB with a populated background_jobs table, identify in this
   table
   entries older than X days.
2. perl misc/cronjobs/cleanup_database.pl
   Note elasticqueue entry shows in help
3. perl misc/cronjobs/cleanup_database.pl --elasticqueue -v
   Note this message:
   Elastic queue (background jobs) purge triggered for 30 days
4. perl misc/cronjobs/cleanup_database.pl --jobsqueue -v
   Note this message:
   Background jobs queue purge triggered for 30 days.
5. perl misc/cronjobs/cleanup_database.pl --jobsqueue 1 -v
   Background jobs queue purge triggered for 1 day.
6. perl misc/cronjobs/cleanup_database.pl --jobsqueue 1 -v --confirm
   Note that number of entries deleted is reporte
7. In Koha > Admin > Background jobs, see the purge queue.
Comment 11 Nick Clemens 2022-10-24 13:55:08 UTC
Created attachment 142508 [details] [review]
Bug 31629: Add elasticqueue options to cleanup_database

This patch adds an elasticqueue option to cleanup_database.pl to allow
for purging completed reindexing jobs

To test:
 1 - Enable elastic search in Koha
 2 - perl misc/maintenance/touch_all_items.pl
 3 - Check db and note there are a bunch of elastic reindex jobs
 4 - Update to make them old
     UPDATE background_jobs SET ended_on = '2022-10-01 00:00:00' WHERE type = 'update_elastic_index'
 5 - perl misc/cronjobs/cleanup_database.pl
 6 - Note elasticqueue entry shows in help
 7 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v
 8 - Note that elasticqueue would have been cleared
 9 - perl misc/cronjobs/cleanup_database.pl --elasticqueue 1 -v --confirm
10 - Note that number of entries deleted is reported
11 - Confirm in staff interface that jobs are gone:
     http://localhost:8081/cgi-bin/koha/admin/background_jobs.pl
     (Uncheck 'Current jobs only')

Signed-off-by: David Nind <david@davidnind.com>
Signed-off-by: Frédéric Demians <f.demians@tamil.fr>
Comment 12 Nick Clemens 2022-10-24 13:55:13 UTC
Created attachment 142509 [details] [review]
Bug 31629: Add jobsqueue options to cleanup_database

This patch adds an jobsqueue option to cleanup_database.pl to allow
for purging background jobs queue

There is also a fix for --elasticqueue default value

To test:

1. On a DB with a populated background_jobs table, identify in this
   table
   entries older than X days.
2. perl misc/cronjobs/cleanup_database.pl
   Note elasticqueue entry shows in help
3. perl misc/cronjobs/cleanup_database.pl --elasticqueue -v
   Note this message:
   Elastic queue (background jobs) purge triggered for 30 days
4. perl misc/cronjobs/cleanup_database.pl --jobsqueue -v
   Note this message:
   Background jobs queue purge triggered for 30 days.
5. perl misc/cronjobs/cleanup_database.pl --jobsqueue 1 -v
   Background jobs queue purge triggered for 1 day.
6. perl misc/cronjobs/cleanup_database.pl --jobsqueue 1 -v --confirm
   Note that number of entries deleted is reporte
7. In Koha > Admin > Background jobs, see the purge queue.

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 13 Nick Clemens 2022-10-24 13:55:18 UTC
Created attachment 142510 [details] [review]
Bug 31629: (follow-up) Fix spelling of background

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 14 Katrin Fischer 2022-10-24 17:20:31 UTC
(In reply to Björn Nylén from comment #8)
> I made a more generalized addition to cleanup_database.pl before I saw this.
> (moved to 22.05 last week so wanted this quickly).
> Can add it as a suggestion if people are interested?

Hi Björn, do you mean for tasks/jobs in general? A separate bug as mentioned in comment#7 would be great.
Comment 15 Björn Nylén 2022-10-24 18:22:45 UTC
(In reply to Katrin Fischer from comment #14)
> (In reply to Björn Nylén from comment #8)
> > I made a more generalized addition to cleanup_database.pl before I saw this.
> > (moved to 22.05 last week so wanted this quickly).
> > Can add it as a suggestion if people are interested?
> 
> Hi Björn, do you mean for tasks/jobs in general? A separate bug as mentioned
> in comment#7 would be great.

Yeah, I made it so you could specify what jobtypes to purge and when. Obv mostly needed for elastic jobs but eventually you'll want to get rid of the other types.

Will post a bug in the morning.
Comment 16 Frédéric Demians 2022-10-26 06:13:56 UTC
In my opinion bug 31969 should be privileged to this one.
Comment 17 Nick Clemens 2022-10-28 14:30:43 UTC

*** This bug has been marked as a duplicate of bug 31969 ***