CockroachDB - research

[PiotrKozimor]

Recently I researched cockroachDB as a database for dendrite. Theoretically it is wire-compatible with postgres. The benefit is better scalability and data resilience.

Experiment setup

I use k8s cluster with local SSDs, 3 cockroach instances are run by operator on n2d-standard-2 machines. Postgres is run in cluster too. I switch between databases by applying different configmaps (which are consumed by dendrite deployment) and then restarting deployment. Postgres and dendrite were assigned to different nodes by k8s scheduler. Tracing is configured as follows:

tracing:
  enabled: true
  jaeger:
    serviceName: "dendrite"
    disabled: false
    rpc_metrics: true
    tags: []
    reporter:
      localAgentHostPort: my-jaeger-agent:6831
    sampler:
      type: const
      param: 1

Latency between k8s nodes is as follows:

# ping -i 0.005 -c 100 10.154.0.24
--- 10.154.0.24 ping statistics ---
100 packets transmitted, 100 received, 0% packet loss, time 494ms
rtt min/avg/max/mdev = 0.040/0.060/0.817/0.076 ms

# ping -i 0.005 -c 100 10.154.0.25
--- 10.154.0.25 ping statistics ---
100 packets transmitted, 100 received, 0% packet loss, time 496ms
rtt min/avg/max/mdev = 0.047/0.063/0.108/0.013 ms

Compatibility

Dendrite uses some SQL extensions specific to postgres that are not implemented in cockroach. Required changes were introduced in commits 37a6463 ...73d0963 in https://github.com/globekeeper/dendrite/commits/feature/cockroachdb-support. These are rather small changes that don't impact implementation.

Performance

I have modified dendrite so that individual SQL statements are traced (commits 8f97a62 to 9fb8b2a in the branch above).
Performance is severely degraded with cockroach. Please compare these two traces:

postgres.pdf

cockroach.pdf

Sending message takes around 30 ms for postgres. For cockroach it is about 370 ms (!). Here are my conclusions so far:

• dendrite makes a lot of queries to send a message
• traces up to StoreEvent takes three times more on cockroach which is acceptable to me.
• InsertEvent is the statement that takes the most time to execute. For postgres it's 1.46 ms and for cockroach it's 59.3 ms.
• In the DB console I can see that INSERT INTO roomserver_events takes about 23 ms:
  
• SetState takes way too much time. UPDATE roomserver_events statement takes 1 ms to execute (looking at the cockroach console)
• I think that the GetLatestEventsForUpdate span might be wrong or misleading.

[tomquas]

@PiotrKozimor awesome work! i was thinking about this, too, while reading about several cloud providers including cockroachdb to their portfolio. operating (postgres || a relational database) is still hard and i'd love to see the dendrite guys pushing for optimizations here.

[PiotrKozimor]

The other nice part about CockroachDB is that you have DB updates with zero downtime - it works well with retry logic in sql golang package. I could just constantly send messages while the CRDB cluster was upgrading, I didn't encounter a single error.

[djradon]

CockroachDB would be great! I wonder if anyone has tried AWS Aurora Serverless as a backend?

[0x0000F1]

I wonder if switching to a embedded db such as pebbledb would be beneficial? From my perspective it seems like it would reduce stress on servers running dendrite which would allocate more resources to services that would need it.

[gedw99]

+1 for cockroachdb.

Has anyone tried it ? it might just work. Just have to change minor things i expect