[FOLIO-2143] verify performance of RMB26 versions of mod-inv-s, mod-circ-s and mod-users Created: 03/Jul/19  Updated: 03/Jun/20  Resolved: 18/Jul/19

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: Task Priority: P2
Reporter: Jakub Skoczen Assignee: Hongwei Ji
Resolution: Done Votes: 0
Labels: platform-backlog
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Attachments: Text File mod-circulation.log     Text File mod-inventory-storage.log     Text File mod-users.log    
Issue links:
Blocks
blocks RMB-430 Audit table PK unique constraint viol... Closed
is blocked by CIRCSTORE-134 Upgrade to RMB 26 Closed
is blocked by CIRCSTORE-136 Update RMB to 26.2.1 Closed
is blocked by MODINVSTOR-285 upgrade to RMB 26 and resolve related... Closed
is blocked by MODUSERS-131 upgrade to RMB 26 Closed
is blocked by RMB-429 fulltext mismatch WRT accents Closed
Relates
relates to RMB-417 optimize search for array relation mo... Closed
relates to RMB-436 Log both CQL and SQL within the missi... Closed
relates to FOLIO-2176 Address index warnings from perf test Closed
relates to FOLIO-2154 add jMeter tests for searching contri... Closed
Sprint: CP: sprint 67, CP: sprint 68
Story Points: 5
Development Team: Core: Platform

 Description   

Please check if there are any performance regressions or API errors on folio-perf-platform when running with mod-circulation-storage, mod-inventory-storage and mod-users based on RMB 26. Please report here any particular regressions as compared to previous succesfull runs.

Also, please review RMB logfiles for those modules to check for any WARN messages about missing indexes, e.g missing FT, GIN and BTREE indexes for specific queries. Please report the occurrences of those warnings and corresponding CQL and SQL queries here.



 Comments   
Comment by Jakub Skoczen [ 04/Jul/19 ]

Hongwei Ji can you please start working on this when you get back next week? Thanks!

Comment by Hongwei Ji [ 09/Jul/19 ]

Below are the failed perf test cases.
1, /inventory/instances?limit=30&query=(identifiers adj "\"value\": \"123*\"") sortby title
error: CQL: only right truncation supported
It works after removing quotes around the value 123*
/inventory/instances?limit=30&query=(identifiers adj "\"value\": 123*") sortby title

2, /inventory/items?query=(barcode==itemBarcode)
The failure is due to slowness (exceeded 300s threshold sometimes).

3, /circulation/check-out-by-barcode
Due to missing permissions in the sample data. Fixed.

Comment by Hongwei Ji [ 10/Jul/19 ]
Missing index messages in the log

See attached files for the details including corresponding CQL and SQL queries

mod-inventory-storage:

WARNING: Doing LIKE search without GIN index for instance_holding_view.ho_jsonb->>'permanentLocationId'
WARNING: Doing LIKE search without GIN index for service_point_user.jsonb->>'userId'

mod-users

WARNING: Doing FT search without FT index proxyfor.jsonb->>'proxyUserId'
WARNING: Doing FT search without FT index users_groups_view.jsonb->>'barcode'
WARNING: Doing FT search without FT index users_groups_view.jsonb->>'externalSystemId'
WARNING: Doing FT search without FT index users_groups_view.jsonb->'personal'->>'email'
WARNING: Doing FT search without FT index users_groups_view.jsonb->'personal'->>'firstName'
WARNING: Doing FT search without FT index users_groups_view.jsonb->'personal'->>'lastName'
WARNING: Doing FT search without FT index users_groups_view.jsonb->>'username'
WARNING: Doing FT search without FT index users.jsonb->>'active'
WARNING: Doing FT search without FT index users.jsonb->>'barcode'
WARNING: Doing FT search without FT index users.jsonb->>'externalSystemId'
WARNING: Doing FT search without FT index users.jsonb->'personal'->>'email'
WARNING: Doing FT search without FT index users.jsonb->'personal'->>'firstName'
WARNING: Doing FT search without FT index users.jsonb->'personal'->>'lastName'
WARNING: Doing FT search without FT index users.jsonb->>'username'
WARNING: Doing LIKE search without GIN index for proxyfor.jsonb->>'userId'
WARNING: Doing LIKE search without GIN index for users.jsonb->>'active'

