Attachment 93153 Details for Bug 23624 – Bug 23624: Count rows in report without (potentially) consuming all memory

[patch] Bug 23624: Count rows in report without (potentially) consuming all memory

Bug-23624-Count-rows-in-report-without-potentially.patch (text/plain), 2.14 KB, created by Tomás Cohen Arazi (tcohen) on 2019-09-25 13:45:00 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Tomás Cohen Arazi (tcohen)

Created: 2019-09-25 13:45:00 UTC

Size: 2.14 KB

patch

obsolete

>From c394aba4b5d1534cbfe0bad08431c8dc2cd929f3 Mon Sep 17 00:00:00 2001
>From: Paul Hoffman <paul@flo.org>
>Date: Tue, 17 Sep 2019 13:00:03 -0400
>Subject: [PATCH] Bug 23624: Count rows in report without (potentially)
> consuming all memory
>
>C4::Reports::Guided::nb_rows (called by get_prepped_report in reports/guided_reports.pl) uses DBI::fetchall_arrayref to retrieve all rows at once; counts them; and then discards the rows and returns the count.  This has the potential, if the number of rows is very large, to exhaust all available memory.
>
>(Other code in guided_reports.pl has the same potential effect, but because the solution to that is much less straightforward it will be addressed in a separate bug report.)
>
>This patch uses the second ($max_rows) parameter to DBI::fetchall_arrayref to retrieve a smaller number (1,000) of rows at a time, looping until all results have been retrieved.  This will only use as much memory as the maximum amount used by a single call to DBI::fetchall_arrayref.
>
>Test Plan:
>1) Create a report the will generate a huge number of results
>2) Run the report, watch your memory usage spike
>3) Apply this patch
>4) Restart all the things!
>5) Run the report again, note your memory usage is much lower
>
>Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
>Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
>
>Signed-off-by: Liz Rea <wizzyrea@gmail.com>
>Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
>---
> C4/Reports/Guided.pm | 9 +++++++--
> 1 file changed, 7 insertions(+), 2 deletions(-)
>
>diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
>index 08c554f55d..aae069195f 100644
>--- a/C4/Reports/Guided.pm
>+++ b/C4/Reports/Guided.pm
>@@ -420,8 +420,13 @@ sub nb_rows {
>     my $sql = shift or return;
>     my $sth = C4::Context->dbh->prepare($sql);
>     $sth->execute();
>-    my $rows = $sth->fetchall_arrayref();
>-    return scalar (@$rows);
>+    my $n = 0;
>+    # Loop through the complete results, fetching 1,000 rows at a time.  This
>+    # lowers memory requirements but increases execution time.
>+    while (my $rows = $sth->fetchall_arrayref(undef, 1000)) {
>+        $n += @$rows;
>+    }
>+    return $n;
> }
> 
> =head2 execute_query
>-- 
>2.23.0

Actions: View | Diff | Splinter Review

Attachments on bug 23624: 92841 | 92903 | 92968 | 92969 | 92970 | 92974 | 92975 | 92976 | 92995 | 93037 | 93153 | 93154 | 93155 | 93156 | 93157