#postgres #pgbench #zfs

Потестил pg_bench PG15 на SSD в ext4 и zfs (с шифрованием/сжатием и без) в Ubuntu 22.04.

ZFS ENCRYPTED & COMPRESSED (LZ4):

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.891 ms
initial connection time = 34.946 ms
tps = 2044.768698 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 4.552 ms
initial connection time = 17.378 ms
tps = 2196.925697 (without initial connection time)

ZFS ENCRYPTED & COMPRESSED (LZ4) TUNED WITH TIMESCALEDB:

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.031 ms
initial connection time = 31.661 ms
tps = 2480.899924 (without initial connection time)

latency average = 3.850 ms
initial connection time = 31.273 ms
tps = 2597.574037 (without initial connection time)

latency average = 3.889 ms
initial connection time = 32.503 ms
tps = 2571.192661 (without initial connection time)


sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.784 ms
initial connection time = 27.329 ms
tps = 2642.536852 (without initial connection time)

latency average = 3.741 ms
initial connection time = 17.922 ms
tps = 2673.011469 (without initial connection time)

latency average = 3.679 ms
initial connection time = 16.235 ms
tps = 2718.262035 (without initial connection time)


ZFS NON-ENCRYPTED & COMPRESSED (LZ4):

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 4.660 ms
initial connection time = 38.052 ms
tps = 2146.130682 (without initial connection time)

latency average = 4.507 ms
initial connection time = 39.378 ms
tps = 2218.757361 (without initial connection time)

latency average = 4.266 ms
initial connection time = 36.697 ms
tps = 2344.329379 (without initial connection time)


sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.936 ms
initial connection time = 21.317 ms
tps = 2540.753366 (without initial connection time)

latency average = 3.915 ms
initial connection time = 17.523 ms
tps = 2554.329177 (without initial connection time)

latency average = 4.076 ms
initial connection time = 21.361 ms
tps = 2453.572519 (without initial connection time)


ZFS NON-ENCRYPTED & COMPRESSED (LZ4) TUNED WITH TIMESCALEDB:

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 3.648 ms
initial connection time = 34.840 ms
tps = 2741.206504 (without initial connection time)

latency average = 3.473 ms
initial connection time = 39.593 ms
tps = 2879.122987 (without initial connection time)


latency average = 3.592 ms
initial connection time = 34.987 ms
tps = 2783.957080 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 3.346 ms
initial connection time = 21.312 ms
tps = 2988.591798 (without initial connection time)

latency average = 3.178 ms
initial connection time = 18.359 ms
tps = 3147.071197 (without initial connection time)

latency average = 3.337 ms
initial connection time = 19.320 ms
tps = 2996.486500 (without initial connection time)

EXT4 NON-ENCRYPTED NON-COMPRESSED TUNED WITH TIMESCALEDB

sudo -i -u postgres pgbench -c 10 -j 2 -t 10000 example

latency average = 2.596 ms
initial connection time = 86.466 ms
tps = 3852.682810 (without initial connection time)

latency average = 2.751 ms
initial connection time = 89.400 ms
tps = 3635.586811 (without initial connection time)

latency average = 2.719 ms
initial connection time = 92.922 ms
tps = 3677.184800 (without initial connection time)

sudo -i -u postgres pgbench -c 10 -j 6 -t 10000 example

latency average = 2.643 ms
initial connection time = 50.687 ms
tps = 3784.041569 (without initial connection time)

latency average = 2.518 ms
initial connection time = 42.306 ms
tps = 3971.158272 (without initial connection time)

latency average = 2.560 ms
initial connection time = 40.711 ms
tps = 3906.430062 (without initial connection time)