Bug 30169 - Prefetching is expensive for performance
Summary: Prefetching is expensive for performance
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: master
Hardware: All All
: P5 - low major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-02-23 17:28 UTC by Nick Clemens
Modified: 2022-12-12 21:23 UTC (History)
8 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2022-02-23 17:28:52 UTC
When working on bug 30168 I checked out 500 items to a patron to test performance

I found that after adding the prefetch, things were worse.

Testing on command line seems to suggest prefetch is worse than repeated fetches

time perl -e 'use Koha::Checkouts; for( my $i=0;$i<500;$i++){ Koha::Checkouts->find({itemnumber=>541}) } print "2\n";'
2
real	0m1.850s
user	0m1.701s
sys	0m0.096s

root@kohadevbox:koha(master)$ time perl -e 'use Koha::Checkouts; for( my $i=0;$i<500;$i++){ Koha::Checkouts->find({itemnumber=>541},{prefetch => ['itemnumber','borrowernumber']}) } print "2\n";'
2

real	0m10.770s
user	0m10.452s
sys	0m0.104s

root@kohadevbox:koha(master)$ time perl -e 'use Koha::Checkouts; for( my $i=0;$i<500;$i++){ my $co =Koha::Checkouts->find({itemnumber=>541},{prefetch => ['itemnumber','borrowernumber']}); my $item = $co->item; my $pat = $co->patron } print "2\n";'
2

real	0m13.153s
user	0m12.600s
sys	0m0.140s

root@kohadevbox:koha(master)$ time perl -e 'use Koha::Checkouts; for( my $i=0;$i<500;$i++){ my $co =Koha::Checkouts->find({itemnumber=>541}); my $item = $co->item; my $pat = $co->patron } print "2\n";'
2

real	0m3.873s
user	0m3.493s
sys	0m0.139s
Comment 1 Nick Clemens 2022-02-24 14:55:49 UTC
Tested with Tomas this morning, a few notes:

using the correct relationships for prefetch: item,patron
that reduces DB calls, otherwise we are still doing a hit every time we use related object
that still doesn't increase performance

Using DBIC directly to avoid Koha objects does not change things, we still see much great performance without prefetch

Using SQL directly is faster, as expected, fetching 500 rows it was about ~1.5 seconds vs ~3 seconds with Koha:Objects, no prefetch
Comment 2 Julian Maurice 2022-02-24 15:22:58 UTC
Can confirm, and the issue is not with Koha::Objects because using DBIC directly shows the same problem:

# 163..212 is just the range of itemnumbers I had in my issues table

time perl -e 'use Koha::Database; my $schema = Koha::Database->schema; for (163..212) { my $issue = $schema->resultset("Issue")->find({itemnumber => $_}, {prefetch => ["item", "patron"]}); my $info = $issue->item->barcode . $issue->patron->firstname }'

perl -e   1,55s user 0,06s system 97% cpu 1,646 total


time perl -e 'use Koha::Database; my $schema = Koha::Database->schema; for (163..212) { my $issue = $schema->resultset("Issue")->find({itemnumber => $_}, {prefetch => []}); my $info = $issue->item->barcode . $issue->patron->firstname }' 

perl -e   1,04s user 0,06s system 96% cpu 1,138 total

I ran both commands multiple times and had the same times

It's weird, as I have seen some improvements in other areas by using prefetch (see bug 30004)
Comment 3 Martin Renvoize 2022-02-24 15:45:56 UTC
I just wrote a benchmark for this:

#!/usr/bin/env perl

use strict;
use warnings;

use Benchmark qw/cmpthese timethese/;
use Koha::Libraries;
use Koha::Database;

my $schema = Koha::Database->schema;

cmpthese(
    -10,
    {
        kohaPrefetch => sub {
            my $checkouts = Koha::Checkouts->search({},{ prefetch => [ 'item', 'patron' ]});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        kohaJoin => sub {
            my $checkouts = Koha::Checkouts->search({});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicPrefetch => sub {
            my $checkouts = $schema->resultset('Issue')->search({},{ prefetch => [ 'item', 'patron' ]});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicJoin => sub {
            my $checkouts = $schema->resultset('Issue')->search({});
            while ( my $checkout = $checkouts->next ) {
                my $item_id = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        }
    }
);

It suggests prefetch is quicker in my findings.. but it's testing at a slightly different level.. I'm testing one prefetch over a large resultset.. you were testing lots of prefetches over a single result... I'll write a benchmark for that too.. 

I think it's object instantiation that's at fault here..  you are effectively instantiating the object for each iteration whereas I'm instantiating one object for all results.

In short.. it depends on how we code as to whether adding prefetch is a good idea or not.
Comment 4 Martin Renvoize 2022-02-24 16:04:32 UTC
               Rate     kohaJoin     dbicJoin kohaPrefetch dbicPrefetch
kohaJoin     1.69/s           --          -4%         -79%         -81%
dbicJoin     1.76/s           4%           --         -78%         -80%
kohaPrefetch 8.09/s         378%         359%           --         -10%
dbicPrefetch 9.03/s         434%         413%          12%           --

Result from above.. shows dbicPrefetch is fastest (I didn't measure straight SQL with and without a join).. and kohaPrefetch isn't as much slower than dbic than I thought it might be.
Comment 5 Martin Renvoize 2022-02-24 16:17:12 UTC
#!/usr/bin/env perl

use strict;
use warnings;

use Benchmark qw/cmpthese timethese/;
use Koha::Libraries;
use Koha::Database;

my $schema = Koha::Database->schema;

cmpthese(
    -100,
    {
        kohaPrefetch => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout = Koha::Checkouts->find( { itemnumber => 10 },
                    { prefetch => [ 'item', 'patron' ] } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        kohaJoin => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout  = Koha::Checkouts->find( { itemnumber => 10 } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicPrefetch => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {
                my $checkout =
                  $schema->resultset('Issue')->find( { itemnumber => 10 },
                    { prefetch => [ 'item', 'patron' ] } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        },
        dbicJoin => sub {
            for ( my $i = 0 ; $i < 250 ; $i++ ) {

                my $checkout =
                  $schema->resultset('Issue')->find( { itemnumber => 10 } );
                my $item_id   = $checkout->item->itemnumber;
                my $patron_id = $checkout->patron->borrowernumber;
            }
        }
    }
);

Alternatively, for instantiation speed.. the prefetch creates a bigger, heavier, object and so clearly will be slower than without.
Comment 6 Martin Renvoize 2022-02-24 16:18:31 UTC
s/iter dbicPrefetch kohaPrefetch     kohaJoin     dbicJoin
dbicPrefetch   4.51           --          -2%         -68%         -68%
kohaPrefetch   4.42           2%           --         -67%         -68%
kohaJoin       1.45         211%         205%           --          -2%
dbicJoin       1.43         217%         210%           2%           --

Results are interesting there.. I would have expected kohaPrefetch to be slower than dbicPrefetch....

But still.. clearly instantiating multiple small objects is faster than instantiating a large object lots of times.
Comment 7 Nick Clemens 2022-03-09 11:45:41 UTC
I think we can close this one - essentialy:
Prefetch for groups of objects that you are going to iterate over
Do not prefetch for individual objects, especially inside a loop :-)