mod-circulation-storage

WARNING: Doing FT search without FT index loan.jsonb->>'userId'
WARNING: Doing LIKE search without GIN index for loan.jsonb->'status'->>'name'
WARNING: Doing LIKE search without GIN index for request.jsonb->>'itemId'
WARNING: Doing LIKE search without GIN index for request.jsonb->>'requestType'
WARNING: Doing LIKE search without GIN index for request.jsonb->>'status'

Comment by Hongwei Ji [ 10/Jul/19 ]

Another issue: running concurrent checkout requests see errors like below. Possibly due to race condition to get next id in the audit table.

duplicate key value violates unique constraint "audit_loan_pkey": Key (id)=(54001cd2-aa6c-4858-8bf3-be926366b673) already exists.

Comment by Hongwei Ji [ 10/Jul/19 ]

Performance is not ideal. I had to reduce the threads from 50 to 20 to avoid timeout errors. See https://jenkins-aws.indexdata.com/job/Automation/job/folio-perf-platform/178/performance/. It is build with the RMB 26 enabled mod-inv-s, mod-circ-s, and mod-users. BTW, RMB 26 mod-circ-s is not available on snapshot site yet.

Comment by Hongwei Ji [ 11/Jul/19 ]

Updated all three modules to use the latest RMB 26.2.0.

Comment by Jakub Skoczen [ 15/Jul/19 ]

Hongwei Ji How is the performance after those updates? Did it help if any of the cases? What slow queries remain?

Comment by Jakub Skoczen [ 15/Jul/19 ]

Hongwei Ji It would be great if those WARNINGS above logged also the CQL query (or at least index relation term for this particular node of the query)

Comment by Hongwei Ji [ 15/Jul/19 ]

The fix made by Adam Dickmeiss (match FT index with FT search in RMB 26.2.0) made a big difference. The new array syntax search time is down from a few minutes to under 100ms. As of now, there are three slow tests left.
1) search item by item barcode. This is due to a mismatch between index and query. Should be an easy fix. I will resolve this today.
2 and 3): checkin and checkout. We have to wait a) the latest mod-circulation-storage build (with RMB 26.2.0). Hopefully the PR this morning to bump up ui-users dependency to loan-storage interface will resolve this); b) audit_table PK violation issue get resolved (WIP by Julian Ladisch).

BTW Jakub Skoczen, RMB does log the CQL query and SQL right after the WARNINGS message. For example:

WARNING: Doing LIKE search without GIN index for instance_holding_view.ho_jsonb->>'permanentLocationId'
10 Jul 2019 00:15:50:478 INFO  CQLWrapper [3439061eqId] CQL >>> SQL: (instance_holding_view.ho_jsonb.permanentLocationId==b241764c-1466-4e1d-a028-1a3684a5da87) >>> WHERE lower(f_unaccent(instance_holding_view.ho_jsonb->>'permanentLocationId')) LIKE lower(f_unaccent('b241764c-1466-4e1d-a028-1a3684a5da87')) LIMIT 10 OFFSET 0
Comment by Julian Ladisch [ 16/Jul/19 ]

The warning is from CQL2PG, the "CQL >>> SQL" message is from RMB CQLWrapper.
Therefore moving the warning below the CQLWrapper line is not possible.

Comment by Hongwei Ji [ 18/Jul/19 ]

Latest perf result looks good. https://github.com/folio-org/mod-inventory-storage/blob/master/ramls/instance.json. The only really slow ones are searching instances by joining other tables.

Generated at Thu Feb 08 23:18:32 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.