Optimizing Pgbench for Cockroach DB Part 3 

Optimizing pgbench for cockroach DB
Kibho Login StaffBlogNo Comments on Optimizing Pgbench for Cockroach DB Part 3 

Optimizing pgbench for cockroach DB, for a long time, only supported serializable isolation. It additionally also supports read committed isolation. The users coming from other RDBMS tend to face many challenges with application migrations due to the excessive contention stemming from serializable isolation. This committed isolation will provide an easy run of those applications. Read the committed isolation is less strict and also has the additional benefits of being more performant at the cost of data anomalies. Let’s read the article and see high-level steps. 

High-level steps 

  • The first step is to deploy a CockroachDB cluster. 
  • Then, capture the baseline using serializable isolation.
  • After this, compare to reading committed isolation

Step by step instructions

Let’s see the step by step instructions.

Deploy a cockroachDB cluster. 

This tutorial is designed to be reproducible. It also has a docker environment, which you can use to try it. 

Capture the baseline using serializable isolation.

We will create a user that we are going to use to execute pgDench, as this will allow us to assign session properties controlling the isolation level. 

CREATE USER foo WITH CREATEDB;
GRANT ALL ON DATABASE example TO foo;

Initialize the benchmark

Optimizing pgbench for cockroach DB
docker exec -it postgresql pgbench \
    --initialize \
    --host=${PGHOST} \
    --username=foo \
    --port=${PGPORT} \
    --no-vacuum \
    --scale=10 \
    ${PGDATABASE}
dropping old tables...
creating tables...
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 1.87 s, remaining 0.00 s)
creating primary keys...
done in 32.49 s (drop tables 0.01 s, create tables 0.09 s, client-side generate 5.11 s, primary keys 27.28 s).

Some of you will notice that we are not initializing the benchmark with foreign keys, for example, – – foreign – keys. Nothing will prevent us from using the foreign keys constraints, read committed is still a work in progress, and it does not currently demonstrate the performance benefits compared to serializable. Attempts to test read committed had shown that serializable was more performant than the old implementation of read committed in CockroachDB. 

You will see the next step, which is to run PgBench using the built-in tpc-b workload without optimizations. 

Also Read:- Optimizing Server Management with HAProxy Advanced Health Checks | Build a Philosophy Quote Generator with Vector Search and Astra DB (Part 3)

docker exec -it postgresql \
 pgbench \
    --host=${PGHOST} \
    --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
