Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum not having enough suction ?

From: "Otto Blomqvist" <o(dot)blomqvist(at)secomintl(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: pg_autovacuum not having enough suction ?
Date: 2005-03-25 20:35:00
Message-ID: d21sdt$7u0$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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.





In response to

pgsql-performance by date

Next:From: Matthew T. O'ConnorDate: 2005-03-25 21:04:25
Subject: Re: pg_autovacuum not having enough suction ?
Previous:From: Simon RiggsDate: 2005-03-25 20:28:49
Subject: Re: Delete query takes exorbitant amount of time

pgsql-hackers by date

Next:From: Steve CrawfordDate: 2005-03-25 20:53:53
Subject: Re: Bug 1500
Previous:From: Tom LaneDate: 2005-03-25 20:33:44
Subject: Re: Bug 1500

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group