Summary: | Prefetching is expensive for performance | ||
---|---|---|---|
Product: | Koha | Reporter: | Nick Clemens <nick> |
Component: | Architecture, internals, and plumbing | Assignee: | Bugs List <koha-bugs> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | major | ||
Priority: | P5 - low | CC: | dcook, jonathan.druart, joonas.kylmala, julian.maurice, kyle, m.de.rooy, martin.renvoize, tomascohen |
Version: | Main | ||
Hardware: | All | ||
OS: | All | ||
Change sponsored?: | --- | Patch complexity: | --- |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: | Version(s) released in: |
Description
Nick Clemens
2022-02-23 17:28:52 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 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) 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. 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. #!/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. 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. 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 :-) |