X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 74AF9D9DC2 for ; Fri, 11 Nov 2005 16:37:41 -0400 (AST) 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 45402-02 for ; Fri, 11 Nov 2005 20:37:39 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id CD839D9D44 for ; Fri, 11 Nov 2005 16:37:39 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i3so850933wra for ; Fri, 11 Nov 2005 12:37:40 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=krQf2maDDoJDvQsQ5btvvY0oMLkHgus12usvdgpXUimvcHTHEe5eUX6dnGy53Q0+IBmZK3togXx5i1sO9CCeCfliIL7Zq/ah5ZkVshd9RFSWaDA4RsqHUgEJJavSbPvw2hFPmHIOTdwnIFRYYHMhwiCXA79NVAUT7rL55v8hkgs= Received: by 10.64.10.8 with SMTP id 8mr2876924qbj; Fri, 11 Nov 2005 12:37:40 -0800 (PST) Received: by 10.65.180.14 with HTTP; Fri, 11 Nov 2005 12:37:40 -0800 (PST) Message-ID: Date: Fri, 11 Nov 2005 15:37:40 -0500 From: Jaime Casanova To: Stephen Byers Subject: Re: autovacuum on updated rows Cc: pgsql-admin@postgresql.org In-Reply-To: <20051111201030.68086.qmail@web30703.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20051111201030.68086.qmail@web30703.mail.mud.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.081 required=5 tests=[AWL=0.081] X-Spam-Score: 0.081 X-Spam-Level: X-Archive-Number: 200511/142 X-Sequence-Number: 19515 On 11/11/05, Stephen Byers wrote: > I am using new postgres 8.1 and also trying out the integrated autovacuum= . > I have a concern. > > I have a table where I perform an update on a row every 10 seconds. The > table is VERY small. Hardly any INSERTS, no DELETES, and a ton of UPDATE= S. > > adbs_db=3D# select count(*) from volumes; > count > ------- > 7 > (1 row) > > > > > > I am looking at the file sizes that correspond to this table and its > indeces. They are growing -- I think I understand why -- dead rows are > occuring on an UPDATE. But what I don't understand is why autovacuum has > not kicked in-- I have kept the default values on autovacuum. > > i will assume you change #autovacuum=3Doff for on in postgresql.conf you have to activet stats_stat_collector and stats_row_level too... here explains what factors are used for determining vacuum needs for tables: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACU= UM > > In about 50 minutes' time, the table itself has grown 2MB, and each index > has grown by about 1MB > > Will autovacuum eventually run on this table???? What rows/columns does > autovacuum look at to determine when to work on a table? I'd like to loo= k > at those rows, too. > > Thanks!! > Steve > > > adbs_db=3D# \d volumes > Table "public.volumes" > Column | Type | Modifiers > ------------------+------------------------+-------------------- > label | character varying(128) | not null > imported | boolean | not null > closed | boolean | not null > active | boolean | not null > online | boolean | not null > offline | boolean | not null > archiver_name | character varying(32) | not null > environment_name | character varying(32) | not null > curr_size | bigint | not null default 0 > vol_num | integer | not null > start_time | integer | > stop_time | integer | > Indexes: > "volumes_i1" unique, btree (label) > "volumes_i2" btree (environment_name) > > -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;)