pgbench: client 5 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/5/0 at timestamp 1701374364.725565161,1 too old; must write at or above 1701374364.783286327,1: "sql txn" meta={id=fe1456c4 key=/Table/109/5/569793/0 iso=Serializable pri=0.00522733 epo=0 ts=1701374364.783286327,1 min=1701374364.518938744,0 seq=2} lock=true stat=PENDING rts=1701374364.725565161,1 wto=false gul=1701374365.018938744,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 5 repeats the transaction after the error (try 1/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374364.881843744,1 too old; must write at or above 1701374364.902487702,1: "sql txn" 
meta={id=1eb035c5 key=/Table/109/5/110840/0 iso=Serializable pri=0.00276957 epo=0 ts=1701374364.902487702,1 min=1701374364.803162244,0 seq=2} lock=true stat=PENDING rts=1701374364.881843744,1 wto=false gul=1701374365.303162244,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 6 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/1/0 at timestamp 1701374365.916704036,1 too old; must write at or above 1701374365.933405161,1: "sql txn" meta={id=cf8b382e key=/Table/109/5/279511/0 iso=Serializable pri=0.00573224 epo=0 ts=1701374365.933405161,1 min=1701374365.839919870,0 seq=2} lock=true stat=PENDING rts=1701374365.916704036,1 wto=false gul=1701374366.339919870,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 6 repeats the transaction after the error (try 1/3)
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374367.028168703,1 too old; must write at or above 1701374367.082025953,1: "sql txn" meta={id=b37541b7 key=/Table/109/5/43919/0 iso=Serializable pri=0.01583132 epo=0 ts=1701374367.082025953,1 min=1701374366.918740453,0 seq=2} lock=true stat=PENDING rts=1701374367.028168703,1 wto=false gul=1701374367.418740453,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
progress: 5.0 s, 238.8 tps, lat 33.228 ms stddev 23.762, 0 failed, 4 retried, 4 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.162224122,1 too old; must write at or above 1701374371.200365997,1: "sql txn" meta={id=9b00388b key=/Table/109/5/173940/0 iso=Serializable 
pri=0.01393375 epo=0 ts=1701374371.200365997,1 min=1701374370.906479830,0 seq=2} lock=true stat=PENDING rts=1701374371.162224122,1 wto=false gul=1701374371.406479830,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.200365997,1 too old; must write at or above 1701374371.221628122,1: "sql txn" meta={id=cecdb1ab key=/Table/109/5/548267/0 iso=Serializable pri=0.00346401 epo=0 ts=1701374371.221628122,1 min=1701374370.973058997,0 seq=2} lock=true stat=PENDING rts=1701374371.200365997,1 wto=false gul=1701374371.473058997,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/7/0 at timestamp 1701374371.308297414,2 too old; must write at or above 1701374371.385186164,1: "sql txn" meta={id=98bf761d key=/Table/109/5/173940/0 iso=Serializable pri=0.00580461 epo=0 ts=1701374371.385186164,1 min=1701374371.234436747,0 seq=2} lock=true stat=PENDING rts=1701374371.308297414,2 wto=false gul=1701374371.734436747,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 2/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374373.132044165,1 too old; must write at or above 1701374373.157914498,1: "sql txn" meta={id=8def4300 key=/Table/109/5/477477/0 iso=Serializable pri=0.00196323 epo=0 ts=1701374373.157914498,1 min=1701374373.001536956,0 seq=2} lock=true stat=PENDING rts=1701374373.132044165,1 wto=false gul=1701374373.501536956,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
progress: 10.0 s, 225.6 tps, lat 35.522 ms stddev 30.196, 0 failed, 3 retried, 4 retries
progress: 15.0 s, 238.0 tps, lat 33.552 ms stddev 20.915, 0 failed, 0 retried, 0 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/6/0 at timestamp 1701374380.138378168,2 too old; must write at or above 1701374380.184955710,1: "sql txn" meta={id=c6ab1fec key=/Table/109/5/796957/0 iso=Serializable pri=0.00219624 epo=0 ts=1701374380.184955710,1 min=1701374379.949286210,0 seq=2} lock=true stat=PENDING rts=1701374380.138378168,2 wto=false gul=1701374380.449286210,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374381.532448085,1 too old; must write at or above 1701374381.532448085,2: "sql txn" meta={id=ab78ff5d key=/Table/109/5/129631/0 iso=Serializable pri=0.02671145 epo=0 ts=1701374381.532448085,2 min=1701374381.403506460,0 seq=2} lock=true stat=PENDING rts=1701374381.532448085,1 wto=false gul=1701374381.903506460,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 7 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/10/0 at timestamp 1701374381.970990086,1 too old; must write at or above 1701374382.002286294,1: "sql txn" meta={id=a3720795 key=/Table/109/5/796844/0 iso=Serializable pri=0.01176172 epo=0 ts=1701374382.002286294,1 
min=1701374381.842546002,0 seq=2} lock=true stat=PENDING rts=1701374381.970990086,1 wto=false gul=1701374382.342546002,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 7 repeats the transaction after the error (try 1/3)
progress: 20.0 s, 215.8 tps, lat 36.918 ms stddev 26.072, 0 failed, 3 retried, 3 retries
progress: 25.0 s, 216.2 tps, lat 37.140 ms stddev 25.942, 0 failed, 0 retried, 0 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374390.624237798,1 too old; must write at or above 1701374390.675212798,1: "sql txn" meta={id=1e77e781 key=/Table/109/5/487347/0 iso=Serializable pri=0.01765513 epo=0 ts=1701374390.675212798,1 min=1701374390.436830756,0 seq=2} lock=true stat=PENDING rts=1701374390.624237798,1 wto=false gul=1701374390.936830756,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/10/0 at timestamp 1701374391.884443715,1 too old; must write at or above 1701374391.909561465,1: "sql txn" meta={id=1c27478f key=/Table/109/5/424569/0 iso=Serializable pri=0.03662449 epo=0 ts=1701374391.909561465,1 min=1701374391.702494549,0 seq=2} lock=true stat=PENDING rts=1701374391.884443715,1 wto=false gul=1701374392.202494549,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 7 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/3/0 at timestamp 1701374393.572487549,1 too old; must write at or above 1701374393.572487549,2: "sql txn" 
meta={id=fdacfb83 key=/Table/109/5/730699/0 iso=Serializable pri=0.03673262 epo=0 ts=1701374393.572487549,2 min=1701374393.376278716,0 seq=2} lock=true stat=PENDING rts=1701374393.572487549,1 wto=false gul=1701374393.876278716,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 7 repeats the transaction after the error (try 1/3)
progress: 30.0 s, 209.0 tps, lat 38.151 ms stddev 31.989, 0 failed, 3 retried, 3 retries
progress: 35.0 s, 195.0 tps, lat 40.949 ms stddev 28.077, 0 failed, 0 retried, 0 retries
pgbench: client 1 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/4/0 at timestamp 1701374399.887792969,1 too old; must write at or above 1701374399.911607302,1: "sql txn" meta={id=855e0eca key=/Table/109/5/165844/0 iso=Serializable pri=0.01764135 epo=0 ts=1701374399.911607302,1 min=1701374399.542153677,0 seq=2} lock=true stat=PENDING rts=1701374399.887792969,1 wto=false gul=1701374400.042153677,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 1 repeats the transaction after the error (try 1/3)
progress: 40.0 s, 145.6 tps, lat 54.621 ms stddev 38.881, 0 failed, 1 retried, 1 retries
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374408.252897556,1 too old; must write at or above 1701374408.252897556,2: "sql txn" meta={id=3c1d5707 key=/Table/109/5/271506/0 iso=Serializable pri=0.00554444 epo=0 ts=1701374408.252897556,2 min=1701374408.060554931,0 seq=2} lock=true stat=PENDING rts=1701374408.252897556,1 wto=false gul=1701374408.560554931,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
progress: 45.0 s, 204.8 tps, lat 39.241 ms stddev 36.493, 0 failed, 1 retried, 1 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374413.178464892,1 too old; must write at or above 1701374413.221088309,1: "sql txn" meta={id=f1f5cf8b key=/Table/109/5/382061/0 iso=Serializable pri=0.00426220 epo=0 ts=1701374413.221088309,1 min=1701374413.072555142,0 seq=2} lock=true stat=PENDING rts=1701374413.178464892,1 wto=false gul=1701374413.572555142,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
progress: 50.0 s, 227.0 tps, lat 35.512 ms stddev 26.517, 0 failed, 1 retried, 1 retries
pgbench: client 4 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374417.439743852,1 too old; must write at or above 1701374417.512724644,1: "sql txn" meta={id=ceae42c3 key=/Table/109/5/647391/0 iso=Serializable pri=0.00061239 epo=0 ts=1701374417.512724644,1 min=1701374417.170008102,0 seq=2} lock=true stat=PENDING rts=1701374417.439743852,1 wto=false gul=1701374417.670008102,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 4 repeats the transaction after the error (try 1/3)
pgbench: client 5 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374417.557735311,1 too old; must write at or above 1701374417.583758061,1: "sql txn" meta={id=921ad19c key=/Table/109/5/292793/0 iso=Serializable pri=0.01234524 epo=0 ts=1701374417.583758061,1 min=1701374417.337672519,0 seq=2} lock=true stat=PENDING rts=1701374417.557735311,1 wto=false gul=1701374417.837672519,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 5 repeats the transaction after the error (try 1/3)
progress: 55.0 s, 227.2 tps, lat 35.162 ms stddev 26.932, 0 failed, 2 retried, 2 retries
pgbench: client 3 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/9/0 at timestamp 1701374420.093249854,1 too old; must write at or above 1701374420.125789020,1: "sql txn" meta={id=254d2603 key=/Table/109/5/924387/0 iso=Serializable pri=0.01254729 epo=0 ts=1701374420.125789020,1 min=1701374419.940787687,0 seq=2} lock=true stat=PENDING rts=1701374420.093249854,1 wto=false gul=1701374420.440787687,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 3 repeats the transaction after the error (try 1/3)
pgbench: client 2 got an error in command 8 (SQL) of script 0; ERROR:  restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError: write for key /Table/110/5/2/0 at timestamp 1701374421.256457729,1 too old; must write at or above 1701374421.285648938,1: "sql txn" meta={id=8a6584e6 key=/Table/109/5/931266/0 iso=Serializable pri=0.00333353 epo=0 ts=1701374421.285648938,1 min=1701374421.081633729,0 seq=2} lock=true stat=PENDING rts=1701374421.256457729,1 wto=false gul=1701374421.581633729,0
HINT:  See: https://www.cockroachlabs.com/docs/v23.2/transaction-retry-error-reference.html
pgbench: client 2 repeats the transaction after the error (try 1/3)
progress: 60.0 s, 232.0 tps, lat 34.473 ms stddev 23.692, 0 failed, 2 retried, 2 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 12883
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 20 (0.155%)
total number of retries: 21
latency average = 37.261 ms
latency stddev = 28.630 ms
initial connection time = 22.223 ms
tps = 214.593890 (without initial connection time)

The Pgbench introduced retry logic in the version 15 and we can see the workload retry until transactions succeed. Now run our optimization from the previous article. who want the workload they can find it in the docker repo.

docker exec -it postgresql pgbench \ 
    --host=${PGHOST} \
    --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 305.4 tps, lat 25.998 ms stddev 22.118, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 297.2 tps, lat 26.940 ms stddev 28.511, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 279.8 tps, lat 28.628 ms stddev 29.997, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 279.0 tps, lat 28.608 ms stddev 26.844, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 265.8 tps, lat 29.923 ms stddev 36.855, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 160.0 tps, lat 50.258 ms stddev 79.162, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 187.2 tps, lat 42.830 ms stddev 42.750, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 293.4 tps, lat 27.239 ms stddev 26.405, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 275.8 tps, lat 29.087 ms stddev 33.249, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 284.8 tps, lat 28.038 ms stddev 30.830, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 279.8 tps, lat 28.593 ms stddev 29.569, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 279.2 tps, lat 28.617 ms stddev 30.770, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 15945
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 30.114 ms
latency stddev = 35.211 ms
initial connection time = 7.962 ms
tps = 265.499744 (without initial connection time)

After this, compare to read committed isolation. Now we will switch to read committed for the user foo . 

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true; 
ALTER USER foo SET default_transaction_isolation = 'read committed';

We can also connect to the cluster using the user account for foo and then confirm which isolation is being used. 

foo@lb:26000/defaultdb> SHOW default_transaction_isolation;                                                                            
  default_transaction_isolation
---------------------------------
  read committed

Also, we can rerun the benchmark using the built-in workload and read committed.

docker exec -it postgresql \            
 pgbench \
    --host=${PGHOST} \
  --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
progress: 5.0 s, 227.0 tps, lat 35.042 ms stddev 18.674, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 213.8 tps, lat 37.408 ms stddev 20.119, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 217.8 tps, lat 36.751 ms stddev 19.447, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 215.8 tps, lat 36.922 ms stddev 19.299, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 217.4 tps, lat 36.918 ms stddev 19.705, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 216.2 tps, lat 36.902 ms stddev 20.906, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 205.8 tps, lat 38.992 ms stddev 24.295, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 211.6 tps, lat 37.757 ms stddev 21.103, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 220.2 tps, lat 36.309 ms stddev 21.630, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 217.6 tps, lat 36.876 ms stddev 20.284, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 212.8 tps, lat 37.545 ms stddev 20.558, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 218.8 tps, lat 36.568 ms stddev 19.725, 0 failed, 0 retried, 0 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 12982
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 36.985 ms
latency stddev = 20.521 ms
initial connection time = 8.775 ms
tps = 216.191364 (without initial connection time)

You have to notice how well read committed isolation handles contention in this run. In the original execution, we see there were 21 retries and even though PgBench retried those transactions, some transactions had to be reprocessed, thereby  wasting computing. However, those retries didn’t even surface. After this, we will also process 100 more transactions 12982 vs 12993 in the original run. In a small scale test it is not significant, a small scale test like mine but every bit counts when these are on a larger scale. 

The next step is we will be running the optimized workload with read committed.

docker exec -it postgresql pgbench \    
    --host=${PGHOST} \    
 --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 283.0 tps, lat 28.122 ms stddev 25.956, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 271.0 tps, lat 29.569 ms stddev 28.935, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 265.8 tps, lat 30.030 ms stddev 29.334, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 261.4 tps, lat 30.605 ms stddev 27.032, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 280.0 tps, lat 28.631 ms stddev 27.362, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 286.4 tps, lat 27.781 ms stddev 27.500, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 281.0 tps, lat 28.577 ms stddev 25.379, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 281.4 tps, lat 28.299 ms stddev 23.310, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 270.6 tps, lat 29.701 ms stddev 30.521, 0 failed, 0 retried, 0 retries

progress: 50.0 s, 271.6 tps, lat 29.482 ms stddev 27.495, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 268.6 tps, lat 29.640 ms stddev 26.566, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 274.4 tps, lat 29.274 ms stddev 29.799, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 16484
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 29.125 ms
latency stddev = 27.490 ms
initial connection time = 5.965 ms
tps = 274.515057 (without initial connection time)

In this voila, we are processing 16484 transactions with read committed vs 15945 with serializable. Then again there are trade-offs with data anomalies but if performance is a concern and the legacy application is built to work with read committed isolation, you will have a better experience with read committed. We will show you that foreign keys are supported with read committed and PgBench for posterity. We have to switch back to serializable isolation as we have to initialize the workload again and currently, the truncate statement is not supported with read committed.

ALTER USER foo SET default_transaction_isolation = 'serializable';
docker exec -it postgresql pgbench \    
    --initialize \
    --foreign-keys \          
    --host=${PGHOST} \
    --username=foo \
    --port=${PGPORT} \
    --no-vacuum \
    --scale=10 \
    ${PGDATABASE}
dropping old tables...
creating tables...
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
NOTICE:  storage parameter "fillfactor" is ignored
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 2.82 s, remaining 0.00 s)
creating primary keys...
creating foreign keys...
done in 38.08 s (drop tables 0.21 s, create tables 0.06 s, client-side generate 5.81 s, primary keys 30.57 s, foreign keys 1.44 s).

