pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Otto Blomqvist <o.blomqvist@secomintl.com>
To: pgsql-performance@postgresql.org
Subject: Re: pg_autovacuum not having enough suction ?
Date: Fri, 25 Mar 2005 12:35:00 -0800
Message-ID: <d21sdt$7u0$1@news.hub.org> (raw)
References: <d1v0a7$iau$1@news.hub.org>
	<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>


> 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.







view thread (19+ messages)  latest in thread

Message-ID: <d21sdt$7u0$1@news.hub.org>
Permalink:  ../d21sdt$7u0$1@news.hub.org/
Also on:    postgresql.org/message-id/d21sdt$7u0$1@news.hub.org

 · 

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: o.blomqvist@secomintl.com
  Subject: Re: pg_autovacuum not having enough suction ?
  In-Reply-To: <d21sdt$7u0$1@news.hub.org>

* 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