API For Requesting Sql Server Instances

[jeffswenson]

There were discussions in the multi-tenant meeting related to allowing cockroachdb to request additional sql servers from the Serverless control plane. The API may be helpful in the following scenarios:

• The DB is planning a distributed sql query and it determines the query would benefit from additional workers.
• A background job does not need to do any work at the moment, but it would like to wake up in a few hours to complete work. Example: a periodic backup job.
• Once we support multi-region serverless, a distributed sql query may need to request a sql server in a different region.

There is precedent in the DB for such an API.

• The tenant directory server is used by the sql proxy to request a pod when a tenant is scaled from 0 to 1 pods.
• The operator monitors a handful of sql server metrics to determine when it is safe to scale down to 0 pods.

From the standpoint of the serverless team, reactive scaling is preferable to the DB requesting pods. The operator is able to make decisions based on historical load and the total capacity available in the cluster. Just because a query can take advantage of 20 pods does not mean it should get 20 pods.

cc: @andy-kimball @ajstorm

Jira issue: CRDB-13530

[andy-kimball]

My thinking was that we'd expand the tenant directory API, and run a sidecar process on every SQL pod. The API would have one or more extra methods that would allow the SQL pod to request CPU capacity in other regions. I think it would be OK if the request contained a hint of what the running operation thinks it needs, in terms of CPU. For example, an Import job might request 20 vCPUs worth of capacity in us-east-1. This would only be a request, though; it might only get some subset of the requested amount.

[ajstorm]

Thanks for opening this @JeffSwenson. This issue will also influence how we design multi-region for Serverless so I've added this to the multi-region board. The request makes sense on the bulk side, but for multi-region we'll need a SQL server pod in that region to accept any DistSQL request for the given tenant (or performance will be impacted). That may imply that when we startup a tenant in a multi-region cluster, we need pods in each region, but that seems wasteful, and so spinning them up as required would be preferable.

[github-actions[bot]]

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!