server,sql: subsystem to provide unique instance IDs and routing metadata for sql pods

[ajwerner]

This is an epic (meta-issue)

Sub-issues:

• server: ensure SQL pods have unique instance IDs within the tenant server: ensure SQL pods have unique instance IDs within the tenant #64474
• server: store instance ID and network addresses of SQL pods server: store instance ID and network addresses of SQL pods #64475
• server: supplement (*statusServer).iterateNodes() with a new iterateSQLPods() server: supplement (*statusServer).iterateNodes() with a new iterateSQLPods() #64477
• rpc,security: set up TLS parameters for pod-to-pod communication rpc,security: set up TLS parameters for pod-to-pod communication #64476

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

Relates to #60584, #50047.

Cockroach has long operated under the assumption that each node has a handle to a cluster-unique, integer identifier. This identifier, originally the NodeID, was assigned in the joining protocol. This ID was stored durably on the node's disk and was generated using a distributed transaction.

With the advent of multi-tenant, we carved out a new abstraction, the SQLInstanceID to represent a similar concept but to encapsulate in the type system that the integer does not carry the same durable semantics. We've been able to make this work by using the node ID as the SQLInstanceID on a system tenant server by just using a hard-coded constant.

cockroach/pkg/server/testserver.go

Line 583 in 8905b9b

const sqlInstanceID = base.SQLInstanceID(10001)

.

These unique IDs get used for the following things:

• unique_rowid() uniqueness
• RPC routing
  • The node-dialer abstraction and all RPCs today assume that we use integers to address machines
  • This mapping was propagated via gossip
  • Note that this ability to fan-out to SQL processes powers many observability
  • This is NodeID only today, hence this project
• SQL descriptor leasing
  • In 21.2 we'd like to replace this with sqlliveness session-based leases.
• Others?

In summary, what we want is a subsystem that provides a mechanism for SQL pods to lease a tenant-unique SQLInstanceID and to associate a network address with this ID.

Describe the solution you'd like

In 20.2 we introduced the sqlliveness subsystem as a mechanism to provide a distributed leasing primitive akin to node-liveness epochs used by the KV layer. These leases power job leases since 20.2 and in 21.1 the sql schema team will be working to adopt this subsystem for schema leases.

Today, a server will create a new session whenever its session fails. This is akin to a kv node having its epoch pushed but not restarting as a process. This solution proposes that we change this behavior for SQL servers to instead crash upon (prior to) the expiration of its session. This is more acceptable because of the stateless nature of SQL pods.

Sketch of the steps:

• Add a channel to sqlliveness.Session which closes when you expire
  • Probably a bit more conservative than this even. Like if you fail to renew before 2x clock offset or something
• Add a new subsystem for sql servers that exposes a sql instance ID (on real kv nodes this will be the node ID)
  • Implement this thing by taking a sqlliveness.Session
  • Also take a network address
  • Add a backing system table
  • Find an ID and lease it
  • Literally crash the server when you receive on the expiration channel
• Start the above subsystem in the SQL server startup

Readers can read from the above subsystem with a scan and a filter on crdb_internal.sql_liveness_is_alive which is cheap. Also, something about cleanup of removed sessions, but I think I'd push that into the leasing of a new session.

Straw man schema

CREATE TABLE sql_instances (
   id INT PRIMARY KEY,
   addr STRING NOT NULL,
   -- maybe something with locality addrs 
   -- maybe other metadata
   session_id BYTES NOT NULL
);

Inserts query:

WITH instances AS (SELECT * FROM sql_instances FOR UPDATE),
          deleted AS (
                    DELETE FROM sql_instances
                          WHERE NOT crdb_internal.sql_liveness_is_alive(session_id)
                      RETURNING id
                  ),
          id AS (
                SELECT id
                  FROM (
                         SELECT id FROM deleted
                         UNION ALL SELECT id
                                     FROM (
                                            SELECT generate_series(1, existing + 1, 1)
                                              FROM (SELECT count(id) AS existing FROM instances)
                                          )
                                    WHERE id NOT IN (SELECT id FROM instances)
                       )
                 LIMIT 1
             )
   INSERT
     INTO sql_instances
   SELECT id, $1 AS addr, $2 AS session_id
     FROM id
RETURNING id;

Describe alternatives you've considered

We could involve SQLInstanceID in the sqlliveness subsystem directly. There's something appealing about that; however,

Epic CRDB-2576

[ajwerner]

@knz here's a first take on the SQLInstanceID and basis for pod2pod communication. It deserves some close reading and iteration.

[knz]

oh sorry I hadn't seen that issue. thanks

[knz]

So this is the overarching issue, which we have decided to split into 3 sub-projects:

1. server: ensure SQL pods have unique instance IDs within the tenant server: ensure SQL pods have unique instance IDs within the tenant #64474

2. server: store instance ID and network addresses of SQL pods server: store instance ID and network addresses of SQL pods #64475

3. iterator:

   • server: supplement (*statusServer).iterateNodes() with a new iterateSQLPods() server: supplement (*statusServer).iterateNodes() with a new iterateSQLPods() #64477
   • rpc,security: set up TLS parameters for pod-to-pod communication rpc,security: set up TLS parameters for pod-to-pod communication #64476

[knz]

Completed.