IBM Power Systems solution for EnterpriseDB Postgres Advanced Server
An article on PostgreSQL and EnterpriseDB Postgres Advanced Server running Linux on IBM POWER8 processor-based servers – installation and tuning best practices for IBM Power servers
The primary focus of this article is on the use, configuration, and optimization of PostgreSQL and EnterpriseDB Postgres Advanced Server running on the IBM® Power Systems™ servers featuring the new IBM POWER8® processor technology.
Note: The Red Hat Enterprise Linux (RHEL) 7.2 operating system was used. The scope of this article is to provide information on how to build and set up of PostgreSQL database from open source and also install and configure EnterpriseDB Postgres Advanced Server on an IBM Power® server for better use. EnterpriseDB Postgres Advanced Server on IBM Power Systems running Linux® is based on the open source database, PostgreSQL, and is capable of handling a wide variety of high-transaction and heavy-reporting workloads.
General recommendation
It is generally recommended to have a root and data disk on separate physical disk or Redundant Array of Independent Disks (RAID). In order to eliminate the impact of performance differences between disparate storage technologies, the database was instead kept in memory on a tmpfs virtual file system.
EnterpriseDB Postgres Advanced Server on IBM Power Systems running Linux
EnterpriseDB (EDB) Postgres Advanced Server is built on PostgreSQL, one of the most advanced open source databases and has additional functionality and capabilities in the following areas:
- Performance
- Compatibility
- Security
- Tooling
With the Oracle compatibility features of EDB Postgres Advanced Server, you can use existing Oracle-based applications on a low-cost, high-performance PostgreSQL-based platform, or run adjacent applications that integrate seamlessly with your mission-critical databases without additional Oracle licenses.
Always ensure that EDB Postgres Advanced Server is installed with the latest service
pack available from the EDB website at:
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Prerequisites
Refer to EDB manuals for software prerequisites.
- Confirm that there is adequate paging space and free space on the /tmp and / (root) file systems.
- Verify the ulimits setting for each of the products using the
ulimit –a
command. - Refer to the EnterpriseDB tuning guide and best practices guide.
- EDB Postgres Advanced Server setup requires Java™. Download the Java SE
version from the IBM developerWorks website. Follow the instruction to install
Java at:
https://www.ibm.com/developerworks/java/jdk/linux/download.html
Setting up PostgreSQL and EnterpriseDB Postgres Advanced Server
EDB Postgres Advanced Server can be installed using the rpm command line or the yum installer. It is recommended to perform the installation using the yum tool.
First, you need to make sure whether a yum repository for RHEL is defined. Also, you can define a yum repository for EDB rpms for easy one-step installations. Defining a yum repository for the base OS and software installation packages enables the installation of software to automatically fetch the rpm packages (which are the prerequisites).
Configuring the yum repository
Confirm the yum repository defined for BASE OS as it helps. A RHEL yum repository can be defined by pointing to a CD/DVD media, a shared network location, or a local directory that contains the RHEL installation media. The test team defined it using a local directory. The yum configuration files are located in the /etc/yum.repos.d/ directory.
You can find more information about yum repositories at:
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/System_Administrators_Guide/sec-Configuring_Yum_and_Yum_Repositories.html
Installing EnterpriseDB Postgres Advanced Server
You can find details about installing EDB Postgres Advanced Server at:
https://www.enterprisedb.com/products-services-training/products/documentation/enterpriseedition
Before getting started with the installation, refer to the information at:
https://www.ibm.com/developerworks/linux/library/l-edb-getstart-trs/
Installation of EDB Postgres Advanced Server on Linux on Power is done using the
yum repository definition as provided by EDB. After the yum
repositories are defined in the system, perform the installation of EDB Postgres
Advanced Server using a simple install command. For example: yum install
ppas95-server
This command automatically installs all the required prerequisites, and also creates a database administrator user, enterprisedb, if not already present.
Before proceeding with creating and configuring the DB instance, it is best to apply tunings. In this exercise, the test team performed various system tunings, including kernel tuning.
Building and installing the PostgreSQL Server
To best use the IBM POWER8 features and optimized builds, it is recommended to use
the latest version of IBM Advance Toolchain for PowerLinux. There is a tool
available to download Advance Toolchain called AT Downloader. The AT Downloader is a
script to download the latest version of the Advance Toolchain packages for a
supported distribution.
ftp://ftp.unicamp.br/pub/linuxpatch/toolchain/at/at_downloader/
Then, manually install the Advance Toolchain packages on the systems.
Confirm that the path is set to include the gcc compiler that comes with AT. Usually it is located in /opt/atX/bin, where X is the version of Advance Toolchain.
You can find all Postgres sources and related materials at www.postgresql.org. You can then download and extract the contents.
- Configure the source code with added options for CFLAGS, CPPFLAGS and LDFLAGS as
"-Wl,-q -mcpu=power8 -mtune=power8 -O3 -m64". For example:
./configure CFLAGS='-Wl,-q -mcpu=power8 -mtune=power8 -O3 -m64' CPPFLAGS='-Wl, -q -mcpu=power8 -mtune=power8 -O3 -m64' LDFLAGS='-Wl,-q -mcpu=power8 -mtune=power8 -O3 -m64'
- In addition, it might be necessary to install additional dependency packages from the base OS media, as needed, based on the configuring process.
- During the build process, be sure to specify
make all
. The contrib folder has additional packages such as pgbench and others.
Power Systems configuration
The following settings were used for this exercise.
- Set single partition mode [non-virtualized logical partition (LPAR)] – The whole system was assigned to a single partition with all cores dedicated. And Hardware Management Console (HMC) access is needed to create this LPAR. This would be the only LPAR on the system.
- Disable the following power saving options for the POWER8 processor-based
system.
- Turn off the Idle Power Saver option.
- Set Dynamic Power Saver mode to enabled, favor performance.
This setting change can be performed only through Advanced System Management Interface (ASMI) which can be accessed using HMC or directly through a browser using the managed system's IP.
Note: The ASMI options might not be available if a firmware other than IBM PowerVM®, such as OPAL, is set. In these cases, set the processor modes through the Linux command line using the cpupower util with the governor option set to performance mode. This setting must be applied to all the cores on the system.
For example: cpupower frequency-set --governor performance
You can find more information at:
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Power_Management_Guide/cpufreq_governors.html
Start the ASMI from HMC for the particular system. The following are only reference screen captures.
Figure 1. Accessing ASMI from HMC

