Sign In with your
Trend Micro Account
Need Help?
Need More Help?

Create a technical support case if you need further support.

VACUUM does not work in PostgreSQL in InterScan Messaging Security Suite (IMSS) 7.1

    • Updated:
    • 9 Oct 2015
    • Product/Version:
    • InterScan Messaging Security Suite 7.1 Linux
    • Platform:
    • Linux - Red Hat RHEL 3 32-bit
    • Linux - Red Hat RHEL 4 32-bit
    • Linux - Red Hat RHEL 5 32-bit
    • Linux - Red Hat RHEL 6 32-bit
    • Linux - SuSE 10
    • Unix - Solaris (Sun) version 10 (SunOS 5.10)
    • Unix - Solaris (Sun) version 8 (SunOS 5.8)
    • Unix - Solaris (Sun) version 9 (SunOS 5.9)
Summary

There have been cases where the database size (specifically pg_largeobject) keeps growing even when VACUUM is done every day. Your database keeps growing until it reaches VACUUM FULL.

However, this does not mean that VACUUM cannot work on pg_largeobject. The command treats all the tables equally in PostgreSQL. The reason why it seems to not work is related to the parameter, "max_fsm_pages", on postgresql.conf.

The Free Space Map (FSM) is where postgreSQL keeps track of pages that have free space available for use. Each time it needs space in a table, it will look into the FSM first. If it cannot find any free space, then it will fall back to adding the information to the end of the table.

FSM has limited size (max_fsm_pages). It is used for VACUUM to find out how many and which pages to look for when it frees up the space. In InterScan Messaging Security Suite (IMSS), the "max_fsm_pages" parameter is set to "20000" by default. This means that every time it VACUUM, it can free 20000*8K = 160MB, at most. If the purged size exceeds 160MB every day, the other dead tuples will not be freed by VACUUM and the DB size will keep growing.

The VACUUM FULL is not limited by "max_fsm_pages" and "max_fsm_relations", but the parameters require an exclusive lock on each table while VACUUM FULL is processing them. Therefore, frequent use of VACUUM FULL can have an extremely negative effect on the performance of the current database.

Details
Public

PostgreSQL suggests that the database is frequently updated by operations, like update, delete, insert, etc., and using VACUUM will be more efficient than VACUUM FULL. Also, by expanding the FSM to cover all the dead tuples, you may never need to run VACUUM FULL again, except on holidays. FSM is kept in the memory by postgreSQL and one FSM occupies 6bytes of memory.

To resolve this issue, adjust the value of the "max_fsm_pages" parameter:

  1. Download the vacuum_pglargeobject_va.sh tool.
  2. Extract and execute vacuum_pglargeobj.sh to run VACUUM FULL.
  3. After VACUUM FULL is complete, open the /var/imss/pgdata/postgresql.conf file.
  4. Look for the "max_fsm_pages" parameter and set its value to "100000".
  5. Save and close the file.
  6. Use the following command to restart all the services, including postgreSQL:

    /opt/trend/imss/script/imssctl.sh restart

VACUUM will take effect on all the tables after changing the value of the parameter.

Premium
Internal
Rating:
Category:
Troubleshoot
Solution Id:
1055349
Feedback
Did this article help you?

Thank you for your feedback!

To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.

If you need additional help, you may try to contact the support team. Contact Support


To help us improve the quality of this article, please leave your email here so we can clarify further your feedback, if neccessary:
We will not send you spam or share your email address.

*This form is automated system. General questions, technical, sales, and product-related issues submitted through this form will not be answered.


Need More Help?

Create a technical support case if you need further support.