pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
index bloat problem
2+ messages / 2 participants
[nested] [flat]

* index bloat problem
@ 2007-03-09 10:05  Monika Cernikova <monika@microstep-mis.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Monika Cernikova @ 2007-03-09 10:05 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-admin

I use Postgres 8.1 on linux
I have several tables to which I need insert about 200-500 records per 
minute.
Records contains timestamp (actual time), and this timestamp is part of 
primary key and index.
I need to keep data for 1 month.  I daily delete data older than 1 month 
and than run vacuum analyze.
Size of primary keys and indexes still grows.
(After 2 months database size is about 35G and vacuum runs about 20 hours.
When I dumped and restored database, new size was 25G. Indexes and 
constraints had half size)

Can you help me how to stop index growing or reindex database if I CAN'T 
STOP writing records?

Thanx,

Monika

Here are my settings in postgresql.conf, that differs from defaults

shared_buffers = 2000          

max_fsm_pages = 400000          
(it isn't much, but there isn't logged in vacuum log that it needs more)

vacuum_cost_delay = 100       
vacuum_cost_page_hit = 6   
vacuum_cost_limit = 100    

wal_buffers = 16        
checkpoint_segments = 8    

autovacuum = off            




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [GENERAL] index bloat problem
@ 2007-03-10 05:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Monika Cernikova <monika@microstep-mis.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2007-03-10 05:27 UTC (permalink / raw)
  To: Monika Cernikova <monika@microstep-mis.com>; +Cc: pgsql-general@postgresql.org; pgsql-admin

Monika Cernikova <monika@microstep-mis.com> writes:
> Can you help me how to stop index growing or reindex database if I CAN'T 
> STOP writing records?

I think you have max_fsm_pages set too small.

> max_fsm_pages = 400000          

That corresponds to about 3Gb, or a tenth the size of your DB.  Not enough.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-03-10 05:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 10:05 index bloat problem Monika Cernikova <monika@microstep-mis.com>
2007-03-10 05:27 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox