Skip site navigation (1) Skip section navigation (2)

Re: Postgresql 'eats' all mi data partition

From: Javier Carlos <fjcarlos(at)correo(dot)insp(dot)mx>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Postgresql 'eats' all mi data partition
Date: 2003-09-26 03:28:28
Message-ID: 1064546908.3f73b25ca1996@correo.insp.mx (view raw or flat)
Thread:
Lists: pgsql-bugs
Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>:

> On Thu, 25 Sep 2003, Javier Carlos wrote:
> 
> > Quoting Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>:
> >
> > > On Thu, 25 Sep 2003, Javier Carlos wrote:
> > >
> > > >
> > >
> ============================================================================
> > > >                         POSTGRESQL BUG REPORT TEMPLATE
> > > >
> > >
> ============================================================================
> > > >
> > > >
> > > > Your name		:	Javier Carlos Rivera
> > > > Your email address	:	fjcarlos ( at ) correo ( dot ) insp ( dot ) mx
> > > >
> > > >
> > > > System Configuration
> > > > ----------------------
> > > >   Architecture (example: Intel Pentium)  	  : Intel Pentium 4
> > > >
> > > >   Operating System (example: Linux 2.0.26 ELF) 	  : Debian GNU/Linux
> > 3.0
> > > 2.4.21
> > > >
> > > >   RAM						  : 256 MB
> > > >
> > > >   PostgreSQL version (example: PostgreSQL-6.3.2)  : PostgreSQL-7.3.4
> > > >
> > > >   Compiler used (example:  gcc 2.7.2)		  : 2.95.4
> > > >
> > > >
> > > >
> > > > Please enter a FULL description of your problem:
> > > > -------------------------------------------------
> > > > 	On Thursday Bruce Momjian was at Mexico; I saw him and asked about
> > > > this problem. He told me to write to this e-mail.
> > > >
> > > > 	When I do a simple 'UPDATE' PostgreSQL 'eats' all my partition space
> > > > of my data directory. For example:
> > > >
> > > > ***** My data directory is in /var
> > > > ***** BEFORE I do the UPDATEs I got this from df:
> > > > OPORTUNIDADES:~# df
> > > > Filesystem           1k-blocks      Used Available Use% Mounted on
> > > > /dev/hda2              2885812     14372   2724848   1% /
> > > > /dev/hda1             14421344   1195132  12493652   9% /var
> > > > /dev/hda3              7692908    888560   6413568  13% /usr
> > > > /dev/hda6             12491804     22704  11834536   1% /javier
> > > > /dev/hda7              1494204     23936   1394364   2% /home
> > > >
> > > >
> > > > ***** Then I do the UPDATEs:
> > > > **** The updates are of this type :
> > > > UPDATE tbl_personas SET "PIDFOLIO"=-2 WHERE "PIDFOLIO" IS NULL;
> > > > UPDATE tbl_personas SET "P5_1"=-2 WHERE "P5_1" IS NULL;
> > > > UPDATE tbl_personas SET "P4PAQ"=-2 WHERE "P4PAQ" IS NULL;
> > > > UPDATE tbl_personas SET "P5_4"=-2 WHERE "P5_4" IS NULL;
> > > > UPDATE tbl_personas SET "P5_5"=-2 WHERE "P5_5" IS NULL;
> > > > UPDATE tbl_personas SET "P36_4"=-2 WHERE "P36_4" IS NULL;
> > > > ..
> > > > UPDATE table_name SET column_name = -2 WHERE column_name IS NULL;
> > > > ..
> > >
> > > If you're not vacuuming, you're going to have a potentially large
> > > number of dead rows.  Does a vacuum between updates or a vacuum full at
> > > the end bring the space usage down to something reasonable?
> > >
> >
> >    I did a vacuumbdb after the updates, and the space usage didn't down to
> > something reasonable. For example, I had a 250MB database, then I did
> about
> > 300 query updates, and mi partition growed up until fill all mi data
> partition
> > space of 15GB. After that I did an vacuumdb and only the space down 100MB.
> > After that I DROPPED the database, and the space down ALL the 15GB; It's
> very
> > weird, don't you think?
> 
> Did you use -f on the vacuumdb?  If not, it did a normal vacuum (which
> isn't likely to help) not a full vacuum.
> 

    Many Thanks!,
 
    I used the -f option and it worked!!

    I really appreciate your help.

    Best regards,
 
    Javier

-------------------------------------------------
http://www.insp.mx

In response to

pgsql-bugs by date

Next:From: Nayib KiuhanDate: 2003-09-26 06:15:13
Subject: plperl: No such file or directory. (Solaris 9, PGSQL 7.4, OpenSSL,Perl)
Previous:From: Ali KarakiDate: 2003-09-25 22:38:29
Subject: pthread.h

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group