Postgres Performance

D

Datawolk BV

New Member
Proxmox Subscriber
Jul 2, 2025
7
0
1
Hi,

Below are two servers: Server 1 is the one we want to replace with Server 2.
But Server 2 is significantly slower than Server 1, even though the hardware should be much better.
The PostgreSQL configuration is the same.
Can anyone point me in the right direction as to where the difference between the two servers might lie?
When I set `synchronous_commit = off` in PostgreSQL, my performance increases by 3x to 4x.
But this is also set to “on” on Server 1.


Server 1 (ProxMox Host):
  • Dell PowerEdge R450
  • CPU(s) 64 x Intel(R) Xeon(R) Silver 4314 CPU @ 2.40GHz (2 Sockets)
  • 4x Synology_SAT5210-960G (RAIDZ)
  • 512GB Memory

Server 2 (ProxMox Host):
  • Asus RS700-E12-RS12U/2KW/12N
  • CPU(s) 96 x Intel(R) Xeon(R) 6520P (2 Sockets)
  • 4x Samsung DC PM9A3, 960GB (RAIDZ)
  • 1024 Memory
root@PVE-A-02:/opt/tacticalmesh# qm config 100
agent: 1
allow-ksm: 0
balloon: 0
boot: order=scsi0;net0
cores: 4
cpu: host
memory: 81920
meta: creation-qemu=10.1.2,ctime=1771507589
name: LS-ALM-12
net0: virtio=BC:24:11:6E:C6:4E,bridge=DMZ,tag=1012
numa: 0
ostype: l26
scsi0: VM-DISK:vm-100-disk-0,backup=0,cache=writeback,discard=on,iothread=1,size=128G,ssd=1
scsihw: virtio-scsi-single
smbios1: uuid=deebaf23-743c-4ad9-8b3b-6165b3210722
sockets: 1
vmgenid: 6cc65519-3406-45b7-8c18-ae43aeb5b8c2

root@PVE-A-00:~# qm config 104
agent: 1
allow-ksm: 0
balloon: 0
boot: order=scsi0;net0
cores: 4
cpu: host
memory: 65536
meta: creation-qemu=10.1.2,ctime=1773411102
name: demo-01
net0: virtio=BC:24:11:09:B4:D7,bridge=vmbr0
numa: 0
ostype: l26
scsi0: VM-DISK:vm-104-disk-0,backup=0,cache=writeback,discard=on,iothread=1,size=128G,ssd=1
scsihw: virtio-scsi-single
smbios1: uuid=7cdeeba6-e7ec-4b96-82a6-d2608af7d1ae
sockets: 1
vmgenid: ad503f69-7b2c-4ab9-9142-1337a1116755



[postgres@LS-ALM-12 ~]$ /usr/pgsql-17/bin/pgbench -p 7012 -c 10 -j 2 -T 60 test
pgbench (17.8)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 310715
number of failed transactions: 0 (0.000%)
latency average = 1.931 ms
initial connection time = 14.512 ms
tps = 5178.910382 (without initial connection time)


[postgres@localhost ~]$ /usr/pgsql-17/bin/pgbench -c 10 -j 2 -T 60 -r test_db
pgbench (17.9)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 138512
number of failed transactions: 0 (0.000%)
latency average = 4.332 ms
initial connection time = 10.964 ms
tps = 2308.460925 (without initial connection time)
 
It might be illuminating to benchmark the used NVMe drives' fsync performance, and compare them to each other.

Are your guest OS the same distro/build, and use the same filesystem for persisting data onto their backing stores?
 
@jtru


The OS and file system are the same.
fsync is indeed much slower on the NVMe, but what could be causing this?
The Proxmox and VM configurations are identical (should be :D).

[postgres@demo-00 ~]$ cat /etc/os-release
NAME="AlmaLinux"
VERSION="9.7 (Moss Jungle Cat)"
ID="almalinux"
ID_LIKE="rhel centos fedora"
VERSION_ID="9.7"
PLATFORM_ID="platform:el9"
PRETTY_NAME="AlmaLinux 9.7 (Moss Jungle Cat)"
ANSI_COLOR="0;34"
LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:almalinux:almalinux:9::baseos"
HOME_URL="https://almalinux.org/"
DOCUMENTATION_URL="https://wiki.almalinux.org/"
BUG_REPORT_URL="https://bugs.almalinux.org/"

