PureMessage for UNIX: Tuning PostgreSQL for PureMessage

  • Article ID: 35003
  • Rating:
  • 4 customers rated this article 4.3 out of 6
  • Updated: 27 Sep 2011

During installation, PureMessage automatically adjusts the PostgreSQL database in an attempt to improve performance. If the adjustments are successful, PureMessage displays a confirmation message. If the adjustments fail, it is likely due to the shared memory configuration of your system. PureMessage will revert the changes and create a postgresql.conf.recommended file that contains appropriate system settings. This tuning guide provides recommendations for hardware usage, system allocation and configuration, and settings for shared memory and semaphores.

Important: The PureMessage installer assumes that the Database Server role is installing PostgreSQL on a system with 1GB of RAM. If more than 1GB of RAM is available, you should follow these instructions to improve the performance of PostgreSQL.

System Hardware Considerations

Disks, RAM and CPU

PostgreSQL, like other transactional RDBMS's, is I/O intensive for updates. A fast SCSI disk array is the single greatest performance factor. After the I/O subsystem, RAM is the next most significant factor, followed by CPU speed. Expect to use up to 4 GB of RAM.

Note: Hyperthreading on Intel Xeon systems should be disabled in the BIOS because it adversely affects PostgreSQL performance.

System Allocation

PostgreSQL competes with other system processes for disk access and OS disk cache, which can make the database and the other processes perform poorly under load. For optimal performance in high-volume deployments, the Database server role (PostgreSQL) should be installed on a separate server from other PureMessage roles (for example, the Mail Filter role and Mail Transfer Agent role). This is particularly important in deployments with multiple mail-filtering servers.

Transaction Log (pg_xlog)

PostgreSQL uses Write-Ahead Logging (WAL) for transactions to protect against data loss. For high traffic systems, the performance of this logging can be increased by moving the log file (pg_xlog) to its own disk.

For example, to move pg_xlog to its own disk and symbolically link it to the default $PGDATA directory:

$ mkdir /disk2/pg_xlog # Or a disk or directory of your choosing
$ pmx-database stop
$ cd /opt/pmx/postgres/var/data
$ find pg_xlog | cpio -pmd /disk2
$ mv pg_xlog pg_xlog.old #Make backup of original pg_xlog
$ ln -s /disk2/pg_xlog . # You must use the full path
$ pmx-database start

RAID 1+0 / 0+1 vs. RAID 5

RAID 5 with 3 disks, though it is a standard configuration, is the slowest array configuration possible for PostgreSQL. A RAID 5 configuration performs as much as two times slower for pg_xlog traffic. This also can give you as little as 50% of the query speed as running on a plain SCSI disk. We recommend RAID 1, 1+0 or 0+1 for any set of 2, 4 or 6 disks.

Shared Memory and Semaphores

PostgreSQL requires semaphore ("sem") and shared memory ("shm") resources. System limits on these resources will restrict the number of concurrent database connections, and affect performance. On Solaris systems, the default system resource parameters are set too low and must be increased.

Detailed instructions on configuring kernel parameters for running PostgreSQL, with formulas for deriving reasonable settings, are available in the "Managing Kernel Resources" section of the PostgreSQL documentation. The PureMessage installation script attempts to set this minimally, but if it is unable to do so, or if you want to reconfigure what it has set, then this section has guidelines for appropriate settings and instructions for making these changes manually.

System Configuration

The table below shows the recommended minimum shared memory (SHM) settings, based on the RAM installed on the machine that is running the PureMessage Database server role (PostgreSQL). On a dedicated PureMessage database server, the SHM setting should represent at least one half of the machine's physical RAM .

Dedicated Linux ServerDedicated Solaris 10 DB Server
RAM in GBSHM in bytesRAM in GBSHM in bytes
2 1073741824 2 1073741824
4 2147483648 4 2147483648
8 4294967295 8 4294967295

Make the changes that are applicable to your operating system and version, in accordance with the table above. These changes must be made as the root user.

Solaris 10:

Linux:

  1. Edit /etc/sysctl.conf, adding or modifying the following lines. This sets the shared memory segment to the specified amount on boot:kernel.shmall=[SHM in bytes from the table above]
    kernel.shmmax=[SHM in bytes from the table above]
  2. To set the shared memory size immediately, without rebooting the server, run the following two commands: $ sysctl -w kernel.shmall=[SHM in bytes from the table above]
    $ sysctl -w kernel.shmmax=[SHM in bytes from the table above]

PostgreSQL Configuration on a Dedicated Server

Adjustments to the shared_buffers and effective_cache_size settings of PostgreSQL may be required.

Detailed instructions on configuring PostgreSQL run-time parameters can be found in the "Run-time Configuration" section of the PostgreSQL documentation.

In general, shared_buffers and effective_cache_size can be set according to the guidelines below. To change these settings:

  1. As the PMX user, shutdown PostgreSQL with the following command: pmx-database stop
  2. Open /opt/pmx/postgres/var/data/postgresql.conf for editing, and enter the values as shown in the following table in accordance with the physical RAM on your dedicated PureMessage database server. This is the server running the Database server role (PostgreSQL).
Postgresql.conf
Parameters
2 GB System4 GB System8 GB System
Solaris 10LinuxSolaris 10LinuxSolaris 10Linux
max_connections
(concurrent connections)
100 100 150 150 200 200
shared_buffers 6000 16384 12000 32768 24000 65536
vacuum_mem 262144
sort_mem 32768
wal_buffers 200
effective_cache
250,000 800,000 500,000 1,600,000 1,000,000 3,200,000
  1. Restart PostgreSQL with the following command: pmx-database start

PostgreSQL Configuration in a Single-Server Deployment

Shared memory is not the total memory PostgreSQL can work with; rather it is the block of dedicated memory PostgreSQL uses for active operations, and it should be a minority of the total RAM on the machine. This is because PostgreSQL must dynamically allocate memory per connection (process) for sorts, for in-memory hash tables, and for other activities that don't use shared memory.

On a single-server PureMessage deployment, milters consume a large amount of memory. So the milters should run with a concurrency limit that leaves enough physical memory for PostgreSQL.

  1. As the "pmx" user, find out how much memory your milters are using by running the following command: pmx-milter status

    The output of this command will look, in part, something like:

    slot#1: pid=34207 vsz=78.9 acc=1/1/1 req=1
  2. Use the vsz value on slot 1, which is the memory in MB, to calculate what the milter concurrency limit using the following formula: (RAM for PostgreSQL x (<server RAM in GB> - 1)) / milter size in MB = optimum milter concurrency limit

    So, in this example, this would be:

    (1024 x (4 - 1)) / 79 = 39 milters
  3. Set the milter concurrency limit by running the following command as the "pmx" user: $ pmx-config concurrency_limit 39
  4. Restart the milter by running the following command as the "pmx" user: pmx-milter restart

 
If you need more information or guidance, then please contact technical support.

Rate this article

Very poor Excellent

Comments