pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: "Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
From: " <shridhar_daithankar@persistent.co.in>
To: Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: Fri, 21 Mar 2003 09:18:54 +0530
Message-ID: <200303210918.54380.shridhar_daithankar@persistent.co.in> (raw)
In-Reply-To: <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE>
References: <20030316060125.GD19570@vt.edu>
	<15804.1047955909@sss.pgh.pa.us>
	<87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE>

On Friday 21 Mar 2003 4:31 am, Florian Weimer wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> > You definitely need to increase the fsm shared memory parameters.  The
> > default max_fsm_relations is just plain too small (try 1000) and the
> > default_max_fsm_pages is really only enough for perhaps a 100Mb
> > database.  I'd try bumping it to 100,000.  Note you need a postmaster
> > restart to make these changes take effect.
>
> Hmm, are there any guidelines for choosing these values?
>
> We have a database with a table into which we insert about 4,000,000
> rows each day, and delete another 4,000,000 rows.  The total row count
> is around 40 million, I guess, and the rows are about 150 bytes long.
> (VACUUM FULL is running at the moment, so I can't check.)

I suggest you split your tables into exactly similar tables using inheritance. 
Your queries won't be affected as you can make them on parent table and get 
same result.

But as far as vacuuming goes, you can probably dump a child table entirely and 
recreate it as a fast alternative to vacuum.

Only catch is, I don't know if inherited tables would use their respective 
indxes other wise your queries might be slow as anything.

> One of the columns is time-based and indexed, so we suffer from the
> creeping index syndrome.  A nightly index rebuild followed by a VACUUM
> ANALYZE isn't a problem (it takes less than six ours), but this
> doesn't seem to be enough (we seem to lose disk space nevertheless).

I am sure a select * from table into another table; drop table; renamre temp 
table kind of hack would be faster than vacuuming in this case..

This is just a suggestion. Good if this works for you..

 Shridhar



view thread (39+ messages)  latest in thread

Message-ID: <200303210918.54380.shridhar_daithankar@persistent.co.in>
Permalink:  ../200303210918.54380.shridhar_daithankar@persistent.co.in/
Also on:    postgresql.org/message-id/200303210918.54380.shridhar_daithankar@persistent.co.in

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: shridhar_daithankar@persistent.co.in, Weimer@CERT.Uni-Stuttgart.DE
  Subject: Re: postgresql meltdown on PlanetMath.org
  In-Reply-To: <200303210918.54380.shridhar_daithankar@persistent.co.in>
  References: <20030316060125.GD19570@vt.edu> <15804.1047955909@sss.pgh.pa.us> <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE> <200303210918.54380.shridhar_daithankar@persistent.co.in>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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