Attachment 126202 Details for Bug 28833 – Bug 28833: Speed up holds queue builder via parallel processing

[patch] Bug 28833: Speed up holds queue builder via parallel processing

Bug-28833-Speed-up-holds-queue-builder-via-paralle.patch (text/plain), 6.32 KB, created by Kyle M Hall (khall) on 2021-10-13 13:53:11 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2021-10-13 13:53:11 UTC

Size: 6.32 KB

patch

obsolete

>From 7ae3ee01972a0625c11051be860fc25b93a9f5a9 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>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__
>    <vhost>__MESSAGE_BROKER_VHOST__</vhost>
>  </message_broker>
> 
>+ <holds_queue_builder>
>+     <parallel_loops_count>1</parallel_loops_count>
>+ </holds_queue_builder>
>+
> </config>
> </yazgfs>
>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 @@
>    <vhost></vhost>
>  </message_broker>
> 
>+ <holds_queue_builder>
>+     <parallel_loops_count>1</parallel_loops_count>
>+ </holds_queue_builder>
>+
> </config>
> </yazgfs>
>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)

Actions: View | Diff | Splinter Review

Attachments on bug 28833: 123656 | 123657 | 123659 | 123671 | 123679 | 123688 | 123689 | 123783 | 123784 | 123785 | 123786 | 123996 | 126200 | 126202 | 126216 | 167349 | 173045 | 173046 | 173262