FameFlower Test Results
...
Overview
Jira Legacy | ||||||
---|---|---|---|---|---|---|
|
...
Frontend:
- folio_inventory-2.0.2
Folio build was deployed with 50+ ECS services distributed randomly across four m5.large EC2 instances and the database was created on the db.r5.xlarge AWS RDS instance. Logging level was set to default INFO.
...
- Most of failed requests were related to GET_/inventory/instances and GET_/instance-bulk/ids that use uses mod-inventory-storageservice, even in a 1-user 30-min test run. modThis is due to a combination of slow running queries, high memory usage when exporting more than 100K records.
- mod-inventory-storage used 99% of container memory on a machine with 8GB RAM to return 50K to 100K records.
The workflow that retrieves more than 100K records became non-responsive even with 1 user
- The workflow with more than 5 users became non-responsive, indicative of exhausted memory usage. Consider add more memory to mod-inventory-storage container.
- fasterxml.jackson.databind.ObjectMapper.readValue method of mod-inventory-storage service consumed storage consumed high CPU resources as there were a lot of JSON decoding, this implementation could be reviewed for optimization possibilities.
- FOLIO performs better without being profiled when the tests are running
Test Runs
30-min Runs for export instance UUIDs workflow:
...
Test
- Some mod-inventory-storage's SQL queries took more than 500ms to run, see the Slow Queries section and Recommended Improvements for the JIRAs created to address these SQL queries.
- mod-inventory-storage generated missing indexes warnings, see Recommended Improvements for the JIRAs that were created by this testing effort.
Test Runs
30-min Runs for export instance UUIDs workflow:
Test | Virtual Users | Duration | OKAPI log level | Profiled | Ramp up (total time in seconds) | Size of response (how many instances were returned) |
1. FameFlower | 1 | 30 min | INFO | No | 5 | 10K~50K instances |
2. FameFlower | 1 | 30 min | INFO | No | 1 | 50K~100K instances |
3. FameFlower | 1 | 30 min | INFO | Yes | 10 | 10K~50K instances |
4. FameFlower | 1 | 30 min | INFO | Yes | 10 | 50K~100K instances |
5. FameFlower | 5 | 30 min | INFO | No | 50 | 10K~50K instances |
6. FameFlower | 5 | 30 min | INFO | No | 10 | 50K~100K instances |
7. FameFlower | 5 | 30 min | INFO | Yes | 50 | 10K~50K instances |
8. FameFlower | 5 | 30 min | INFO | Yes | 50 | 50K~100K instances |
...
Fig 3.1: mod-inventory-storage service consumed 99% of allocated RAM memory in the 5 users and 10K-50K instances retrieved test run.
Fig 3.1: mod-inventory-storage service consumed 99% of allocated RAM memory in the 5 users and 50K-100K instances retrieved test run.
...
Database CPU usage was not high, but the queries took a long time to return, see below.
6. Database Slow
...
queries
Anchor | ||||
---|---|---|---|---|
|
Slowest queries which took the most of execution time were initiated by the mod-inventory-storage service presented in the following table:
Percent of total time | Average Time,ms | Calls | Query |
32% | 10,796 | 15 | SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE to_tsvector($1, f_unaccent(concat_space_sql(instance.jsonb->>$2 , concat_array_object_values(instance.jsonb->$3,$4) , concat_array_object_values(instance.jsonb->$5,$6)))) @@ (to_tsquery($7, f_unaccent($8))) ORDER BY left(lower(f_unaccent(instance.jsonb->>$9)),$10), lower(f_unaccent(instance.jsonb->>$11)) |
23% | 22,250 | 5 | SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector($1, f_unaccent(concat_space_sql(instance.jsonb->>$2 , concat_array_object_values(instance.jsonb->$3,$4) , concat_array_object_values(instance.jsonb->$5,$6)))) @@ (to_tsquery($7, f_unaccent($8)))) AND (to_tsvector($9, f_unaccent(instance.jsonb->>$10)) @@ replace((to_tsquery($11, f_unaccent($12)))::text, $13, $14)::tsquery) ORDER BY left(lower(f_unaccent(instance.jsonb->>$15)),$16), lower(f_unaccent(instance.jsonb->>$17)) |
13% | 1,709 | 37 | SELECT COUNT(*) FROM (SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE to_tsvector($1, f_unaccent(concat_space_sql(instance.jsonb->>$2 , concat_array_object_values(instance.jsonb->$3,$4) , concat_array_object_values(instance.jsonb->$5,$6)))) @@ (to_tsquery($7, f_unaccent($8))) ORDER BY left(lower(f_unaccent(instance.jsonb->>$9)),$10), lower(f_unaccent(instance.jsonb->>$11)) LIMIT $12) x |
12% | 1,818 | 34 | 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)))) AND left(lower(f_unaccent(jsonb->>$10)),$11) < ( SELECT left(lower(f_unaccent(jsonb->>$12)),$13) FROM fs09000000_mod_inventory_storage.instance ORDER BY left(lower(f_unaccent(jsonb->>'title')),600) OFFSET $14 LIMIT $15 ) ORDER BY left(lower(f_unaccent(jsonb->>$16)),$17) LIMIT $18 OFFSET $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 (SELECT COUNT(*) FROM headrecords) < $29 ) SELECT jsonb, title, $30 AS count FROM headrecords WHERE (SELECT COUNT(*) FROM headrecords) >= $31 UNION (SELECT jsonb, title, (SELECT COUNT(*) FROM allrecords) AS count FROM allrecords ORDER BY title LIMIT $32 OFFSET $33 ) ORDER BY title |
4% | 2,804 | 7 | SELECT COUNT(*) FROM (SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE (to_tsvector($1, f_unaccent(concat_space_sql(instance.jsonb->>$2 , concat_array_object_values(instance.jsonb->$3,$4) , concat_array_object_values(instance.jsonb->$5,$6)))) @@ (to_tsquery($7, f_unaccent($8)))) AND (to_tsvector($9, f_unaccent(instance.jsonb->>$10)) @@ replace((to_tsquery($11, f_unaccent($12)))::text, $13, $14)::tsquery) ORDER BY left(lower(f_unaccent(instance.jsonb->>$15)),$16), lower(f_unaccent(instance.jsonb->>$17)) LIMIT $18) x |
3% | 1,865 | 9 | EXPLAIN ANALYZE 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('''english''')))) 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('''english''')))) 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 |
7. Database Missing indexes
8. JVM Profiling result
JVM profiling of the most resources consuming mod-inventory-storage service showed 6 methods which had a high CPU usage and impact on the overall service performance.
fasterxml.jackson.databind.ObjectMapper.readValue method uses most of CPU capacity which leads to performance degradation
...
) FROM allrecords) AS count FROM allrecords ORDER BY title LIMIT 100 OFFSET 0 ) ORDER BY title |
7. Database Missing indexes
8. JVM Profiling result
Anchor | ||||
---|---|---|---|---|
|
JVM profiling of the most resources consuming mod-inventory-storage service showed 6 methods which had a high CPU usage and impact on the overall service performance.
fasterxml.jackson.databind.ObjectMapper.readValue method uses most of CPU capacity which leads to performance degradation
Recommended improvement
Anchor | ||||
---|---|---|---|---|
|
Consider adding more memory to mod-inventory-storage container if there is a need to export over 100K UUIDs.
The following JIRAs are created for mod-inventory-storage missing indexes and
Jira Legacy | ||||||
---|---|---|---|---|---|---|
|
Jira Legacy | ||||||
---|---|---|---|---|---|---|
|
Jira Legacy | ||||||
---|---|---|---|---|---|---|
|
Appendix
See Attached FameFlower Performance Test Runs.xlsx for details