First, let’s switch to read committed. Build a workload by using explicit transactions.

docker exec -it postgresql \         
  pgbench \                                 
    --host=${PGHOST} \
   --no-vacuum \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
pgbench: warning: scale option ignored, using count from pgbench_branches table (10)
progress: 5.0 s, 200.0 tps, lat 39.704 ms stddev 21.335, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 193.6 tps, lat 41.307 ms stddev 21.592, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 199.4 tps, lat 40.088 ms stddev 19.551, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 197.0 tps, lat 40.730 ms stddev 21.442, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 204.8 tps, lat 38.967 ms stddev 19.367, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 198.4 tps, lat 40.368 ms stddev 22.252, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 196.8 tps, lat 40.791 ms stddev 22.013, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 192.4 tps, lat 41.471 ms stddev 21.291, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 198.8 tps, lat 40.249 ms stddev 21.359, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 193.0 tps, lat 41.309 ms stddev 22.361, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 193.8 tps, lat 41.319 ms stddev 21.147, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 190.6 tps, lat 42.024 ms stddev 22.474, 0 failed, 0 retried, 0 retries
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 11800
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 40.687 ms
latency stddev = 21.375 ms
initial connection time = 11.911 ms
tps = 196.451948 (without initial connection time)

You have seen, the read committed handles the contention, but it still underperforms compared to the execution without foreign keys. This improves the further enhancement work in read committed implementation. 

