4 ms·

How does cockroach compares in terms of performance to manual sharded databases ? My intuition is that a properly sharded database will perform faster-or-same

by bsaul 4y ago

How does cockroach compares in terms of performance to manual sharded databases ? My intuition is that a properly sharded database will perform faster-or-same as a non-sharded one in all scenarios. Whereas automatically-sharded database will actually perform worst until you start reaching critical traffic that a single instance won't handle no matter what. Am i wrong ?

jandrewrogers 4y ago
Where the line gets blurry is that there can be layers of sharding inside the database even if it is never exposed to the end user. At the limit, disk pages are essentially a shard and treated similarly. Some database kernels do true sharding and resharding within a single server transparently for performance reasons, it isn't just for having data on multiple servers or cores. There is no intrinsic performance penalty for automatic sharding, it can be as fast or faster than any other scheme. The "faster" cases are when the sharding structure must be adaptive to have uniform distribution of data and load, such as when the key distribution is intrinsically unpredictable or when you are doing multi-attribute sharding. In these cases, effective sharding can only be created by software at runtime.
hilbertseries 4y ago
> My intuition is that a properly sharded database will perform faster-or-same as a non-sharded one in all scenarios. This is workload dependent, if your queries only go to one shard, this is likely true. But if you have cross shard queries, then it’s no longer true.
crazygringo 4y ago
> My intuition is that a properly sharded database will perform faster-or-same as a non-sharded one in all scenarios. No, because as soon as you have to JOIN across shards performance plummets. I mean, you can't JOIN across shards so you have to simulate that client-side which is very slow. The secret to performant manual sharding is to figure out a way to minimize JOINs across shards, and that the ones you have to do are very small. Or to start replicating data which quickly becomes its own nightmare of consistency.
- rad_gruchalski 4y ago
  You can join like that with YugabyteDB. But it would be good to get intimate with explain analyze.
arjunnarayan 4y ago
[disclosure, former cockroachdb engineer] you can get expected "single shard" performance in CockroachDB by manually splitting the shards (called "ranges" in CockroachDB) along the lines of the expected single shard queries (what you call a "properly shared database"). This is easy to do with a single SQL command. (This is what we do today; we use CockroachDB for strongly consistent metadata). The difference between CockroachDB and a manually sharded database is that when you _do_ have to perform some cross-shard transactions (which you inevitably have to do at some point), in CockroachDB you can execute them (with a reasonable performance penalty) with strong consistency and 2PC between the shards, whereas in your manually sharded database... good luck! Hope you implement 2PC correctly.
- dravita 4y ago
  The point about strong consistency and 2PC cross-shard is a good one. Even among other "auto-sharding" distributed relational databases, not all of them provide that.

by bsaul 4y ago

jandrewrogers 4y ago

Where the line gets blurry is that there can be layers of sharding inside the database even if it is never exposed to the end user. At the limit, disk pages are essentially a shard and treated similarly. Some database kernels do true sharding and resharding within a single server transparently for performance reasons, it isn't just for having data on multiple servers or cores. There is no intrinsic performance penalty for automatic sharding, it can be as fast or faster than any other scheme. The "faster" cases are when the sharding structure must be adaptive to have uniform distribution of data and load, such as when the key distribution is intrinsically unpredictable or when you are doing multi-attribute sharding. In these cases, effective sharding can only be created by software at runtime.

hilbertseries 4y ago

> My intuition is that a properly sharded database will perform faster-or-same as a non-sharded one in all scenarios. This is workload dependent, if your queries only go to one shard, this is likely true. But if you have cross shard queries, then it’s no longer true.

crazygringo 4y ago

> My intuition is that a properly sharded database will perform faster-or-same as a non-sharded one in all scenarios. No, because as soon as you have to JOIN across shards performance plummets. I mean, you can't JOIN across shards so you have to simulate that client-side which is very slow. The secret to performant manual sharding is to figure out a way to minimize JOINs across shards, and that the ones you have to do are very small. Or to start replicating data which quickly becomes its own nightmare of consistency.

rad_gruchalski 4y ago
You can join like that with YugabyteDB. But it would be good to get intimate with explain analyze.

arjunnarayan 4y ago

[disclosure, former cockroachdb engineer] you can get expected "single shard" performance in CockroachDB by manually splitting the shards (called "ranges" in CockroachDB) along the lines of the expected single shard queries (what you call a "properly shared database"). This is easy to do with a single SQL command. (This is what we do today; we use CockroachDB for strongly consistent metadata). The difference between CockroachDB and a manually sharded database is that when you _do_ have to perform some cross-shard transactions (which you inevitably have to do at some point), in CockroachDB you can execute them (with a reasonable performance penalty) with strong consistency and 2PC between the shards, whereas in your manually sharded database... good luck! Hope you implement 2PC correctly.

dravita 4y ago
The point about strong consistency and 2PC cross-shard is a good one. Even among other "auto-sharding" distributed relational databases, not all of them provide that.