Introduction
Setting up PostgreSQL and EnterpriseDB Postgres Advanced Server
EnterpriseDB/PostgreSQL database setup
PostgreSQL and EnterpriseDB Postgres Advanced Server database benchmarking
Downloadable resources
Related topics
Comments

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

Deepak Narayana
Published on October 21, 2016

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

View image at full size

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

View image at full size

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

View image at full size

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_features
The 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.
1. Enter the following command in the Postgresql source directory.
  cd postgresql
2. Manually change the huge page size to 16 MB in
  src/backend/port/sysv_shmem.c:360

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
or
huge_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 68719476736
This 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_PRELOAD
In 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 startIt can also be started using the edb-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.