This is the optimized workload. 

docker exec -it postgresql pgbench \
    --host=${PGHOST} \
   --no-vacuum \
    --file=tpcb-cockroach.sql@1 \
    --client=8 \
    --jobs=8 \
    --username=foo \
    --port=${PGPORT} \
    --scale=10 \
    --failures-detailed \
    --verbose-errors \
    --max-tries=3 \
    ${PGDATABASE} \
    -T 60 \
    -P 5
pgbench (16.1 (Debian 16.1-1.pgdg120+1), server 13.0.0)
progress: 5.0 s, 233.2 tps, lat 34.079 ms stddev 26.993, 0 failed, 0 retried, 0 retries
progress: 10.0 s, 200.7 tps, lat 39.841 ms stddev 36.275, 0 failed, 0 retried, 0 retries
progress: 15.0 s, 220.3 tps, lat 36.399 ms stddev 29.333, 0 failed, 0 retried, 0 retries
progress: 20.0 s, 226.2 tps, lat 35.320 ms stddev 29.787, 0 failed, 0 retried, 0 retries
progress: 25.0 s, 220.0 tps, lat 36.370 ms stddev 28.896, 0 failed, 0 retried, 0 retries
progress: 30.0 s, 222.4 tps, lat 35.921 ms stddev 30.289, 0 failed, 0 retried, 0 retries
progress: 35.0 s, 214.0 tps, lat 37.306 ms stddev 34.033, 0 failed, 0 retried, 0 retries
progress: 40.0 s, 216.2 tps, lat 37.187 ms stddev 31.789, 0 failed, 0 retried, 0 retries
progress: 45.0 s, 227.6 tps, lat 35.093 ms stddev 31.301, 0 failed, 0 retried, 0 retries
progress: 50.0 s, 230.2 tps, lat 34.797 ms stddev 29.103, 0 failed, 0 retried, 0 retries
progress: 55.0 s, 226.2 tps, lat 35.291 ms stddev 30.328, 0 failed, 0 retried, 0 retries
progress: 60.0 s, 217.6 tps, lat 36.763 ms stddev 29.471, 0 failed, 0 retried, 0 retries
transaction type: tpcb-cockroach.sql
scaling factor: 10
query mode: simple
number of clients: 8
number of threads: 8
maximum number of tries: 3
duration: 60 s
number of transactions actually processed: 13279
number of failed transactions: 0 (0.000%)
number of serialization failures: 0 (0.000%)
number of deadlock failures: 0 (0.000%)
number of transactions retried: 0 (0.000%)
total number of retries: 0
latency average = 36.154 ms
latency stddev = 30.686 ms
initial connection time = 7.224 ms
tps = 221.166896 (without initial connection time)

Conclusion: Optimizing pgbench for cockroach DB

You see, the read committed has performance advantages to sewable isolation, and as bugs get worked out it will get better. You will consider the trade-offs with read committed isolation if your application can tolerate the anomalies, which will provide a much better on-ramp experience for applications. This is all the information, I hope you like this information.

Optimizing Pgbench for Cockroach DB Part 3 

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top