How to due with a frequently write and read table to avoid bottleneck using CockroachDB?

The table is around 20GB with about 100 more ranges that spread in 9 nodes.
But the new data are written just to one node, from which new data are always queried.
So that, this single node may encounter bottleneck in given interval time.
How to due with it?
How to let the new data are processed with other nodes evenly?

Could you run show create table <table name>; on the table involved.

Also need more information on the type of work that happens on this table. Can you show the queries you use to insert/update/select?

Thank You!

CREATE TABLE trade_history (

orderid INT8 NOT NULL,

accountid INT8 NULL,

ordertimestamp INT8 NULL,

price DECIMAL(36,9) NULL,


“primary” PRIMARY KEY (orderid ASC),

INDEX trade_history_ordertimestamp_idx ( ordertimestamp ASC), INDEX),
(some other index’s statements)

range_min_bytes = 134217728,
range_max_bytes = 536870912,
gc.ttlseconds = 90000,
num_replicas = 3,
constraints = [’’],
lease_preferences = [[’’]]

The select stament look like:

SELECT * FROM trade_history

WHERE ordertimestamp >= $1

AND ordertimestamp <= $2

AND accountid = $3

ORDER BY ordertimestamp

L imit 1000;

The constraint of the order timestamp is between 24 hours.

Frequently update and insert request are happening to this table at trade time.

Let’s take with this scenarios:

There are 9 nodes with the cluster, num_replicas=3, range_min_bytes=128M,

kv.range_split.by_load_enabled=true, kv.range_split.load_qps_threshold=2500.

If the full-work-load of a single node is 10,000 write/read request per second,

What will happen when it encounter with 12,000 write/read request per second, but the range’s size is 60M or smaller?

How the cluster rebalances the write/read request with this range’s leaseholder when the range’s size is smaller than 60M?

As the Docs, the leaseholder is attached to a single node, that will take the node encounter bottleneck.

Hi Lewis,

The reason I asked to see the table and the workload is because I suspected you were using sequentially incrementing values, and it looks like you are. I’m going to assume that the ordertimestamp field is a value which always increases as time passes, and if there’s a lot of activity around the same time, then the same node will get hit because those values are grouped together in the same range as part of the trade_history_ordertimestamp_idx index. Similarly, with an integer orderid, I’m going to again assume that increases by one for each new order, so again new entries always go to the same range and therefore the same node.

This blog explains it better than I can, so I suggest you read this thoroughly, then try and add some hash sharded indexes on orderid and ordertimestamp.

1 Like

yes,that’s the case. thank you!

while using hash shared indexes, such as:
create index on trade_history(ordertimestamp) using hash with bucket_count=5;

Is there any effects on the table’s old data?

will it shard the data into different ranges with the scenarios of normal but not frequently request of write at daily time?

The old data won’t be affected. A new index will be built which may take a while depending on the amount of data in the table.

It doesn’t really affect ranges. If you have a very small table with just one range, you will still have one range after applying the hash. What happens is that data with an incremental value isn’t added at just one position in the keyspace any more, but at 5 different points in the keyspace (5 is based on your given bucket count).

When the range does start splitting due to the usual conditions (size and/or load) and you have many ranges, then you’ll be dividing all the work among 5 different ranges instead of just one like before.

Cockroach will also move lease holders around to keep the workload balanced amongst all nodes, so you will likely have 5 ranges being added/modified across 5 nodes. However, that will depend on what other work the cluster is doing.

1 Like