After adding the index for "last_activity_at", we did not see a noticeable improvement in response time and the 95th percentile was still unusually high. There was a significant improvement in the error rate. The chart below shows a visual correlation between spikes and the response time. Upon inspecting these remaining errors, it was found that they all shared the "asc" sort field in common. This could explain the unexpected response time where we were expecting an improvement.
The test started at 3:50pm. The index started at 4:00pm. The index finished at 4:28pm.
| Course | reqs | # | fails | Avg | Min | Max | Median | req/s | 95% |
|---|---|---|---|---|---|---|---|---|---|
| Large | GET_comment_list | 60 | 0(0.00%) | 386 | 191 | 1095 | 330 | 0 | 760 |
| GET_thread | 3541 | 0(0.00%) | 240 | 9 | 1941 | 220 | 1.8 | 360 | |
| GET_thread_list | 1620 | 238(12.81%) | 1037 | 187 | 5380 | 920 | 0.7 | 2200 | |
| PATCH_comment | 60 | 0(0.00%) | 519 | 226 | 1131 | 480 | 0 | 970 | |
| PATCH_thread | 65 | 0(0.00%) | 384 | 173 | 2059 | 320 | 0 | 770 | |
| Small | GET_comment_list | 1846 | 0(0.00%) | 240 | 152 | 1171 | 210 | 0.4 | 400 |
| GET_thread | 111004 | 0(0.00%) | 222 | -25 | 2395 | 200 | 26.1 | 340 | |
| GET_thread_list | 59369 | 151(0.25%) | 603 | -25 | 5817 | 550 | 12.8 | 1200 | |
| PATCH_comment | 1837 | 7(0.38%) | 402 | 182 | 2582 | 350 | 0.4 | 770 | |
| PATCH_thread | 1995 | 35(1.72%) | 310 | 148 | 1611 | 270 | 0.4 | 550 |
After removing the "asc" parameter, we saw some improvement but unfortunately, because of the way PATCH was implemented in the loadtest, it caused the response times to gradually become slower. At this point in time, because "asc" was not being used by the browser and will not be used by the mobile app, adding that index has been bumped down in priority.