Databases with Automatic Rebalance Benchmark (TIDB vs YugabyteDB vs CockroachDB)

Automatic rebalance/repair/self-healing (we can remove or add new node, and it will distribute the data and rebalance itself, data are replicated to more than 1 node). Previous benchmark doesn't really care about this awesome feature (no more cutoff downtime to kill master instance and promote slave as master then switch every client to connect to new master -- if not using any proxy).

Some databases that I found that support this feature:

1. Aerospike (in-mem kv database, community edition max 4 billion object) https://aerospike.com/products/product-matrix/
2. Couchbase (document database, there's community edition) https://www.couchbase.com/products/editions
3. MongoDB (document database, without SQL syntax)
4. Cassandra (columnar database, CQL a subset of SQL)
5. ScyllaDB (columnar database, cassandra-compatible, oss version max 5 node) https://www.scylladb.com/pricing/
6. CockroachDB (postgresql compatible, there's core edition) https://www.cockroachlabs.com/compare/
7. TiDB (mysql compatible)
8. YugaByteDB (postgresql/cassandra/redis compatible)
9. RavenDB (community edition max ram 6GB) https://ravendb.net/buy
10. SingleStore/MemSQL (mysql compatible, free edition max 4 node) https://www.singlestore.com/free-software/

Reproducibility

The repository are here: https://github.com/kokizzu/hugedbbench on the 2021 folder. We're going to test local single (if possible) and multi server deployment using docker. Why using docker? because i don't want to ruin my computer/server with trash files they are creating in system directory (if any). Some of databases not included if not supporting SQL or if a license key required to start. Why only benchmarking 2 column? because it fit my project's most common use case, where there's 1 PK (bigint or string), and 1 unique key (mostly string), and the rest mostly some indexed or non-indexed column. Why are you even doing this? Just want to select the best thing for my next side project's techstack (and because my past companies I've work with seems love to move around database server location a lot).

The specs for the server that used in this benchmark: 32-core 128GB RAM 500GB NVMe disk.

CockroachDB

CockroachDB is one of NewSQL movement that support PostgreSQL syntax, to deploy in single node we can use docker compose. The UI for cluster monitor on port 8080 is quite ok :3 better than nothing.

Here's the result for 100 inserts x 1000 goroutines:

Version used: v21.1.11

CockroachDB InsertOne 10.034616078s

CockroachDB Count 42.326487ms

CockroachDB UpdateOne 12.804722812s

CockroachDB Count 78.221432ms

CockroachDB SelectOne 2.281355728s

CockroachDB Total 25.2420225s

$ sudo du -h --max-depth 0 2021/cockroachdb/cockroach1

442M    2021/cockroachdb/cockroach1

CockroachDB InsertOne 7.125466063s

CockroachDB Count 39.753102ms

CockroachDB UpdateOne 10.221870484s

CockroachDB Count 70.624908ms

CockroachDB SelectOne 2.196985441s

CockroachDB Total 19.655920219s

432M    2021/cockroachdb/cockroach1

# multiple cockroach docker but connect only into one

# seems high availability (>1 RF) turned on by default

# but you have to init the cluster manually after docker-compose up

CockroachDB InsertOne 13.979257573s

CockroachDB Count 46.824883ms

CockroachDB UpdateOne 1m22.941738013s

CockroachDB Count 42.374814ms

CockroachDB SelectOne 2.676679427s

CockroachDB Total 1m39.687566436s

433M    2021/cockroachdb/cockroach1

292M    2021/cockroachdb/cockroach2

222M    2021/cockroachdb/cockroach3

TiDB

TiDB is one of NewSQL movement that support MySQL syntax, the recommended way is using tiup command, but we're going to use docker so it would be fair with other database product. The official docker use 3 placement driver and 3 kv server, so I try that first. The cluster monitor in port 10080 but it blocked by chrome, so I moved it on 10081, it's very plaintexty compared to other products.

Version used: 5.7.25-TiDB-v5.0.1

TiDB InsertOne 14.063953386s

TiDB Count 32.523526ms

TiDB UpdateOne 11.329688001s

TiDB Count 49.320725ms

TiDB SelectOne 2.110410282s

TiDB Total 27.601866351s

$ sudo du -h --max-depth 0 2021/tidb/t*/

24G     2021/tidb/tikv0/

24G     2021/tidb/tikv1/

24G     2021/tidb/tikv2/

123M    2021/tidb/tipd0/

123M    2021/tidb/tipd1/

123M    2021/tidb/tipd2/

TiDB InsertOne 13.434256392s

TiDB Count 44.192782ms

TiDB UpdateOne 12.575839233s

TiDB Count 63.126285ms

TiDB SelectOne 2.00257672s

TiDB Total 28.134319527s

24G     2021/tidb/tikv0/

24G     2021/tidb/tikv1/

24G     2021/tidb/tikv2/

123M    2021/tidb/tipd0/

62M     2021/tidb/tipd1/

62M     2021/tidb/tipd2/

# reducing to single server mode (1 pd, 1 kv, 1 db), first run:

TiDB InsertOne 3.216365486s

TiDB Count 34.30629ms

TiDB UpdateOne 3.913131711s

TiDB Count 62.202395ms

TiDB SelectOne 1.991229179s

TiDB Total 9.233077269s

24G     2021/tidb/tikv0/

62M     2021/tidb/tipd0/

YugaByteDB

YugaByteDB is one of NewSQL movement that support PostgreSQL syntax, to deploy in single node we can use docker compose too. The cluster monitor on port :7000 is quite ok. The tmp directory mounted because if it isn't it would stuck starting on 2nd time unless the temporary file manually deleted. limits.conf applied.

Version used: 2.9.1.0

YugaByteDB InsertOne 11.402609701s

YugaByteDB Count 159.357304ms

YugaByteDB UpdateOne 19.232827282s

YugaByteDB Count 214.389496ms

YugaByteDB SelectOne 2.778803557s

YugaByteDB Total 33.834838111s

$ sudo du -h --max-depth 0 2021/yugabytedb/yb*1

25M     2021/yugabytedb/ybmaster1

519M    2021/yugabytedb/ybtserver1

YugaByteDB InsertOne 13.536083917s

YugaByteDB Count 202.381009ms

YugaByteDB UpdateOne 20.78337085s

YugaByteDB Count 190.119437ms

YugaByteDB SelectOne 2.849347721s

YugaByteDB Total 37.607747856s

25M     2021/yugabytedb/ybmaster1

519M    2021/yugabytedb/ybtserver1

# multiple ybtserver but only connect to one

# replication factor 1, first run:

YugaByteDB InsertOne 15.260747636s

YugaByteDB Count 66.599257ms

YugaByteDB UpdateOne 26.246382158s

YugaByteDB Count 63.119089ms

YugaByteDB SelectOne 3.213271599s

YugaByteDB Total 44.90095282s

25M     2021/yugabytedb/ybmaster1

242M    2021/yugabytedb/ybtserver1

156M    2021/yugabytedb/ybtserver2

132M    2021/yugabytedb/ybtserver3

# after changing replication factor to 2, first run:

YugaByteDB InsertOne 38.614091068s

YugaByteDB Count 76.615212ms

YugaByteDB UpdateOne 56.796680169s

YugaByteDB Count 84.35411ms

YugaByteDB SelectOne 3.14747611s

YugaByteDB Total 1m38.756226195s

26M     2021/yugabytedb/ybmaster1

343M    2021/yugabytedb/ybtserver1

349M    2021/yugabytedb/ybtserver2

349M    2021/yugabytedb/ybtserver3

# after changing replication factor to 3, first run:

YugaByteDB InsertOne 45.289805293s

YugaByteDB Count 97.112383ms

YugaByteDB UpdateOne 54.665380464s

YugaByteDB Count 64.206741ms

YugaByteDB SelectOne 3.125693618s
YugaByteDB Total 1m43.290014042s

26M     2021/yugabytedb/ybmaster1/

513M    2021/yugabytedb/ybtserver1/

512M    2021/yugabytedb/ybtserver2/

512M    2021/yugabytedb/ybtserver3/

Conclusion

Here's the recap of 100 records x 1000 goroutine insert/update/select duration, only for single instance:

Only Best of 2 runs	CockroachDB (single)	TiDB (single)	YugaByteDB (single)
InsertOne (s)	7.1	(best) 2.9	(worst) 11.4
UpdateOne (s)	10.2	(best) 3.9	(worst) 19.2
SelectOne (s)	2.1	(best) 1.9	(worst) 2.7
Total (s)	19.6	(best) 9.2	(worst) 33.8
Disk Usage (MB)	(best) 432	(worst) 24062	544

So, at best, it roughly on average take 29 μs to insert, 39 μs to update, 19 μs to select one record.

Comparing only multi (RF=2+):

Only Best of 2 runs	CockroachDB (multi)	TiDB (multi)	YugaByteDB (multi)	YugaByteDB (multi 2)	YugaByteDB (multi 3)
InsertOne (s)	12.5	(best) 3.1	14.3	34.9	(worst) 45.2
UpdateOne (s)	(worst) 60.1	(best) 4.1	19.6	52.7	54.6
SelectOne (s)	(worst) 3.1	(best) 2.1	2.8	(worst) 3.1	2.9
Total (s)	77.2	(best) 9.5	40.8	91.7	(worst) 103.2
Disk Usage (MB)	1015	(worst) 72247	(best) 521	1046	1563

So, at best, it roughly on average take 31 μs to insert, 41 μs to update, 21 μs to select one record.

Comparing only multi with replication factor with true HA:

Only Best of 2 runs	CockroachDB (multi)	TiDB (multi)	YugaByteDB (multi 3)
InsertOne (s)	13.9	(best) 3.1	(worst) 45.2
UpdateOne (s)	(worst) 82.9	(best) 4.1	54.6
SelectOne (s)	2.6	(best) 2.1	2.9
Total (s)	(worst) 99.6	(best) 9.5	(worst) 103.2
Disk Usage (MB)	(best) 1015	(worst) 72247	1563

It seems TiDB has most balanced performance in expense the need to have pre-allocated disk space, while CockroachDB has worst performance on multi-instance update task, and YugabyteDB has worst performance on multi-instance insert task.

What happened if we do the benchmark once more, remove one storage node (docker stop), then redo the benchmark (only for RF=2+)?

Yugabytedb test doesn't even entering the insert stage after 5 minutes '__') may be because of truncate is slow? so I changed the benchmark scenario only for yugabyte to be 1 node be killed after 2 seconds of insertion phase, but still yugabyte giving an error "ERROR: Timed out: Write RPC (request call id 3873) to 172.21.0.5:9100 timed out after 60.000s (SQLSTATE XX000)", it cannot complete. EDIT yugabyte staff on slack suggested that it should be using RF=3 so it would still survive when one node died.

Only 1 run	CockroachDB (multi, kill 1)	TiDB (multi, kill 1 tikv)	TiDB (multi, kill 1 tipd)	TiDB (multi, kill 1 tikv 1 tipd)	YugaByteDB (multi 3, kill 1)
InsertOne (s)	(worst) 35.9	19.2	14.3	(best) 9.2	34.8
UpdateOne (s)	18.6	11.6	16.0	(best) 9.9	(worst) 68.8
SelectOne (s)	4.0	1.9	(best) 1.8	2.3	3.1
Total (s)	58.8	32.9	34.0	(best) 21.5	(worst) 106.9
Disk Usage (MB)	(best) 1076	(worst) 72364	72308	72308	2659

TiDB seems to be the winner also for case when a node died, in expense of the need of 7 initial node (1 tidb [should be at least 2 for HA], 3 tipd, 3 tikv, but probably can be squeezed to be 1 tidb, 1 tipd, 2 tikv, since apparently the default replication factor is 3), where cockroachdb only need 3, and yugabytedb need 4 (1 ybmaster, 3 ybserver). Not sure tho what would happened if 1 tidb/ybmaster instance is died. The recap spreadsheet are here.

Next time we're gonna test how simple is it to add and remove node (and securely, if possible only limited set of servers can join without have to set firewall/DMZ to restrict unprivileged servers) then re-benchmark with more complex common use case (like UPSERT, range queries, WHERE-IN, JOIN, and secondary index). If automatic rebalance not in the requirement, I would still use Tarantool (since 2020.09) and Clickhouse (since 2021.04), but now I found one more new favorite automatic-rebalance database other than Aerospike (since 2016.11), :3 myahaha! So this is probably the reason lots of companies moving to TiDB.

Btw do not comment on this blog (since it's too much spammy comment and there's no notification whether new comment added), just use github issue or reddit instead.

UPDATE #1: redo the benchmark for all database after updating the limits.conf, TiDB improved by a lot, while CockroachDB remains the same except for update benchmark.

 

UPDATE #2: TiKV can be set to use only a little of initial disk space by setting "reserve-space" it would only use 4.9GB per TiKV after the test

 

 

Against Golang Interface{Method}-abuse/pollution

As you already know, after doing a lot of maintenance work of other people's code, I don't like to follow blindly so called "best practice" or popular practice that are proven painful in the long run when it's followed blindly/doesn't fit project's use case, eg.

• using dynamically-typed language (JS, Python, PHP, Ruby, etc) just because it's the most popular language -- only for short/discardable project
• mocking -- there's better way
• microservice without properly splitting domain -- modular monolith is better for small teams, introducing network layer just to split a problem without properly assessing surely will be a hassle in a short and long run
• overengineering -- eg. adding stack that you don't need when current stack suffice, for example,  dockerizing or kubernetesizing just because everyone using it, adding ElasticSearch just because it's search use case, but the records needs to be searched are very little and rps are very low, a more lightweight aproach more  make sense: eg. TypeSense or MeiliSearch or even database's built-in FTS are more make sense for lower rps target/simpler search feature.
• premature "clean architecture" -- aka. over-layering everything that you'll are almost never replace -- dependency tracking is better
• unevaluated standard -- sticking with standard just because it's a standard, just like being brainwashed/peer-pressured by dead people's will (tradition) without rethinking is it still make sense to be followed for this use case?
• not making SRS/Software Requirement Specification (roles/who can do what action/API) and SDS/Software Design Specification (this action/API will mutate/command or read/query which datastore or hit which 3rd party) -- this helps new guy to be onboarded to the project really fast

I have one more unpopular opinion, interface (-overuse) in Golang is almost always bad for jumping around (jump to declaration-implementation) inside source code which causes everyday overhead when reading code and debugging. For example when you want to create a fake/mock/stub of certain method:

type Bla interface { 
  Get(string) string
  Set(string)
}

struct RealBla struct {} // wraps a 3rd party/client library
func (*RealBla) Get(string) string { return `` }
func (*RealBla) Set(string) { }

struct FakeBla struct {} // our fake/stub/mock implementation
func (*FakeBla) Get(string) string { return `` }
func (*FakeBla) Set(string) { }

// usage

func TestBla(t *testing.T) {
   var b Bla = FakeBla{...}
   // usually as data member of other method that depends on RealBla
   b.Set(...)
   x := b.Get(...)   
}

func main() {
   var b Bla = RealBla{...}
   b.Set(...)
   x := b.Get(...)
}

the problem with this approach is, it's harder to jump around between declaration and implementation (usually RealBla that we want, not FakeBla), how often we switch implementation anyway? YAGNI (vs overengineering). It's better for our cognitive/understanding that we keep both coupled, this violates single responsibility principle from SOLID, but it's easier to reason/understand, since the real and fake are in the same file and near each other, so we can catch bug easily without have to switch, something like this:

struct BlaWrapper {

  // declare/use 3rd party client here

  UseFake bool
  // create fake/in-mem here

}

func (b *BlaWrapper) Get(s string) string {
  if b.UseFake {
    // do with fake

    return
  }
  // do with real 3rd party
}

func (b  *BlaWrapper) Set(s string) {
  if b.UseFake {
    // do with fake

    return
  }
  // do with real 3rd party

}

// usage

func TestBla(t *testing.T) {
  var b = BlaWrapper{UseFake:true,...}

  b.Set(...)
  x := b.Get(...)
}

func TestBla(t *testing.T) {
  var b = BlaWrapper{...}

  b.Set(...)
  x := b.Get(...)
}

by doing this, we could compare easily between our fake and real implementation (you could easily spot the bug, whether your fake implementation differ way too much from real implementation), and we can still jump around simply by ctrl+click the IDE on that function since there's only 1 implementation. The only pros I could see from doing interface-based is when you are creating a 3rd party library (eg. io.Writer, io.Reader, etc) and you have more than 2 implementation (DRY only good when its more than 2), but since you're only making this for internal project that could be easily refactored within the project itself, it doesn't make sense to abuse interface. See more tips from this video: Go Worst Practice. 

After all being said, I won't use this kind of thing (UseFake property) for testing databases (2nd party), because I prefer to do integration (contract-based) testing instead of unit testing, since i'm using a fast database anyway (not a slow but popular RDBMSes).