Is it reasonable: update noindex faster than update index

General
#1

When we run sysbench, we found that update no index records is much faster than update index records.
Is it reasonable?

the CockroachDB version is 2.0.2, update no index about 4x faster than update index.

Thanks.

#2

Hi Adun,

That sounds strange. Can you share your tables and queries? There are a couple reasons that this could happen, but I’d need to replicate the setup to confirm.

#3

hi Tim,

We use sysbench the stress CockroachDB:

  1. sysbench version: 1.0.13
  2. we run 5 sysbench, each sysbench has 32 threads
  3. sysbench prepare 10 tables with 100 million records each table
  4. 5 CockroachDB nodes with ssd, each node have 40 cpu cores and 256GB Memory
#4

Hi Adun

thanks for the extra detail. It would help if you would share the SQL of the UPDATE queries you are sending to the server. We need to figure out if you are affected by transaction contention:
https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html#understanding-and-avoiding-transaction-contention

#5
  1. sysbench command to prepare data:
/usr/bin/sysbench  /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-host=x.x.x.x --pgsql-port=26257 --pgsql-user=cockroach --pgsql-db=cockroach --table-size=30000000 --tables=40 --threads=8 --auto_inc=off --report-interval=120 prepare
  1. table schema:
CREATE TABLE sbtest1 (
  id INTEGER NOT NULL,
  k INTEGER NOT NULL DEFAULT 0:::INT,
  c STRING(120) NOT NULL DEFAULT '':::STRING,
  pad STRING(60) NOT NULL DEFAULT '':::STRING,
  CONSTRAINT "primary" PRIMARY KEY (id ASC),
  INDEX k_1 (k ASC),
  FAMILY "primary" (id, k, c, pad)
)
  1. sysbench command to run non index update:
sysbench  /usr/share/sysbench/oltp_update_non_index.lua --db-driver=pgsql --pgsql-host=x.x.x.x --pgsql-port=26257 --pgsql-user=cockroach --pgsql-db=cockroach --table-size=30000000 --tables=40 --threads=300 --report-interval=10 --time=600 --max-requests=0 --percentile=95 --skip-trx=on run
  1. example SHOW QUERIES for non index update:

UPDATE sbtest13 SET c = $1 WHERE id = $2

  1. sysbench command to run index update:
sysbench  /usr/share/sysbench/oltp_update_index.lua --db-driver=pgsql --pgsql-host=x.x.x.x --pgsql-port=26257 --pgsql-user=cockroach --pgsql-db=cockroach --table-size=30000000 --tables=40 --threads=300 --report-interval=10 --time=600 --max-requests=0 --percentile=95 --skip-trx=on run
  1. example SHOW QUERIES for index update:

UPDATE sbtest30 SET k = k + 1 WHERE id = $1

#6

Hi Adun,
thanks for the details!
In this specific set of queries, the performance difference is indeed expected.

  • In the “no index” case, the update only needs to “put” a single KV pair for (id, c).
  • In the “with index” case, the update must:
    • issue a KV “get” to read the current value of k (to compute k+1)
    • issue a KV “put” for (id, k) on the primary index
    • issue a KV “del” for the old value of (k, id) on the secondary index “k_1”
    • issue a KV “put” for the new value of (k,id) on the secondary index

So there are 4 times more operations in the latter case than the former.

#7

I would expect you would only see a 3x performance difference if the 2nd query was doing UPDATE ... SET k =$1 WHERE id = $2

(i.e. do not compute k + 1)

#8

Hi, Raphael,

thanks for the help!

I have tried to change the update index to UPDATE sbtest16 SET k = $1 WHERE id = $2,
the $1 is integer 1 in the test.

I am sorry, the performance haven’t improved, but a little regress.

results before modify:

[ 30s ] thds: 300 tps: 2116.50 qps: 2116.50 (r/w/o: 0.00/2116.50/0.00) lat (ms,95%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 2045.06 qps: 2045.06 (r/w/o: 0.00/2045.06/0.00) lat (ms,95%): 846.57 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 2025.93 qps: 2025.93 (r/w/o: 0.00/2025.93/0.00) lat (ms,95%): 802.05 err/s: 0.00 reconn/s: 0.00

after set k =$1:

[ 10s ] thds: 300 tps: 1792.49 qps: 1792.49 (r/w/o: 0.00/1792.49/0.00) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 300 tps: 1535.58 qps: 1535.58 (r/w/o: 0.00/1535.58/0.00) lat (ms,95%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 300 tps: 1397.40 qps: 1397.40 (r/w/o: 0.00/1397.40/0.00) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 300 tps: 1661.00 qps: 1661.00 (r/w/o: 0.00/1661.00/0.00) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 300 tps: 1665.30 qps: 1665.30 (r/w/o: 0.00/1665.30/0.00) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 300 tps: 1496.80 qps: 1496.80 (r/w/o: 0.00/1496.80/0.00) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 300 tps: 1492.57 qps: 1492.57 (r/w/o: 0.00/1492.57/0.00) lat (ms,95%): 877.61 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 300 tps: 1595.32 qps: 1595.32 (r/w/o: 0.00/1595.32/0.00) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00

differ for the sysbench’s oltp_common.lua:

261,262c269,270
<       "UPDATE sbtest%u SET k=k+1 WHERE id=?",
<       t.INT},
---
>       "UPDATE sbtest%u SET k=? WHERE id=?",
>       t.INT, t.INT},
456c464,465
<       param[tnum].index_updates[1]:set(get_id())
---
>       param[tnum].index_updates[1]:set(1)
>       param[tnum].index_updates[2]:set(get_id())
#9

Ok so the initial 4x performance drop was expected, but that a simple UPDATE without a prior read is now 5x slower is actually surprising. Do you mind filing an issue and detail how to reproduce the measurement? We need to look into it.

#10

issue: https://github.com/cockroachdb/cockroach/issues/26848

Best Regards.