Cockroach DB Slow Writes/Inserts Compared To MariaDB & Timescale DB

[Zub4ir]

Hi,

I work in an investment house where we process time series data and fund position data for quantitative analytics and data science. We query about 1800 time series (4.5mil rows), run some calculations and insert the results multiple times per hour. About 200 of these processes run throughout the day. We also have on-the-fly reports/analysis/optimizations generated on our analytics platform.

I replicated a simple business use case as a benchmark for MariaDB, CRDB and Timescale DB. I setup pretty much standard tables for all three - no fancy indexes or hyper tables from timescale. I used two types of data sets

• time series data - long narrow tables, star schema, 1 fact table for long time series data, and 3 dim tables
• wide tables data - long wide tables, not efficiently stored, various data types, 2 tables

Both Maria DB and Timescale is "single node". CRDB is running as 3 nodes.

Can anyone explain the differences here, specifically as to why CRDB is so slow with inserts?

All inserts/queries were run in Python3 using sqlalchemy create_engine , pandas read_sql_query and DataFrame.to_sql. Below is the elapsed time and rows per second (r/s) for each benchmark. Less time elapsed and more r/s is better.

I know this is probably lacking in detail which I can share. But understand that these are simple setups and using out-of-the-box configs. I do not want to jump through hoops to optimize given that other products are out-of-the-box faster with simpler and more convenient setups.

[zjyuan]

I think it's because crdb is a distributed newsql database. Your data in crdb is stored in a sorted map. This sorted map is divided into ranges. Each range is replicated 3 times by default. So for each write, there is overhead of replication compared to monolithic database.

[Zub4ir]

I ran it on a single node setup as well. Performance was slightly worse than 3 node.

[zjyuan]

I am a learner of crdb and would like to help. Would you mind to share your data and setup?

[gnat]

Cockroach is going to need more hardware to scale your writes- Your replication factor determines your "quorum group" size: you need more "quorum groups".

As of the current day, v23, those numbers are fairly decent for one "quorum group".

You get stronger INSERT performance by:

• Only creating indexes you absolutely need because each index is +1 INSERT (each index is another small table.. this can feel like a performance cliff if you have many).
• Leverage gen_random_uuid() as it can write right away and bypass the "quorum group" consistency checks.

I wish CockroachDB had a way to trade consistency for INSERT speed when desired, like ScyllaDB, or have more types that bypassed the quorum check like ULID. Any patterns to help increase performance are much welcomed.