24.3 QA: PCR DistSQL.md

QA Plan - DistSQL with PCR

Background

Reads from stand-by virtual clusters (VCs) are supported by creating another read-only virtual cluster. The original replicating VC is offline and cannot be accessed while we can connect to the reader VC to read data that is "external" to it (but "internal" to the replicating one). This makes it so that some of the observability features (e.g. crdb_internal.ranges virtual table) can be misleading because they show the state of the reader key space (which is empty) and not of the actual stand-by VC (which is non-empty).

More background on the feature can be found in the design doc.

Setup 3 node demo cluster with PCR Read from Standby enabled

cockroach demo --empty --nodes 3 --insecure

We'll create a table with 30k rows and a reduced range size so that we get 8 ranges per index.

\c cluster:demoapp

CREATE TABLE t (
  k INT PRIMARY KEY,
  a INT,
  b INT,
  data STRING,
  INDEX a_idx (a, data)
);

ALTER TABLE t CONFIGURE ZONE USING range_max_bytes = 67108864, range_min_bytes = 2097152;

INSERT INTO t (k, a, b, data)
SELECT i, (random()*30000)::INT, 1, repeat('a', 10000)
FROM generate_series(1, 10000) AS g(i);

INSERT INTO t (k, a, b, data)
SELECT i, (random()*30000)::INT, 1, repeat('a', 10000)
FROM generate_series(10001, 20000) AS g(i);

INSERT INTO t (k, a, b, data)
SELECT i, (random()*30000)::INT, 1, repeat('a', 10000)
FROM generate_series(20001, 30000) AS g(i);

Setting up PCR with reads from the stand-by.

\c cluster:system

CREATE VIRTUAL CLUSTER standby FROM REPLICATION OF demoapp ON 'demo://system' WITH READ VIRTUAL CLUSTER;
-- wait until replicated_time is non-NULL
SHOW VIRTUAL CLUSTERS WITH REPLICATION STATUS;

Testing

\c cluster:standby-readonly

-- Without streamer.
SELECT count(*) FROM t WHERE b > 0;
-- Use EXPLAIN (VEC) to confirm the physical plan. Note that the range cache on the reader
-- cluster needs to be populated already (which is done by the query right above)
EXPLAIN (VEC) SELECT count(*) FROM t@a_idx WHERE b > 0;
EXPLAIN ANALYZE (DEBUG) SELECT count(*) FROM t@a_idx WHERE b > 0;


-- With streamer.
SELECT count(*) FROM t@a_idx WHERE b > 0;
EXPLAIN (VEC) SELECT count(*) FROM t@a_idx WHERE b > 0;
EXPLAIN ANALYZE (DEBUG) SELECT count(*) FROM t@a_idx WHERE b > 0;

Are there any problems with streamer's concurrency and creation of fresh RootTxns?

• No, things seem to be fine because we're creating fresh and separate RootTxns while the concurrent usage of the same RootTxn is not allowed. ✅

Do physical plans looks reasonable / similar to what we get on the demoapp VC?

• ‼️ Physical plan on standby oscillates between two variants while we always get the same on the source. Looking at the stmt bundle we see that the unexpected plan is when we used follower reads, so let's disable them for consistency with the source.

\c cluster:system

SET CLUSTER SETTING kv.closed_timestamp.follower_reads.enabled = false;

Once the range cache of the reader is populated according to the actual range placement in the stand-by, physical plans look good. ✅

Examine the stmt bundles and the trace.

All files are there, trace looks right. ✅