cockroachdb / cockroachdb/cockroach
kv: count(*) query fails under serializable isolation
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
On the DRT cluster, we've observed that large `count(*)` queries fail when run under serializable isolation. We have yet to observe a similar failure under read committed. The error complains about the batch timestamp being before the replica GC threshold:
```
root@localhost:26257/application/cct_tpcc> select count(*) from order_line;
ERROR: batch timestamp 1706039299.373539955,1 must be after replica GC threshold 1706039413.927861585,0
```
Of note is that the `order_line` table has a `gc.ttlseconds` setting of 4 hours. This is important to note because the query is long-running - the read committed version of the query fairly consistently takes around two hours:
```
root@localhost:26257/application/cct_tpcc> select count(*) from order_line;
count
---------------
17614458879
(1 row)
Time: 6987.016s total (execution 6987.014s / network 0.002s)
root@localhost:26257/application/cct_tpcc> select count(*) from order_line;
count
---------------
17635454393
(1 row)
Time: 6703.116s total (execution 6703.115s / network 0.000s)
```
In instances where the serializable execution of the query succeeds, it takes considerably longer (though, still not near the 4 hour `gc.ttlseconds` setting):
```
root@localhost:26257/application/cct_tpcc> select count(*) from order_line;
count
---------------
17600486508
(1 row)
Time: 9925.820s total (execution 9925.819s / network 0.000s)
```
A couple of questions worth investigating:
1) Why does the query often fail when running under serializable?
2) Why does the query perform so much worse under serializable than it does under read committed?
To reproduce, first login to the DRT cluster's application tenant. Then :
```
use cct_tpcc
select count(*) from order_line
```
More details about the investigation can be found [here](https://cockroachlabs.slack.com/archives/C01RX2G8LT1/p1705959831317779).
Jira issue: CRDB-35668
Contributor guide
Assessment
This issue has not been assessed yet.