Re: [PoC] Non-volatile WAL buffer - Mailing list pgsql-hackers

From Takashi Menjo
Subject Re: [PoC] Non-volatile WAL buffer
Date
Msg-id CAOwnP3OFofOsFtmeikQcbMp0YWdJn0kVB4Ka_0tj+Urq7dtAzQ@mail.gmail.com
Whole thread Raw
In response to Re: [PoC] Non-volatile WAL buffer  (Takashi Menjo <takashi.menjo@gmail.com>)
List pgsql-hackers
Hi Sawada,

Thank you for your performance report.

First, I'd say that the latest v5 non-volatile WAL buffer patchset
looks not bad itself. I made a performance test for the v5 and got
better performance than the original (non-patched) one and our
previous work. See the attached figure for results.

I think steps and/or setups of Tomas's, yours, and mine could be
different, leading to the different performance results. So I show my
steps and setups for my performance test. Please see the tail of this
mail for them.

Also, I write performance tips to the PMEM page at PostgreSQL wiki
[1]. I wish it could be helpful to improve performance.

Regards,
Takashi

[1] https://wiki.postgresql.org/wiki/Persistent_Memory_for_WAL#Performance_tips



# Environment variables
export PGHOST=/tmp
export PGPORT=5432
export PGDATABASE="$USER"
export PGUSER="$USER"
export PGDATA=/mnt/nvme0n1/pgdata

# Steps
Note that I ran postgres server and pgbench in a single-machine system
but separated two NUMA nodes. PMEM and PCI SSD for the server process
are on the server-side NUMA node.

01) Create a PMEM namespace (sudo ndctl create-namespace -f -t pmem -m
fsdax -M dev -e namespace0.0)
02) Make an ext4 filesystem for PMEM then mount it with DAX option
(sudo mkfs.ext4 -q -F /dev/pmem0 ; sudo mount -o dax /dev/pmem0
/mnt/pmem0)
03) Make another ext4 filesystem for PCIe SSD then mount it (sudo
mkfs.ext4 -q -F /dev/nvme0n1 ; sudo mount /dev/nvme0n1 /mnt/nvme0n1)
04) Make /mnt/pmem0/pg_wal directory for WAL
05) Make /mnt/nvme0n1/pgdata directory for PGDATA
06) Run initdb (initdb --locale=C --encoding=UTF8 -X /mnt/pmem0/pg_wal ...)
    - Also give -P /mnt/pmem0/pg_wal/nvwal -Q 81920 in the case of
"Non-volatile WAL buffer"
07) Edit postgresql.conf as the attached one
08) Start postgres server process on NUMA node 0 (numactl -N 0 -m 0 --
pg_ctl -l pg.log start)
09) Create a database (createdb --locale=C --encoding=UTF8)
10) Initialize pgbench tables with s=50 (pgbench -i -s 50)
11) Stop the postgres server process (pg_ctl -l pg.log -m smart stop)
12) Remount the PMEM and the PCIe SSD
13) Start postgres server process on NUMA node 0 again (numactl -N 0
-m 0 -- pg_ctl -l pg.log start)
14) Run pg_prewarm for all the four pgbench_* tables
15) Run pgbench on NUMA node 1 for 30 minutes (numactl -N 1 -m 1 --
pgbench -r -M prepared -T 1800 -c __ -j __)
    - It executes the default tpcb-like transactions

I repeated all the steps three times for each (c,j) then got the
median "tps = __ (including connections establishing)" of the three as
throughput and the "latency average = __ ms " of that time as average
latency.

# Setup
- System: HPE ProLiant DL380 Gen10
- CPU: Intel Xeon Gold 6240M x2 sockets (18 cores per socket; HT
disabled by BIOS)
- DRAM: DDR4 2933MHz 192GiB/socket x2 sockets (32 GiB per channel x 6
channels per socket)
- Optane PMem: Apache Pass, AppDirect Mode, DDR4 2666MHz 1.5TiB/socket
x2 sockets (256 GiB per channel x 6 channels per socket; interleaving
enabled)
- PCIe SSD: DC P4800X Series SSDPED1K750GA
- Distro: Ubuntu 20.04.1
- C compiler: gcc 9.3.0
- libc: glibc 2.31
- Linux kernel: 5.7.0 (built by myself)
- Filesystem: ext4 (DAX enabled when using Optane PMem)
- PMDK: 1.9 (built by myself)
- PostgreSQL (Original): 9e7dbe3369cd8f5b0136c53b817471002505f934 (Jan
18, 2021 @ master)
- PostgreSQL (Mapped WAL file): Original + v5 of "Applying PMDK to WAL
operations for persistent memory" [2]
- PostgreSQL (Non-volatile WAL buffer): Original + v5 of "Non-volatile
WAL buffer" [3]; please read the files' prefix "v4-" as "v5-"

[2] https://www.postgresql.org/message-id/CAOwnP3O3O1GbHpddUAzT%3DCP3aMpX99%3D1WtBAfsRZYe2Ui53MFQ%40mail.gmail.com
[3] https://www.postgresql.org/message-id/CAOwnP3Oz4CnKp0-_KU-x5irr9pBqPNkk7pjwZE5Pgo8i1CbFGg%40mail.gmail.com

-- 
Takashi Menjo <takashi.menjo@gmail.com>

Attachment

pgsql-hackers by date:

Previous
From: Amit Langote
Date:
Subject: Re: POC: postgres_fdw insert batching
Next
From: Joel Jacobson
Date:
Subject: Re: GSoC 2017: Foreign Key Arrays