© Copyright IBM Corporation 2016 TrademarksIBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 1 of 12

IBM Power Systems solution for EnterpriseDBPostgres Advanced ServerAn article on PostgreSQL and EnterpriseDB Postgres AdvancedServer running Linux on IBM POWER8 processor-based servers –installation and tuning best practices for IBM Power servers

Deepak Narayana October 21, 2016

This article describes the general installation and tuning of EnterpriseDB Postgres AdvancedServer database on IBM Power Systems servers running Linux. IBM Power servers offersignificant advantages compared to similar configurations of Intel Xeon processor-basedsystems (Broadwell).

Introduction

The primary focus of this article is on the use, configuration, and optimization of PostgreSQL andEnterpriseDB Postgres Advanced Server running on the IBM® Power Systems™ servers featuringthe new IBM POWER8® processor technology.

Note: The Red Hat Enterprise Linux (RHEL) 7.2 operating system was used. The scope of thisarticle is to provide information on how to build and set up of PostgreSQL database from opensource 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 runningLinux® is based on the open source database, PostgreSQL, and is capable of handling a widevariety 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 RedundantArray of Independent Disks (RAID). In order to eliminate the impact of performance differencesbetween disparate storage technologies, the database was instead kept in memory on a tmpfsvirtual file system.

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 2 of 12

EnterpriseDB Postgres Advanced Server on IBM Power Systems runningLinuxEnterpriseDB (EDB) Postgres Advanced Server is built on PostgreSQL, one of the most advancedopen 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 existingOracle-based applications on a low-cost, high-performance PostgreSQL-based platform, orrun adjacent applications that integrate seamlessly with your mission-critical databases withoutadditional Oracle licenses.

Always ensure that EDB Postgres Advanced Server is installed with the latest service packavailable from the EDB website at:https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

PrerequisitesRefer to EDB manuals for software prerequisites.

• Confirm that there is adequate paging space and free space on the /tmp and / (root) filesystems.

• 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 ServerEDB 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 definea yum repository for EDB rpms for easy one-step installations. Defining a yum repository for thebase OS and software installation packages enables the installation of software to automaticallyfetch the rpm packages (which are the prerequisites).

Configuring the yum repositoryConfirm the yum repository defined for BASE OS as it helps. A RHEL yum repository can bedefined by pointing to a CD/DVD media, a shared network location, or a local directory thatcontains the RHEL installation media. The test team defined it using a local directory. The yumconfiguration files are located in the /etc/yum.repos.d/ directory.

You can find more information about yum repositories at:

ibm.com/developerWorks/ developerWorks®

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 3 of 12

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 ServerYou 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 yumrepository 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. Forexample:yum install ppas95-server

This command automatically installs all the required prerequisites, and also creates a databaseadministrator user, enterprisedb, if not already present.

Before proceeding with creating and configuring the DB instance, it is best to apply tunings. In thisexercise, the test team performed various system tunings, including kernel tuning.

Building and installing the PostgreSQL ServerTo best use the IBM POWER8 features and optimized builds, it is recommended to use the latestversion of IBM Advance Toolchain for PowerLinux. There is a tool available to download AdvanceToolchain called AT Downloader. The AT Downloader is a script to download the latest version ofthe 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 thendownload 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 OSmedia, as needed, based on the configuring process.

• During the build process, be sure to specify make all. The contrib folder has additionalpackages such as pgbench and others.

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 4 of 12

Power Systems configurationThe following settings were used for this exercise.

• Set single partition mode [non-virtualized logical partition (LPAR)] – The whole system wasassigned 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 managedsystem's IP.

Note: The ASMI options might not be available if a firmware other than IBM PowerVM®, such asOPAL, is set. In these cases, set the processor modes through the Linux command line using thecpupower util with the governor option set to performance mode. This setting must be applied toall 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 screencaptures.

Figure 1. Accessing ASMI from HMC

To turn off the Idle Power Saver option, log in and click System Configuration -> PowerManagement -> Idle Power Saver.

ibm.com/developerWorks/ developerWorks®

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 5 of 12

Figure 2. ASMI – Idle Power Saver page

