What is the best way to query the cockroachDB with high currency?

Hi guys,

I am seeing a big performance degradation when qps is high (randomly fired off 2000 qps from a 16 CPU gke node) with following code to query a k-v table in a cockroachdb with 5 16 CPU gke nodes :

Seeing the cockroach admin dashboard with

  • 12.6 ms p50
  • 906.0 ms p99
  • 1300 qps (seem 700 qps are stuck in the box)

However the latency from the box running following code is around

  • 200 ms median
  • 2k ms 95percentile

Cpu load is at ~30 but cpu utility is only around 3% for that box.
The end-to-end timeout is 5 secs so from the stats I only see very few queries got cancelled.

And when the qps is below 500, it seem both p99 on dashboard and 95percentile from the box is around 10ms level and very close to each other.

Did I miss something here?

stmt := fmt.Sprintf("SELECT %s FROM %s WHERE %s =", conf.ValColName, conf.TableName, conf.KeyColName)

queryStart := time.Now()
row := p.DB.QueryRowContext(ctx, fmt.Sprintf("%s '%s'", stmt, key))
statsd.RecordDuration(factorsDBDuration, time.Since(queryStart))
var v string
err := row.Scan(&v)
switch {
case err == sql.ErrNoRows:
	statsd.IncrCount(factorsQueryMissCnt)
	return "", false
case ctx.Err() != nil:
	statsd.IncrCount(factorsQueryCxtCancelCnt)
	return "", false
case err != nil:
	statsd.IncrCount(factorsQueryErrorCnt)
	return "", false
default:
	statsd.IncrCount(factorsQueryHitCnt)
	return v, true
}

Not sure if related, I have seen file descriptors limit need to be tuned here https://www.cockroachlabs.com/docs/stable/recommended-production-settings.html#cluster-topology
Not sure how this applies to kubernetes setting with persistent volumes

Is this a good example to do it?
where SPLIT AT and SCATTER was used but cannot find good documentation about them.

Hi Jiale,

SPLIT AT and SCATTER are some fairly advanced tuning knobs; they are used in that test to manipulate the data on a cluster in order to properly test fully distributed data. That works well in an environment like a test, where the expected inputs are limited and fully known, but it might be less effective in a production environment.

To answer what they do:

  • SPLIT AT splits a table into multiple ranges at the given key. "Splits"in this case are a low-level concept that affects how data in a table is distributed across the cluster; it does not affect the logical view of the table at all. Normally, splits happen when a table grows beyond a certain size, but “SPLIT AT” can be used to pre-split a table into multiple ranges.
  • SCATTER is used to randomly distribute the ranges of a table between nodes on the cluster. When a range of a table splits, it creates two new ranges (a right and left side). Initially, both new ranges are on the same nodes as the parent range; the cluster will eventually balance them to different nodes based on load. SCATTER forces this process to happen immediately. This is not something you should need to typically need to do on your own: CockroachDB will automatically rebalance your ranges across the cluster based on load. It is only used in this testing environment to force it to happen in an immediate time frame.

In terms of making your application faster, some recommendations:

  • Make sure your table is indexed by the field conf.KeyColName. That will make each individual SELECT much, much faster (otherwise each select will do a full table scan).
  • Try to have the lookup key randomly distributed across the table; see (this documentation)[https://www.cockroachlabs.com/docs/stable/uuid.html#create-a-table-with-auto-generated-unique-row-ids] for an example of one way to do this. This will help ensure that your incoming requests are evenly distributed between the different ranges of your table.
  • Connect to the CockroachDB cluster through a load balancer, so that incoming client requests are distributed evenly across your nodes. If all of your incoming requests are serviced by a single node, its CPU may be overloaded while other nodes sit with idle resources.

Finally, if your load is evenly distributed and you are still running out of resources, consider adding more nodes; CockroachDB does scale horizontally with this type of workload, and adding additional nodes can raise your QPS potential.

Keep in mind that no matter how well you distribute your application load, there will still be a point where cluster performance will red-line. 2000qps does seem low and you likely have opportunities to improve that, but there will always be a limit.