Greenplum Database heap tables use the PostgreSQL Multiversion Concurrency Control (MVCC) storage implementation. A deleted or updated row is logically deleted from the database, but a non-visible image of the row remains in the table. These deleted rows, also called expired rows, are tracked in a free space map. Running
VACUUM marks the expired rows as free space that is available for reuse by subsequent inserts.
If the free space map is not large enough to accommodate all of the expired rows, the
VACUUM command is unable to reclaim space for expired rows that overflowed the free space map. The disk space may only be recovered by running
VACUUM FULL, which locks the table, copies rows one-by-one to the beginning of the file, and truncates the file. This is an expensive operation that can take an exceptional amount of time to complete with a large table. It should be used only on smaller tables. If you attempt to terminate a
VACUUM FULL operation, the system can be disrupted.
It is very important to run
VACUUM after large
DELETE operations to avoid the necessity of ever running
If the free space map overflows and it is necessary to recover the space it is recommended to use the
CREATE TABLE...AS SELECT command to copy the table to a new table, which will create a new compact table. Then drop the original table and rename the copied table.
It is normal for tables that have frequent updates to have a small or moderate amount of expired rows and free space that will be reused as new data is added. But when the table is allowed to grow so large that active data occupies just a small fraction of the space, the table has become significantly "bloated." Bloated tables require more disk storage and additional I/O that can slow down query execution.
Bloat affects heap tables, system catalogs, and indexes.
VACUUM statement on tables regularly prevents them from growing too large. If the table does become significantly bloated, the
VACUUM FULL statement (or an alternative procedure) must be used to compact the file. If a large table becomes significantly bloated, it is better to use one of the alternative methods described in Removing Bloat from Database Tables to remove the bloat.
VACUUM FULL <database_name> and do not run
VACUUM FULL on large tables in a Greenplum Database.
Expired rows in heap tables are added to a shared free space map when you run
VACUUM. The free space map must be adequately sized to accommodate these rows. If the free space map is not large enough, any space occupied by rows that overflow the free space map cannot be reclaimed by a regular
VACUUM statement. You will have to use
VACUUM FULL or an alternative method to recover the space.
You can avoid overflowing the free space map by running the
VACUUM statement regularly. The more bloated a table becomes, the more rows that must be tracked in the free space map. For very large databases with many objects, you may need to increase the size of the free space map to prevent overflow.
max_fsm_pages configuration parameter sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Each page slot consumes six bytes of shared memory. The default value for
max_fsm_pages is 200,000.
max_fsm_relations configuration parameter sets the maximum number of relations for which free space will be tracked in the shared memory free-space map. It should be set to a value larger than the total number of tables, indexes, and system tables in the database. It costs about 60 bytes of memory for each relation per segment instance. The default value is 1000.
See the Greenplum Database Reference Guide for detailed information about these configuration parameters.
The statistics collected by the
ANALYZE statement can be used to calculate the expected number of disk pages required to store a table. The difference between the expected number of pages and the actual number of pages is a measure of bloat. The
gp_toolkit schema provides a
gp_bloat_diag view that identifies table bloat by comparing the ratio of expected to actual pages. To use it, make sure statistics are up to date for all of the tables in the database, then run the following SQL:
gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_diag; bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag ----------+------------+------------+-------------+-------------+--------------------------------------- 21488 | public | t1 | 97 | 1 | significant amount of bloat suspected (1 row)
The results include only tables with moderate or significant bloat. Moderate bloat is reported when the ratio of actual to expected pages is greater than four and less than ten. Significant bloat is reported when the ratio is greater than ten.
gp_toolkit.gp_bloat_expected_pages view lists the actual number of used pages and expected number of used pages for each database object.
gpadmin=# SELECT * FROM gp_toolkit.gp_bloat_expected_pages LIMIT 5; btdrelid | btdrelpages | btdexppages ----------+-------------+------------- 10789 | 1 | 1 10794 | 1 | 1 10799 | 1 | 1 5004 | 1 | 1 7175 | 1 | 1 (5 rows)
btdrelid is the object ID of the table. The
btdrelpages column reports the number of pages the table uses; the
btdexppages column is the number of pages expected. Again, the numbers reported are based on the table statistics, so be sure to run
ANALYZE on tables that have changed.
VACUUM command adds expired rows to the free space map so that the space can be reused. When
VACUUM is run regularly on a table that is frequently updated, the space occupied by the expired rows can be promptly reused, preventing the table file from growing larger. It is also important to run
VACUUM before the free space map is filled. For heavily updated tables, you may need to run
VACUUM at least once a day to prevent the table from becoming bloated.
Warning: When a table is significantly bloated, it is better to run
VACUUM before running
ANALYZE. Analyzing a severely bloated table can generate poor statistics if the sample contains empty pages, so it is good practice to vacuum a bloated table before analyzing it.
When a table accumulates significant bloat, running the
VACUUM command is insufficient. For small tables, running
VACUUM FULL <table_name> can reclaim space used by rows that overflowed the free space map and reduce the size of the table file. However, a
VACUUM FULL statement is an expensive operation that requires an
ACCESS EXCLUSIVE lock and may take an exceptionally long and unpredictable amount of time to finish. Rather than run
VACUUM FULL on a large table, an alternative method is required to remove bloat from a large file. Note that every method for removing bloat from large tables is resource intensive and should be done only under extreme circumstances.
The first method to remove bloat from a large table is to create a copy of the table excluding the expired rows, drop the original table, and rename the copy. This method uses the
CREATE TABLE <table_name> AS SELECT statement to create the new table, for example:
gpadmin=# CREATE TABLE mytable_tmp AS SELECT * FROM mytable; gpadmin=# DROP TABLE mytable; gpadmin=# ALTER TABLE mytabe_tmp RENAME TO mytable;
A second way to remove bloat from a table is to redistribute the table, which rebuilds the table without the expired rows. Follow these steps:
Make a note of the table's distribution columns.
Change the table's distribution policy to random:
ALTER TABLE mytable SET WITH (REORGANIZE=false) DISTRIBUTED randomly;
This changes the distribution policy for the table, but does not move any data. The command should complete instantly.
Change the distribution policy back to its initial setting:
ALTER TABLE mytable SET WITH (REORGANIZE=true) DISTRIBUTED BY (*<original distribution columns\>*);
This step redistributes the data. Since the table was previously distributed with the same distribution key, the rows are simply rewritten on the same segment, excluding expired rows.
VACUUM command only recovers space from tables. To recover the space from indexes, recreate them using the
To rebuild all indexes on a table run
REINDEX table_name;. To rebuild a particular index, run
REINDEX sets the
relpages to 0 (zero) for the index, To update those statistics, run
ANALYZE on the table after reindexing.
Greenplum Database system catalogs are also heap tables and can become bloated over time. As database objects are created, altered, or dropped, expired rows are left in the system catalogs. Using
gpload to load data contributes to the bloat since
gpload creates and drops external tables. (Rather than use
gpload, it is recommended to use
gpfdist to load data.)
Bloat in the system catalogs increases the time require to scan the tables, for example, when creating explain plans. System catalogs are scanned frequently and if they become bloated, overall system performance is degraded.
It is recommended to run
VACUUM on the system catalog nightly and at least weekly. At the same time, running
REINDEX SYSTEM removes bloat from the indexes. Alternatively, you can reindex system tables using the
reindexdb utility with the
--system) option. After removing catalog bloat, run
ANALYZE to update catalog table statistics.
These are Greenplum Database system catalog maintenance steps.
REINDEX on the system catalog tables to rebuild the system catalog indexes. This removes bloat in the indexes and improves
Note: When performing
REINDEX on the system catalog tables, locking will occur on the tables and might have an impact on currently running queries. You can schedule the
REINDEX operation during a period of low activity to avoid disrupting ongoing business operations.
VACUUM on system catalog tables.
ANALYZE on the system catalog tables to update the table statistics.
If you are performing catalog maintenance during a maintenance period and you need to stop a process due to time constraints, run the Greenplum Database function
pg_cancel_backend(<PID>) to safely stop a Greenplum Database process.
The following script runs
ANALYZE on the system catalogs.
#!/bin/bash DBNAME="<database_name>" SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a, pg_namespace b \ where a.relnamespace=b.oid and b.nspname='pg_catalog' and a.relkind='r'" reindexdb -s -d $DBNAME psql -tc "SELECT 'VACUUM' || $SYSTABLES" $DBNAME | psql -a $DBNAME analyzedb -a -s pg_catalog -d $DBNAME
If the system catalogs become significantly bloated, you must perform an intensive system catalog maintenance procedure. The
CREATE TABLE AS SELECT and redistribution key methods for removing bloat cannot be used with system catalogs. You must instead run
VACUUM FULL during a scheduled downtime period. During this period, stop all catalog activity on the system;
VACUUM FULL takes exclusive locks against the system catalog. Running
VACUUM regularly can prevent the need for this more costly procedure.
These are steps for intensive system catalog maintenance.
REINDEXon the system catalog tables to rebuild the system catalog indexes. This removes bloat in the indexes and improves
VACUUM FULLon the system catalog tables. See the following Note.
ANALYZEon the system catalog tables to update the catalog table statistics.
Note: The system catalog table
pg_attribute is usually the largest catalog table. If the
pg_attribute table is significantly bloated, a
VACUUM FULL operation on the table might require a significant amount of time and might need to be performed separately. The presence of both of these conditions indicate a significantly bloated
pg_attribute table that might require a long
VACUUM FULL time:
pg_attributetable contains a large number of records.
significant amount of bloatin the
Append-optimized tables are handled much differently than heap tables. Although append-optimized tables allow updates, inserts, and deletes, they are not optimized for these operations and it is recommended to not use them with append-optimized tables. If you heed this advice and use append-optimized for load-once/read-many workloads,
VACUUM on an append-optimized table runs almost instantaneously.
If you do run
DELETE commands on an append-optimized table, expired rows are tracked in an auxiliary bitmap instead of the free space map.
VACUUM is the only way to recover the space. Running
VACUUM on an append-optimized table with expired rows compacts a table by rewriting the entire table without the expired rows. However, no action is performed if the percentage of expired rows in the table exceeds the value of the
gp_appendonly_compaction_threshold configuration parameter, which is 10 (10%) by default. The threshold is checked on each segment, so it is possible that a
VACUUM statement will compact an append-only table on some segments and not others. Compacting append-only tables can be deactivated by setting the
gp_appendonly_compaction parameter to
Parent topic: System Monitoring and Maintenance