Postgres vacuum question (7.2)

Grant Shipley gshipley at gmail.com
Mon Mar 5 13:51:53 MST 2007


Hello all:

I am seeing that postgres is not freeing up disk space on vacuum or
vacuum full.  I run the following test:
1) Initial disk space used in /var/lib/pgsql/data = 98Meg
2) Created 1 million sessions.  Disk space = 554Meg
3) Deleted all records. Disk space = 554Meg
4) Vacuum. Disk space = 550Meg
5) Vacuum full. Disk space = 390

I repeated steps 1-5 and noticed our disk space kept growing 400 meg each run.

I found the following options that might help?:
# - Free Space Map -

#max_fsm_pages = 20000                  # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000               # min 100, ~70 bytes each

http://archives.postgresql.org/pgsql-general/2002-07/msg00972.php
http://www.arcknowledge.com/gmane.comp.db.postgresql.sql/2002-08/msg00099.html

I also read that indexes never free their space?  Does anyone know
more about this?

This is a heavy used table and I am seeing the data dir taking up 38gb
of memory after long runs.

--
grant


--- cut --
create or replace function breakme(int4) RETURNS TEXT AS '
DECLARE
   numRecords     ALIAS FOR $1;
   i         int4;
   sessionData    bytea;
   appName     varchar(30);
BEGIN
   sessionData :=
1800080008000000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000800080008000;

   delete from tomcat_sessions;
   FOR i in 1..numRecords LOOP
    insert into tomcat_sessions (session_id, valid_session,
max_inactive, last_access, app_name, session_data)
           values (i, 1, 1800, 1173118816972, ''cservice2'', sessionData);
   END LOOP;
   return ''Records Created'';
END;
' LANGUAGE 'plpgsql';
---



More information about the PLUG mailing list