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:
The Groups Web Interface reports are available at:
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:
- As the 'pmx' user, make the following changes to the
Message Log Consumers:
Change the action line to:
action = 'pmx-reports-consume-message-log --v2'
Blocklist Log Consumers:
Change the action line to:
action = 'pmx-reports-consume-blocklist-log --v2'
as the 'pmx' user, restart the scheduler:
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 ;\
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.
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
pmx-blocklist-data-update scheduled jobs.
Log on to the database server as the 'pmx' user, and run:
pmx-blocker stop ;\
mv ../etc/init.d/pmx-blocker ~ ;\
pmx-scheduler disable blocklist-compile ;\
pmx-scheduler disable consume-blocklist-log ;\
pmx-scheduler disable pmx-blocklist-data-update ;\
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 ;\
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.
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
To disable the insert rule hits:
- As the 'pmx' user, back up the current
In a text editor, edit
/opt/pmx/var/qdir/db.conf as follows:
# insert_rule_hits = 0
insert_rule_hits = 0
- 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
To reduce the size of the quarantine_rule_hits table and the message_body table:
As the 'pmx' user, log on to the database server, and run:
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:
V. Dump and restore the database
Follow the procedure outlined in the following support article: