Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

mod_audit.circulation_logs query utilizing excessive DB CPU

Description

We are experiencing AWS RDS excessive CPU alarms on iris - mod-audit-2.0.1

Attached is screenshot from Performance Insights.

mod_audit.circulation_logs table has 282527 records.

The RDS logs had many of the following queries with durations ranging from 5000-15,000 ms

2021-04-07 20:03:02 UTC:10.23.36.149(60020):fs09000000_mod_audit@folio:[32259]:LOG: duration: 7228.793 ms statement: EXPLAIN ANALYZE SELECT * FROM fs09000000_mod_audit.circulation_logs WHERE ((CASE WHEN length(lower(f_unaccent('2021-02-01T00:00:00.000'))) <= 600 THEN left(lower(f_unaccent(circulation_logs.jsonb->>'date')),600) >= lower(f_unaccent('2021-02-01T00:00:00.000')) ELSE left(lower(f_unaccent(circulation_logs.jsonb->>'date')),600) >= left(lower(f_unaccent('2021-02-01T00:00:00.000')),600) AND lower(f_unaccent(circulation_logs.jsonb->>'date')) >= lower(f_unaccent('2021-02-01T00:00:00.000')) END) AND (CASE WHEN length(lower(f_unaccent('2021-04-07T23:59:59.999'))) <= 600 THEN left(lower(f_unaccent(circulation_logs.jsonb->>'date')),600) <= lower(f_unaccent('2021-04-07T23:59:59.999')) ELSE left(lower(f_unaccent(circulation_logs.jsonb->>'date')),600) <= left(lower(f_unaccent('2021-04-07T23:59:59.999')),600) AND lower(f_unaccent(circulation_logs.jsonb->>'date')) <= lower(f_unaccent('2021-04-07T23:59:59.999')) END)) AND (CASE WHEN length(lower(f_unaccent('Checked out'))) <= 600 THEN left(lower(f_unaccent(circulation_logs.jsonb->>'action')),600) LIKE lower(f_unaccent('Checked out')) ELSE left(lower(f_unaccent(circulation_logs.jsonb->>'action')),600) LIKE left(lower(f_unaccent('Checked out')),600) AND lower(f_unaccent(circulation_logs.jsonb->>'action')) LIKE lower(f_unaccent('Checked out')) END) ORDER BY left(lower(f_unaccent(circulation_logs.jsonb->>'date')),600) DESC, lower(f_unaccent(circulation_logs.jsonb->>'date')) DESC LIMIT 100 OFFSET 6500

 

Test cases:

Change (system) testing

not UI related, will be verified by BE dev

2) Regression testing:
1. https://foliotest.testrail.io/index.php?/cases/view/16997
2. https://foliotest.testrail.io/index.php?/cases/view/16981 
3. https://foliotest.testrail.io/index.php?/cases/view/16978

4. https://foliotest.testrail.io/index.php?/cases/view/16980 

 

CSP Request Details

Approved in Slack (2-7-2022)

CSP Rejection Details

None

Potential Workaround

None

Attachments

3

Checklist

hide

TestRail: Results

Activity

Show:

Denis March 10, 2022 at 7:19 PM

Viachaslau Khandramai March 10, 2022 at 7:18 PM
Edited

New module version is deployed on bugfest env. That means that actual schema is applied and type of index is changed.

Oleksii Petrenko February 28, 2022 at 4:09 PM

Deployed to Kiwi BF. Please proceed with verification

Denis February 27, 2022 at 1:04 PM

Sobha Duvvuri February 2, 2022 at 6:00 PM

Yes please

Done

Details

Assignee

Reporter

Labels

Priority

Story Points

Sprint

Development Team

Firebird

Fix versions

Release

Kiwi (R3 2021) Hot Fix #2

RCA Group

Data related (ex. Can be detected with large dataset only)

CSP Approved

Yes

Affected Institution

Cornell

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs
Created April 7, 2021 at 10:48 PM
Updated March 10, 2022 at 9:14 PM
Resolved February 8, 2022 at 1:55 PM
TestRail: Cases
TestRail: Runs