Optimizing PgBench for CockroachDB Part 3
CockroachDB will support Read Committed isolation starting with the next release. I am going to test PgBench with RC in today's article.
By
CORE
·
·
Tutorial
CORE
·
Likes
(2)
Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!
Comment
Save
4.9K Views
Join the DZone community and get the full member experience.
Join For FreePrevious Articles
Previous articles on PgBench and CockoachDB:
- Using PgBench with CockroachDB Serverless
- Optimizing PgBench for CockroachDB Part 1
- Optimizing PgBench for CockroachDB Part 2
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 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 the additional benefit 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 it 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 reproducible; 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.
MySQL
CREATE USER foo WITH CREATEDB;
GRANT ALL ON DATABASE example TO foo;Initialize the Benchmark
Shell
docker exec -it postgresql pgbench \
--initialize \
--host=${PGHOST} \
--username=foo \
--port=${PGPORT} \
--no-vacuum \
--scale=10 \
${PGDATABASE}
Shell
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 a 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.
Shell
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
Shell
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)There's nothing surprising here, as we've seen these retries in my previous articles. PgBench introduced retry logic in 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.
Shell
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
Shell
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.
MySQL
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.
Shell
foo@lb:26000/defaultdb> SHOW default_transaction_isolation;
default_transaction_isolation
---------------------------------
read committedWe can rerun the benchmark using the built-in workload and read committed.
Shell
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
Shell
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 a larger scale.
In the next step, we're going to be running the optimized workload with read committed.
Shell
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
Shell
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 a 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.
MySQL
ALTER USER foo SET default_transaction_isolation = 'serializable';
Shell
docker exec -it postgresql pgbench \ --initialize --foreign-keys \
--host=${PGHOST} \
--username=foo \
--port=${PGPORT} \
--no-vacuum \
--scale=10 \
${PGDATABASE}
Shell
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:
Shell
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
Shell
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)Even though 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 will improve.
Optimized workload:
Shell
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
Shell
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.
CockroachDB
Docker (software)
optimization
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments