PureMessage for UNIX: Database Disk Usage Growth

Issue
Database is using a lot of disk space and is growing.

Sophos Product
PureMessage for UNIX 5.3.2+

Details
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" run as the pmx user.

Cause

Possible causes for rapid disk usage growth include the following bugs that have been fixed as of PureMessage 5.4.2:

DEF19189 V2 Reduction Job needs to run more frequently.
SUG09918 pmx-prd-reduce-v2 fails if reporting database not named pmx_quarantine
DEF13635 prd_mta_action_count grows too large with data.
DEF15569 V2 consumers should prune more data for 'Top' reports

Reporting Style

PureMessage has two sets of reporting data refered as V1 and V2. By default, both of these reports are enabled.

The V1 reporting is available in the following interface:

https://<ManagementServerHostname>:18080

The V2 reporting is available in the following interface:

http://<ManagementServerHostname>:28080/groups

If you are experiencing large amounts of database growth due to a high volume site, it is recommended that you move to V2 style reporting utilizing the groups interface.

To do this edit the following file (as the pmx user):

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'

You will need to restart the scheduler (as the pmx user):

pmx-scheduler restart

Once your installation has been moved to V2 style reporting, you can truncate the following tables which will reduce space (as the pmx user):

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

V2 reporting does not currently have the scheduling capability that V1 reporting has.  This has been noted by Product Management and will be included in a future release (SUG18525: Have the option to schedule V2 reports in the Groups UI ).

Please 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 out-going email messages, then it is safe to disable IP Blocker service and the blocklist-compile, the consume-blocklist-log as well as the pmx-blocklist-data-update scheduled jobs.

Log on to the database server as the PureMessage user, pmx by default, 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 V2 reports are being used, disable the oshealth_on_fours and the oshealth_on_nines scheduled jobs (as the pmx user):

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

These actions free up memory that could otherwise be used by PostgreSQL and minimize unnecessary disk IO operations.  These reports will be discontinued in future versions of PureMessage.

II: PostgreSQL performance tuning

The default values for various PostgreSQL settings are conservative and do work well for most deployments. However, customers also have the option to further tune PostgreSQL to achieve optimal performance based on their system specifications. We recommend customers to review our PostgreSQL performance tuning guide online at http://pminfo.sophos.com/pminfo/docs/PureMessage/5.3/PostgresTuning.html 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 enables customers to perform quarantine search by rule hit in advanced mode. It does so by inserting rule hits data into the quarantine_rule_hits table for every message processed at the cost of disk usage.

If the quarantine_rule_hit 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, see instructions later.

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 in the /opt/pmx/var/log/message_log file directly.

PureMessage 5.2 has changed how data is inserted into and retrieved from the message_body table; the message_body table no longer needs to hold data for all messages processed. For this reason, we recommend PureMessage 5.2.x and 5.3.x customers to reduce the size of the message_body table, see instructions later.

Steps to disable the insert rule hits:

Log on as the PureMessage user, pmx by default.
Backup the current /opt/pmx/var/qdir/db.conf file.
Open /opt/pmx/var/qdir/db.conf in a text editor and dhange the following line

# insert_rule_hits = 0

to

insert_rule_hits = 0

and then save the change.

Note: No PureMessage services need to be restarted.  In multi-server environment, perform these three steps on each PureMessage server.

Steps to reduce size of the quarantine_rule_hits table and the message_body table:

Log on to the database server as the PureMessage user, pmx by default, and run:

export PGDATABASE=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;"

Note: The two "delete from autovac_state ..." statements do not apply to PureMessage 5.2.x. Do not run them.

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 well. To rectify this issue, log on to the database server as the PureMessage user, pmx by default, and run:

pmx-database analyze

V. Dump and restore the database

Follow the procedure outlined in the following support article:

http://www.sophos.com/support/knowledgebase/article/15422.html