Get Circulation/loans queries
IN PROGRESS
Overview:
Using the Carrier-io framework and CloudWach for capturing and analyzing performance test results we've done few tests (1,5,8 users) to understand how various layers contribute to the final response time moreover,compare and find differences between two queries on the same API call.
API call to test:
GET /circulation/loans
Queries to test::
?limit=2147483647&query=(itemId==${current_item} and status.name==Open);
?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*").
Tests are done with 1, 5, 8 users.
Test data used :
Version:
Okapi
Version 3.1.2
circulation 19.0.9
High Level Summary
There are 2 test set with the three tests each. 1,5,8 users for GET /circulation/loans?limit=2147483647&query=(itemId==${current_item} and status.name==Open) and 1,5,8 users for GET /circulation/loans?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*").
Second one is much faster than the first one.
Test Runs:
Test | Virtual users | Query | Profiled | Duration in seconds | Ramp Up in seconds |
|---|---|---|---|---|---|
1 | 1 | ?limit=2147483647&query=(itemId==${current_item} and status.name==Open) | no | 1800 | 0 |
2 | 5 | ?limit=2147483647&query=(itemId==${current_item} and status.name==Open) | no | 1800 | 50 |
3 | 8 | ?limit=2147483647&query=(itemId==${current_item} and status.name==Open) | no | 1800 | 80 |
4 | 1 | ?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*") | no | 1800 | 0 |
5 | 5 | ?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*") | no | 1800 | 50 |
6 | 8 | ?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*") | no | 1800 | 80 |
Results:
As you can see from table below- there a big difference between 75 percentiles for ?limit=2147483647&query=(itemId==${current_item} and status.name==Open) (1 user- 289 ms, 5 users-357 ms, 8 users - 414 ms)
and for ?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*") (1 user- 41 ms, 5 users-51 ms, 8 users - 58 ms). Which means that second query are much faster that the first one, that's why I would recommend to use second one ?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*").
Comparison of test runs below:
Test | Virtual Users | Requests | Total | OK | 75th pct | 95th pct | 99th pct | Max |
Test 1 | 1 | GET_circulation/loans?limit=2147483647&query=(itemId==${current_item} and status.name==Open) | 6389 | 6389 | 0.289 | 0.331 | 0.379 | 1.454 |
Test 2 | 5 | 26097 | 26097 | 0.357 | 0.422 | 0.51 | 1.399 | |
Test 3 | 8 | 49808 | 32221 | 0.414 | 0.502 | 0.649 | 17.104 | |
Test 4 | 1 | GET_circulation/loans?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*") | 40467 | 40467 | 0.041 | 0.056 | 0.118 | 1.281 |
Test 5 | 5 | 170235 | 170235 | 0.051 | 0.074 | 0.24 | 4.08 | |
Test 6 | 8 | 247471 | 247471 | 0.058 | 0.087 | 0.156 | 7.27 |
Comparison chart:
As you may see new query (lines at the right end) working way much faster
8 Users tests backend comparison:
Old query: (?limit=2147483647&query=(itemId==${current_item} and status.name==Open)) 8 User test
Instance CPU utilization:
Service CPU utilization
DB CPU utilization:
DB queries:
Note: one duplicate index found, but it appears often on other tests. No slow queries found.
Giraffe Chart
New query: (?limit=2147483647&query=(itemId==${current_item} and status.name=="Open*")) 8 User test
Instance CPU utilization
Service CPU utilization
DB CPU utilization
DB queries (one slow query found) :
Note that using new query we'll have less instance and service level CPU utilization however it produces higher DB CPU usage.
Giraffe Chart
Note: As you can see, the the giraffe chart contains less inside calls that the previous one. That may explain slowness of old query .