[Bug 31856] New: Improve performance of serials subscriptions search
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Bug ID: 31856 Summary: Improve performance of serials subscriptions search Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Serials Assignee: koha-bugs@lists.koha-community.org Reporter: glasklas@gmail.com QA Contact: testopia@bugs.koha-community.org Currently the serials subscriptions search is very inefficient for searches producing a large number of results. The current patch introduces caching for authorized value lookups, the retrieval of search results has also been optimized to drastically reduces the number of performed queries and creation of Koha objects resulting in a speedup of about 5.5x. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #1 from David Gustafsson <glasklas@gmail.com> --- Created attachment 142058 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=142058&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and take not of the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure tests in t/db_dependent/AuthorisedValues.t all pass. Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #2 from David Gustafsson <glasklas@gmail.com> --- Also removed a test in t/db_dependent/AuthorisedValues.t that I found both not very useful and incorrect as categories where sorting is performed on the database level is compared with the same list of categories sorted in Perl (which does not sort on the same conditions as collation etc is not taken into account). -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #3 from David Gustafsson <glasklas@gmail.com> --- Created attachment 142156 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=142156&action=edit Bug 31856: Don't fetch additional field valudes if there are no fields -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #4 from David Gustafsson <glasklas@gmail.com> --- Created attachment 142157 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=142157&action=edit Bug 31856: Add test for get_description_by_category_and_authorised_value -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |glasklas@gmail.com |ity.org | CC| |david@davidnind.com --- Comment #5 from David Nind <david@davidnind.com> --- Updated the assignee.. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #6 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Is there a trick or script we could use to add a lot of subscriptions? Have been pondering how to best test this. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #142058|0 |1 is obsolete| | Attachment #142156|0 |1 is obsolete| | Attachment #142157|0 |1 is obsolete| | --- Comment #7 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 150651 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150651&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and take not of the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure tests in t/db_dependent/AuthorisedValues.t all pass. Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #8 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 150652 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150652&action=edit Bug 31856: Don't fetch additional field valudes if there are no fields -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #9 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 150653 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=150653&action=edit Bug 31856: Add test for get_description_by_category_and_authorised_value -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart+koha@gmail. | |com --- Comment #10 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Patches rebased against master. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #11 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Is the change in SearchSubscriptions really makes things faster? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #12 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- (In reply to Jonathan Druart from comment #11)
Is the change in SearchSubscriptions really makes things faster?
Right, there is no FK. Maybe we are just missing an index on a DB column then? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #13 from David Gustafsson <glasklas@gmail.com> --- Created attachment 151077 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=151077&action=edit Bug 31856: Prefetch additional fields for increased performance -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #14 from David Gustafsson <glasklas@gmail.com> --- Created attachment 151078 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=151078&action=edit Bug 31856: Refactor GetAuthorisedValueDesc -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #15 from David Gustafsson <glasklas@gmail.com> --- (In reply to Jonathan Druart from comment #11)
Is the change in SearchSubscriptions really makes things faster?
Yes, fetching the subsctiption object is quite expensive, so the change (in SearchSubscriptions alone) makes searches about twice as fast. Also decided to prefetch additional values, so now is done one query instead of on per subscripton witch speeds things up an additional 25% or so. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #16 from David Gustafsson <glasklas@gmail.com> --- (In reply to Jonathan Druart from comment #12)
(In reply to Jonathan Druart from comment #11)
Is the change in SearchSubscriptions really makes things faster?
Right, there is no FK. Maybe we are just missing an index on a DB column then?
I tried adding an index for that column and did not make much of a difference, perhaps a few percent at most if even that. 99% of the cost is DBIx, the database query itself is a neglectable part if using and index or not. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #17 from David Gustafsson <glasklas@gmail.com> --- Not saying there should not be an index, perhaps there are use cases where it could matter, my guess is that in the absolute majority of cases the number of rows in that table is small enough that a sequential scan will be about as fast as using an index. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |33746 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33746 [Bug 33746] [omnibus] speed improvement -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #18 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- What's the purpose of this line? 632 $subscriptions_by_id{$field_value->record_id}->{additional_fields}->{$field_name} = $field_value->value; -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #19 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- ok got it. I will need another review another day, with a fresh brain. We should make the code a bit more readable, I find it a bit obscure at the moment. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Mason James <mtj@kohaaloha.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mtj@kohaaloha.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |33746 Depends on|33746 | Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33746 [Bug 33746] [omnibus] speed improvement -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #20 from David Gustafsson <glasklas@gmail.com> --- (In reply to Jonathan Druart from comment #18)
What's the purpose of this line?
632 $subscriptions_by_id{$field_value->record_id}->{additional_fields}-
{$field_name} = $field_value->value;
Additional fields are first prefetched for all subscription. This line assigns each additional field value to the subscription it belongs to, using a hash where subscriptions are indexed by id. It is not obvious to me how this code could be made more readable? Previously additional fields where fetched repeatedly for each subscription, by fetching them all at once there is only one database query instead of one per subscription, which is much more efficient. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #21 from David Nind <david@davidnind.com> --- Is it possible to provide a sample file with a large number of serial records and items, rather than manually creating them? This would make it much easier to test using koha-testing-docker (KTD). -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150651|0 |1 is obsolete| | --- Comment #22 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177383 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177383&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and take not of the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure tests in t/db_dependent/AuthorisedValues.t all pass. Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150652|0 |1 is obsolete| | --- Comment #23 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177384 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177384&action=edit Bug 31856: Don't fetch additional field valudes if there are no fields -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #150653|0 |1 is obsolete| | --- Comment #24 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177385 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177385&action=edit Bug 31856: Add test for get_description_by_category_and_authorised_value -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #151077|0 |1 is obsolete| | --- Comment #25 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177386 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177386&action=edit Bug 31856: Prefetch additional fields for increased performance -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #151078|0 |1 is obsolete| | --- Comment #26 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177387 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177387&action=edit Bug 31856: Refactor GetAuthorisedValueDesc -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #27 from David Gustafsson <glasklas@gmail.com> --- Rebased against master, will add bug 36350 as a dependency since some of the local caching can then be removed and refactor the patch. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |BLOCKED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |36350 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36350 [Bug 36350] Add subclass of Koha::Objects that provides caching for find and search -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |clemens.tubach@kit.edu, | |michaela.sieber@kit.edu -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177383|0 |1 is obsolete| | --- Comment #28 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177482 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177482&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and take not of the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure tests in t/db_dependent/AuthorisedValues.t all pass. Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177384|0 |1 is obsolete| | --- Comment #29 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177483 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177483&action=edit Bug 31856: Don't fetch additional field valudes if there are no fields -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177385|0 |1 is obsolete| | --- Comment #30 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177484 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177484&action=edit Bug 31856: Add test for get_description_by_category_and_authorised_value -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177386|0 |1 is obsolete| | --- Comment #31 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177485 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177485&action=edit Bug 31856: Prefetch additional fields for increased performance -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177387|0 |1 is obsolete| | --- Comment #32 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177486 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177486&action=edit Bug 31856: Refactor GetAuthorisedValueDesc -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #33 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177487 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177487&action=edit Bug 31856: Refactor after adding bug 36350 as a dependency -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #34 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177509 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177509&action=edit Bug 31856: Fix additional fields key value and use field id instead of name -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #35 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177510 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177510&action=edit Before Flamegraph of serials search before changes. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #36 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177511 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177511&action=edit Memory cache Flamegraph using in place memory cache. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #37 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177512 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177512&action=edit Koha::Object find/search cache Flamegraph using Koha::Object find/search cache. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #38 from David Gustafsson <glasklas@gmail.com> --- Created attachment 177514 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=177514&action=edit Before with record_id index Benchmark before changes with index added for record_id in additional_field_values table. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #39 from David Gustafsson <glasklas@gmail.com> --- I saw that the record_id field in the additional_field_values table really should have an index, after adding one the execution time was cut to 1/3 of before. With caching and other optimizations in this patch the index makes little difference, but there is perhaps other places in Koha where there could be a slight boost in performence. Leaving the patch as it is, but will consider creating a new bug with this change. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177482|0 |1 is obsolete| | Attachment #177483|0 |1 is obsolete| | Attachment #177484|0 |1 is obsolete| | Attachment #177485|0 |1 is obsolete| | Attachment #177486|0 |1 is obsolete| | Attachment #177487|0 |1 is obsolete| | Attachment #177509|0 |1 is obsolete| | --- Comment #40 from David Gustafsson <glasklas@gmail.com> --- Created attachment 192967 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192967&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and check the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure the following tests pass: t/db_dependent/AuthorisedValues.t t/db_dependent/Koha/Item.t t/db_dependent/Koha/Filter/ExpandCodedFields.t t/db_dependent/Koha/OAI/Server/Repository.t Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192967|0 |1 is obsolete| | --- Comment #41 from David Gustafsson <glasklas@gmail.com> --- Created attachment 192968 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192968&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and check the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure the following tests pass: t/db_dependent/AuthorisedValues.t t/db_dependent/Koha/Item.t t/db_dependent/Koha/Filter/ExpandCodedFields.t t/db_dependent/Koha/OAI/Server/Repository.t Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #42 from David Gustafsson <glasklas@gmail.com> --- I have now reworked this patch to make use of the objects caching in bug 36350 in more instances for the caching of authorised values, resulting in less code duplication and custom caching/cache expiration logic. The old caching used persistent caching through memcached while the objects cache is memory based. I reviewed the code and did not find any cases where persistent caching is really warranted. Persistent caches makes sense where for quite expensive operations, but as far as I can gather all the cached calls are database queries that should only be a problem if called repeatedly each request, which transient in memory caching is sufficient to handle. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192968|0 |1 is obsolete| | --- Comment #43 from David Gustafsson <glasklas@gmail.com> --- Created attachment 192977 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192977&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and check the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure the following tests pass: t/db_dependent/AuthorisedValues.t t/db_dependent/Koha/Item.t t/db_dependent/Koha/Filter/ExpandCodedFields.t t/db_dependent/Koha/OAI/Server/Repository.t Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Improve performance of |Refactor authorised values |serials subscriptions |description caching using |search |object caching -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177510|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177511|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177512|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #177514|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192977|0 |1 is obsolete| | --- Comment #44 from David Gustafsson <glasklas@gmail.com> --- Created attachment 192978 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192978&action=edit Bug 31856: Refactor authorised values description caching using object caching To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and check the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure the following tests pass: t/db_dependent/AuthorisedValues.t t/db_dependent/Koha/Item.t t/db_dependent/Koha/Filter/ExpandCodedFields.t t/db_dependent/Koha/OAI/Server/Repository.t Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #45 from David Gustafsson <glasklas@gmail.com> --- I renamed the issue as it now has little to do with serials performance, and more a use case for the object cache and how it can be used to get rid of custom caching logic. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #46 from David Gustafsson <glasklas@gmail.com> --- I also removed the optimized query for fetching additional fields for serials, and will create a separate bug for this, even though as previously wrote I think an index on record_id is sufficient to address the performance issue. Will try to do some local benchmark to see if there is any point at all. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Refactor authorised values |Improve performance of |description caching using |serials subscriptions |object caching |search -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 David Gustafsson <glasklas@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192978|0 |1 is obsolete| | --- Comment #47 from David Gustafsson <glasklas@gmail.com> --- Created attachment 193028 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193028&action=edit Bug 31856: Improve performance of serials subscriptions search To test: 1) Perform an advanced search that produces a reasonably large resultset for serial subscriptions (preferably at least a couple of hundred) and check the execution time. 2) Apply the patch. 3) Perform the search again, the execution should now be about one fifth of the previous search. 4) Ensure the following tests pass: t/db_dependent/AuthorisedValues.t t/db_dependent/Koha/Item.t t/db_dependent/Koha/Filter/ExpandCodedFields.t t/db_dependent/Koha/OAI/Server/Repository.t Sponsored-by: Gothenburg University Library -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #48 from David Gustafsson <glasklas@gmail.com> --- I think it was a mistake to rename the bug and break out the optimized fetching of additional fields. I wrote an earlier comment that it was not needed if an index was added for record_id in the additional_field_values table. But trying that now actually resulted in a significant slowdown, so while a little bit confused about how I came to that earlier conclusion it actually seems like this change is needed, as get_additional_field_values_for_template seems like the main culprit with regards to the slow serial search performance. It performs one query per subscription, and also bypasses the cache as searches performed on an existing resultset are excluded from cache. I will attach screenshots with profiles before and after the change. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #49 from David Gustafsson <glasklas@gmail.com> --- Created attachment 193030 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193030&action=edit Without the patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #50 from David Gustafsson <glasklas@gmail.com> --- Created attachment 193031 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193031&action=edit With object caching -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #51 from David Gustafsson <glasklas@gmail.com> --- Created attachment 193032 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193032&action=edit With object caching and faster query -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=31856 --- Comment #52 from David Gustafsson <glasklas@gmail.com> --- Some comments about the flame graphs. An empty search was performed on 164 subscriptions, (in production we have many more, but search results are limited to 1000). In the "With object caching" flame graph (a version of with only the object caching), Koha::Template::Plugin::AuthorisedValues->get_by_code went from 1150ms to 30ms, and Koha::Template::Plugin::AuthorisedValues->GetDescriptionByKohaField from 466ms to 269. I think those to cover most of the speedups. In the "With object caching" in addition to the above, Koha::Object::Mixin::AdditionalField->get_additional_field_values_for_template, which took 3900 ms, was replaced with one query to fetch the fields of all subscription results at once, which took about 25ms. Without the patch think there is more or less a linear relationship between execution time and the number of serials, so while around 100 or so is still pretty slow, it gets almost unusable with say a thousand (even though it's not a practical amount of search results, it's still frustrating for librarians to have to wait for a response if accidentally did an empty search, or a search that produced many hits. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org