X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CE13E5363A for ; Fri, 25 Mar 2005 20:31:01 +0000 (GMT) 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 94917-05 for ; Fri, 25 Mar 2005 20:31:00 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8A9A053637 for ; Fri, 25 Mar 2005 20:30:59 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A31B730959; Fri, 25 Mar 2005 21:30:57 +0100 (MET) From: "Otto Blomqvist" X-Newsgroups: pgsql.performance Subject: Re: pg_autovacuum not having enough suction ? Date: Fri, 25 Mar 2005 12:35:00 -0800 Organization: Hub.Org Networking Services Lines: 63 Message-ID: References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <00f201c53168$96903480$e602a8c0@blomqvist> <42446A66.6010504@zeut.net> <11991.1111780527@sss.pgh.pa.us> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.912 tagged_above=0 required=5 tests=AWL, PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200503/560 X-Sequence-Number: 11265 > Another thing to check is whether the reltuples (and relpages!) that > autovacuum is reporting are the same as what's actually in the pg_class > row for the relation. I'm wondering if this could be a similar issue > to the old autovac bug where it wasn't reading the value correctly. These values where extracted at roughly the same time. relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relacl ---------+--------------+---------+----------+-------+-------------+-------- -------+----------+-----------+---------------+---------------+------------- +-------------+---------+----------+-----------+-------------+----------+--- -------+---------+------------+------------+-------------+----------------+- ------- file_92 | 2200 | 9384220 | 100 | 0 | 9384219 | 0 | 6624 | 113082 | 0 | 0 | t | f | r | 23 | 0 | 1 | 0 | 0 | 0 | t | f | f | f | (1 row) secom=# select count(*) from file_92; count ------- 17579 (1 row) [2005-03-25 12:16:32 EST] INFO: table name: secom."public"."file_92" [2005-03-25 12:16:32 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-25 12:16:32 EST] INFO: reltuples: 113082.000000; relpages: 6624 [2005-03-25 12:16:32 EST] INFO: curr_analyze_count: 993780; curr_vacuum_count: 732470 [2005-03-25 12:16:32 EST] INFO: last_analyze_count: 923820; last_vacuum_count: 662699 [2005-03-25 12:16:32 EST] INFO: analyze_threshold: 113582; vacuum_threshold: 227164 Hope this helps, if there is anything else I can do please let me know. > If they are the same then it seems like it must be a backend issue. > > One thing that is possibly relevant here is that in 8.0 a plain VACUUM > doesn't set reltuples to the exactly correct number, but to an > interpolated value that reflects our estimate of the "steady state" > average between vacuums. I wonder if that code is wrong, or if it's > operating as designed but is confusing autovac. This average steady state value might be hard to interpolete in this case since this is only a temporary holding place for the records ..? Normaly the table has < 10 records in it at the same time. In the lab we create a "lump-traffic" by sending over 50000 Records. It takes about 20 hours to transfer over all of the 50k records.