| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
...
In this workflow we are checking the performance of the check in items workflow running in the Fameflower release
| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
...
Test | Virtual Users | Duration | OKAPI log level | Profiled | Ramp up (total time in seconds) |
1. | 1 | 30 min | WARNING | No | 1 |
2. | 5 | 30 min | WARNING | No | 50 |
3. | 8 | 30 min | WARNING | No | 80 |
4. | 20 | 30 min | WARNING | No | 200 |
5. | 8 | 30 min | WARNING | Yes | 80 |
...
Results
Overall check in items API workflow time in seconds
For 30 minutes Profiler Average 75th %tile 1 user OFF 379.88 ms 379.88 ms 5 users OFF 715.4 ms 789.13 ms 8 users OFF 781 ms 822.02 ms 20 users OFF 1127.57 ms 1203.27 ms 8 users ON 1352.67 ms 1415.58 ms Slow APIs taking more than 100 ms to return (Profiling OFF)
API 1 user (75th %tile) 5 users (75th %tile) 8 users (75th %tile) 20 Users (75th %tile) POST checkin-by-barcode
379.88 ms 470.19 ms 508.61 ms 750.88 ms Get inventory/items 168.20 ms 183.79 ms 208.23 ms 289.92 ms
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
WARNING: Doing SQL query without index for scheduled_notice.jsonb->>'nextRunTime', CQL >>> SQL: nextRunTime < 2020-07-01T06:06:52.364Z >>> scheduled_notice.jsonb->>'nextRunTime' <'2020-07-01T06:06:52.364Z'WARNING: Doing LIKE search without index for scheduled_notice.jsonb->'noticeConfig'->>'sendInRealTime', CQL >>> SQL: noticeConfig.sendInRealTime == true >>> lower(f_unaccent(scheduled_notice.jsonb->'noticeConfig'->>'sendInRealTime')) LIKE lower(f_unaccent('true'))
WARNING: Doing LIKE search without index for scheduled_notice.jsonb->>'triggeringEvent', CQL >>> SQL: triggeringEvent == "Hold expiration" >>> lower(f_unaccent(scheduled_notice.jsonb->>'triggeringEvent')) LIKE lower(f_unaccent('Hold expiration'))
WARNING: Doing LIKE search without index for scheduled_notice.jsonb->>'triggeringEvent', CQL >>> SQL: triggeringEvent == "Request expiration" >>> lower(f_unaccent(scheduled_notice.jsonb->>'triggeringEvent')) LIKE lower(f_unaccent('Request expiration'))
WARNING: Doing LIKE search without index for jsonb->>'requestId', CQL >>> SQL: requestId == 5a1739e3-e9e2-4fe9-82a2-1c8285252fc9 >>> lower(f_unaccent(jsonb->>'requestId')) LIKE lower(f_unaccent('5a1739e3-e9e2-4fe9-82a2-1c8285252fc9'))
2020-07-01T06:08:38.776Z WARNING: Doing LIKE search without index for for patron_action_session.jsonb->>'requestIdpatronId', CQL >>> SQL: requestIdpatronId == 5a1739e32c5e835a-e9e2c12d-4fe945b4-82a2a949-1c8285252fc9262c0dc2f65d >>> lower(f_unaccent(patron_action_session.jsonb->>'requestIdpatronId')) LIKE lower(f_unaccent('5a1739e32c5e835a-e9e2c12d-4fe945b4-82a2a949-1c8285252fc9262c0dc2f65d'))
2020-07-01T06:08:38.776Z
WARNING: Doing LIKE search without index for patron_action_session.jsonb->>'patronIdactionType', CQL >>> SQL: patronIdactionType == 2c5e835a-c12d-45b4-a949-262c0dc2f65dCheck-in >>> lower(f_unaccent(patron_action_session.jsonb->>'patronIdactionType')) LIKE lower(f_unaccent('2c5e835a-c12d-45b4-a949-262c0dc2f65dCheck-in')) |
Database Slow queries
| Anchor | ||||
|---|---|---|---|---|
|
Slowest queries which took the most of execution time were initiated by the mod-inventory-storage and mod_circulation_storage services presented in the following table:
Percent of total time | Average Time,ms | Calls | Module | Query |
| 49% | 39 ms | 39,233 | mod_inventory_storage | SELECTCOUNT(*) FROM (SELECT jsonb,idFROM fs09000000_mod_inventory_storage.item WHERElower(f_unaccent(item.jsonb->>$1)) LIKElower(f_unaccent($2)) LIMIT $3) x E.x. SELECT COUNT(*) FROM (SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('090761167')) LIMIT 1 ) x |
| 48% | 39 ms | 39,232 | mod_inventory_storage | SELECT jsonb,idFROM fs09000000_mod_inventory_storage.item WHERElower(f_unaccent(item.jsonb->>$1)) LIKElower(f_unaccent($2)) LIMIT $3OFFSET $4 E.x. SELECT jsonb,idFROM fs09000000_mod_inventory_storage.item WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('090761167')) LIMIT 1 OFFSET 0 |
| 0.3% | 56 ms | 185 | mod_circulation_storage | SELECT jsonb,idFROM fs09000000_mod_circulation_storage.loan WHERE (lower(f_unaccent(loan.jsonb->$1->>$2)) LIKElower(f_unaccent($3))) AND (loan.jsonb->>$4 ~ $5) LIMIT $6OFFSET $7 E.x. SELECT jsonb,idFROM fs09000000_mod_circulation_storage.loan WHERE (lower(f_unaccent(loan.jsonb->'status'->>'name')) LIKE lower(f_unaccent('Open'))) AND (lower(f_unaccent(loan.jsonb->>'itemId')) LIMIT 1 OFFSET 0 |
...
- In mod-circulation and okapi consider using a more efficient JSON package or calling use the existing jackson serialization calls in a different way to address the item: JVM profiling shows JSON de/serialization operations one of the slowest operations.
- The found missing indexes warnings are covered by the stories
Jira Legacy server System JiraJIRA serverId 01505d01-b853-3c2e-90f1-ee9b165564fc key CIRCSTORE-215 Jira Legacy server System JiraJIRA columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 01505d01-b853-3c2e-90f1-ee9b165564fc key CIRCSTORE-216 - Have follow-up stories to study the performance of the two APIs that are still taking over 100ms to return to see where performance could improve.
Jira Legacy server System JiraJIRA serverId 01505d01-b853-3c2e-90f1-ee9b165564fc key PERF-79 Jira Legacy server System JiraJIRA serverId 01505d01-b853-3c2e-90f1-ee9b165564fc key PERF-90
...