X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E2B91D885B for ; Tue, 6 Sep 2005 22:07:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 39658-06 for ; Wed, 7 Sep 2005 01:07:23 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1919AD87F0 for ; Tue, 6 Sep 2005 22:07:24 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j8717R25022357; Tue, 6 Sep 2005 21:07:27 -0400 (EDT) To: jan.l.peterson@gmail.com Cc: pgsql-performance@postgresql.org, "Thomas F. O'Connell" Subject: Re: poor VACUUM performance on large tables In-reply-to: <72e966b005090617543ad88811@mail.gmail.com> References: <72e966b00509032316166ff0cf@mail.gmail.com> <9274.1125874870@sss.pgh.pa.us> <72e966b005090617543ad88811@mail.gmail.com> Comments: In-reply-to Jan Peterson message dated "Tue, 06 Sep 2005 18:54:24 -0600" Date: Tue, 06 Sep 2005 21:07:27 -0400 Message-ID: <22356.1126055247@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200509/103 X-Sequence-Number: 14411 Jan Peterson writes: > Based on this, it looks like we could stand to bump up our FSM another > couple hundred thousand. Does it buy us anything to reduce the number > of FSM relations from the default of 1000? Not a lot; as the comment says, those slots are only about 50 bytes each. (I think the true figure is closer to 70, according to some measurements I did recently on CVS tip, but in any case it's less than 100 bytes apiece.) Still, a byte saved is a byte earned ... > It looks like we are using the defaults for work_mem (1024) and > maintenance_work_mem (16384). We could certainly bump these up. Is > there a good way to determine what settings would be reasonable? I'd bump up maintenance_work_mem by a factor of 10 and see if it makes a difference. It should reduce the number of passes over the indexes when vacuuming up lots of deleted rows. If you have lots of RAM you might be able to increase it more, but try that for starters. regards, tom lane