This Blog is about intallation and testing PostgreSQL 14 latest on an ARM based 80 core Server using Rocky Linux 8.6.

Many thanks to Happyware for providing the machine used for this Blog.

https://happyware.com/

The machine used for this Blog is a Gigabyte R272-P30 with Ampere Altra Q80-30 CPU, 80 cores at 3GHz.

https://happyware.com/gigabyte/arm-server-ampere-altra-r272-p30-q80-30/6nr272p30mr-00

Very nice IPMI:

IPMI Settings

CPU Invetory within IPMI:

CPU inventory

All Memory channels used.

Memory inventory

The cpu itself is very interesting, 80 cores, 1 MByte Cache per core, 8 Memory channels, and 128 PCIE 4 Lines.

The OS used for this Blog is Rocky Linux 8.6 for ARM64:

[[email protected] ~]# cat /etc/os-release
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky Linux"
ROCKY_SUPPORT_PRODUCT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8"
[[email protected] ~]#

Interesting is the output of htop on this machine.

[[email protected] ~]# lscpu
Architecture:        aarch64
Byte Order:          Little Endian
CPU(s):              80
On-line CPU(s) list: 0-79
Thread(s) per core:  1
Core(s) per socket:  80
Socket(s):           1
NUMA node(s):        1
Vendor ID:           ARM
BIOS Vendor ID:      Ampere(R)
Model:               1
Model name:          Neoverse-N1
BIOS Model name:     Ampere(R) Altra(R) Processor
Stepping:            r3p1
CPU max MHz:         3300.0000
CPU min MHz:         1000.0000
BogoMIPS:            50.00
L1d cache:           64K
L1i cache:           64K
L2 cache:            1024K
NUMA node0 CPU(s):   0-79
Flags:               fp asimd evtstrm aes pmull sha1 sha2 crc32 atomics fphp asimdhp cpuid asimdrdm lrcpc dcpop asimddp ssbs
[[email protected] ~]#

The storage in the background are two Samsung SSD 980Pro of 500GB, so nothing very special.

[[email protected] ~]# lsblk
NAME        MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
nvme1n1     259:0    0 465.8G  0 disk
└─nvme1n1p1 259:2    0 465.8G  0 part
  └─rl-home 253:2    0   856G  0 lvm  /home
nvme0n1     259:1    0 465.8G  0 disk
├─nvme0n1p1 259:3    0   600M  0 part /boot/efi
├─nvme0n1p2 259:4    0     1G  0 part /boot
└─nvme0n1p3 259:5    0 464.2G  0 part
  ├─rl-root 253:0    0    70G  0 lvm  /
  ├─rl-swap 253:1    0     4G  0 lvm  [SWAP]
  └─rl-home 253:2    0   856G  0 lvm  /home
[[email protected] ~]#

The installation of PostgreSQL will follow the documentation I have described in my atricle at heise.de, it is in german.

https://www.heise.de/ratgeber/PostgreSQL-installieren-mit-den-Community-Paketen-4877556.html

Means installing postgresql.org repository and disabling Rocky Linux postgresql modules.

