Created
January 23, 2026 19:14
-
-
Save kmoppel/d16d188f992aaebc29c800e59432bbbf to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SYNC_COMMIT=on | |
| SCALE=100 # ~2 GB with FF 70%, should fit in-mem for disk testing | |
| FF=70 # Aim for HOT updates only to reduce randomness | |
| CLIENTS=2 | |
| DURATION=300 | |
| JOBS=2 | |
| set -e | |
| echo -e "\n\n************* STARTING track_commit_timestamp TEST************\n" | |
| echo "SYNC_COMMIT=$SYNC_COMMIT SCALE=$SCALE FF=$FF CLIENTS=$CLIENTS DURATION=$DURATION JOBS=$JOBS" | |
| date | |
| psql -X -c "create extension if not exists pg_prewarm" -c "create extension if not exists pg_stat_statements" | |
| echo -e "\n*** track_commit_timestamp = OFF ***\n" | |
| psql -X -c "alter system set track_commit_timestamp to off" | |
| sudo systemctl restart postgresql@18-main.service | |
| echo "pgbench -iq -s $SCALE -F $FF &>/dev/null" | |
| pgbench -iq -s $SCALE -F $FF &>/dev/null | |
| psql -X -c "select pg_size_pretty( pg_total_relation_size( 'pgbench_accounts' ) )" | |
| psql -X -c checkpoint -c "select pg_prewarm( 'pgbench_accounts' )" -c "select pg_stat_statements_reset()" | |
| echo "PGOPTIONS='-c synchronous_commit=$SYNC_COMMIT' pgbench --random-seed=666 -N -M prepared -c $CLIENTS -j $JOBS -T $DURATION" | |
| PGOPTIONS="-c synchronous_commit=$SYNC_COMMIT" pgbench --random-seed=666 -N -M prepared -c $CLIENTS -j $JOBS -T $DURATION | tee pgbench_results_off.log | |
| TPS1=$( egrep '^tps = ([0-9\.]+)' pgbench_results_off.log | egrep -o '[0-9\.]+' ) | |
| psql -X -c "select ( 100::numeric * shared_blks_hit / ( shared_blks_hit + shared_blks_read ) )::numeric(5,2) sb_hit_pct, calls, mean_exec_time, query from pg_stat_statements where query ~ '^(SELECT|UPDATE).*pgbench_accounts.*WHERE' order by 1 desc ;" | |
| echo -e "\n*** track_commit_timestamp = ON ***\n" | |
| psql -X -c "alter system set track_commit_timestamp to on" | |
| sudo systemctl restart postgresql@18-main.service | |
| echo "pgbench -iq -s $SCALE -F $FF &>/dev/null" | |
| pgbench -iq -s $SCALE -F $FF &>/dev/null | |
| psql -X -c "select pg_size_pretty( pg_total_relation_size( 'pgbench_accounts' ) )" | |
| psql -X -c checkpoint -c "select pg_prewarm( 'pgbench_accounts' )" -c "select pg_stat_statements_reset()" | |
| echo "PGOPTIONS='-c synchronous_commit=$SYNC_COMMIT' pgbench --random-seed=666 -N -M prepared -c $CLIENTS -j $JOBS -T $DURATION" | |
| PGOPTIONS="-c synchronous_commit=$SYNC_COMMIT" pgbench --random-seed=666 -N -M prepared -c $CLIENTS -j $JOBS -T $DURATION | tee pgbench_results_on.log | |
| TPS2=$( egrep '^tps = ([0-9\.]+)' pgbench_results_on.log | egrep -o '[0-9\.]+' ) | |
| psql -X -c "select ( 100::numeric * shared_blks_hit / ( shared_blks_hit + shared_blks_read ) )::numeric(5,2) sb_hit_pct, calls, mean_exec_time, query from pg_stat_statements where query ~ '^(SELECT|UPDATE).*pgbench_accounts.*WHERE' order by 1 desc ;" | |
| echo -e "\n=== track_commit_timestamp=on PENALTY: ===\n" | |
| echo "TPS with track_commit_timestamp=off: $TPS1" | |
| echo "TPS with track_commit_timestamp=on: $TPS2" | |
| psql -X -c "select 100::numeric * ( $TPS2 - $TPS1) / $TPS1 as pct_diff" | |
| echo "Done" | |
| date |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment