Postgres vacuum question (7.2)

Hill, Greg grhill at corp.untd.com
Mon Mar 5 14:00:46 MST 2007


We ran into something similar when I worked at backcountry.  Eventually,
the VACUUM took so long to run that our session database could never be
fully defragged.  Unfortunately I left before any solution was
discovered, so I couldn't tell you if they ever figured it out.  I know
that the best option we knew of was to upgrade to postgres 8, but there
were some issues involved with doing that, too.  If that's an option for
you, do it.  They fixed a lot of the vacuum problems in 8.  If that
isn't an option, I'd suggest looking into file-based sessions or setting
up a mysql database just to handle sessions.  Of course, there might be
some great solution that we weren't aware of, but we couldn't find an
easy fix.

Greg

> -----Original Message-----
> From: plug-bounces at plug.org [mailto:plug-bounces at plug.org] On Behalf
Of
> Grant Shipley
> Sent: Monday, March 05, 2007 1:52 PM
> To: Provo Linux Users Group Mailing List
> Subject: Postgres vacuum question (7.2)
> 
> 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 :=
>
180008000800000080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
>
008000800080008000800080008000800080008000800080008000800080008000800080
00
>
800080008000800080008000800080008000800080008000800080008000800080008000
80
> 00800080008000800080008000800080008000800080008000;
> 
>    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';
> ---
> 
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */



More information about the PLUG mailing list