[[email protected] ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-aarch64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 1:36:40 ago on Thu 21 Jul 2022 07:15:54 AM EDT.
pgdg-redhat-repo-latest.noarch.rpm                                                                                                                                                 4.5 kB/s |  11 kB     00:02
Dependencies resolved.
===================================================================================================================================================================================================================
 Package                                                 Architecture                                  Version                                           Repository                                           Size
===================================================================================================================================================================================================================
Installing:
 pgdg-redhat-repo                                        noarch                                        42.0-26                                           @commandline                                         11 k

Transaction Summary
===================================================================================================================================================================================================================
Install  1 Package

Total size: 11 k
Installed size: 13 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                           1/1
  Installing       : pgdg-redhat-repo-42.0-26.noarch                                                                                                                                                           1/1
  Verifying        : pgdg-redhat-repo-42.0-26.noarch                                                                                                                                                           1/1

Installed:
  pgdg-redhat-repo-42.0-26.noarch

Complete!
[[email protected] ~]# dnf -qy module disable postgresql
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
Importing GPG key 0x6D960B89:
 Userid     : "PostgreSQL RPM Repository <[email protected]>"
 Fingerprint: 33EC A7E4 0671 479E 2279 EA81 A8AC 42ED 6D96 0B89
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-AARCH64
[[email protected] ~]#

Within the pgdg-redhat-all.repo file I have disabled all PostgreSQL versions except 14 which I want to use. The installation itself is the same like on other servers using Intel or AMD.

dnf install postgresql14 postgresql14-server postgresql14-contrib

On that system a stripe of two NVME SSDs is used for /home, so I changed the PGDATA for the service file creating a override.conf.

[[email protected] ~]# systemctl edit postgresql-14.service

And add:

[Service]
Environment=PGDATA=/home/PG14/data

Now we can run initdb.

[[email protected] ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

[[email protected] ~]#

Starting PostgreSQL and enabling the service.

[[email protected] opt]# systemctl start postgresql-14.service
[[email protected] opt]# systemctl enable postgresql-14.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
[[email protected] opt]#

PostgreSQL 14 is up and running on this ARM based host.

[[email protected] ~]$ psql
psql (14.4)
Type "help" for help.

postgres=#

The configuration of PostgreSQL is following best practices.

[[email protected] ~]# cat /home/PG14/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
max_connections = '1000'
effective_cache_size = '192 GB'
shared_buffers = '64 GB'
work_mem = '64 MB'
maintenance_work_mem = '8000 MB'
max_worker_processes = '80'
max_parallel_workers = '80'
max_parallel_workers_per_gather = '40'
max_parallel_maintenance_workers = '40'
shared_preload_libraries = 'pg_stat_statements'
checkpoint_timeout  = '15 min'
checkpoint_completion_target = 0.9
min_wal_size = '1024 MB'
max_wal_size = '16384 MB'
wal_buffers = '512MB'
wal_compression = on
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
jit = on
[[email protected] ~]#

I’ using the tuned.conf for PostgreSQL out of the dbi DMK Package.

#
# dbi services tuned profile for PostgreSQL servers
#

[main]
summary=dbi services tuned profile for PostgreSQL servers
include=throughput-performance

[bootloader]
cmdline = "transparent_hugepage=never"

[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
# Explicitly disable deep c-states to reduce latency on OLTP workloads.
force_latency=1

[disk]
readahead=>4096

[sysctl]
kernel.sched_min_granularity_ns = 10000000
kernel.sched_wakeup_granularity_ns = 15000000
kernel.sched_migration_cost_ns=50000000
# this one is for pgpool
## http://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html => num_init_children
net.core.somaxconn=256
net.ipv4.tcp_timestamps=0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.overcommit_memory=2
vm.overcommit_ratio=75
vm.swappiness=1
vm.dirty_ratio=2
vm.dirty_background_ratio=1
#vm.nr_hugepages=1200

[vm]
transparent_hugepages=never
[[email protected] ~]$

I have activated that profile and after a reboot I was starting to work with pgbech for testing.

I have created a pgbenchdb with 1 billion records, the DB itself is about 146GB.

[pos[email protected] ~]$ /usr/pgsql-14/bin/pgbench -i -s 10000 pgbenchdb
generating data (client-side)...
1000000000 of 1000000000 tuples (100%) done (elapsed 740.31 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1568.00 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 742.72 s, vacuum 113.22 s, primary keys 712.03 s).
[[email protected] ~]$
[[email protected] ~]$ psql
psql (14.4)
Type "help" for help.

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 pgbenchdb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 146 GB  | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8857 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8705 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8705 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#

I run pgbench with a simulation of 10 clients, 25 clients, 50 clients, 100 clients, 250 clients, 500 clients and 1000 clients, pgbench istelf was used with 10 threads. For every client 1000 transactions simulated.

[[email protected] ~]$ /usr/pgsql-14/bin/pgbench -c 1000 -j 10 -t 1000 pgbenchdb
pgbench (14.4)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10000
query mode: simple
number of clients: 1000
number of threads: 10
number of transactions per client: 1000
number of transactions actually processed: 1000000/1000000
latency average = 22.659 ms
initial connection time = 436.926 ms
tps = 44132.846931 (without initial connection time)
[[email protected] ~]$

Very interesting result, over 44000 tps. But also interesting was the scaling of that system over the count of clients.

I’m very satisfied with this performance I did not expect, the system is scaling very well.