Thread: pg_class.reltuples not reset by VACUUM?

pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
I thought that VACUUM ANALYZE would always restore some sense of reality
to the internal statistics for a table.  However ...


# select count(*) from job_queue;

 count
-------
   834
(1 row)

# select reltuples from pg_class where relname = 'job_queue';

 reltuples
-----------
    206277
(1 row)

# vacuum analyze job_queue;

# select reltuples from pg_class where relname = 'job_queue';

 reltuples
-----------
    2062xx   (slightly larger number)
(1 row)


This is of concern to me mostly because:

# explain select * from job_queue;

NOTICE:  QUERY PLAN:

Seq Scan on job_queue  (cost=0.00..5989.77 rows=206277 width=95)

Obviously, the optimizer thinks that the table has 200k rows in it!


--
Jeff Boes                                      vox 616.226.9550 ext 24
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Tom Lane
Date:
Jeff Boes <jboes@nexcerpt.com> writes:
> I thought that VACUUM ANALYZE would always restore some sense of reality
> to the internal statistics for a table.  However ...

Could we see the output of VACUUM VERBOSE for that table?

I suspect you have lots of dead-but-not-yet-reclaimable tuples in the
table, presumably because there is some very old transaction lurking
in the background.

            regards, tom lane


Re: pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
On Tue, 2003-04-08 at 13:52, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> > I thought that VACUUM ANALYZE would always restore some sense of reality
> > to the internal statistics for a table.  However ...
>
> Could we see the output of VACUUM VERBOSE for that table?

No, not since I've dropped and recreated it...

>
> I suspect you have lots of dead-but-not-yet-reclaimable tuples in the
> table, presumably because there is some very old transaction lurking
> in the background.
>

Well, here's the present state of the table, 24 hours later:

 explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=11248.76..11248.76 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10357.81 rows=356381 width=0)

EXPLAIN

# select count(*) from job_queue;
 count
-------
  2369
(1 row)

# vacuum analyze verbose job_queue;

NOTICE:  --Relation job_queue--
NOTICE:  Pages 6831: Changed 2, Empty 0; Tup 358441: Vac 0, Keep 356048,
UnUsed 5.
    Total CPU 0.00s/0.15u sec elapsed 0.16 sec.
NOTICE:  --Relation pg_toast_292377168--
NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing job_queue
VACUUM

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=11311.51..11311.51 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10415.41 rows=358441 width=0)

EXPLAIN

# analyze verbose job_queue;
NOTICE:  Analyzing job_queue
ANALYZE
xifos:~ # explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=6861.41..6861.41 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..6855.33 rows=2433 width=0)

EXPLAIN


--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Tom Lane
Date:
Jeff Boes <jboes@nexcerpt.com> writes:
> Well, here's the present state of the table, 24 hours later:

> # select count(*) from job_queue;
>  count
> -------
>   2369
> (1 row)

> # vacuum analyze verbose job_queue;

> NOTICE:  --Relation job_queue--
> NOTICE:  Pages 6831: Changed 2, Empty 0; Tup 358441: Vac 0, Keep 356048,
> UnUsed 5.
>     Total CPU 0.00s/0.15u sec elapsed 0.16 sec.

Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
Look for some client that's holding an open transaction for long
periods.

            regards, tom lane


Re: pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
On Tue, 2003-04-08 at 15:10, Tom Lane wrote:

> Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
> Look for some client that's holding an open transaction for long
> periods.

It's even weirder than that:  note in the following how VACUUM sets the
tuple count high, and ANALYZE sets it low.  This appears to happen even
if there are no transactions open.

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=7064.26..7064.26 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..7056.81 rows=2981 width=0)

EXPLAIN
# vacuum job_queue;
VACUUM

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=11690.88..11690.88 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10764.70 rows=370470 width=0)

EXPLAIN

# analyze job_queue;
ANALYZE

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=7097.34..7097.34 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..7089.87 rows=2987 width=0)

EXPLAIN


--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Tom Lane
Date:
Jeff Boes <jboes@nexcerpt.com> writes:
> On Tue, 2003-04-08 at 15:10, Tom Lane wrote:
>> Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
>> Look for some client that's holding an open transaction for long
>> periods.

> It's even weirder than that:  note in the following how VACUUM sets the
> tuple count high, and ANALYZE sets it low.

That doesn't surprise me, given what they count.

> This appears to happen even if there are no transactions open.

You have open transactions.  Go find 'em...

            regards, tom lane


Re: pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
On Tue, 2003-04-08 at 15:19, Tom Lane wrote:

> > This appears to happen even if there are no transactions open.
>
> You have open transactions.  Go find 'em...
>

Hmm.  Maybe, maybe not.  After I shut down everything, I got

"Parent tuple not found"

errors.  I bounced the database, and it cleared up.

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Robert Treat
Date:
Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older
system?

Robert Treat

On Tue, 2003-04-08 at 16:30, Jeff Boes wrote:
> On Tue, 2003-04-08 at 15:19, Tom Lane wrote:
>
> > > This appears to happen even if there are no transactions open.
> >
> > You have open transactions.  Go find 'em...
> >
>
> Hmm.  Maybe, maybe not.  After I shut down everything, I got
>
> "Parent tuple not found"
>
> errors.  I bounced the database, and it cleared up.
>
> --
> Jeff Boes                                      vox 269.226.9550 ext 24
> Database Engineer                                     fax 269.349.9076
> Nexcerpt, Inc.                                 http://www.nexcerpt.com
>            ...Nexcerpt... Extend your Expertise
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
On Thu, 2003-04-10 at 14:51, Robert Treat wrote:
> Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older
> system?
>

These are VACUUMs as the user "nexcerpt". Database version is 7.2.3.

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Jeff Boes
Date:
On Thu, 2003-04-10 at 14:51, Robert Treat wrote:
> Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older
> system?
>

And I probably didn't answer your question.  The username used to
perform all VACUUMs is marked "usesuper = t" in pg_shadow.  I interpret
that as a superuser.  Am I right?

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise


Re: pg_class.reltuples not reset by VACUUM?

From
Robert Treat
Date:
On Thu, 2003-04-10 at 16:42, Jeff Boes wrote:
> On Thu, 2003-04-10 at 14:51, Robert Treat wrote:
> > Out of curiosity, are you doing non-superuser vacuums on 7.2.3 or older
> > system?
> >
>
> And I probably didn't answer your question.  The username used to
> perform all VACUUMs is marked "usesuper = t" in pg_shadow.  I interpret
> that as a superuser.  Am I right?
>

usesuper does indicate that the user is a super user. The reason I asked
was your problem sounded suspiciously like the "No one parent tuple was
found" error that was fixed in 7.2.4. I *thought* that those cases we're
the result of vacuuming as non-superuser, but you may want to search the
archives if it pops up again, there might be other cases that cause it.

Oh, I guess I should make the obligatory "you might want to upgrade"
suggestion as well :-)

Robert Treat