X-Original-To: pgsql-performance@postgresql.org Received: from Mail.CERT.Uni-Stuttgart.DE (Mail.CERT.Uni-Stuttgart.DE [129.69.16.17]) by postgresql.org (Postfix) with ESMTP id 125A0475C15 for ; Thu, 20 Mar 2003 18:01:17 -0500 (EST) Received: from rusfw by Mail.CERT.Uni-Stuttgart.DE with local (Exim 4.10) id 18w92E-0000mB-00 for pgsql-performance@postgresql.org; Fri, 21 Mar 2003 00:01:18 +0100 To: pgsql-performance@postgresql.org Subject: Re: postgresql meltdown on PlanetMath.org References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <20030317061011.GH23355@perrin.int.nxad.com> <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> <495.1047882860@sss.pgh.pa.us> <15804.1047955909@sss.pgh.pa.us> From: Florian Weimer Date: Fri, 21 Mar 2003 00:01:18 +0100 In-Reply-To: <15804.1047955909@sss.pgh.pa.us> (Tom Lane's message of "Mon, 17 Mar 2003 21:51:49 -0500") Message-ID: <87fzphk5hd.fsf@Login.CERT.Uni-Stuttgart.DE> User-Agent: Gnus/5.090008 (Oort Gnus v0.08) Emacs/21.2 (i386-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200303/143 X-Sequence-Number: 1441 Tom Lane 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