pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matthew T. O'Connor <matthew@zeut.net>
To: Otto Blomqvist <o.blomqvist@secomintl.com>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: pg_autovacuum not having enough suction ?
Date: Fri, 25 Mar 2005 14:45:42 -0500
Message-ID: <42446A66.6010504@zeut.net> (raw)
In-Reply-To: <00f201c53168$96903480$e602a8c0@blomqvist>
References: <d1v0a7$iau$1@news.hub.org>
	<42430FA0.9080204@zeut.net>
	<002b01c530c5$ad5a1620$e602a8c0@blomqvist>
	<42435408.30303@zeut.net>
	<00f201c53168$96903480$e602a8c0@blomqvist>

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" <matthew@zeut.net>
>To: "Otto Blomqvist" <o.blomqvist@secomintl.com>;
><pgsql-performance@postgresql.org>
>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
>
>  
>




view thread (19+ messages)  latest in thread

Message-ID: <42446A66.6010504@zeut.net>
Permalink:  ../42446A66.6010504@zeut.net/
Also on:    postgresql.org/message-id/42446A66.6010504@zeut.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: matthew@zeut.net, o.blomqvist@secomintl.com, tgl@sss.pgh.pa.us
  Subject: Re: pg_autovacuum not having enough suction ?
  In-Reply-To: <42446A66.6010504@zeut.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox