Skip to content

Instantly share code, notes, and snippets.

@dbist

dbist/cockroachdb-pgbench.md

Last active November 30, 2023 21:00
Show Gist options
  • Save dbist/30d50d3ef32409be1456be94cfaffe00 to your computer and use it in GitHub Desktop.
Save dbist/30d50d3ef32409be1456be94cfaffe00 to your computer and use it in GitHub Desktop.
Download ZIP
Optimizing PgBench for CockroachDB Part 3
Raw
cockroachdb-pgbench.md

Optimizing pgbench for CockroachDB Part 3

This is a long anticipated third article on optimizing pgbench for CockroachDB. I've been wanting to write a follow up ever since I learned about new improvements in the next release of CockroachDB.

Previous articles

Previous articles on PgBench and CockoachDB:

Motivation

For the longest time CockroachDB only supported serializable isolation. In our next release after 23.1, CockroachDB will additionally support read committed isolation. The idea here is that users coming from other RDBMS tend to face many challenges with application migrations due to the excessive contention stemming from serializable isolation. To ease the migration woes, read committed isolation will provide an easy on-ramp for those applications. Since read committed isolation is less strict, it has additional benefits of being more performant at the cost of data anomalies. Today, we're going to take everything we've learned so far in my series and apply to read committed isolation.

High Level Steps

  • Deploy a CockroachDB cluster
  • Capture the baseline using serializable isolation
  • Compare to read committed isolation
  • Conclusion

Step by step instructions

Deploy a CockroachDB cluster

I've designed this tutorial to be reproduceable, in fact I have a Docker environment you may use to try or use our serverless tier, the same way as I did in the original article.

Capture the baseline using serializable isolation

Let's create a user we are going to use to execute PgBench 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

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 readers will notice that I am not initializing the benchmark with foreign keys, i.e. --foreign-keys. Though nothing prevents me from using the foreign keys constraints, since read committed is still work in progress, it does not currently demonstrate the performance benefits compared to serializable. In fact, my previous attempts testing read committed had shown serializable was more performant than the old implementation of read committed in CockroachDB. As the engineering works out the kinks, I imagine read committed will only get better with time.

The next step is to run PgBench using the built-in tpc-b workload without optimizations.

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)

Nothing surpising here as we've seen these retries in my previous articles. PgBench introduced retry logic in the version 15 and we can see the workload retry until transactions succeed.

Let's run our optimization from the previous article. You can find the workload in my 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)

Notice a much cleaner run but we knew this already from the second article.

Compare to read committed isolation

We're now going to 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 connect to the cluster using the user account for foo and confirm which isolation is being used.

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

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)

Notice how well read committed isolation handles contention in this run. In our original execution, there were 21 retries and even though PgBench retried those transactions, some transactions had to be reprocessed, thereby wasting compute. Here however, those retries didn't even surface. We also processed 100 more transactions 12982 vs 12883 in the original run. This is not significant in a small scale test like mine but every bit counts when these tests are on larger scale.

The next step we're going to 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)

Voila, we are processing 16484 transactions with read committed vs. 15945 with serializable. 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 better experience with read committed.

For posterity, let me show you that foreign keys are supported with read committed and PgBench.

We do have to switch back to serializable isolation as we have to initialize the workload again and currently 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).

let's switch to read committed first

Built-in workload 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)

Eventhough read committed handles the contention, it still underperforms compared to the execution without foreign keys. Hopefully with further enhancement work in read committed implementation this improves.

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

As you see, read committed has performance advantages to serializable isolation, and as bugs get worked out, it will only get better. Considering the trade-offs with read committed isolation, if your application can tolerate the anomalies, it will provide a much better on-ramp experience for applications.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment