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

Re: database size growing continously

From: Chris Ernst <cernst(at)esoft(dot)com>
To: Peter Meszaros <pme(at)prolan(dot)hu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: database size growing continously
Date: 2009-10-29 15:58:36
Message-ID: 4AE9BBAC.30601@esoft.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Peter,

Sounds like you're experiencing index bloat and vacuums do nothing to
help that.  You can do one of 2 thing to remedy this:

1) The fastest and simplest (but most disruptive) way is to use REINDEX.
 But this will exclusively lock the table while rebuilding the indexes:

  REINDEX TABLE phaseangle;

2) The slower but less disruptive way is to do a concurrent build of
each index and then drop the old ones. For example, to rebuild the "i"
index:

  CREATE INDEX CONCURRENTLY i_new ON phaseangle (<indexed columns>);
  DROP INDEX i;
  ALTER INDEX i_new RENAME TO i;
  ANALYZE phaseangle (<indexed columns>);

Do this regularly to keep the index sizes in check.

	- Chris

Peter Meszaros wrote:
> Hi All,
> 
> I use postgresql 8.3.7 as a huge queue. There is a very simple table
> with six columns and two indices, and about 6 million records are
> written into it in every day continously commited every 10 seconds from
> 8 clients. The table stores approximately 120 million records, because a
> cron job daily deletes those ones are older than 20 day. Autovacuum is
> on and every settings is the factory default except some unrelated ones
> (listen address, authorization). But my database is growing,
> characteristically ~600MByte/day, but sometimes much slower (eg. 10MB,
> or even 0!!!).
> 
> I've also tried a test on another server running the same postgresql,
> where 300 million record was loaded into a freshly created database,
> and 25 million was deleted with single DELETE command.  The 'vacuum
> verbose phaseangle;' command seems to be running forever for hours:
> 
> phasor=# vacuum VERBOSE phaseangle;
> INFO:  vacuuming "public.phaseangle"
> INFO:  scanned index "i" to remove 2796006 row versions
> DETAIL:  CPU 9.49s/120.30u sec elapsed 224.20 sec.
> INFO:  scanned index "t" to remove 2796006 row versions
> DETAIL:  CPU 13.57s/105.70u sec elapsed 192.71 sec.
> INFO:  "phaseangle": removed 2796006 row versions in 24748 pages
> DETAIL:  CPU 0.65s/0.30u sec elapsed 39.97 sec.
> INFO:  scanned index "i" to remove 2795924 row versions
> DETAIL:  CPU 9.58s/121.63u sec elapsed 239.06 sec.
> INFO:  scanned index "t" to remove 2795924 row versions
> DETAIL:  CPU 13.10s/103.59u sec elapsed 190.84 sec.
> INFO:  "phaseangle": removed 2795924 row versions in 24743 pages
> DETAIL:  CPU 0.68s/0.28u sec elapsed 40.21 sec.
> INFO:  scanned index "i" to remove 2796014 row versions
> DETAIL:  CPU 9.65s/117.28u sec elapsed 231.92 sec.
> INFO:  scanned index "t" to remove 2796014 row versions
> DETAIL:  CPU 13.48s/103.59u sec elapsed 194.49 sec.
> INFO:  "phaseangle": removed 2796014 row versions in 24774 pages
> DETAIL:  CPU 0.69s/0.28u sec elapsed 40.26 sec.
> INFO:  scanned index "i" to remove 2795935 row versions
> DETAIL:  CPU 9.55s/119.02u sec elapsed 226.85 sec.
> INFO:  scanned index "t" to remove 2795935 row versions
> DETAIL:  CPU 13.09s/102.84u sec elapsed 194.74 sec.
> INFO:  "phaseangle": removed 2795935 row versions in 25097 pages
> DETAIL:  CPU 0.67s/0.28u sec elapsed 41.21 sec.
> 
> still running...
> 
> These are the very same problems?
> Should I delete mor frequently in smaller chunks? It seems to have a
> limit...
> 
> Thanks 
> 
> Peter
> 


In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-10-29 16:00:18
Subject: Re: database size growing continously
Previous:From: Anj AduDate: 2009-10-29 15:40:20
Subject: Re: sub-select in IN clause results in sequential scan

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