The Sophos Community will be unavailable from 13:00 to 18:00 UTC this Saturday, October 1st for upgrades. Stay tuned to our Twitter account @SophosSupport for updates.
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 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:
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
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 ;\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: AnyMinutes: 49Seconds: 00Month: AnyDay: AnyWeek Day: Any
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:
In a text editor, edit /opt/pmx/var/qdir/db.conf as follows:Change
# insert_rule_hits = 0
insert_rule_hits = 0
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:
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:
V. Dump and restore the database
Follow the procedure outlined in the following support article:
Every comment submitted here is read (by a human) but we do not reply to specific technical questions. If you need technical support please post a question to our community. Alternatively for licensed products open a support ticket.