ALMALINUX_MANTISBT_PROJECT="AlmaLinux-9"
ALMALINUX_MANTISBT_PROJECT_VERSION="9.7"
REDHAT_SUPPORT_PRODUCT="AlmaLinux"
REDHAT_SUPPORT_PRODUCT_VERSION="9.7"
SUPPORT_END=2032-06-01

[postgres@LS-ALM-12 ~]$ mount | grep -E 'ext4|xfs'
/dev/mapper/almalinux-root on / type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)
/dev/sda1 on /boot type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)

[postgres@demo-00 ~]$ mount | grep -E 'ext4|xfs'
/dev/mapper/almalinux-root on / type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)
/dev/sda1 on /boot type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)
/dev/mapper/almalinux-home on /home type xfs (rw,relatime,attr2,inode64,logbufs=8,logbsize=32k,noquota)

[postgres@LS-ALM-12 ~]$ /usr/pgsql-17/bin/pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in "wal_sync_method" preference order, except fdatasync is Linux's default)
open_datasync 2950,561 ops/sec 339 usecs/op
fdatasync 2700,786 ops/sec 370 usecs/op
fsync 1693,146 ops/sec 591 usecs/op
fsync_writethrough n/a
open_sync 1737,103 ops/sec 576 usecs/op

Compare file sync methods using two 8kB writes:
(in "wal_sync_method" preference order, except fdatasync is Linux's default)
open_datasync 1494,178 ops/sec 669 usecs/op
fdatasync 2538,318 ops/sec 394 usecs/op
fsync 1511,548 ops/sec 662 usecs/op
fsync_writethrough n/a
open_sync 875,946 ops/sec 1142 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 1523,652 ops/sec 656 usecs/op
2 * 8kB open_sync writes 857,365 ops/sec 1166 usecs/op
4 * 4kB open_sync writes 434,853 ops/sec 2300 usecs/op
8 * 2kB open_sync writes 230,023 ops/sec 4347 usecs/op
16 * 1kB open_sync writes 117,280 ops/sec 8527 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 1758,185 ops/sec 569 usecs/op
write, close, fsync 1701,387 ops/sec 588 usecs/op

Non-sync'ed 8kB writes:
write 1157454,928 ops/sec 1 usecs/op

[postgres@demo-00 ~]$ /usr/pgsql-17/bin/pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in "wal_sync_method" preference order, except fdatasync is Linux's default)
open_datasync 822,152 ops/sec 1216 usecs/op
fdatasync 1202,488 ops/sec 832 usecs/op
fsync 324,800 ops/sec 3079 usecs/op
fsync_writethrough n/a
open_sync 441,488 ops/sec 2265 usecs/op

Compare file sync methods using two 8kB writes:
(in "wal_sync_method" preference order, except fdatasync is Linux's default)
open_datasync 423,559 ops/sec 2361 usecs/op
fdatasync 827,904 ops/sec 1208 usecs/op
fsync 546,069 ops/sec 1831 usecs/op
fsync_writethrough n/a
open_sync 183,647 ops/sec 5445 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 364,599 ops/sec 2743 usecs/op
2 * 8kB open_sync writes 176,747 ops/sec 5658 usecs/op
4 * 4kB open_sync writes 82,877 ops/sec 12066 usecs/op
8 * 2kB open_sync writes 43,455 ops/sec 23012 usecs/op
16 * 1kB open_sync writes 21,453 ops/sec 46613 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 405,838 ops/sec 2464 usecs/op
write, close, fsync 459,738 ops/sec 2175 usecs/op

Non-sync'ed 8kB writes:
write 2034658,524 ops/sec 0 usecs/op
 
Last edited:
You must log in or register to reply here.
Top Bottom
Back