Do these benchmark numbers look reasonable?

I’m doing some benchmarking of CRDB using SQL. My code is in Golang, with the pq driver, and I’ve tried to make my code as performant as possible.

The hardware is:
1 Client: AWS c5.2xlarge us-east-2a
3 Servers: AWS i3.4large us-east-2a, 2b, and 2c

The client machine has 128 connections to each server. (ulimit -n is 10000.)

All writes use 16-byte ASCII keys (for the key column) and 128-byte ASCII values (for the value column). All keys are unique. The order of inserts is random. The numbers reported are the arithmetic averages of throughputs after writing 500000 rows.

These are the experiments I ran:

  1. Key/value writes and reads, writes have 1 row
  • Schema:
    CREATE TABLE keyvalue (key VARCHAR PRIMARY KEY, val VARCHAR)

  • Prepared statements, executed in a 1:1 ratio:
    INSERT INTO keyvalue (key, val) VALUES ($1, $2)
    SELECT val FROM keyvalue WHERE key = $1 LIMIT 1

  • Observed performance:
    4429 rows written / sec
    4425 rows read / sec

  1. Key/value writes and reads, writes have 2 rows
  • Schema:
    CREATE TABLE keyvalue (key VARCHAR PRIMARY KEY, val VARCHAR)

  • Prepared statements, executed in a 1:2 ratio:
    INSERT INTO keyvalue (key, val) VALUES ($1, $2), ($3, $4)
    SELECT val FROM keyvalue WHERE key = $1 LIMIT 1

  • Observed performance:
    5881 rows written / sec
    5879 rows read / sec

  1. Key/value writes and reads, writes have 4 rows
  • Schema:
    CREATE TABLE keyvalue (key VARCHAR PRIMARY KEY, val VARCHAR)

  • Prepared statements, executed in a 1:4 ratio:
    INSERT INTO keyvalue (key, val) VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8)
    SELECT val FROM keyvalue WHERE key = $1 LIMIT 1

  • Observed performance:
    5816 rows written / sec
    5804 rows read / sec

  1. Key/value writes and reads, writes have 1 row, look up key by value, which has an index
  • Schema:
    CREATE TABLE keyvalue (key VARCHAR PRIMARY KEY, val VARCHAR)
    CREATE INDEX ON keyvalue (val, key)

  • Prepared statements, executed in a 1:1 ratio:
    INSERT INTO keyvalue (key, val) VALUES ($1, $2)
    SELECT key FROM keyvalue WHERE val = $1::text LIMIT 1

  • Observed performance:
    2328 rows written / sec
    2326 rows read / sec

Do these numbers look reasonable?

Hi @rwww,

What version of CockroachDB are you running?

Are you performing any range splits before running this test?

Thanks,
Matt

@mattvardi

We’re running CRDB 2.1.3, and we are not performing range splits.

Do those numbers look reasonable to you?

The numbers look low to me.

However, that depends on a few factors. I can suggest the following:

  1. Running this test on the latest stable version of CRDB.
  2. Performing manual range splits before the inserts(based on the knowledge of your keys).

Are you referring to the 128 connections as your concurrency?

I understand the key’s are unique. How are they generated? Using UUID’s should yield better performance.

Thanks,
Matt