Bug 37657 - Improve speed of koha-preferences CLI tool (by using minimal dbh)
Summary: Improve speed of koha-preferences CLI tool (by using minimal dbh)
Status: Pushed to main
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: David Cook
QA Contact: Martin Renvoize
URL:
Keywords: Academy
Depends on:
Blocks: 37682
  Show dependency treegraph
 
Reported: 2024-08-16 03:44 UTC by David Cook
Modified: 2024-08-27 10:15 UTC (History)
1 user (show)

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


Attachments
Bug 37657: Make koha-preferences CLI tool faster (1.98 KB, patch)
2024-08-20 04:19 UTC, David Cook
Details | Diff | Splinter Review
Bug 37657: Make koha-preferences CLI tool faster (1.85 KB, patch)
2024-08-21 06:44 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description David Cook 2024-08-16 03:44:35 UTC
If you have a large number of instances on a server, "koha-list --elasticsearch" takes a very long time to run.

I think it's about 1-2 seconds per instance. That probably doesn't sound like much, but if you multiple it against a large number then it starts adding up.

If it's run as part of an "apt-get install koha-common", that means that you've got a lot of downtime for what should be a very simple task.

It's not an urgent issue, but I figure at some point I'll look at optimizing this lookup.

The fun part with apt-get install koha-common is that "koha-list --elasticsearch" gets run 2 times. 

On some test machines I'm running, the slowest part of an update is "koha-list --elasticsearch".
Comment 1 David Cook 2024-08-19 06:12:08 UTC
If I use Koha::Database->dbh instead of C4::Context->dbh, I can get "misc/admin/koha-preferences get SearchEngine" down from 0.914s to 0.328s.

That's pretty impressive.

However, if I remove the other Koha::/C4:: module loads from the top of the script, I can reduce it even further down to 0.087s.

That's the kind of speed I'm really looking for here.

--

I reckon lazy-loading the modules needed for each command isn't a bad way to go. Even if the community doesn't accept it, I'm going to do it locally anyway, as the speed improvement is just too good not to do. 

But perhaps I'll use this bug report to do that first speed improvement, and then open a separate one to do the lazy-loading to get the final speed improvement, since that one will be more controversial.
Comment 2 David Cook 2024-08-20 04:19:12 UTC
Created attachment 170490 [details] [review]
Bug 37657: Make koha-preferences CLI tool faster

By using Koha::Database->dbh() to use a minimal database handle
which doesn't preload the whole DBIx::Class schema, we're able to
run the same command 2-3 times faster.

This is beneficial when running the tool in a loop which runs the
command serially one by one.

Test plan:

1. time misc/admin/koha-preferences get SearchEngine
2. Note time is about 1 second
3. time misc/admin/koha-preferences dump
4. Note time is about 1 second
5. Create sysprefs.yml
---
marcflavour: MARC21
viewMARC: 1
6. time misc/admin/koha-preferences load -i sysprefs.yml

7. Apply patch
8. Repeat the koha-preferences commands above
9. Note that the command runs about 2-3 times faster
Comment 3 David Cook 2024-08-20 06:19:15 UTC
Note: I think the only place that "misc/admin/koha-preferences" is used directly by Koha is in debian/scripts/koha-functions.sh where it is used in the function "is_elasticsearch_enabled".

So I think this is a very low risk change as well.
Comment 4 Martin Renvoize 2024-08-21 06:44:09 UTC
Created attachment 170520 [details] [review]
Bug 37657: Make koha-preferences CLI tool faster

By using Koha::Database->dbh() to use a minimal database handle
which doesn't preload the whole DBIx::Class schema, we're able to
run the same command 2-3 times faster.

This is beneficial when running the tool in a loop which runs the
command serially one by one.

Test plan:

1. time misc/admin/koha-preferences get SearchEngine
2. Note time is about 1 second
3. time misc/admin/koha-preferences dump
4. Note time is about 1 second
5. Create sysprefs.yml

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 5 Martin Renvoize 2024-08-21 06:46:13 UTC
Trivial, clear, meaningful patch that certainly reduced memory footprint and increased speed for these scripts.

Going straight for Passing QA.

I don't entirely understand why C4::Context->dbh isn't just a passthrough for Koha::Database->dbh at this point.. I can't really see why you'd want a Schema loaded when you're clearly only asking for the database handle back... one for another bug though that.
Comment 6 Katrin Fischer 2024-08-27 10:15:21 UTC
Pushed for 24.11!

Well done everyone, thank you!