[performance] Document impact of database connection pool size

[barroco]

Is your feature request related to a problem? Please describe.

The default application connection pool size is 4 (i.e. -datastore_max_open_conns=4), which is ok for queries under low latency.
However, under high latency conditions, this limits the queries throughput to the database as each query will be exclusively using a DB connection for as long as it is not completed.
In addition to the bottleneck it creates, in case of a client-side query retry, another connection from the pool will be required leading to wait time if none is available, not leveraging the full potential of the database when the pool is full.

This is especially important with the locust load test which hits at the moment only one API per run, thus only one pool of connection is used. This currently serves a protection mechanism to limit the number of requests to cockroach, however, rate limiting should be done upstream to reduce impact on retries.

We observed this while using locust load testing suite against a local deployment with network delay injected between nodes using netem.
During the tests, metrics in cockroachdb didn't demonstrate particular stress signals in terms of load or contention we would expect from the load generated by the locust.
Therefore, configuring the datastore_max_open_conns unlocked query throughput as shown in the following table describing the impact of various values for the application
connection pool size on top of #1558 with -enable_time_based_notification_index=true.

Detailled test setup

To limit variance and measure the worst case scenario, we are only testing against INTER DSS instances (so NUM_NODES=1).

Test setup

On the monitoring repository

export NUM_USS=21
export NUM_NODES=1
export DB_TYPE=crdb
export INTRA_USS_NETEM_CONF="delay 600us 40us 25% distribution normal loss 0.0005%"
export INTER_USS_NETEM_CONF="delay 36ms 40ms 50% distribution paretonormal loss 0.25% 15%"
<edit monitoring/build/dev/startup/core_service.sh to specify -datastore_max_open_conns and -enable_time_based_notification_index=false>
export DSS_IMAGE=<PR#1158>

Test runner

AUTH_SPEC="DummyOAuth(http://localhost:8085/token,locust)" uv run --index https://pypi.org/simple locust -f ./monitoring/loadtest/locust_files/FlightsInSub.py -H http://localhost:8010 --uss-base-url http://testdummy.interuss.org/uss --cluster-count 11 --base-lat 34 --base-lng -118 --area-radius 15182 --max-flight-distance 10000 --web-port=8090

Test parameters

INTER_USS_NETEM_CONF="delay 36ms 40ms 50% distribution paretonormal loss 0.25% 15%" is used as netem configuration.
Various datastore_max_open_conns are tested, from 10 to 30.

Results

# crdb nodes	locust clients	application connection pool size	requests / second	errors > 0.1%	latency p50 (ms)	latency p95 (ms)
21	10	10	1.3	no
21	30	10	6	no
21	40	20	10	no	3200
21	30	20	11.5	no	2300
21	10	20	5.4	no	1600
21	10	30	9.3	no	1800
21	20	30	13.7	no	1800
21	30	30	15.8	no	2200
21	40	30	15.9	no	2900	5400
21	100	30	15.9	0.4%	6500	10000

Describe the solution you'd like

1. Document this configuration flag in the user deployment guide to help users select the size of the application connection pool and provide example values as above depending on various conditions (especially, number of nodes and latency).
2. Terraform, tanka and helm need to be updated to expose this flag in the deployment resources as implemented in #.
3. Move load protection (rate limiting) at request level instead of database connection to mitigate retrial side-effect.