Skip to content

Instantly share code, notes, and snippets.

@NikolayS

NikolayS/becnh_m5.2xlarge.out

Last active November 17, 2021 21:52
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save NikolayS/e6bc0132d749e29efcb8ee10d059748d to your computer and use it in GitHub Desktop.
Save NikolayS/e6bc0132d749e29efcb8ee10d059748d to your computer and use it in GitHub Desktop.
Download ZIP
pgbench: simple INSERTs, UPDATEs with and without triggers
Raw
becnh_m5.2xlarge.out
# m5.2xlarge 32.0 GiB 8 vCPUs
# s=100, n=10
*** Only SELECTs, -T 30 -j4 -c12
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 5216283
latency average = 0.069 ms
tps = 173790.562064 (including connections establishing)
tps = 173806.448112 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.005 \set aid random(1, 100000 * :scale)
0.058 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
*** Mixed load, -T 30 -j4 -c12
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 734142
latency average = 0.491 ms
tps = 24451.605824 (including connections establishing)
tps = 24453.869337 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.003 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.044 BEGIN;
0.083 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.064 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.082 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.092 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.063 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.056 END;
*** Only INSERTs, -T 30 -j4 -c12
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 2737537
latency average = 0.132 ms
tps = 91234.430670 (including connections establishing)
tps = 91242.171952 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.004 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.030 begin;
0.045 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.047 end;
*** Only UPDATEs, -T 30 -j4 -c12
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 1926720
latency average = 0.187 ms
tps = 64218.885017 (including connections establishing)
tps = 64224.607529 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.036 begin;
0.103 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.047 end;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_
since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count |
analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------
--------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+-
--------------+-------------------
16388 | public | pgbench_tellers | 1 | 1000 | 2660862 | 2660862 | 1000 | 2660862 | 0 | 2660822 | 1000 | 2416 |
1703678 | 2018-07-08 21:29:52.509255+00 | 2018-07-08 21:29:56.381897+00 | 2018-07-08 21:28:08.814494+00 | 2018-07-08 21:29:56.40407+00 | 5 | 1 |
1 | 1
16391 | public | pgbench_accounts | 1 | 10000000 | 6684567 | 6684567 | 10000000 | 734142 | 0 | 734142 | 10000037 | 222470 |
734142 | 2018-07-08 21:28:10.203631+00 | | 2018-07-08 21:28:10.343934+00 | | 1 | 0 |
1 | 0
16385 | public | pgbench_history | 0 | 0 | | | 3471679 | 0 | 0 | 0 | 0 | 0 |
0 | 2018-07-08 21:28:10.354697+00 | | 2018-07-08 21:28:10.354851+00 | 2018-07-08 21:29:56.425069+00 | 1 | 0 |
1 | 1
16394 | public | pgbench_branches | 734145 | 73414500 | 0 | 0 | 100 | 734142 | 0 | 734091 | 100 | 0 |
0 | 2018-07-08 21:29:52.508837+00 | | 2018-07-08 21:28:08.813811+00 | 2018-07-08 21:29:56.414615+00 | 5 | 0 |
1 | 1
(4 rows)
The end.
Raw
becnh_m5.4xlarge.out
# m5.4xlarge 64.0 GiB 16 vCPUs
# s=200, n=20
*** Only SELECTs, -T 30 -j4 -c12
transaction type: <builtin: select only>
scaling factor: 200
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 8152041
latency average = 0.044 ms
tps = 271733.538529 (including connections establishing)
tps = 271756.989492 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.044 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
*** Mixed load, -T 30 -j4 -c12
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 200
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 965509
latency average = 0.373 ms
tps = 32183.135820 (including connections establishing)
tps = 32185.751994 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.025 BEGIN;
0.059 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.040 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.062 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.110 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.040 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.035 END;
*** Only INSERTs, -T 30 -j4 -c12
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 3771155
latency average = 0.095 ms
tps = 125704.414175 (including connections establishing)
tps = 125714.549393 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.023 begin;
0.040 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.030 end;
*** Only UPDATEs, -T 30 -j4 -c12
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 2517477
latency average = 0.143 ms
tps = 83915.246163 (including connections establishing)
tps = 83922.746282 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.025 begin;
0.082 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.035 end;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+-----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
16388 | public | pgbench_tellers | 1 | 2000 | 3482986 | 3482986 | 2000 | 3482986 | 0 | 3482961 | 2000 | 3707 | 2517477 | 2018-07-08 21:41:28.711411+00 | | 2018-07-08 21:39:37.228834+00 | 2018-07-08 21:41:17.482133+00 | 5 | 0 | 1 | 1
16391 | public | pgbench_accounts | 1 | 20000000 | 10083059 | 10083059 | 20000000 | 965509 | 0 | 965509 | 20000031 | 406749 | 965509 | 2018-07-08 21:39:40.080833+00 | | 2018-07-08 21:39:40.207156+00 | | 1 | 0 | 1 | 0
16385 | public | pgbench_history | 0 | 0 | | | 4736664 | 0 | 0 | 0 | 0 | 0 | 941732 | 2018-07-08 21:39:40.217827+00 | | 2018-07-08 21:39:40.217967+00 | 2018-07-08 21:41:19.862628+00 | 1 | 0 | 1 | 1
16394 | public | pgbench_branches | 965512 | 193102400 | 0 | 0 | 200 | 965509 | 0 | 965276 | 200 | 0 | 0 | 2018-07-08 21:41:28.710906+00 | | 2018-07-08 21:39:37.227955+00 | 2018-07-08 21:41:17.492718+00 | 5 | 0 | 1 | 1
(4 rows)
The end.
Raw
becnh_x1.16xlarge.out
# -j1 -c200 !
*** Only SELECTs, -T 30 -j1 -c200
transaction type: <builtin: select only>
scaling factor: 700
query mode: prepared
number of clients: 200
number of threads: 1
duration: 30 s
number of transactions actually processed: 5033074
latency average = 1.192 ms
tps = 167751.784628 (including connections establishing)
tps = 167767.204043 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
1.167 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
*** Mixed load, -T 30 -j1 -c200
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 700
query mode: prepared
number of clients: 200
number of threads: 1
duration: 30 s
number of transactions actually processed: 516159
latency average = 11.629 ms
tps = 17198.449115 (including connections establishing)
tps = 17200.068994 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
1.597 BEGIN;
1.634 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
1.585 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
1.637 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
1.746 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
1.587 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.577 END;
*** Only INSERTs, -T 30 -j1 -c200 [21/1641]
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 1
duration: 30 s
number of transactions actually processed: 1807327
latency average = 0.199 ms
tps = 60243.227970 (including connections establishing)
tps = 60248.764823 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.059 begin;
0.073 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.065 end;
*** Only UPDATEs, -T 30 -j1 -c200
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 1
duration: 30 s
number of transactions actually processed: 1870050
latency average = 0.193 ms
tps = 62334.364121 (including connections establishing)
tps = 62340.019978 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.000 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.035 begin;
0.110 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.046 end;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup |
n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
| vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+
------------+---------------------+-------------------------------+-------------------------------+-------------------------------+---------------------------
----+--------------+------------------+---------------+-------------------
16388 | public | pgbench_tellers | 1 | 7000 | 2386209 | 2386209 | 7000 | 2386209 | 0 | 2384538 | 7000 |
1047 | 1563732 | 2018-04-04 23:37:36.30328+00 | | 2018-04-04 23:35:00.814231+00 | 2018-04-04 23:37:41.758616
+00 | 5 | 0 | 1 | 2
16391 | public | pgbench_accounts | 1 | 70000000 | 6065392 | 6065392 | 70000000 | 516159 | 0 | 516159 | 70000000 |
442100 | 516159 | 2018-04-04 23:35:14.692129+00 | | 2018-04-04 23:35:14.874184+00 |
| 1 | 0 | 1 | 0
16385 | public | pgbench_history | 0 | 0 | | | 2323486 | 0 | 0 | 0 | 0 |
0 | 0 | 2018-04-04 23:35:14.885631+00 | | 2018-04-04 23:35:14.885871+00 | 2018-04-04 23:37:41.780025
+00 | 1 | 0 | 1 | 2
16394 | public | pgbench_branches | 3 | 2100 | 516159 | 516159 | 700 | 516159 | 0 | 515475 | 700 |
0 | 0 | 2018-04-04 23:37:36.302578+00 | 2018-04-04 23:36:41.741787+00 | 2018-04-04 23:35:00.811072+00 | 2018-04-04 23:37:41.769548
+00 | 5 | 1 | 1 | 2
(4 rows)
The end.
Finished working with instance i-0479a1cafdc33b2df, termination requested, current status: "shutting-down"
Raw
bench_c5.18xlarge.out
*** Only SELECTs, -T 120 -j4 -c12
transaction type: <builtin: select only>
scaling factor: 2000
query mode: prepared
number of clients: 12
number of threads: 4
duration: 120 s
number of transactions actually processed: 41098844
latency average = 0.035 ms
tps = 342490.035904 (including connections establishing)
tps = 342497.650092 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.034 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
*** Only INSERTs, -T 120 -j4 -c12
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 120 s
number of transactions actually processed: 22060221
latency average = 0.065 ms
tps = 183834.871991 (including connections establishing)
tps = 183839.475865 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.015 begin;
0.027 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.022 end;
*** Only UPDATEs, -T 120 -j4 -c12
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 120 s
number of transactions actually processed: 14270589
latency average = 0.101 ms
tps = 118921.368311 (including connections establishing)
tps = 118924.121587 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.017 begin;
0.058 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.024 end;
# pg_stat_user_tables:
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
16391 | public | pgbench_accounts | 1 | 200000000 | 52858567 | 52858567 | 200000000 | 6025590 | 0 | 6025590 | 200000000 | 3436913 | 6025590 | 2018-01-23 19:11:30.581497+00 | | 2018-01-23 19:11:30.701498+00 | | 1 | 0 | 1 | 0
16388 | public | pgbench_tellers | 1 | 20000 | 20522448 | 20522448 | 20000 | 20522448 | 0 | 19766444 | 20000 | 4053 | 4755856 | 2018-01-23 19:19:13.683032+00 | 2018-01-23 19:17:42.794293+00 | 2018-01-23 19:11:03.692766+00 | 2018-01-23 19:20:34.631495+00 | 5 | 2 | 1 | 6
16385 | public | pgbench_history | 0 | 0 | | | 28067435 | 0 | 0 | 0 | 0 | 0 | 0 | 2018-01-23 19:11:30.712121+00 | | 2018-01-23 19:11:30.712271+00 | 2018-01-23 19:19:34.623659+00 | 1 | 0 | 1 | 4
16394 | public | pgbench_branches | 3 | 6000 | 6025590 | 6025590 | 2000 | 6025590 | 0 | 5510149 | 2078 | 0 | 0 | 2018-01-23 19:19:13.67405+00 | 2018-01-23 19:17:49.942092+00 | 2018-01-23 19:11:03.688922+00 | 2018-01-23 19:17:50.249056+00 | 5 | 3 | 1 | 4
(4 rows)
Raw
bench_triggers.out
# Intel Xeon E5-2686 v4 (Broadwell) @ 2.3 GHz, 244GB RAM, NVMe SSD
*** Only SELECTs, -T30 -j4 -c12
transaction type: <builtin: select only>
scaling factor: 700
query mode: prepared
number of clients: 12
number of threads: 4
duration: 30 s
number of transactions actually processed: 5504882
latency average = 0.065 ms
tps = 183494.667416 (including connections establishing)
tps = 183517.062852 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.064 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
*** Only INSERTs, -T30 -j4 -c12
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 4079304
latency average = 0.177 ms
tps = 67986.010257 (including connections establishing)
tps = 67991.536335 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.044 begin;
0.072 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.057 end;
*** Only UPDATEs, -T30 -j4 -c12
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 2835460
latency average = 0.254 ms
tps = 47257.347846 (including connections establishing)
tps = 47261.019373 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.044 begin;
0.146 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.060 end;
*** Only INSERTs, but this time with a primitive trigger
# create or replace function trig() returns trigger as 'begin return new; end;' language plpgsql;
CREATE FUNCTION
# create trigger t_trig before insert or update on pgbench_history for each row execute procedure trig();
CREATE TRIGGER
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 3762246
latency average = 0.191 ms
tps = 62703.764277 (including connections establishing)
tps = 62708.924911 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.044 begin;
0.084 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.059 end;
*** Only UPDATEs, but this time with a primitive trigger
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 2832051
latency average = 0.254 ms
tps = 47200.372761 (including connections establishing)
tps = 47204.188749 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.044 begin;
0.146 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.060 end;
*** Only INSERTs, but this time with 2 (two) primitive triggers [0/1726]
# create or replace function trig2() returns trigger as 'begin return new; end;' language plpgsql;
CREATE FUNCTION
# create trigger t_trig2 before insert or update on pgbench_history for each row execute procedure trig2();
CREATE TRIGGER
transaction type: /tmp/ins.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 3815111
latency average = 0.189 ms
tps = 63584.827579 (including connections establishing)
tps = 63590.608149 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.042 begin;
0.086 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.056 end;
*** Only UPDATEs, but this time with 2 (two) primitive trigger
transaction type: /tmp/upd.bench
scaling factor: 1
query mode: prepared
number of clients: 12
number of threads: 4
duration: 60 s
number of transactions actually processed: 2851610
latency average = 0.252 ms
tps = 47526.363217 (including connections establishing)
tps = 47530.251933 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
0.044 begin;
0.145 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.060 end;
Raw
run.sh
#!/bin/bash
define(){ IFS='\n' read -r -d '' ${1} || true; }
set -ueo pipefail
set -ueox pipefail # to debug
instanceIdDefined="${instanceIdDefined:-}"
pgVers="${pgVers:-10}"
S3_BUCKET="${S3_BUCKET:-p-dumps}"
ec2Type="${ec2Type:-r3.large}"
ec2Price="${ec2Price:-0.035}"
n="${n:-50}"
s="${s:-700}"
increment="${increment:-50}"
duration="${duration:-30}"
pgConfig=$(cat "$ec2Type.conf")
if [ -z "$pgConfig" ]
then
echo "ERROR: cannot find Postgres config for $ec2Type" 1>&2
exit 1
fi
d=$(date)
echo "*******************************************************"
echo "TEST: pgbench "
echo "Current date/time: $d"
echo "EC2 node type: $ec2Type"
echo "Postgres major version: $pgVers"
echo "*******************************************************"
define ec2Opts <<EC2OPT
{
"MarketType": "spot",
"SpotOptions": {
"MaxPrice": "$ec2Price",
"SpotInstanceType": "one-time",
"InstanceInterruptionBehavior": "terminate"
}
}
EC2OPT
if [ -z "$instanceIdDefined" ]
then
cmdout=$(aws ec2 run-instances --image-id "ami-9d751ee7" --count 1 \
--instance-type "$ec2Type" \
--instance-market-options "$ec2Opts" \
--security-group-ids "sg-069a1372" \
--key-name awskey)
instanceId=$(echo $cmdout | jq -r '.Instances[0].InstanceId')
else
instanceId="$instanceIdDefined"
fi
function cleanup {
cmdout=$(aws ec2 terminate-instances --instance-ids "$instanceId" | jq '.TerminatingInstances[0].CurrentState.Name')
echo "Finished working with instance $instanceId, termination requested, current status: $cmdout"
}
trap cleanup EXIT
instanceState=$(echo $cmdout | jq -r '.Instances[0].State.Code')
echo "Instance requested, id: $instanceId, state code: $instanceState"
while true; do
status=$(aws ec2 describe-instance-status --instance-ids "$instanceId" | jq -r '.InstanceStatuses[0].SystemStatus.Status')
if [[ "$status" == "ok" ]]; then
break
fi
echo "Status is $status, waiting 30 seconds…"
sleep 30
done
instanceIP=$(aws ec2 describe-instances --instance-ids "$instanceId" | jq -r '.Reservations[0].Instances[0].PublicIpAddress')
echo "Public IP: $instanceIP"
shopt -s expand_aliases
alias sshdo='ssh -i ~/.ssh/awskey.pem -o "StrictHostKeyChecking no" "ubuntu@$instanceIP"'
define upd <<CONF
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
begin;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
end;
CONF
define ins <<CONF
\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
begin;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
end;
CONF
sshdo "echo \"$ins\" > /tmp/ins.bench"
sshdo "echo \"$upd\" > /tmp/upd.bench"
#sshdo "tmux new-session -d -s prep"
#sshdo "tmux send -t prep 'sudo mkfs -t ext4 /dev/xvdb && sudo mkdir /var/lib/postgresql && sudo mount /dev/xvdb /var/lib/postgresql'"
#sshdo "tmux send-keys -t prep Enter"
sshdo "sudo mkdir /dev/postgresql && sudo ln -s /dev/postgresql /var/lib/postgresql"
sshdo "sudo sh -c 'echo \"deb http://apt.postgresql.org/pub/repos/apt/ \`lsb_release -cs\`-pgdg main\" >> /etc/apt/sources.list.d/pgdg.list'"
sshdo 'wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -'
sshdo 'sudo apt-get update >/dev/null'
sshdo "sudo apt-get install -y postgresql-$pgVers"
sshdo "echo \"$pgConfig\" >/tmp/111 && sudo sh -c 'cat /tmp/111 >> /etc/postgresql/$pgVers/main/postgresql.conf'"
sshdo "sudo /etc/init.d/postgresql restart"
sshdo "sudo -u postgres psql -c 'create database test;'"
sshdo "sudo -u postgres pgbench -i -F70 -s $s test"
sshdo "sudo -u postgres psql test -c 'create extension pg_prewarm;'"
sshdo "echo \"select format('select %L, pg_prewarm(%L);', relname, relname) from pg_class c join pg_namespace n on n.oid = c.relnamespace and n.nspname = 'public' where relkind in ('r', 'i')\gexec\" | sudo -u postgres psql test -qX"
echo "*** Only SELECTs, -T $duration -j4 -c12"
sshdo "sudo -u postgres pgbench -T $duration -j4 -c12 -M prepared -rS test"
#echo "*** Mixed load, -T $duration -j4 -c12"
#sshdo "sudo -u postgres pgbench -T $duration -j4 -c12 -M prepared -r test"
#echo "*** Mixed load, -s$s, -c$n, -n starting from $n, increment ny $increment"
#for iter in {1..8}
#do
# sshdo "sudo -u postgres pgbench -s $s -j $n -c $n -M prepared test"
# let "n+=$increment"
#done
echo "*** Only INSERTs, -T $duration -j4 -c12"
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/ins.bench -r test"
echo "*** Only UPDATEs, -T $duration -j4 -c12"
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/upd.bench -r test"
echo
echo "*** Only INSERTs, but this time with a primitive trigger"
#sshdo "sudo -u postgres psql test -c \"alter table pgbench_history add column iii int;\""
sshdo "sudo -u postgres psql test -c \"create or replace function trig() returns trigger as 'begin return new; end;' language plpgsql;\""
sshdo "sudo -u postgres psql test -c \"create trigger t_trig before insert or update on pgbench_history for each row execute procedure trig();\""
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/ins.bench -r test"
echo "*** Only UPDATEs, but this time with a primitive trigger"
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/upd.bench -r test"
echo "*** Only INSERTs, but this time with 2 (two) primitive triggers"
sshdo "sudo -u postgres psql test -c \"create or replace function trig2() returns trigger as 'begin return new; end;' language plpgsql;\""
sshdo "sudo -u postgres psql test -c \"create trigger t_trig2 before insert or update on pgbench_history for each row execute procedure trig2();\""
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/ins.bench -r test"
echo "*** Only UPDATEs, but this time with 2 (two) primitive trigger"
sshdo "sudo -u postgres pgbench -T $duration -j4 -c 12 -M prepared -f /tmp/upd.bench -r test"
cleanup
echo "The end"
exit 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment