-
Notifications
You must be signed in to change notification settings - Fork 10
Scale and performance metrics PostgresAndPVC
Data obtained running Locust jobs on cluster. All response times are in milliseconds. Here, the api uses materialized view (MV) with 3 indexes for reltionship queries - composite unique index(on sourceid, destid, edgetype) and index on sourceid and destid
- 5 users (5 new per second)
- LIMIT of 1000 resources
Environment - operation | # reqs | # fails | Avg | Min | Max | Median | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
GP2 storage | ||||||||
searchByCount | 84 | 0(0.00%) | 525 | 220 | 1344 | 460 | 0.17 | 0.00 |
searchByFilter | 81 | 0(0.00%) | 1412 | 994 | 2502 | 1400 | 0.16 | 0.00 |
searchByKeyword | 70 | 0(0.00%) | 2361 | 1404 | 4117 | 2200 | 0.14 | 0.00 |
searchComplete | 77 | 0(0.00%) | 569 | 328 | 1477 | 490 | 0.15 | 0.00 |
searchRelatedCount | 78 | 0(0.00%) | 4710 | 3310 | 7345 | 4600 | 0.16 | 0.00 |
searchRelatedItems | 77 | 0(0.00%) | 4746 | 3245 | 6836 | 4700 | 0.15 | 0.00 |
Aggregated | 467 | 0(0.00%) | 2356 | 220 | 7345 | 1700 | 0.94 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 470 | 550 | 600 | 620 | 750 | 1100 | 1200 | 1300 | 1300 | 1300 | 1300 | 84 |
searchByFilter | 1400 | 1500 | 1600 | 1600 | 1900 | 2000 | 2400 | 2500 | 2500 | 2500 | 2500 | 81 |
searchByKeyword | 2300 | 2500 | 2800 | 3000 | 3300 | 3600 | 3800 | 4100 | 4100 | 4100 | 4100 | 70 |
searchComplete | 490 | 610 | 670 | 690 | 980 | 1200 | 1400 | 1500 | 1500 | 1500 | 1500 | 77 |
searchRelatedCount | 4700 | 4900 | 5200 | 5200 | 5800 | 6500 | 6800 | 7300 | 7300 | 7300 | 7300 | 78 |
searchRelatedItems | 4700 | 5000 | 5300 | 5400 | 5900 | 6500 | 6500 | 6800 | 6800 | 6800 | 6800 | 77 |
Aggregated | 1700 | 3300 | 4100 | 4500 | 5100 | 5600 | 6300 | 6500 | 7300 | 7300 | 7300 | 467 |
Environment - operation | # reqs | # fails | Avg | Min | Max | Median | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
GP2 storage | ||||||||
searchByCount | 113 | 0(0.00%) | 300 | 212 | 659 | 280 | 0.23 | 0.00 |
searchByFilter | 146 | 0(0.00%) | 123 | 65 | 550 | 91 | 0.29 | 0.00 |
searchByKeyword | 126 | 0(0.00%) | 179 | 120 | 705 | 150 | 0.25 | 0.00 |
searchComplete | 118 | 0(0.00%) | 337 | 281 | 691 | 310 | 0.24 | 0.00 |
searchRelatedCount | 156 | 0(0.00%) | 283 | 69 | 957 | 270 | 0.31 | 0.00 |
searchRelatedItems | 130 | 0(0.00%) | 264 | 72 | 740 | 260 | 0.26 | 0.00 |
Aggregated | 789 | 0(0.00%) | 244 | 65 | 957 | 250 | 1.58 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 280 | 300 | 330 | 350 | 420 | 490 | 580 | 590 | 660 | 660 | 660 | 113 |
searchByFilter | 91 | 100 | 120 | 150 | 240 | 320 | 370 | 420 | 550 | 550 | 550 | 146 |
searchByKeyword | 150 | 170 | 180 | 200 | 280 | 310 | 410 | 690 | 710 | 710 | 710 | 126 |
searchComplete | 310 | 330 | 350 | 370 | 430 | 450 | 560 | 640 | 690 | 690 | 690 | 118 |
searchRelatedCount | 270 | 300 | 330 | 350 | 450 | 560 | 640 | 790 | 960 | 960 | 960 | 156 |
searchRelatedItems | 260 | 280 | 290 | 310 | 370 | 490 | 570 | 670 | 740 | 740 | 740 | 130 |
Aggregated | 250 290 | 310 | 320 | 380 | 450 | 580 | 670 | 960 | 960 | 960 | 789 |
Environment - operation | # reqs | # fails | Avg | Min | Max | Median | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
GP2 storage | x | x | x | x | x | |||
searchByCount | 126 | 0(0.00%) | 292 | 195 | 533 | 260 | 0.25 | 0.00 |
searchByFilter | 115 | 0(0.00%) | 246 | 77 | 759 | 230 | 0.23 | 0.00 |
searchByKeyword | 147 | 0(0.00%) | 160 | 114 | 666 | 140 | 0.29 | 0.00 |
searchComplete | 136 | 0(0.00%) | 321 | 258 | 876 | 290 | 0.27 | 0.00 |
searchRelatedCount | 127 | 0(0.00%) | 294 | 223 | 681 | 260 | 0.25 | 0.00 |
searchRelatedItems | 118 | 0(0.00%) | 291 | 223 | 646 | 270 | 0.24 | 0.00 |
Aggregated | 769 | 0(0.00%) | 265 | 77 | 876 | 260 | 1.54 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 260 | 300 | 330 | 360 | 420 | 450 | 520 | 520 | 530 | 530 | 530 | 126 |
searchByFilter | 230 | 260 | 300 | 310 | 410 | 530 | 640 | 650 | 760 | 760 | 760 | 115 |
searchByKeyword | 140 | 150 | 160 | 170 | 230 | 280 | 370 | 420 | 670 | 670 | 670 | 147 |
searchComplete | 290 | 310 | 340 | 350 | 380 | 460 | 630 | 850 | 880 | 880 | 880 | 136 |
searchRelatedCount | 260 | 290 | 310 | 330 | 410 | 480 | 530 | 560 | 680 | 680 | 680 | 127 |
searchRelatedItems | 270 | 300 | 310 | 330 | 360 | 440 | 530 | 630 | 650 | 650 | 650 | 118 |
Aggregated | 260 | 280 | 300 | 320 | 380 | 450 | 530 | 640 | 880 | 880 | 880 | 769 |
Environment - operation | # reqs | # fails | Avg | Min | Max | Median | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
GP2 storage | x | x | x | x | x | |||
searchByCount | 98 | 0(0.00%) | 2063 | 1184 | 5492 | 1800 | 0.20 | 0.00 |
searchByFilter | 131 | 0(0.00%) | 146 | 74 | 628 | 100 | 0.26 | 0.00 |
searchByKeyword | 101 | 0(0.00%) | 233 | 119 | 1105 | 160 | 0.20 | 0.00 |
searchComplete | 115 | 0(0.00%) | 2060 | 1487 | 3820 | 1800 | 0.23 | 0.00 |
searchRelatedCount | 104 | 0(0.00%) | 442 | 222 | 1669 | 320 | 0.21 | 0.00 |
searchRelatedItems | 98 | 0(0.00%) | 400 | 231 | 1247 | 310 | 0.20 | 0.00 |
Aggregated | 647 | 0(0.00%) | 876 | 74 | 5492 | 360 | 1.29 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 1800 | 2000 | 2200 | 2400 | 3200 | 3900 | 5000 | 5500 | 5500 | 5500 | 5500 | 98 |
searchByFilter | 100 | 130 | 160 | 190 | 270 | 310 | 450 | 620 | 630 | 630 | 630 | 131 |
searchByKeyword | 160 | 210 | 260 | 330 | 420 | 510 | 570 | 990 | 1100 | 1100 | 1100 | 101 |
searchComplete | 1800 | 2100 | 2400 | 2500 | 2900 | 3400 | 3600 | 3700 | 3800 | 3800 | 3800 | 115 |
searchRelatedCount | 330 | 420 | 530 | 580 | 810 | 980 | 1400 | 1500 | 1700 | 1700 | 1700 | 104 |
searchRelatedItems | 320 | 420 | 490 | 540 | 690 | 780 | 1200 | 1200 | 1200 | 1200 | 1200 | 98 |
Aggregated | 360 | 1100 | 1600 | 1700 | 2100 | 2600 | 3400 | 3700 | 5500 | 5500 | 5500 | 647 |
Environment - operation | # reqs | # fails | Avg | Min | Max | Median | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
GP2 storage | x | x | x | x | x | |||
searchByCount | 218 | 0(0.00%) | 8212 | 2805 | 19322 | 7600 | 0.44 | 0.00 |
searchByFilter | 210 | 0(0.00%) | 1962 | 84 | 10988 | 1700 | 0.42 | 0.00 |
searchByKeyword | 212 | 0(0.00%) | 2255 | 164 | 7412 | 1800 | 0.42 | 0.00 |
searchComplete | 227 | 0(0.00%) | 7664 | 2108 | 15849 | 7400 | 0.46 | 0.00 |
searchRelatedCount | 221 | 0(0.00%) | 3558 | 419 | 16920 | 3300 | 0.44 | 0.00 |
searchRelatedItems | 221 | 0(0.00%) | 3700 | 248 | 13461 | 3300 | 0.44 | 0.00 |
Aggregated | 1309 | 0(0.00%) | 4602 | 84 | 19322 | 3800 | 2.62 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 7700 | 9100 | 10000 | 11000 | 13000 | 15000 | 16000 | 17000 | 19000 | 19000 | 19000 | 218 |
searchByFilter | 1700 | 2300 | 2500 | 2700 | 3800 | 5100 | 7000 | 8700 | 11000 | 11000 | 11000 | 210 |
searchByKeyword | 1900 | 2600 | 3200 | 3500 | 4300 | 5300 | 6000 | 7000 | 7400 | 7400 | 7400 | 212 |
searchComplete | 7400 | 8300 | 8900 | 9300 | 10000 | 12000 | 12000 | 13000 | 16000 | 16000 | 16000 | 227 |
searchRelatedCount | 3300 | 4000 | 4700 | 4900 | 6500 | 7400 | 9800 | 10000 | 17000 | 17000 | 17000 | 221 |
searchRelatedItems | 3300 | 4200 | 4700 | 5200 | 7000 | 8200 | 10000 | 11000 | 13000 | 13000 | 13000 | 221 |
Aggregated | 3800 | 5500 | 6800 | 7400 | 9200 | 11000 | 13000 | 15000 | 19000 | 19000 | 19000 | 1309 |
Here, the worst performing queries either use ILIKE comparison for case-insensitive comparison on property KIND or is querying a property with no index.
SearchComplete Query: SELECT DISTINCT "data"->>'name' FROM "search"."resources" WHERE ("data"->>'name' IS NOT NULL) ORDER BY "data"->>'name' ASC LIMIT 1000
searchByCount Query: SELECT COUNT("uid") FROM "search"."resources" WHERE ("data"->>'kind' ILIKE ANY ('{"Pod"}'))
Recommendation: Use materialized view with index on sourceid, destid instead of view (current) for relations query. We can read concurrently from the materialized view while it is updating.
Unknown: RBAC and Cost of refreshing materialized views when underlying table is updated.
Environment - operation | # reqs | # fails | Avg | Min | Max | Med | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
searchByCount | 83 | 0(0.00%) | 1661 | 1185 | 4825 | 1400 | 0.17 | 0.00 |
searchByFilter | 100 | 0(0.00%) | 176 | 107 | 865 | 160 | 0.20 | 0.00 |
searchByKeyword | 94 | 0(0.00%) | 209 | 156 | 469 | 180 | 0.19 | 0.00 |
searchComplete | 108 | 0(0.00%) | 4681 | 3181 | 16628 | 4000 | 0.22 | 0.00 |
searchRelatedCount | 111 | 0(0.00%) | 102 | 75 | 876 | 82 | 0.22 | 0.00 |
searchRelatedItems | 98 | 0(0.00%) | 109 | 75 | 867 | 80 | 0.20 | 0.00 |
Aggregated | 594 | 0(0.00%) | 1183 | 75 | 16628 | 170 | 1.19 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 1400 | 1500 | 1700 | 1900 | 2300 | 3600 | 4400 | 4800 | 4800 | 4800 | 4800 | 83 |
searchByFilter | 160 | 160 | 180 | 180 | 220 | 310 | 480 | 870 | 870 | 870 | 870 | 100 |
searchByKeyword | 180 | 200 | 220 | 240 | 300 | 340 | 440 | 470 | 470 | 470 | 470 | 94 |
searchComplete | 4000 | 4400 | 4800 | 5000 | 6200 | 9900 | 12000 | 13000 | 17000 | 17000 | 17000 | 108 |
searchRelatedCount | 82 | 85 | 90 | 98 | 140 | 160 | 250 | 470 | 880 | 880 | 880 | 111 |
searchRelatedItems | 80 | 82 | 87 | 92 | 170 | 190 | 870 | 870 | 870 | 870 | 870 | 98 |
Aggregated | 170 | 400 | 1400 | 2200 | 4000 | 4700 | 5900 | 9900 | 17000 | 17000 | 17000 | 594 |
Environment - operation | # reqs | # fails | Avg | Min | Max | Med | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
searchByCount | 72 | 0(0.00%) | 5657 | 2005 | 11602 | 4700 | 0.15 | 0.00 |
searchByFilter | 60 | 0(0.00%) | 263 | 81 | 1905 | 210 | 0.12 | 0.00 |
searchByKeyword | 61 | 0(0.00%) | 435 | 148 | 1760 | 310 | 0.12 | 0.00 |
searchComplete | 67 | 0(0.00%) | 10853 | 4969 | 26280 | 9800 | 0.14 | 0.00 |
searchRelatedCount | 66 | 0(0.00%) | 774 | 221 | 5738 | 560 | 0.13 | 0.00 |
searchRelatedItems | 70 | 0(0.00%) | 654 | 224 | 5176 | 530 | 0.14 | 0.00 |
Aggregated | 396 | 0(0.00%) | 3216 | 81 | 26280 | 660 | 0.80 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | # reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 4700 | 6500 | 8200 | 8500 | 10000 | 11000 | 11000 | 12000 | 12000 | 12000 | 12000 | 72 |
searchByFilter | 220 | 250 | 290 | 320 | 540 | 680 | 690 | 1900 | 1900 | 1900 | 1900 | 60 |
searchByKeyword | 310 | 420 | 500 | 560 | 840 | 1400 | 1400 | 1800 | 1800 | 1800 | 1800 | 61 |
searchComplete | 9800 | 11000 | 12000 | 12000 | 21000 | 23000 | 25000 | 26000 | 26000 | 26000 | 26000 | 67 |
searchRelatedCount | 580 | 660 | 720 | 780 | 1300 | 1800 | 4100 | 5700 | 5700 | 5700 | 5700 | 66 |
searchRelatedItems | 550 | 660 | 750 | 830 | 940 | 1100 | 1600 | 5200 | 5200 | 5200 | 5200 | 70 |
Aggregated | 660 | 2700 | 5100 | 6800 | 10000 | 11000 | 18000 | 23000 | 26000 | 26000 | 26000 | 396 |
Timings after updating the queries in https://github.com/stolostron/backlog/issues/24475
Name | # reqs | # fails | Avg | Min | Max | Med | req/s | failures/s |
---|---|---|---|---|---|---|---|---|
searchByCount | 298 | 0(0.00%) | 999 | 525 | 4879 | 820 | 0.25 | 0.00 |
searchByFilter | 314 | 0(0.00%) | 241 | 71 | 3047 | 110 | 0.26 | 0.00 |
searchByKeyword | 273 | 0(0.00%) | 115 | 45 | 753 | 66 | 0.23 | 0.00 |
searchComplete | 267 | 0(0.00%) | 104 | 52 | 762 | 70 | 0.22 | 0.00 |
searchRelatedCount | 308 | 0(0.00%) | 575 | 302 | 3658 | 430 | 0.26 | 0.00 |
searchRelatedItems | 297 | 0(0.00%) | 325 | 158 | 3633 | 230 | 0.25 | 0.00 |
Aggregated | 1757 | 0(0.00%) | 402 | 45 | 4879 | 240 | 1.47 | 0.00 |
Response time percentiles (approximated)
Name | 50% | 66% | 75% | 80% | 90% | 95% | 98% | 99% | 99.9% | 99.99% | 100% | #reqs |
---|---|---|---|---|---|---|---|---|---|---|---|---|
searchByCount | 830 | 1000 | 1200 | 1300 | 1500 | 2000 | 2500 | 3200 | 4900 | 4900 | 4900 | 298 |
searchByFilter | 110 | 180 | 300 | 350 | 480 | 670 | 990 | 2000 | 3000 | 3000 | 3000 | 314 |
searchByKeyword | 66 | 91 | 120 | 160 | 240 | 360 | 610 | 710 | 750 | 750 | 750 | 273 |
searchComplete | 70 | 85 | 97 | 110 | 200 | 310 | 430 | 700 | 760 | 760 | 760 | 267 |
searchRelatedCount | 430 | 530 | 660 | 740 | 1000 | 1300 | 1600 | 1800 | 3700 | 3700 | 3700 | 308 |
searchRelatedItems | 230 | 300 | 370 | 410 | 540 | 740 | 1100 | 1500 | 3600 | 3600 | 3600 | 297 |
Aggregated | 240 | 410 | 560 | 640 | 930 | 1300 | 1700 | 2300 | 3800 | 4900 | 4900 | 1757 |