Re: pg_autovacuum not having enough suction ? - Mailing list pgsql-performance

From Otto Blomqvist
Subject Re: pg_autovacuum not having enough suction ?
Date
Msg-id d21sdt$7u0$1@news.hub.org
Whole thread Raw
In response to pg_autovacuum not having enough suction ?  ("Otto Blomqvist" <o.blomqvist@secomintl.com>)
List pgsql-performance
> 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.





pgsql-performance by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Delete query takes exorbitant amount of time
Next
From: "Matthew T. O'Connor"
Date:
Subject: Re: pg_autovacuum not having enough suction ?