Pivotal Knowledge Base

Follow

VACUUM / VACUUM FULL does not reclaim dead space

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x

Symptom

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

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

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


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

cem=# set gp_select_invisible=on;
SET

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


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

cem=# vacuum pg_class;
VACUUM

cem=# vacuum pg_attribute;
VACUUM

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


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

Cause

There may be still some idle sessions which have been staying for quite a long time. The master process for those idle sessions are 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, acn_daniel_quake cem 10.68.151.106(60662) con4365189 10.68.151.106(60662) cmd5 idle
gpadmin 103416 359557 0 Jun17 ? 00:00:24 postgres: port 5432, acn_fangying_foo cem 10.68.151.106(60678) con4368086 10.68.151.106(60678) cmd9 idle
gpadmin 183246 359557 0 Jun17 ? 00:00:26 postgres: port 5432, acn_fangying_foo cem 10.68.150.103(60423) con4288111 10.68.150.103(60423) cmd115 idle
gpadmin 189397 359557 0 Jun17 ? 00:00:43 postgres: port 5432, acn_fangying_foo cem 10.68.150.103(60424) con4288124 10.68.150.103(60424) cmd5170 idle
gpadmin 393522 359557 0 Jun17 ? 00:00:25 postgres: port 5432, acn_fangying_foo cem 10.68.150.103(60429) con4289137 10.68.150.103(60429) cmd7 idle
gpadmin 438245 359557 0 Jun17 ? 00:00:30 postgres: port 5432, cemfsrbo cem 10.68.36.30(62219) con4321296 10.68.36.30(62219) cmd23 idle
gpadmin 559063 359557 0 Jun15 ? 00:00:38 postgres: port 5432, acn_daniel_quake cem 10.68.151.106(54801) con3727899 10.68.151.106(54801) cmd317 idle
gpadmin 631805 359557 0 Jun15 ? 00:00:38 postgres: port 5432, acn_daniel_quake cem 10.68.151.106(54802) con3727946 10.68.151.106(54802) cmd3836 idle
gpadmin 633091 359557 0 Jun17 ? 00:00:24 postgres: port 5432, acn_daniel_quake cem 10.68.151.106(60657) con4362300 10.68.151.106(60657) cmd374 idle
gpadmin 798546 359557 0 Jun17 ? 00:00:25 postgres: port 5432, acn_daniel_quake cem 10.68.151.106(60134) con4296490 10.68.151.106(60134) cmd9 idle
cem=# 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
-----------+---------+-------------------------------+-------------------------------
cem | 3727899 | 2016-06-20 10:19:07.780882+08 | 2016-06-15 11:00:35.601402+08
cem | 3727946 | 2016-06-17 11:48:14.567129+08 | 2016-06-15 11:00:56.058893+08
cem | 4288111 | 2016-06-17 11:20:56.429657+08 | 2016-06-17 11:14:27.503132+08
cem | 4288124 | 2016-06-17 18:15:06.287033+08 | 2016-06-17 11:14:29.91555+08
cem | 4289137 | 2016-06-17 11:21:44.82581+08 | 2016-06-17 11:20:40.336715+08
cem | 4296490 | 2016-06-17 11:56:10.237102+08 | 2016-06-17 11:51:08.332621+08
cem | 4321296 | 2016-06-17 14:03:27.082331+08 | 2016-06-17 14:02:12.686701+08
cem | 4362300 | 2016-06-17 17:24:40.838667+08 | 2016-06-17 17:20:52.715955+08
cem | 4365189 | 2016-06-17 17:31:48.739918+08 | 2016-06-17 17:31:48.628522+08
cem | 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:

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

Comments

Powered by Zendesk