pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / 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