To enable the dynamic power saver mode, perform the following steps:

1. Click Power Mode Setup in the left pane, and select the Enable Dynamic Power Saver(favor performance) mode option in the right pane.

2. Click Continue.

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 6 of 12

Figure 3. ASMI – Power Mode Setup page

These settings help in increasing the processor speed. This can be verified at the shell by usingthe ppc64_cpu –freq command. This operation does not require the system to be shut down orrestarted. 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 IBMPowerVM®, such as OPAL, is set. In these cases, set the processor modes through the Linuxcommand line using the cpupower util with the governor option set to performance mode. Thissetting 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 kernelparameter settings are to be applied as a root user. Perform the activity with caution and set thevalues 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

ibm.com/developerWorks/ developerWorks®

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 7 of 12

• 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 needadjustments accordingly:

• shmallshmall=$(( `grep MemTotal /proc/meminfo |awk '{print $2}'` * 1024 * 9 / (`getconf

PAGE_SIZE` * 10)))

sysctl -w kernel.shmall=$shmall

• shmmaxshmmax=$(( `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 asbeneficial.

• Set the processor's simultaneous multithreading (SMT) snooze delay to be higher usingthe 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 ofa higher priority wants to use the processor. So, if there are many processes with similarpriorities, 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 (usuallyto 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.

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 8 of 12

• 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 pagesize 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 insrc/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 beadapted 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 haveshown in pgbench testing to as beneficial.

• Add the following entries to the limits.conf file for the EDB user (or Postgres useraccordingly) /etc/security/limits.confenterprisedb 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 beresolved 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. Referto 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 wasallocated for EDB or PostgreSQL purposes. The total space used during the run was noted to bein the range of 15 GB to 19 GB for pgbench scale of 1000.

ibm.com/developerWorks/ developerWorks®

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 9 of 12

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 asneeded.

Also, stop or disable any services or process that is not needed for this run or is not critical to thesystem. For example, IPV6 can be disabled, if not in use. The mail server can be stopped, and soon.

EnterpriseDB/PostgreSQL database setupThis section explains how to initialize a database cluster instance, apply the DB cluster parametertunings, and create a database.

To begin with, log in as an enterprise/postgres user and make sure that the server utility binariesare 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 numberPostgreSQL - /usr/local/pgsql/bin

a. Initialize the DB cluster instanceThe initdb utility is used to create a DB cluster instance. The location for the DB files needs to bespecified as a parameter in the command. The tmpfs file system created in the earlier step is usedhere 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 parametersFind the default DB cluster parameter in the postgresql.conf file. For this exercise, the followingparameters 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. Therecommendation 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

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 10 of 12

Note: Some of the parameter tunings might become invalid in the future release of the product andsome of them are valid only for EDB Postgres Advanced Server. If any parameter fails or displaysan error, comment it out. As a general recommendation, if a predefined parameter named in thelist 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 isredirected 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 chosenas the database name.createdb pgbench

PostgreSQL and EnterpriseDB Postgres Advanced Server databasebenchmarkingThis 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 orPostgreSQL. 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 insertqueries) modes. For this exercise, the select only option of the pgbench was used. This utility canbe run on the same system as PostgreSQL or EDB Postgres Advanced Server. This utility canalso 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 ascaling 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 around16 GB of memory.

Run the benchmarking tool

The parameter passed is -T, which specifies the duration for which the tool runs. -S enablesselect only loads, -c is the number of clients, and -j is the number of worker threads. This run

ibm.com/developerWorks/ developerWorks®

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 11 of 12

is performed for various client and thread counts. For this test, the client and thread resided onthe same core. The pgbench tool is run for 5 minutes (300 seconds). For stable results, runningpgbench 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) includingand excluding connections.

Figure 4. Sample result output from pgbench tool

developerWorks® ibm.com/developerWorks/

IBM Power Systems solution for EnterpriseDB PostgresAdvanced Server

Page 12 of 12

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

© Copyright IBM Corporation 2016(www.ibm.com/legal/copytrade.shtml)Trademarks(www.ibm.com/developerworks/ibm/trademarks/)