Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C446C9F9545 for ; Tue, 21 Aug 2007 18:41:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 93511-04 for ; Tue, 21 Aug 2007 18:41:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp.icemail.com (unknown [66.117.50.180]) by postgresql.org (Postfix) with ESMTP id 50A1C9F9567 for ; Tue, 21 Aug 2007 18:41:39 -0300 (ADT) Received: from EXVS01.IceMAIL.com ([10.2.23.110] RDNS failed) by smtp.icemail.com with Microsoft SMTPSVC(6.0.3790.3959); Tue, 21 Aug 2007 17:38:00 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: Autovacuum is running forever Date: Tue, 21 Aug 2007 17:37:59 -0400 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Autovacuum is running forever Thread-Index: AcfkOslrj0I1BNkhQxS37NRGJvc4rgAAONEg From: "Sachchida Ojha" To: "Michael Glaesemann" Cc: X-OriginalArrivalTime: 21 Aug 2007 21:38:00.0937 (UTC) FILETIME=[8C2DE590:01C7E43B] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/216 X-Sequence-Number: 26346 Total RAM in the system is 2GB=20 #----------------------------------------------------------------------- ---- # AUTOVACUUM PARAMETERS #----------------------------------------------------------------------- ---- vacuum_cost_delay =3D 200 # 0-1000 milliseconds vacuum_cost_page_hit =3D 1 # 0-10000 credits vacuum_cost_page_miss =3D 10 # 0-10000 credits vacuum_cost_page_dirty =3D 20 # 0-10000 credits vacuum_cost_limit =3D 200 # 0-10000 credits autovacuum =3D on autovacuum_naptime =3D 3600 #autovacuum_vacuum_threshold =3D 1000 #autovacuum_analyze_threshold =3D 500 #autovacuum_vacuum_scale_factor =3D 0.4 #autovacuum_analyze_scale_factor =3D 0.2 #autovacuum_vacuum_cost_delay =3D -1 #autovacuum_vacuum_cost_limit =3D -1 Some other parameter max_connections =3D 350 shared_buffers =3D 400MB # set = to 16 MB min 16 or max_connections*2, 8KB each temp_buffers =3D 8MB # min 100, 8KB each max_prepared_transactions =3D 5 # can = be 0 or more work_mem =3D 4MB # min 64, size in KB maintenance_work_mem =3D 256MB # min 1024, = size in KB max_stack_depth =3D 2048 # min 100, size in KB max_fsm_pages =3D 2000000 # min max_fsm_relations*16, 6 bytes each max_fsm_relations =3D 1000 # min 100, ~70 bytes each max_files_per_process =3D 1000 # min 25 bgwriter_delay =3D 200 # 10-10000 milliseconds between rounds bgwriter_lru_percent =3D 1.0 # 0-100% of = LRU buffers scanned/round bgwriter_lru_maxpages =3D 5 # = 0-1000 buffers max written/round bgwriter_all_percent =3D 0.333 # 0-100% of = all buffers scanned/round bgwriter_all_maxpages =3D 5 # = 0-1000 buffers max written/round=20 Regards Sachchida -----Original Message----- From: Michael Glaesemann [mailto:grzm@seespotcode.net]=20 Sent: Tuesday, August 21, 2007 5:36 PM To: Sachchida Ojha Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Autovacuum is running forever On Aug 21, 2007, at 16:26 , Sachchida Ojha wrote: > In my OLTP system, we are inserting, updating and deleting the data=20 > every second. > > Autovacuum started and never finished slowing down the whole system. There's the possibility that your autovacuum settings aren't aggressive enough for your system, so it's never able to catch up. =20 Without knowing details it's hard to say for certain. What are your autovacuum settings and other details about the load on your system? Michael Glaesemann grzm seespotcode net