Vacuum Error: "WARNING: Relation "xxxx" Contains more than "max_fsm_pages" pages with useful free space"

Products

• VMware Tanzu Greenplum

Issue/Introduction

When running VACUUM on a table results in the message, "WARNING: relation "xxxx" contains more than "max_fsm_pages" pages with useful free space". 
 

Error Message

VACUUM ANALYZE pg_catalog.pg_attribute;
WARNING:  relation "pg_catalog.pg_attribute" contains more than "max_fsm_pages" pages with useful free space  (seg19 sdw3:1028 pid=316679)
HINT:  Consider compacting this relation or increasing the configuration parameter "max_fsm_pages".

In the PostgreSQL documentation, the max_fsm_pages is defined as:

Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot. This setting must be at least 16 * max_fsm_relations. The default is chosen by initdb depending on the amount of available memory and can range from 20k to 200k pages. This parameter can only be set at server start.
 

In other words, it warns if the table has a lot of bloat and during VACUUM (without FULL) your datavase hits the limit of max_fsm_pages capacity to record that free space. Since these free spaces are now not recorded, VACUUM will not help in marking that dead space as free and the bloat on the object remains.

The parameter max_fsm_pages is not present in GPDB 6.x and above.

Environment

OS: RHEL 5.x

Resolution

Workaround

1. Change the current settings by increasing the parameter "max_fsm_pages" to a higher value. Changes to the parameter will require a database restart,

Or

2. Run VACUUM FULL on the object to release unused space to the OS and space not recorded on the FSM pages.

Notes:

• An exclusive lock is issued when you run VACUUM FULL, which might lead to blocked user queries.
• The VACUUM FULL can also take a long time to run. It should be run when the system is under very low load.
• In 5.x, it may be quicker to REORGANIZE the table (non-catalog tables only), This option is only valid for user tables and is described below.
• With architecture changes in GPDB 6, it is recommended to use VACUUM FULL over REORGANIZE. 

 

ALTER TABLE <schema-name>.<table-name> set with (reorganize=false) distributed randomly;
ALTER TABLE <schema-name>.<table-name> set with (reorganize=true) distributed by (<distribution-column>)

To release the bloat of an index, use this command:

REiNDEX TABLE <schema-name>.<table-name>;