Feature Request: Support dynamically adjusting the number of transaction connection pools and read-only connection pools

[ISADBA]

Feature Description

Support dynamically adjusting the number of transaction connection pools and read-only connection pools like: --queryserver-config-pool-size --queryserver-config-transaction-cap

Use Case(s)

Our Vitess cluster has more than 400 vttablets. The deployment method is based on k8s. We mainly use unmanaged mode and the backend is cloud RDS. When we want to adjust --queryserver-config-pool-size or --queryserver-config-transaction -cap, the pod will be rebuilt, causing business errors. So we hope to be able to dynamically adjust the number of vttablet-level connection pools through vtctldclient.

Why we need to dynamically adjust the number of connection pools is because the total number of connections in our cloud RDS is limited, so the initial number of connection pools in vitess will not be too high.

[deepthi]

There is already a way to set these dynamically, and that is through the /debug/env endpoint of vttablet. We can also enable changing them dynamically through a config file. That will be an enhancement.

[harshit-gangal]

There was an attempt to use viper config to allow dynamic loading of some of the config in tabletserver https://github.com/vitessio/vitess/pull/14548

[harshit-gangal]

@ISADBA have you tried the /debug/env to update the pool size? Warn: This is an in-memory change and not persisted as config change.

[ISADBA]

@harshit-gangal

@ISADBA have you tried the /debug/env to update the pool size? Warn: This is an in-memory change and not persisted as config change.

Thank you for your response. I will try to update the connection pool using /debug/env. The method of modification is similar ？ /Users/jojo/Documents/github.com/vitess/go/vt/vttablet/endtoend/config_test.go::changeVar()

[ISADBA]

@harshit-gangal What is the command for the operation? curl -X POST http://172.1.0.100:15000/debug/env?format=json -H "Content-Type: application/x-www-form-urlencoded" -d "varname=PoolSize&value=10"

I used this command, but it didn’t work?

more informations: RowStreamerMaxInnoDBTrxHistLen and QueryCacheCapacity can take effect。

[mattlord]

This is how I've changed other values successfully:

curl -d "Action=Modify&varname=RowStreamerMaxInnoDBTrxHistLen&value=990000000" -X POST localhost:15000/debug/env

[ISADBA]

This is how I've changed other values successfully:

curl -d "Action=Modify&varname=RowStreamerMaxInnoDBTrxHistLen&value=990000000" -X POST localhost:15000/debug/env

@harshit-gangal @mattlord

sad，It’s not work，My vitess version is v16.0.0，this version is not support this feature？

vitess@vitess-cluster-zone1-vtctld-8ba63c48-86b6cd755b-b4xwv:/$ curl -X POST http://172.1.0.100:15000/debug/env?format=json -H "Content-Type: application/x-www-form-urlencoded" -d "Action=Modify&varname=PoolSize&value=10" {"Consolidator":"enable","MaxResultSize":"100000","PoolSize":"5","QueryCacheCapacity":"33554433","RowStreamerMaxInnoDBTrxHistLen":"999999","RowStreamerMaxMySQLReplLagSecs":"43200","StreamPoolSize":"96","ThrottleMetricThreshold":"1","TxPoolSize":"5","UnhealthyThreshold":"2h0m0s","WarnResultSize":"0"}

[harshit-gangal]

This works with v19 and onwards.