mod-search queries performance optimisation
Description
Environment
Ramsons
Potential Workaround
CSP Request Details
CSP Rejection Details
CSP Approved
blocks
continues
has to be done before
is cloned by
Checklist
hideActivity
Christine Schultz-Richert January 10, 2025 at 4:51 PM
Hey - can you review Valery’s comment?
Valery_Pilko January 6, 2025 at 5:06 PM
Verified in the scope of on Ramsons Bugfest environments from Manual QA side.
Hey - please review and close this ticket if it’s not needed anymore. Thank you!
Roman_Fedynyshyn January 3, 2025 at 3:12 PM
PTF tested:
Data imports (from 1K to 500K) non-ecs env.
Data imports 25K -50K ECS env.
Tested Bulk edits + DI (on the background)
In all cases no slow queries observed.
However recently we’ve found that there’s another one happening during (and after) marc-migration process.
This query occupying DB for several hours (1,5-2) after Marc-migration saving is finished. Probably causing functional issues of data saving by occupying ±50% of DB CPU
Natalia Zaitseva December 30, 2024 at 2:52 PM
currently ticket is in PTF - testing
Magda Zacharska December 5, 2024 at 5:54 PM
The issue with mod-search index updates affects following areas:
Saving instances and holdings UUIDs in Inventory
Retrieving holdings and items records from Central tenant in Bulk edit and Data export apps
Retrieving list of instance languages in FQM (for Query builder used in Bulk edit and Lists)
Please note that for those issues there are no separate tickets created (with the exception of that is possibly related) but the above flows will need to be retested once the fix is in place.
Details
Details
Assignee
Kalibek TurgumbayevReporter
Roman_FedynyshynLabels
Priority
P1
Intro:
New features for mod-search was introduced in Ramsons. And a lot of functionality of document merging were moved to DB side. New tables also added to mod-search schema (contributor, contributor_instance, instance, holding, item, etc. )
Issue description:
As mod-search now got multiple processes running on DB side it may affect severely another DB related processes (for example data import).
It was observed by PTF that with running mod-search, DI 25 K MARC BIB created affected. (17 minutes with running mod-search vs 11 min without it).
It’s clearly visible that queries like this start to run as soon as DI started, occupying almost 4 VCPU’s of DB.
Moreover this query continue running even after DI already completed (as mod-search working asynchronously).
Please optimise this typical queries to not load DB as it is now.