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.
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:
CPU Invetory within IPMI:
All Memory channels used.
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:
[root@localhost ~]# 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"
[root@localhost ~]#Interesting is the output of htop on this machine.
[root@localhost ~]# 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
[root@localhost ~]#The storage in the background are two Samsung SSD 980Pro of 500GB, so nothing very special.
[root@localhost ~]# 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
[root@localhost ~]#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.
[root@localhost ~]# 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!
[root@localhost ~]# 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
[root@localhost ~]#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-contribOn 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.
[root@localhost ~]# systemctl edit postgresql-14.serviceAnd add:
[Service]
Environment=PGDATA=/home/PG14/dataNow we can run initdb.
[root@localhost ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
[root@localhost ~]#Starting PostgreSQL and enabling the service.
[root@localhost opt]# systemctl start postgresql-14.service
[root@localhost 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.
[root@localhost opt]#PostgreSQL 14 is up and running on this ARM based host.
[postgres@localhost ~]$ psql
psql (14.4)
Type "help" for help.
postgres=#The configuration of PostgreSQL is following best practices.
[root@localhost ~]# 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
[root@localhost ~]#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
[postgres@localhost ~]$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.
[postgres@localhost ~]$ /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).
[postgres@localhost ~]$
[postgres@localhost ~]$ 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.
[postgres@localhost ~]$ /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)
[postgres@localhost ~]$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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/10/KLE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/NME_web-min-scaled.jpg)