The various search patterns afforded to customers make it challenging to generally optimize the query performed by the /admin/record endpoint.
This gist explores several strategies that offer significant improvement for specific query patterns.
Last active
April 30, 2022 00:15
-
-
Save jcuffe/6712c1722b0c004655bd9f6874ff2741 to your computer and use it in GitHub Desktop.
ApacheBench results for traditionally slow-running requests under various strategies
This request was performed against a DB with a full text index on field_record.value
Origin request against production took 45 seconds:
{"requestDuration":44781,"requestId":"7e81b1cd-dfa5-45b6-8013-6ec08544aa2b","statusCode":200,"url":"/admin/records?search=Raymond+Cook&limit=20&statusDaysOld=90&_=1651234008624"}
Document Path: /admin/records?search=Raymond+Cook&limit=20&statusDaysOld=90
Document Length: 295 bytes
Concurrency Level: 1
Time taken for tests: 3.968 seconds
Complete requests: 20
Failed requests: 0
Total transferred: 10080 bytes
HTML transferred: 5900 bytes
Requests per second: 5.04 [#/sec] (mean)
Time per request: 198.419 [ms] (mean)
Time per request: 198.419 [ms] (mean, across all concurrent requests)
Transfer rate: 2.48 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 177 198 36.9 192 351
Waiting: 176 197 36.8 191 350
Total: 177 198 36.9 192 351
Percentage of the requests served within a certain time (ms)
50% 192
66% 195
75% 196
80% 196
90% 210
95% 351
98% 351
99% 351
100% 351 (longest request)
This request was performed using 'prefix wildcard%' pattern matching against a DB with an index on field_record.value
Original request against production app took 25 seconds:
{"requestDuration":25456,"requestId":"ce2eed17-6798-4df4-b6c4-1e321c1d2022","statusCode":200,"url":"/admin/records?search=119533947&limit=20&statusDaysOld=30&_=1651267058899"}
Document Path: /admin/records?search=119533947&limit=20&statusDaysOld=30
Document Length: 290 bytes
Concurrency Level: 1
Time taken for tests: 3.016 seconds
Complete requests: 20
Failed requests: 0
Total transferred: 9980 bytes
HTML transferred: 5800 bytes
Requests per second: 6.63 [#/sec] (mean)
Time per request: 150.788 [ms] (mean)
Time per request: 150.788 [ms] (mean, across all concurrent requests)
Transfer rate: 3.23 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 141 151 6.1 149 164
Waiting: 139 150 6.0 148 163
Total: 141 151 6.1 149 164
Percentage of the requests served within a certain time (ms)
50% 149
66% 153
75% 156
80% 157
90% 160
95% 164
98% 164
99% 164
100% 164 (longest request)
This request was performed using a STRAIGHT_JOIN from record_status to record against a DB with an index on record_status.created_at
Original request against production took 28 seconds:
{"requestDuration":28092,"requestId":"3959330d-85d3-4728-8721-283bdd2d1407","statusCode":200,"url":"/admin/records?search=8858829&limit=20&statusDaysOld=30&_=1650983471908"}
Document Path: /admin/records?search=8858829&limit=20&statusDaysOld=30
Document Length: 575 bytes
Concurrency Level: 1
Time taken for tests: 263.031 seconds
Complete requests: 20
Failed requests: 0
Total transferred: 15680 bytes
HTML transferred: 11500 bytes
Requests per second: 0.08 [#/sec] (mean)
Time per request: 13151.551 [ms] (mean)
Time per request: 13151.551 [ms] (mean, across all concurrent requests)
Transfer rate: 0.06 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 13039 13151 86.6 13135 13443
Waiting: 13037 13150 86.7 13134 13441
Total: 13039 13152 86.6 13135 13443
Percentage of the requests served within a certain time (ms)
50% 13135
66% 13163
75% 13206
80% 13208
90% 13225
95% 13443
98% 13443
99% 13443
100% 13443 (longest request)
This request was performed using a STRAIGHT_JOIN from record_status to record against a DB with an index on record_status.created_at
The one week duration demonstrates the effect of the index on response times.
Document Path: /admin/records?search=8858829&limit=20&statusDaysOld=7
Document Length: 575 bytes
Concurrency Level: 1
Time taken for tests: 100.704 seconds
Complete requests: 20
Failed requests: 0
Total transferred: 15680 bytes
HTML transferred: 11500 bytes
Requests per second: 0.20 [#/sec] (mean)
Time per request: 5035.195 [ms] (mean)
Time per request: 5035.195 [ms] (mean, across all concurrent requests)
Transfer rate: 0.15 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.0 0 0
Processing: 4973 5035 30.5 5037 5081
Waiting: 4972 5034 30.5 5036 5080
Total: 4973 5035 30.5 5037 5081
Percentage of the requests served within a certain time (ms)
50% 5037
66% 5044
75% 5068
80% 5073
90% 5077
95% 5081
98% 5081
99% 5081
100% 5081 (longest request)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment