VACUUM / VACUUM FULL does not Reclaim Dead Space

Products

VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

When attempting a "VACUUM/VACUUM FULL" of some relations (e.g. pg_class/pg_attribute), it failed to reclaim the dead space as shown below:

db01=# show gp_select_invisible;
 gp_select_invisible
---------------------
 off
(1 row)

db01=# select count(*) from pg_class;
 count
-------
 9488
(1 row)


db01=# select count(*) from pg_attribute;
 count
--------
 170920
(1 row)

db01=# set gp_select_invisible=on;
SET

db01=# select count(*) from pg_class;
 count
--------
 778282
(1 row)


db01=# select count(*) from pg_attribute;
 count
---------
 3330755
(1 row)

db01=# vacuum pg_class;
VACUUM

db01=# vacuum pg_attribute;
VACUUM

db01=# select count(*) from pg_class;
 count
--------
 778282
(1 row)


db01=# select count(*) from pg_attribute;
 count
---------
 3330755
(1 row)

Environment

Cause

There may be still some idle sessions which have been staying for quite a long time. The master process for those idle sessions is probably still referring (holding file descriptor) to the data file of the to-be-vacuumed tables on a master node. As such VACUUM/VACUUM FULL won't take effect.

PRD gpadmin@mdw:~$ ps -ef|grep postgres|grep con|grep Jun
gpadmin 47679 359557 0 Jun17 ? 00:00:24 postgres: port 5432, ###_###_#### db01 10.##.###.###(60662) con4365189 10.##.###.###(60662) cmd5 idle
gpadmin 103416 359557 0 Jun17 ? 00:00:24 postgres: port 5432,###_###_#### db01 10.##.###.###(60678) con4368086 10.##.###.###(60678) cmd9 idle
gpadmin 183246 359557 0 Jun17 ? 00:00:26 postgres: port 5432,###_###_#### db01 10.##.###.###(60423) con4288111 10.##.###.###(60423) cmd115 idle
gpadmin 189397 359557 0 Jun17 ? 00:00:43 postgres: port 5432,###_###_#### db01 10.##.###.###(60424) con4288124 10.##.###.###(60424) cmd5170 idle
gpadmin 393522 359557 0 Jun17 ? 00:00:25 postgres: port 5432,###_###_#### db01 10.##.###.###(60429) con4289137 10.##.###.###(60429) cmd7 idle
gpadmin 438245 359557 0 Jun17 ? 00:00:30 postgres: port 5432,###_###_#### db01 10.##.###.###(62219) con4321296 10.##.###.###(62219) cmd23 idle
gpadmin 559063 359557 0 Jun15 ? 00:00:38 postgres: port 5432,###_###_#### db01 10.##.###.###(54801) con3727899 10.##.###.###(54801) cmd317 idle
gpadmin 631805 359557 0 Jun15 ? 00:00:38 postgres: port 5432,###_###_#### db01 10.##.###.###(54802) con3727946 10.##.###.###(54802) cmd3836 idle
gpadmin 633091 359557 0 Jun17 ? 00:00:24 postgres: port 5432,###_###_#### db01 10.##.###.###(60657) con4362300 10.##.###.###(60657) cmd374 idle
gpadmin 798546 359557 0 Jun17 ? 00:00:25 postgres: port 5432,###_###_#### db01 10.##.###.###(60134) con4296490 10.##.###.###(60134) cmd9 idle

db01=# select datname,sess_id, query_start, backend_start from pg_stat_activity where current_query = '<IDLE>' order by backend_start;
 datname | sess_id | query_start | backend_start
-----------+---------+-------------------------------+-------------------------------
 db01 | 3727899 | 2016-06-20 10:19:07.780882+08 | 2016-06-15 11:00:35.601402+08
 db01 | 3727946 | 2016-06-17 11:48:14.567129+08 | 2016-06-15 11:00:56.058893+08
 db01 | 4288111 | 2016-06-17 11:20:56.429657+08 | 2016-06-17 11:14:27.503132+08
 db01 | 4288124 | 2016-06-17 18:15:06.287033+08 | 2016-06-17 11:14:29.91555+08
 db01 | 4289137 | 2016-06-17 11:21:44.82581+08  | 2016-06-17 11:20:40.336715+08
 db01 | 4296490 | 2016-06-17 11:56:10.237102+08 | 2016-06-17 11:51:08.332621+08
 db01 | 4321296 | 2016-06-17 14:03:27.082331+08 | 2016-06-17 14:02:12.686701+08
 db01 | 4362300 | 2016-06-17 17:24:40.838667+08 | 2016-06-17 17:20:52.715955+08
 db01 | 4365189 | 2016-06-17 17:31:48.739918+08 | 2016-06-17 17:31:48.628522+08
 db01 | 4368086 | 2016-06-17 17:48:02.43697+08  | 2016-06-17 17:48:02.309867+08

Resolution

There are two ways to resolve this issue:

Terminate those long-running idle sessions to ensure no activity on the system. Run VACUUM/VACUUM FULL again.
Restart the database system when possible, and run VACUUM/VACUUM FULL again.