From 7ae3ee01972a0625c11051be860fc25b93a9f5a9 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Mon, 9 Aug 2021 13:46:00 -0400 Subject: [PATCH] Bug 28833: Speed up holds queue builder via parallel processing The holds queue builder can take a very long time to run on systems with many holds. For example, a partner with 124,784 unfilled ( not found ) holds, is taking about 64 minutes to run. If we run that same number of holds in 5 parallel chunks ( splitting the number of records as evenly as possible, but *not* taking into account the holds per bib ), it takes 21.5 minutes. If we use 10 loops, it takes less then 14 minutes. Test Plan: 0) Install the Perl library Parallel::ForkManager 1) Generate a huge number of holds ( a few thousand at the minimum ) 2) Run the holds queue builder, use the `time` utility to track how much time it took to run 3) Set HoldsQueueParallelLoopsCount to 10 4) Repeat step 2, note the improvement in speed 5) Experiment with other values for HoldsQueueParallelLoopsCount 6) prove t/db_dependent/HoldsQueue.t --- C4/HoldsQueue.pm | 66 ++++++++++++++++++------ debian/templates/koha-conf-site.xml.in | 4 ++ etc/koha-conf.xml | 4 ++ misc/cronjobs/holds/build_holds_queue.pl | 4 +- 4 files changed, 60 insertions(+), 18 deletions(-) diff --git a/C4/HoldsQueue.pm b/C4/HoldsQueue.pm index b4d2b026f2..b70e652dbc 100644 --- a/C4/HoldsQueue.pm +++ b/C4/HoldsQueue.pm @@ -30,8 +30,10 @@ use Koha::Items; use Koha::Patrons; use Koha::Libraries; -use List::Util qw( shuffle ); use List::MoreUtils qw( any ); +use List::Util qw( shuffle ); +use POSIX qw(ceil); +use Parallel::ForkManager; our (@ISA, @EXPORT_OK); BEGIN { @@ -165,16 +167,13 @@ Top level function that turns reserves into tmp_holdsqueue and hold_fill_targets =cut sub CreateQueue { - my $dbh = C4::Context->dbh; + my $loops = shift || 1; + + my $dbh = C4::Context->dbh; $dbh->do("DELETE FROM tmp_holdsqueue"); # clear the old table for new info $dbh->do("DELETE FROM hold_fill_targets"); - my $total_bibs = 0; - my $total_requests = 0; - my $total_available_items = 0; - my $num_items_mapped = 0; - my $branches_to_use; my $transport_cost_matrix; my $use_transport_cost_matrix = C4::Context->preference("UseTransportCostMatrix"); @@ -190,19 +189,49 @@ sub CreateQueue { my $bibs_with_pending_requests = GetBibsWithPendingHoldRequests(); + # Split the list of bibs into chunks to run in parallel + if ( $loops > 1 ) { + my $i = 0; + while (@$bibs_with_pending_requests) { + push( @{ $chunks[$i] }, pop(@$bibs_with_pending_requests) ); + + $i++; + $i = 0 if $i >= $loops; + } + + my $pm = Parallel::ForkManager->new($loops); + + DATA_LOOP: + foreach my $chunk (@chunks) { + my $pid = $pm->start and next DATA_LOOP; + _ProcessBiblios( $chunk, $branches_to_use, $transport_cost_matrix ); + $pm->finish; + } + + $pm->wait_all_children; + } else { + _ProcessBiblios( $bibs_with_pending_requests, $branches_to_use, $transport_cost_matrix ); + } +} + +=head2 _ProcessBiblios + +=cut + +sub _ProcessBiblios { + my $bibs_with_pending_requests = shift; + my $branches_to_use = shift; + my $transport_cost_matrix = shift; + foreach my $biblionumber (@$bibs_with_pending_requests) { - $total_bibs++; my $hold_requests = GetPendingHoldRequestsForBib($biblionumber); my $available_items = GetItemsAvailableToFillHoldRequestsForBib($biblionumber, $branches_to_use); - $total_requests += scalar(@$hold_requests); - $total_available_items += scalar(@$available_items); my $item_map = MapItemsToHoldRequests($hold_requests, $available_items, $branches_to_use, $transport_cost_matrix); $item_map or next; my $item_map_size = scalar(keys %$item_map) or next; - $num_items_mapped += $item_map_size; CreatePicklistFromItemMap($item_map); AddToHoldTargetMap($item_map); if (($item_map_size < scalar(@$hold_requests )) and @@ -227,12 +256,15 @@ that have one or more unfilled hold requests. sub GetBibsWithPendingHoldRequests { my $dbh = C4::Context->dbh; - my $bib_query = "SELECT DISTINCT biblionumber - FROM reserves - WHERE found IS NULL - AND priority > 0 - AND reservedate <= CURRENT_DATE() - AND suspend = 0 + my $bib_query = "SELECT biblionumber, + COUNT(biblionumber) AS holds_count + FROM reserves + WHERE found IS NULL + AND priority > 0 + AND reservedate <= CURRENT_DATE() + AND suspend = 0 + GROUP BY biblionumber + ORDER BY biblionumber DESC "; my $sth = $dbh->prepare($bib_query); diff --git a/debian/templates/koha-conf-site.xml.in b/debian/templates/koha-conf-site.xml.in index 6525e4ce97..e27a9806dd 100644 --- a/debian/templates/koha-conf-site.xml.in +++ b/debian/templates/koha-conf-site.xml.in @@ -449,5 +449,9 @@ __END_SRU_PUBLICSERVER__ __MESSAGE_BROKER_VHOST__ + + 1 + + diff --git a/etc/koha-conf.xml b/etc/koha-conf.xml index 118b6b8207..5e00ea6475 100644 --- a/etc/koha-conf.xml +++ b/etc/koha-conf.xml @@ -266,5 +266,9 @@ + + 1 + + diff --git a/misc/cronjobs/holds/build_holds_queue.pl b/misc/cronjobs/holds/build_holds_queue.pl index 6e1437dcd3..2b9fff4965 100755 --- a/misc/cronjobs/holds/build_holds_queue.pl +++ b/misc/cronjobs/holds/build_holds_queue.pl @@ -18,8 +18,10 @@ BEGIN { use Koha::Script -cron; use C4::HoldsQueue qw(CreateQueue); use C4::Log qw( cronlogaction ); +use C4::Context; cronlogaction(); -CreateQueue(); +my $loops = C4::Context->config('holds_queue_builder')->{parallel_loops_count}; +CreateQueue($loops); -- 2.30.1 (Apple Git-130)