PureMessage for UNIX: Database Disk Usage Growth

  • Article ID: 22823
  • Updated: 25 Aug 2011

Issue

The database is using a lot of disk space and is growing. Database-related activities or jobs, such as reporting, are running slowly.

PureMessage database table growth can be monitored using the command pgdisk, which is run as the PureMessage user ('pmx' by default).

PureMessage Reporting Options

PureMessage has two sets of reporting data. One is available via the PureMessage Manager; the other via the Groups Web Interface. By default, both are enabled. Even if you are not using the Groups Web Interface to delegate administrative tasks, you may still prefer to view reports with this interface.

The Manager reports are available at:

https://<ManagementServerHostname>:18080

The Groups Web Interface reports are available at:

http://<ManagementServerHostname>:28080/groups

If you are experiencing large amounts of database growth due to a high-volume site, it is recommended that you view and manage reports using the Groups Web Interface.

To switch to Groups Web Interface reports:

  1. As the 'pmx' user, make the following changes to the scheduler.d file:

    Message Log Consumers:
    /opt/pmx/etc/scheduler.d/consume-message-log.conf

    Change the action line to:

    action = 'pmx-reports-consume-message-log --v2'

    Blocklist Log Consumers:
    /opt/pmx/etc/scheduler.d/consume-blocklist-log.conf

    Change the action line to:

    action = 'pmx-reports-consume-blocklist-log --v2'

  2. as the 'pmx' user, restart the scheduler:

    pmx-scheduler restart

  3. Once your installation has been converted to Groups Web Interface reporting, you can truncate the following tables, which will reduce space. As the 'pmx' user, run:

    export PGDATABASE=pmx_quarantine ;\
    PATH=/opt/pmx/postgres/bin:$PATH ;\
    psql -qc "truncate prd_sender;" ;\
    psql -qc "truncate prd_relay;" ;\

Ensure that the scheduled job pmx-prd-reduce-v2 is scheduled to run as described below.
This can be accessed from https://<ManagementServerHostname>:18080 -> Local Services -> Scheduled Jobs -> pmx-prd-reduce-V2.

Hour: Any
Minutes: 49
Seconds: 00
Month: Any
Day: Any
Week Day: Any

Further Mitigation Techniques

I: The database server is doing unnecessary work

If the database server does not filter incoming email messages from external untrusted MTAs and outgoing email messages, then it is safe to disable the IP Blocker service, as well as the blocklist-compile, consume-blocklist-log, and pmx-blocklist-data-update scheduled jobs.

Log on to the database server as the 'pmx' user, and run:

pmx-blocker stop ;\
cd ;\
mv ../etc/init.d/pmx-blocker ~ ;\
pmx-scheduler disable blocklist-compile ;\
pmx-scheduler disable consume-blocklist-log ;\
pmx-scheduler disable pmx-blocklist-data-update ;\
pmx-scheduler restart

If you are using Groups Web Interface reports, as the 'pmx' user, disable the oshealth_on_fours and the oshealth_on_nines scheduled jobs:

pmx-scheduler disable oshealth_on_fours ;\
pmx-scheduler disable oshealth_on_nines ;\
pmx-scheduler restart

This frees up memory that could otherwise be used by PostgreSQL,  and it minimizes unnecessary disk operations. 

II: PostgreSQL performance tuning

The default values for various PostgreSQL settings are conservative, and they work well for most deployments. However, you also have the option to further tune PostgreSQL to achieve optimal performance based on your system specifications. We recommend that you review our PostgreSQL performance tuning guide, and then edit applicable settings accordingly. This will improve database-related operations.


III: The quarantine_rule_hits table and the message_body table

By default, PureMessage 5.x allows you  to perform quarantine searches by rule hit in advanced mode. It does so by inserting rule hits data into the quarantine_rule_hits table for every message processed, consuming additional disk space.

If the quarantine_rule_hits table is using a lot of disk space and growing rapidly, then customers are encouraged to disable this "insert rule hits" feature, and reduce the size of this table.

When the "insert rule hits" feature is disabled, customers can access the rule hits information under the Quarantine Info tab in the message details window or directly in the /opt/pmx/var/log/message_log file.

To disable the insert rule hits:

  1. As the 'pmx' user, back up the current /opt/pmx/var/qdir/db.conf file.
  2. In a text editor, edit /opt/pmx/var/qdir/db.conf as follows:

    Change

    # insert_rule_hits = 0

    to

    insert_rule_hits = 0

  3. Save the change.

Note: You do not have to restart any PureMessage services. However, in a multi-server environment, you must perform the three steps above on each PureMessage server.

As of PureMessage 5.2, the message_body table no longer needs to hold data for all messages processed. For this reason, we recommend that you reduce the size of the message_body table.

To reduce the size of the quarantine_rule_hits table and the message_body table:

  1. As the 'pmx' user, log on to the database server, and run:

    exportPGDATABASE=pmx_quarantine ;\
    PATH=/opt/pmx/postgres/bin:$PATH ;\
    psql -qc "truncate quarantine_rule_hit;" ;\
    psql -qc "truncate message_body;" ;\
    psql -qc "delete from autovac_state where name ~ 'quarantine_rule_hit';" ;\
    psql -qc "delete from autovac_state where name ~ 'message_body';" ;\
    psql -qc "reindex database pmx_quarantine;"

IV. Analyze the database

When the database is using a lot of disk space and growing, it is possible that the database has not been analyzed properly. To rectify this issue, log on to the database server as the 'pmx' user, and run:

    pmx-database analyze

V. Dump and restore the database

Follow the procedure outlined in the following support article:

http://www.sophos.com/en-us/support/knowledgebase/15422.aspx

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

      Rate this article

      Very poor Excellent

      Comments