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 65012534D4 for ; Thu, 24 Mar 2005 23:58:11 +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 12828-03 for ; Thu, 24 Mar 2005 23:58:04 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 17907534A4 for ; Thu, 24 Mar 2005 23:58:03 +0000 (GMT) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=[192.168.0.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.44) id 1DEcDG-000Idd-79; Thu, 24 Mar 2005 18:58:06 -0500 Message-ID: <42435408.30303@zeut.net> Date: Thu, 24 Mar 2005 18:58:00 -0500 From: "Matthew T. O'Connor" Organization: Terrie O'Connor Realtors User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Otto Blomqvist , "pgsql-performance@postgresql.org" Subject: Re: pg_autovacuum not having enough suction ? References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> In-Reply-To: <002b01c530c5$ad5a1620$e602a8c0@blomqvist> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.80.199.173 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/525 X-Sequence-Number: 11230 I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: >It does not seem to be a Stats collector problem. > > oid | relname | relnamespace | relpages | relisshared | reltuples | >schemaname | n_tup_ins | n_tup_upd | n_tup_del >---------+---------+--------------+----------+-------------+-----------+---- >--------+-----------+-----------+----------- > 9384219 | file_92 | 2200 | 8423 | f | 49837 | >public | 158176 | 318527 | 158176 >(1 row) > >I insert 50000 records > >secom=# select createfile_92records(1, 50000); <--- this is a pg script >that inserts records 1 threw 50000. > createfile_92records >---------------------- > 0 > > > oid | relname | relnamespace | relpages | relisshared | reltuples | >schemaname | n_tup_ins | n_tup_upd | n_tup_del >---------+---------+--------------+----------+-------------+-----------+---- >--------+-----------+-----------+----------- > 9384219 | file_92 | 2200 | 8423 | f | 49837 | >public | 208179 | 318932 | 158377 >(1 row) > >reltuples does not change ? Hmm. n_tup_ins looks fine. > > That is expected, reltuples only gets updated by a vacuum or an analyze. >This table is basically a queue full of records waiting to get transfered >over from our 68030 system to the PG database. The records are then moved >into folders (using a trigger) like file_92_myy depending on what month the >record was created on the 68030. During normal operations there should not >be more than 10 records at a time in the table, although during the course >of a day a normal system will get about 50k records. I create 50000 records >to simulate incoming traffic, since we don't have much traffic in the test >lab. > >After a few hours we have > >secom=# select count(*) from file_92; > count >------- > 42072 > >So we have sent over approx 8000 Records. > > oid | relname | relnamespace | relpages | relisshared | reltuples | >schemaname | n_tup_ins | n_tup_upd | n_tup_del >---------+---------+--------------+----------+-------------+-----------+---- >--------+-----------+-----------+----------- > 9384219 | file_92 | 2200 | 8423 | f | 49837 | >public | 208218 | 334521 | 166152 >(1 row) > > >n_tup_upd: 318932 + (50000-42072)*2 = 334788 pretty close. (Each record >gets updated twice, then moved) >n_tup_del: 158377 + (50000-42072) = 166305 pretty close. (there are also >minor background traffic going on) > > >I could send over the full vacuum verbose capture as well as the autovacuum >capture if that is of interest. > That might be helpful. I don't see a stats system problem here, but I also haven't heard of any autovac problems recently, so this might be something new. Thanks, Matthew O'Connor