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>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pg_autovacuum not having enough suction ?
Date: Thu, 24 Mar 2005 18:58:00 -0500
Message-ID: <42435408.30303@zeut.net> (raw)
In-Reply-To: <002b01c530c5$ad5a1620$e602a8c0@blomqvist>
References: <d1v0a7$iau$1@news.hub.org>
	<42430FA0.9080204@zeut.net>
	<002b01c530c5$ad5a1620$e602a8c0@blomqvist>

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





view thread (19+ messages)  latest in thread

Message-ID: <42435408.30303@zeut.net>
Permalink:  ../42435408.30303@zeut.net/
Also on:    postgresql.org/message-id/42435408.30303@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
  Subject: Re: pg_autovacuum not having enough suction ?
  In-Reply-To: <42435408.30303@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