| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Table of Contents |
|---|
...
In this workflow we are checking the performance of the loading items on instance record (in holdings accordion) workflow running in the Fameflower release. The series of steps can be found in the attached screencast.
| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
...
Test | Virtual Users | Duration | OKAPI log level | Profiled | Ramp up (total time in seconds) |
1. | 1 | 30 min | INFO | No | 1 |
2. | 5 | 30 min | INFO | No | 50 |
3. | 8 | 30 min | INFO | No | 80 |
4. | 20 | 30 min | INFO | No | 200 |
5. | 1 | 30 min | INFO | Yes | 1 |
6. | 5 | 30 min | INFO | Yes | 50 |
7. | 8 | 30 min | INFO | Yes | 80 |
8. | 20 | 30 min | INFO | Yes | 200 |
...
There aren't any WARNING statements of missing indexes in mod-inventory-storage, mod-source-record-storage modules.
The following WARNING statements of missing indexes were generated during a test run and logged by mod-circulation-storage:
...
Slowest queries which took the most of execution time were initiated by the mod_source_record_storage, mod-inventory-storage and mod_circulation_storage services presented in the following table:
Percent of total time | Average Time,ms | Calls | Module | Query |
| 52% | 20,855 ms | 1,325 | mod_source_record_storage | select get_record_by_external_id($1, $2) Examples: EXPLAINANALYZEselect get_record_by_external_id('89a7ec1c-608f-4d24-90b6-57da21557874', 'instanceId') EXPLAIN ANALYZE select get_record_by_external_id('1abdfd67-a4c3-46ba-a66b-82619f633e9a', 'instanceId') EXPLAIN ANALYZE select get_record_by_external_id('ff2f06c1-caf7-427a-9544-d8716eda90a6', 'instanceId') |
| 0.8% | 669 ms | 658 | mod_inventory_storage | WITH headrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>$1)) AS title FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector($2, f_unaccent(concat_space_sql(instance.jsonb->>$3 , concat_array_object_values(instance.jsonb->$4,$5) , concat_array_object_values(instance.jsonb->$6,$7)))) @@ (to_tsquery($8, f_unaccent($9)))) ANDleft(lower(f_unaccent(jsonb->>$10)),$11) < ( SELECTleft(lower(f_unaccent(jsonb->>$12)),$13) FROM fs09000000_mod_inventory_storage.instance ORDERBYleft(lower(f_unaccent(jsonb->>'title')),600) OFFSET $14LIMIT $15 ) ORDERBYleft(lower(f_unaccent(jsonb->>$16)),$17) LIMIT $18OFFSET $19 ), allrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>$20)) AS title FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector($21, f_unaccent(concat_space_sql(instance.jsonb->>$22 , concat_array_object_values(instance.jsonb->$23,$24) , concat_array_object_values(instance.jsonb->$25,$26)))) @@ (to_tsquery($27, f_unaccent($28)))) AND (SELECTCOUNT(*) FROM headrecords) < $29 ) SELECT jsonb, title, $30AScountFROM headrecords WHERE (SELECTCOUNT(*) FROM headrecords) >= $31UNION (SELECT jsonb, title, (SELECTCOUNT(*) FROM allrecords) AScountFROM allrecords ORDERBY title LIMIT $32OFFSET $33 ) ORDERBY title E.x. WITH headrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS title FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector('simple', f_unaccent(concat_space_sql(instance.jsonb->>'title' , concat_array_object_values(instance.jsonb->'contributors','name') , concat_array_object_values(instance.jsonb->'identifiers','value')))) @@ (to_tsquery('simple', f_unaccent('''aba''')))) AND left(lower(f_unaccent(jsonb->>'title')),600) < ( SELECT left(lower(f_unaccent(jsonb->>'title')),600) FROM fs09000000_mod_inventory_storage.instance ORDER BY left(lower(f_unaccent(jsonb->>'title')),600) OFFSET 10000 LIMIT 1 ) ORDER BY left(lower(f_unaccent(jsonb->>'title')),600) LIMIT 100 OFFSET 0 ), allrecords AS ( SELECT jsonb, lower(f_unaccent(jsonb->>'title')) AS title FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector('simple', f_unaccent(concat_space_sql(instance.jsonb->>'title' , concat_array_object_values(instance.jsonb->'contributors','name') , concat_array_object_values(instance.jsonb->'identifiers','value')))) @@ (to_tsquery('simple', f_unaccent('''aba''')))) AND (SELECT COUNT(*) FROM headrecords) < 100 ) SELECT jsonb, title, 0 AS count FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= 100 UNION (SELECT jsonb, title, (SELECT COUNT(*) FROM allrecords) AS count FROM allrecords ORDER BY title LIMIT 100 OFFSET 0 ) ORDER BY title |
| 0.8% | 214 ms | 1,918 | mod_circulation_storage | SELECT COUNT(*) FROM (SELECT jsonb,id FROM fs09000000_mod_circulation_storage.request WHERE (to_tsvector($1, f_unaccent(request.jsonb->>$2)) @@ replace((to_tsquery($3, f_unaccent($4)))::text, $5, $6)::tsquery) AND (to_tsvector($7, f_unaccent(request.jsonb->>$8)) @@ replace((to_tsquery($9, f_unaccent($10)))::text, $11, $12)::tsquery) LIMIT $13) x E.x. SELECT COUNT(*) FROM (SELECT jsonb,id FROM fs09000000_mod_circulation_storage.request WHERE (to_tsvector('simple', f_unaccent(request.jsonb->>'requesterId')) @@ replace((to_tsquery('simple', f_unaccent('''c2d0fc7d-85a9-4527-945d-6e5e29e74efc''')))::text, '&', '<->')::tsquery) AND (to_tsvector('simple', f_unaccent(request.jsonb->>'status')) @@ replace((to_tsquery('simple', f_unaccent('''Open''')))::text, '&', '<->')::tsquery) LIMIT 100) x |
| 0.6% | 177 ms | 1,914 | 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('19878861')) LIMIT 1) x |
| 0.6% | 165 ms | 1,914 | 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('19878861')) LIMIT 1 OFFSET 0 |
CPU Utilization
| 1 user | 5 users | 8 users | 20 users | |||||
|---|---|---|---|---|---|---|---|---|
| Average % | Range % | Average % | Range % | Average % | Range % | Average % | Range % | |
| Okapi | 2.12 | 0.29-8.94 | 2.87 | 1.56-13.65 | 2.78 | 0.31-16.82 | 2.85 | 0.32-11.65 |
| mod-inventory | 0.84 | 0.31-14.82 | 0.68 | 0.30-4.01 | 0.67 | 0.26-7.79 | 0.65 | 0.27-3.72 |
| mod-inventory-storage | 1.59 | 0.33-8.17 | 1.87 | 0.56-9.25 | 1.81 | 0.24-9.15 | 1.78 | 0.25-23.28 |
| mod-circulation | 0.67 | 0.24-4.12 | 0.82 | 0.48-4.44 | 0.96 | 0.26-4.04 | 0.82 | 0.28-4.12 |
| mod-circulation-storage | 0.71 | 0.25-6.60 | 0.74 | 0.31-2.79 | 0.86 | 0.29-3.27 | 0.71 | 0.27-2.50 |
| mod-source-record-storage | 0.34 | 0.23-1.43 | 0.50 | 0.26-1.86 | 0.53 | 0.31-2.41 | 0.43 | 0.29-1.92 |
...
- The following JIRAs have been created for mod_source_record_storage:
Jira Legacy server System JiraFOLIO Issue Tracker serverId 01505d016ccf3fe4-b8533301-3c2e368a-90f1983e-ee9b165564fc20c466b11a49 key MODSOURCE-149
Jira Legacy server System JiraFOLIO Issue Tracker serverId 01505d016ccf3fe4-b8533301-3c2e368a-90f1983e-ee9b165564fc20c466b11a49 key MODSOURCE-150
- On average, the performance can be improved by 100%. if we remove redundant "EXPLAIN ANALYZE" query call on mod_source_record_storage module. EXPLAIN ANALYZE statements are debugging statements that added a second execution (along with the first one) therefore increasing the overall time by 100%.
- Missing Indexes Warnings are covered by the story
Jira Legacy server System JiraFOLIO Issue Tracker serverId 01505d016ccf3fe4-b8533301-3c2e368a-90f1983e-ee9b165564fc20c466b11a49 key CIRCSTORE-215 - In mod-inventory-storage 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.
Appendix
See Attached FameFlower Performance Test Runsload-item-records-FF.xlsx for details