Scale and performance metrics PostgresAndPVC - stolostron/search-v2-operator GitHub Wiki

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

July 11

User queries (search-api)

  • 5 users (5 new per second)
  • LIMIT of 1000 resources

With 100 small managed clusters on Postgres DB (July 11)

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

Increased shared buffer to 128MB

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

With gp2 PVC - with shared buffer 128MB

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

With gp2 PVC - with shared buffer 128MB; 600 clusters (2M records)

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

With gp2 PVC - with shared buffer 128MB; 600 clusters (2M records/resources)

20 users (20 new per second)

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.

With AWS RDS external DB; 600 SNO clusters (~3M records/resources)

5 users (5 new per second)

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

With postgres with pvc (gp2); 600 SNO clusters (~3M records/resources), shared buffer 128MB

5 users (5 new per second)

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

With postgres with pvc (gp2); 600 SNO clusters (~3M records/resources), shared buffer 128MB

5 users (5 new per second)

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