Details
-
Bug
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Done
-
3.0.1
-
Folijet Sprint 112, Folijet Sprint 113
-
3
-
Folijet
-
R1 2021 Bug Fix
Description
Overview:
The Data Import landing page queries the back-end SRM for job's status. The SQL query to poll is not performant, and can use up to 10% of CPU for one user. The query is: https://github.com/folio-org/mod-source-record-manager/blob/222a6749009b174a8ffe3e3ef92cfd5489345cae/mod-source-record-manager-server/src/main/resources/templates/db_scripts/get_job_execution_without_parent_multiple.sql
It is slow enough that an EXPLAIN ANALYZE clause is triggered and logged:
EXPLAIN ANALYZE SELECT
CASE
WHEN job_execution_progress.jsonb IS NULL THEN job_executions.jsonb
ELSE jsonb_set(job_executions.jsonb, '{progress}', jsonb_build_object('jobExecutionId', job_execution_progress.jsonb -> 'jobExecutionId',
'total', job_execution_progress.jsonb -> 'total',
'current', (job_execution_progress.jsonb ->> 'currentlySucceeded')::int + (job_execution_progress.jsonb ->> 'currentlyFailed')::int))
END as jsonb, (SELECT COUNT(id) FROM fs09000000_mod_source_record_manager.job_executions WHERE (((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND ((job_executions.jsonb->>'status' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_phrase(f_unaccent('DISCARDED'))) IS NOT TRUE))) AND (((lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('PREPARING\_FOR\_PREVIEW'))) OR (lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('READY\_FOR\_PREVIEW')))) OR (lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('RUNNING')))) ) AS total_rows
FROM fs09000000_mod_source_record_manager.job_executions
LEFT JOIN fs09000000_mod_source_record_manager.job_execution_progress ON job_executions.id = (job_execution_progress.jsonb ->> 'jobExecutionId')::uuid
WHERE (((job_executions.jsonb->>'subordinationType' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'subordinationType')) @@ tsquery_phrase(f_unaccent('PARENT_MULTIPLE'))) IS NOT TRUE)) AND ((job_executions.jsonb->>'status' ~ '') AND ( (get_tsvector(f_unaccent(job_executions.jsonb->>'status')) @@ tsquery_phrase(f_unaccent('DISCARDED'))) IS NOT TRUE))) AND (((lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('PREPARING\_FOR\_PREVIEW'))) OR (lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('READY\_FOR\_PREVIEW')))) OR (lower(f_unaccent(job_executions.jsonb->>'uiStatus')) LIKE lower(f_unaccent('RUNNING'))))
LIMIT 50 OFFSET 0;
Potentially if many users land on this page the database can quickly be busy making this SQL call and/or brings down the system. This obviously has a performance impact on other workflows such as check-in-check-out and even during a data import sesssion as well.
Steps to Reproduce:
- Log into some FOLIO environment
- Go to /data-import. Launch 15 more windows with this page and observe that the DB's CPU utilization rockets up. In PTF environment 16 windows is enough to get it peaked out at 100%.
Expected Results:
- No performance impact or minimal CPU usage for loading the DI home page.
Actual Results:
- 16 windows on the DI page used up 100% of the DB's CPU.
TestRail: Results
Attachments
Issue Links
- blocks
-
MODSOURMAN-438 Release v3.0.4
-
- Closed
-
- defines
-
UXPROD-2614 NFR: Data Import (Batch Importer for Bib Acq) & PubSub R1 2021 Technical, NFR, & Misc bug work
-
- Closed
-
- relates to
-
-
- Closed
-