From: Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
To: pgsql-performance@postgresql.org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: Fri, 21 Mar 2003 00:01:18 +0100
Message-ID: <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE> (raw)
In-Reply-To: <15804.1047955909@sss.pgh.pa.us>
References: <20030316060125.GD19570@vt.edu>
<3E741B93.1090203@joeconway.com>
<20030316075206.GE19570@vt.edu>
<Pine.LNX.4.53.0303170059580.8767@neo.magick.org>
<20030317061011.GH23355@perrin.int.nxad.com>
<33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com>
<495.1047882860@sss.pgh.pa.us>
<Pine.LNX.4.53.0303171845190.8636@neo.magick.org>
<15804.1047955909@sss.pgh.pa.us>
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.)
The database is used as a research tool, and we run moderately complex
ad-hoc queries on it. As a consequence, I don't see much room for
optimization.
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 can't afford a regular VACUUM FULL because it takes down the
database for over ten hours, and this starts to cut into the working
hours no matter when it starts.
Can you suggest some tweaks to the FSM values so that we can avoid the
full VACUUM? The database runs 7.3.2 and resides on a 4-way Xeon box
with 4 GB of RAM and a severely underpowered disk subsystem (Linux
software RAID1 on two 10k 36 GB SCSI drives -- don't ask, this
database application is nothing but an accident which happened after
purchase of the box).
--
Florian Weimer Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT fax +49-711-685-5898
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: Weimer@CERT.Uni-Stuttgart.DE
Subject: Re: postgresql meltdown on PlanetMath.org
In-Reply-To: <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE>
References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <Pine.LNX.4.53.0303170059580.8767@neo.magick.org> <20030317061011.GH23355@perrin.int.nxad.com> <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> <495.1047882860@sss.pgh.pa.us> <Pine.LNX.4.53.0303171845190.8636@neo.magick.org> <15804.1047955909@sss.pgh.pa.us> <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE>
* 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