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 2080E53642 for ; Fri, 25 Mar 2005 20:20:53 +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 89492-07 for ; Fri, 25 Mar 2005 20:20:50 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 842B35360B for ; Fri, 25 Mar 2005 20:20:49 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 31E7830959; Fri, 25 Mar 2005 21:20:50 +0100 (MET) From: "Otto Blomqvist" X-Newsgroups: pgsql.performance Subject: Re: pg_autovacuum not having enough suction ? Date: Fri, 25 Mar 2005 12:24:51 -0800 Organization: Hub.Org Networking Services Lines: 282 Message-ID: References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <00f201c53168$96903480$e602a8c0@blomqvist> <42446A66.6010504@zeut.net> 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.802 tagged_above=0 required=5 tests=AWL, PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200503/557 X-Sequence-Number: 11262 > Was reltuples = 113082 correct right after the vacuum? No, There where about 31000 rows after the vacuum. I'm no expert but tuples = rows, right ? This is not a "normal" table though, in the sence that it is only a temporary holding ground as I explained earlier. I create 50000 records and these get sent over from our custom 68030 system, to tables like file_92_myy, depending on the date of the record. A pl/pgsql script is used as a trigger to move the records after they get data from the 68030. Don't know if that is of interest or not. I could post the trigger if you'd like. ""Matthew T. O'Connor"" wrote in message news:42446A66.6010504@zeut.net... > hmm.... the value in reltuples should be accurate after a vacuum (or > vacuum analyze) if it's not it's a vacuum bug or something is going on > that isn't understood. If you or pg_autovacuum are running plain > analyze commands, that could explain the invalid reltules numbers. > > Was reltuples = 113082 correct right after the vacuum? > > Matthew > > > Otto Blomqvist wrote: > > >It looks like the reltuples-values are screwed up. Even though rows are > >constantly being removed from the table the reltuples keep going up. If I > >understand correctly that also makes the Vacuum threshold go up and we end > >up in a vicious circle. Right after pg_autovacuum performed a vacuum analyze > >on the table it actually had 31000 records, but reltuples reports over 100k. > >I'm not sure if this means anything But i thought i would pass it along. > > > >PG version 8.0.0, 31MB tarred DB. > > > >[2005-03-25 09:16:14 EST] INFO: dbname: testing > >[2005-03-25 09:16:14 EST] INFO: oid: 9383816 > >[2005-03-25 09:16:14 EST] INFO: username: (null) > >[2005-03-25 09:16:14 EST] INFO: password: (null) > >[2005-03-25 09:16:14 EST] INFO: conn is null, (not connected) > >[2005-03-25 09:16:14 EST] INFO: default_analyze_threshold: 1000 > >[2005-03-25 09:16:14 EST] INFO: default_vacuum_threshold: 500 > > > > > >[2005-03-25 09:05:12 EST] INFO: table name: secom."public"."file_92" > >[2005-03-25 09:05:12 EST] INFO: relid: 9384219; relisshared: 0 > >[2005-03-25 09:05:12 EST] INFO: reltuples: 49185.000000; relpages: > >8423 > >[2005-03-25 09:05:12 EST] INFO: curr_analyze_count: 919274; > >curr_vacuum_count: 658176 > >[2005-03-25 09:05:12 EST] INFO: last_analyze_count: 899272; > >last_vacuum_count: 560541 > >[2005-03-25 09:05:12 EST] INFO: analyze_threshold: 49685; > >vacuum_threshold: 100674 > > > > > >[2005-03-25 09:10:12 EST] DEBUG: Performing: VACUUM ANALYZE > >"public"."file_92" > >[2005-03-25 09:10:33 EST] INFO: table name: secom."public"."file_92" > >[2005-03-25 09:10:33 EST] INFO: relid: 9384219; relisshared: 0 > >[2005-03-25 09:10:33 EST] INFO: reltuples: 113082.000000; relpages: > >6624 > >[2005-03-25 09:10:33 EST] INFO: curr_analyze_count: 923820; > >curr_vacuum_count: 662699 > >[2005-03-25 09:10:33 EST] INFO: last_analyze_count: 923820; > >last_vacuum_count: 662699 > >[2005-03-25 09:10:33 EST] INFO: analyze_threshold: 113582; > >vacuum_threshold: 227164 > > > > > >[2005-03-25 09:16:14 EST] INFO: table name: secom."public"."file_92" > >[2005-03-25 09:16:14 EST] INFO: relid: 9384219; relisshared: 0 > >[2005-03-25 09:16:14 EST] INFO: reltuples: 113082.000000; relpages: > >6624 <-- Actually has 31k rows > >[2005-03-25 09:16:14 EST] INFO: curr_analyze_count: 923820; > >curr_vacuum_count: 662699 > >[2005-03-25 09:16:14 EST] INFO: last_analyze_count: 923820; > >last_vacuum_count: 662699 > >[2005-03-25 09:16:14 EST] INFO: analyze_threshold: 113582; > >vacuum_threshold: 227164 > > > >DETAIL: Allocated FSM size: 1000 relations + 2000000 pages = 11784 kB > >shared memory. > > > > > > > > > >----- Original Message ----- > >From: "Matthew T. O'Connor" > >To: "Otto Blomqvist" ; > > > >Sent: Thursday, March 24, 2005 3:58 PM > >Subject: Re: [PERFORM] pg_autovacuum not having enough suction ? > > > > > > > > > >>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 > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >> http://archives.postgresql.org > >> > >> > >> > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 8: explain analyze is your friend > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >