PostgreSQL recommendations

The database is an important part of BMC Client Management , especially in highly distributed environments. For each of the three basic types of database-Web Application (Web), Online Transaction Processing (OLTP), and Data Warehousing (DW)-the hardware component priorities are different. Client Management works like a web application with web services; it behaves like an OLTP application when integrating all information reported by devices; and it performs like a DW application for report generation, aggregating the results and displaying inventory views.

This topic provides recommendations for the appropriate database configuration for PostgreSQL 9.1 in highly distributed environments:

Disk layout

When you run the Create_TS.pgsql.sql script, which is provided for tablespace creation on your database, BMC recommends that you segregate the tablespaces on different disks, as shown in the following table:

Tablespaces for tables

Tablespaces for indexes

Recommended minimum disk layout

Recommended best disk layout

Recommended RAID implementation

Storage capacity sizing

When sizing storage capacity for a PostgreSQL system, you need to consider the following PostgreSQL database components:

• Database files
• Transaction log files
• Backup

System tuning for write-heavy operations

You can tune your system for better performance for the following write-heavy operations:

File access times

Applying the noatime attribute can significantly improve the file I/O performance of your server. Edit the /etc/fstab file and add the noatime attribute as follows:

/dev/sdb1 on / PostgreSQL ext3   rw,noatime
/dev/sdc1 on / Pg_xlog ext3   rw,noatime
/dev/sdd1 on / Tablespaces for tables ext3   rw,noatime
/dev/sde1 on / Tablespaces for indexes ext3 rw,noatime 
# repeat for each additional disk

Read ahead

Modifying the Read-Ahead parameter can improve the read performance of a disk.

Type # blockdev --getra /dev/sda to check the Read-Ahead value of an individual disk. The default value is generally 256.

To permanently increase this value to 4096, edit the /etc/rc.local file by adding the following block to the end of the file:

blockdev --setra 4096 /dev/sda 
blockdev --setra 4096 /dev/sdb 
blockdev --setra 4096 /dev/sdc  
# repeat for each additional disks

Read caching and swapping

Modifying the following parameters increases database performance:

To permanently change these parameter values, edit the /etc/sysctl.conf file as follows:

vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.swappiness = 0

To set the values without rebooting, run the sysctl -p command after saving and exiting the sysctl.conf file.

Shared memory and semaphores

A large PostgreSQL installation can quickly exhaust various operating system resource limits. The following example shows how to increase the shared memory to 16 GB of RAM:

# sysctl -w kernel.shmmax=8420048896
# sysctl -w kernel.shmall=2055676
# sysctl -w kernel.sem = 250 32000 32 128

PostgreSQL configuration

PostgreSQL can easily be tuned using the third-party-tool pgtune . It is licensed under a standard three-clause BSD license. pgtune works by taking an existing postgresql.conf file as an input and making changes to it based on the amount of RAM in your server and a suggested workload. pgtune then outputs a new file with suggestions.

The following example is for a server with 16 GB of RAM and a suggested workload of 100 simultaneous connections to the server.

1. Enter the following command line:# ./pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune --type MIXED --connections=100
   The values recommended by pgtune are appended at the end of the postgresql.conf.pgtune file, for example:

   #------------------------------------------------------------------------------
   # pgtune run on 2013-07-08 
   # based on 16445412 KB RAM, platform Linux 
   #------------------------------------------------------------------------------
   default_statistics_target = 100
   maintenance_work_mem = 960MB
   checkpoint_completion_target = 0.9
   effective_cache_size = 11GB
   work_mem = 80MB
   wal_buffers = 16MB
   checkpoint_segments = 32
   shared_buffers = 3840MB

2. Add the following additional values at the end of the postgresql.conf.pgtune file:

   bgwriter_lru_maxpages = 1000 
   bgwriter_lru_multiplier = 4.0 
   random_page_cost = 2.0 
   cpu_tuple_cost = 0.03 
   log_autovacuum_min_duration = 0 
   autovacuum_max_workers = 5 
   autovacuum_vacuum_cost_delay = 10ms

3. Run the following commands to apply the new parameters and values to the original postgresql.conf file by renaming the postgresql.conf.pgtune pgtune file to postgresql.conf and then restarting PostgreSQL:

   mv $PGDATA/postgresql.conf $PGDATA/postgresql.conf.sav 
   mv $PGDATA/postgresql.conf.pgtune $PGDATA/postgresql.conf
   chown postgres:postgres $PGDATA/postgresql.conf
   chmod 0644 $PGDATA/postgresql.conf
   service postgresql restart