[Bug 42878] New: Batch the available-item check in ES marc_records_to_documents
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Bug ID: 42878 Summary: Batch the available-item check in ES marc_records_to_documents Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Searching - Elasticsearch Assignee: koha-bugs@lists.koha-community.org Reporter: martin.renvoize@openfifth.co.uk QA Contact: testopia@bugs.koha-community.org Target Milestone: --- When building Elasticsearch documents, marc_records_to_documents runs one query per record to set the 'available' flag: SELECT COUNT(*) FROM items WHERE biblionumber=? AND onloan IS NULL AND itemlost=0 (Koha/SearchEngine/Elasticsearch.pm, the "at least one available item" block). marc_records_to_documents is normally given a whole batch of records, so this is N queries per batch. Availability for every biblionumber in the batch could be fetched with a single grouped query instead. This matters most on a networked database, where it is one extra round-trip per record during reindex. Test plan: 1. prove t/db_dependent/Koha/SearchEngine/Elasticsearch.t 2. Confirm the 'available' flag is unchanged for records with/without available items. 3. Count DB statements during a batch reindex before/after. -- 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=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42881 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42881 [Bug 42881] Improve Elasticsearch Indexing performance -- 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=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |martin.renvoize@openfifth.c |ity.org |o.uk Status|NEW |ASSIGNED -- 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=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff Patch complexity|--- |Small patch QA Contact|testopia@bugs.koha-communit |nick@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 --- Comment #1 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 200641 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=200641&action=edit Bug 42878: Batch the available-item check in ES marc_records_to_documents Previously, the marc_records_to_documents method performed a per-record COUNT query to check if a biblionumber had any available items. For a batch of N records, this resulted in N separate database queries: SELECT COUNT(*) FROM items WHERE biblionumber=? AND onloan IS NULL AND itemlost=0 This commit optimizes the approach by: 1. Before the per-record loop (for BIBLIOS index only), collecting all biblionumbers from the batch of records. 2. Running a single grouped query to fetch biblionumbers that have at least one available item: SELECT DISTINCT biblionumber FROM items WHERE biblionumber IN (...) AND onloan IS NULL AND itemlost=0 3. Building a hash %has_available mapping biblionumber => 1 for fast lookups. 4. In the per-record loop, replacing the per-record COUNT query with a simple hash lookup. The semantics are preserved identically: - Records with no biblionumber field (missing or empty) do not get an 'available' key, exactly as before. - Records with a biblionumber but no available items get available => \0. - Records with at least one available item get available => \1. For large batches, this can reduce database queries from N to 1. Test plan: Run the existing Elasticsearch tests to verify all available-item tests pass. The new batch test verifies that multiple records in one call are correctly evaluated for availability. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 --- Comment #2 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 208522 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208522&action=edit Benchmark scripts for full-reindex / batch indexing (bug 42881 family) Clarifying the goal of this work (bug 42881 family): the target is infrastructure-facing - the time and CPU a FULL REINDEX (misc/search_tools/rebuild_elasticsearch.pl) and LARGE BATCH re-indexes (batch record modification, imports; these enqueue index jobs of up to chunk_size = 5000 records) consume. It is not about how quickly a cataloguer sees one edit: in KTD we measured the edit-to-searchable time through the stock es_indexer_daemon and it is dominated by job dispatch (roughly one job per second), so these patches do not change it. I am attaching a small benchmark package (bug42881-es-indexing-bench.tar.gz, see README.txt inside) so testers can reproduce the before/after numbers below and, importantly, verify that the generated Elasticsearch documents are byte-for-byte identical (SHA-256 over every document) with and without the patch. Setup, once, in a KTD with Elasticsearch (stock sample data works; 10,000 generated records give steadier numbers - gen_records.pl + bulkmarcimport.pl, see README): mkdir -p _bench && cp <extracted files> _bench/ Test plan for this bug 1. prove t/db_dependent/Koha/SearchEngine/Elasticsearch.t (includes the new "batch query available items" subtest) 2. Before/after, with this patch applied on a branch: _bench/ab_bench.sh main HEAD Expect: "output identical to baseline" = yes; DB statements per record fall (2.0 -> 1.0 at batch 500, 2.1 -> 1.2 at batch 10). 3. Semantics: pick a biblio whose only items are lost (itemlost != 0) or checked out, and one with a normally available item; after reindexing, the ES document has available=false / available=true respectively: curl -s es:9200/koha_<instance>_biblios/_doc/<biblionumber> | jq '._source.available' (inside the KTD container; in KTD <instance> is kohadev). Records without a biblionumber field get no "available" key, as before. 4. Optional, full reindex wall-clock: bash _bench/bench_rebuild.sh before / after. Our results (10,436 generated records, KTD + ES 9, one core, best of 3 interleaved rounds, spread 0-1%), decode + mapping, ms per record: batch 1: 24.8 -> 24.9 (1.00x, nothing to batch) batch 10: 6.6 -> 5.5 (1.22x) batch 500: 4.6 -> 3.3 (1.42x) full rebuild_elasticsearch.pl: 103.8 s -> 89.2 s (1.16x) Note KTD's database is local; the saving is one round trip per record, so on a networked database server the effect should be larger. If you can test against a remote DB, please report. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 --- Comment #3 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 208527 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=208527&action=edit Bug 42878: Batch the available-item check in ES marc_records_to_documents (rebased) Rebased on top of 42877, which this bug now depends on. The module change is unchanged; only the new unit test was re-placed and the Test::More plan count updated so the four bugs apply in sequence without the test-file conflict they previously had. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #200641|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=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |42877 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42877 [Bug 42877] Memoize _get_marc_mapping_rules to speed up record indexing -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42878 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42879 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42879 [Bug 42879] Avoid per-record plugin lookup in ES marc_records_to_documents -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org