Sophos

Online support

Product maintenance

Contact support

Support services

PureMessage for UNIX: Tuning PostgreSQL for PureMessage 5.x

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, Mail filtering, Centralized Server Manager (CSM)). 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 or FreeBSD DB ServerDedicated Solaris 8 or 9 DB ServerDedicated Solaris 10 DB Server
RAM in GBSHM in bytesRAM in GBSHM in bytesRAM in GBSHM in bytes
210737418242107374182421073741824
421474836484214748364842147483648
842949672968429496729684294967296

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 8 and 9:
    1. Edit /etc/system, adding or modifying the following lines. This sets the shared memory segment to the specified amount on boot. 
      set shmsys:shminfo_shmmax=[SHM in bytes from the table above]
      set shmsys:shminfo_shmmni=256
      set shmsys:shminfo_shmseg=256
      set semsys:seminfo_semmap=256
      set semsys:seminfo_semmni=512
      set semsys:seminfo_semmns=512
      set semsys:seminfo_semmsl=32
    2. Reboot Solaris for the changes to take effect.

  • 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]
  • FreeBSD:

    To set the shared memory segment to the specified amount on boot:

    1. Edit /etc/sysctl.conf, adding or modifying the following lines. 
      kern.ipc.shmall=[SHM in bytes from the table above]
      kern.ipc.shmmax=[SHM in bytes from the table above]
      kern.ipc.semmap=256
    2. Edit /boot/loader.conf, adding or modifying the following lines: 
      kern.ipc.semmni=256
      kern.ipc.semmns=256
      kern.ipc.semmnu=256

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 Parameters2 GB System4 GB System8 GB System
Solaris 9Solaris 10Other OSSolaris 9Solaris 10Other OSSolaris 9Solaris 10Other OS
max_connections
(concurrent connections)		100100100150150150200200200
shared_buffers6000600016384120001200032768120002400065536
vacuum_mem262144
sort_mem32768
wal_buffers200
effective_cache_size250,000250,000800,000500,000500,0001,600,0001,000,0001,000,0003,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.