To turn off the Idle Power Saver option, log in and click System Configuration -> Power Management -> Idle Power Saver.
Figure 2. ASMI – Idle Power Saver page

To enable the dynamic power saver mode, perform the following steps:
- Click Power Mode Setup in the left pane, and select the Enable Dynamic Power Saver (favor performance) mode option in the right pane.
- Click Continue.
Figure 3. ASMI – Power Mode Setup page

These settings help in increasing the processor speed. This can be verified at the
shell by using the ppc64_cpu –freq
command. This operation does not
require the system to be shut down or restarted. It takes effect immediately.
Note: These options might have moved around during future updates and releases of firmware. Just poke around and find the required configuration.
Note: The ASMI settings shown in Figures 1-3 might not be available if a firmware other than IBM PowerVM®, such as OPAL, is set. In these cases, set the processor modes through the Linux command line using the cpupower util with the governor option set to performance mode. This setting must be applied to all the cores on the system.
For example: cpupower frequency-set --governor performance
You can find more information at:
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/Power_Management_Guide/cpufreq_governors.html
Kernel tunings and OS tunings
The following kernel parameters have shown favorable results in internal tests. These kernel parameter settings are to be applied as a root user. Perform the activity with caution and set the values according to the system's hardware specifications and operating system implementation.
- fs.file-max
- vm.dirty_background_bytes
- vm.dirty_ratio
- vm.dirty_background_ratio
- vm.hugetlb_shm_group
- vm.dirty_bytes
- vm.swappiness
- vm.hugepages_treat_as_movable
- vm.zone_reclaim_mode
- vm.drop_caches
- kernel.sched_migration_cost
- kernel.sched_autogroup_enabled
- kernel.numa_balancing
- vm.zone_reclaim_mode
The following settings would be best set based on your system memory sizes and would need adjustments accordingly:
- shmall
shmall=$(( `grep MemTotal /proc/meminfo |awk '{print $2}'` * 1024 * 9 / (`getconf PAGE_SIZE` * 10)))
sysctl -w kernel.shmall=$shmall
- shmmax
shmmax=$(( `grep MemTotal /proc/meminfo |awk '{print $2}'` * 1024 * 8 / 10 ))
sysctl -w kernel.shmmax=$shmmax
Apply the following OS changes. These tuning changes have shown in pgbench testing to as beneficial.
- Set the processor's simultaneous multithreading (SMT) snooze delay to be higher
using the following command (this is only applicable on IBM POWER7® and IBM
POWER7+™ hardware and not required on POWER8 hardware).
ppc64_cpu --smt-snooze-delay=16777215
The default value is 100. This is a tunable parameter to delay the entry to nap state.
- Use the following command to turn off I/O preemption.
mount -t debugfs debugfs /sys/kernel/debug
echo NO_WAKEUP_PREEMPT > /sys/kernel/debug/sched_featuresThe preempt scheduler relates to the releasing of the processor time when a process of a higher priority wants to use the processor. So, if there are many processes with similar priorities, the processor time can be consumed with this swapping in and out. In some cases, the following settings worked.
LATENCY=$(cat /proc/sys/kernel/sched_latency_ns)
echo $((LATENCY/2)) > /proc/sys/kernel/sched_min_granularity_ns - Run the following command on your Linux system to disable hardware data prefetch
(usually to improve Postgresql performances).
ppc64_cpu -dscr=1
- Run the following command to restore the default value.
ppc64_cpu -dscr=0
PostgreSQL (open source)
Optional: When planning to use huge pages on Power servers, consider the following instructions.
- Huge pages size is hardcoded to 2 MB (Intel® size) in PostgreSQL source
code (sysv_shmem.c). It should be changed because IBM Power Architecture®
uses a huge page size of 16 MB.
- Enter the following command in the Postgresql source
directory.
cd postgresql
- Manually change the huge page size to 16 MB
in
src/backend/port/sysv_shmem.c:360
- Enter the following command in the Postgresql source
directory.
Use the following kernel value to set up a huge page pool of 32 GB (16 MB * 2000)
(Size must be adapted accordingly to the postgresql.org configuration)
vm.nr_hugepages=2000
vm.nr_overcommit_hugepages=512
Modify postgresql.conf to tell Postgres to use huge pages.
huge_page = try #it will use huge pages if exists
orhuge_page=on #it will force to use huge pages
EDB Postgres Advanced Server
Apply the following changes to EDB Postgres Advanced Server. These tuning changes have shown in pgbench testing to as beneficial.
- Add the following entries to the limits.conf file for the EDB user (or Postgres
user accordingly) /etc/security/limits.conf
enterprisedb soft memlock 68719476736
enterprisedb hard memlock 68719476736This limits the maximum amount of locked-in-memory address space.
- Optional: Enable huge pages for EDB Postgres Advanced Server by adding the
following lines to the EDB user shell profile.
HUGETLB_SHM=yes
LD_PRELOAD='/usr/lib64/libhugetlbfs.so'
export HUGETLB_SHM
export LD_PRELOADIn case, EDB Postgres Advanced Server complains of usage of huge pages, it could be resolved by running this command as root.
hugeadm --pool-pages-min DEFAULT:16M
Note: Confirm if the huge pages in EDB for Power is really set to 16 MB and not 2 MB. Refer to previous section of PostgreSQL (open source).
Pin the database file system into the memory
The name of the file system used for this exercise is tmpfs. In the test lab, a memory of 20 GB was allocated for EDB or PostgreSQL purposes. The total space used during the run was noted to be in the range of 15 GB to 19 GB for pgbench scale of 1000.
Run the following commands to create the tmfs file system: mkdir -p /media/tmp
mount -t tmpfs -o size=20G tmpfs /media/tmp
Note: A system reboot deletes this file system and its information. It should be backed up as needed.
Also, stop or disable any services or process that is not needed for this run or is not critical to the system. For example, IPV6 can be disabled, if not in use. The mail server can be stopped, and so on.
EnterpriseDB/PostgreSQL database setup
This section explains how to initialize a database cluster instance, apply the DB cluster parameter tunings, and create a database.
To begin with, log in as an enterprise/postgres user and make sure that the server utility binaries are defined in the path. Find the binary files at the default installation location at:
EnterpriseDB - /usr/ppas-X/bin. Where X is the
release version number
PostgreSQL - /usr/local/pgsql/bin
a. Initialize the DB cluster instance
The initdb utility is used to create a DB cluster instance. The location for the DB files needs to be specified as a parameter in the command. The tmpfs file system created in the earlier step is used here as the database instance folder.
initdb -D /media/tmp/data
This creates a database repository along with a database parameter file, postgresql.conf.
b. Tune the DB server parameters
Find the default DB cluster parameter in the postgresql.conf file. For this exercise, the following parameters were changed to the recommended values based on the selected workload (pgbench). Parameters might differ according to the specific workload being run.
- shared_buffers = 20 GB (Tune this based on the memory allocated for the LPAR. The recommendation is that this cannot exceed 1/4th of the LPAR memory.)
- maintenance_work_mem = 512 MB
- checkpoint_completion_target = 0.9
- effective_cache_size = 64 GB
- work_mem = 512 MB
- wal_buffers = 16 MB
- checkpoint_segments = 300
- synchronous_commit = off
- Disable Dynatune by commenting out the edb_dynatune and edb_dynatune_profiles
Note: Some of the parameter tunings might become invalid in the future release of the product and some of them are valid only for EDB Postgres Advanced Server. If any parameter fails or displays an error, comment it out. As a general recommendation, if a predefined parameter named in the list provided in this section is not found, skip it.
c. Start the DB cluster and create a DB
The DB cluster is started using the pg_ctl utility that comes with the server binaries. Output is redirected to a file using the –l option.
pg_ctl -D /media/tmp/data -l logfile start
It can also be started using theedb-postgres -D /media/tmp/data
command.- Database can be created using the createdb command-line utility. Here,
pgbench is chosen as the database name.
createdb pgbench
PostgreSQL and EnterpriseDB Postgres Advanced Server database benchmarking
This section discusses the benchmark test that ran on the database after initializing the workload.
Reinitializing the DB is not needed for every run, but is recommended as it refreshes the DB.
Benchmarking tool pgbench
pgbench is a simple utility to run the benchmark tests on EDB Postgres
Advanced Server or PostgreSQL. Find more information about this utility at:
https://www.postgresql.org/docs/devel/static/pgbench.html
pgbench offers various read-only (select only query) and read/write (select, update, and insert queries) modes. For this exercise, the select only option of the pgbench was used. This utility can be run on the same system as PostgreSQL or EDB Postgres Advanced Server. This utility can also be run on a separate computer over the network as well.
Initialize the database
First, the database must be initialized. The pgbench utility is invoked
using the -I option and a scaling factor is specified. In this
exercise, the test team used a scaling factor of 1000. pgbench -i -s 1000
pgbench
Initialization takes some time as it populates the DB. Scaling of 1000 typically consumes around 16 GB of memory.
Run the benchmarking tool
The parameter passed is -T, which specifies the duration for which
the tool runs. -S enables select only loads,
-c is the number of clients, and -j is the
number of worker threads. This run is performed for various client and thread
counts. For this test, the client and thread resided on the same core. The
pgbench tool is run for 5 minutes (300 seconds). For stable results,
running pgbench for 5 minutes or longer is recommended. pgbench
-n -S -c 64 -j 64 pgbench -T 300
At the end of the run, this utility displays the results as transactions per second (TPS) including and excluding connections.
Figure 4. Sample result output from pgbench tool

Downloadable resources
Related topics
- IBM Linux on Power (all topics)
- IBM Linux Technology Centers
- The Linux on Power Community
- IBM developerWorks – The Linux on Power Community
- IBM PowerVM Virtualization Introduction and Configuration
- IBM PowerVM Virtualization Managing and Monitoring
- IBM Techdocs – technical white papers
- EnterpriseDB Postgres Advanced Server general information
- EnterpriseDB Postgres Advanced Server manuals address general installations and configuration
- Red Hat Enterprise manuals address the system requirements and physical hardware setup