Horton

Thread

  1. Big number of "unused" pages as reported by VACUUM

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-09-06T06:04:14Z

    Hello!

Some time ago I've got troubles with performance of my PG.
After investigation I had found that the most probable reason was the big
number of "unused" pages. Below follows what VACUUM reported:

=======================
vacuum verbose goods;
NOTICE:  --Relation goods--
NOTICE:  Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep 0, UnUsed 465938.
=======================
select count(*) from goods;
 count
-------
 16157
=======================

The same schema with the almost identical number of rows gives completely
different result on another table:
=======================
vacuum verbose goods;
NOTICE:  --Relation goods--
NOTICE:  Pages 912: Changed 0, Empty 0; Tup 11209: Vac 0, Keep 0, UnUsed
19778.
=======================
select count(*) from goods;
 count
-------
 11209
=======================

Two questions:

1) Where to seek the real source of the enormous big number of unused
pages?

2) How to shrink the table (i.e. how can I get rid those unused pages)?

PG: was 7.2.1, now 7.2.2.

-- 
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

  2. Re: [PATCHES] Big number of "unused" pages as reported by VACUUM

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-09-06T06:29:19Z

    Hi Yury,

This question should not be posted to -patches, changed accordingly.

What happens if you go 'VACUUM VERBOSE FULL goods;'?

Your on-disk files won't shrink or have unused tuples removed unless you
VACUUM FULL.  The problem with doing VACUUM FULL is that it totally locks
the whole table while it's running, meaning no-one can use the table.  This
is bad in production environments, so it's not the default.  Bear in mind
that postgres will re-use the unused portion of the table as you add new
tuples...

Chris

> Some time ago I've got troubles with performance of my PG.
> After investigation I had found that the most probable reason was the big
> number of "unused" pages. Below follows what VACUUM reported:
>
> =======================
> vacuum verbose goods;
> NOTICE:  --Relation goods--
> NOTICE:  Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep
> 0, UnUsed 465938.
> =======================
> select count(*) from goods;
>  count
> -------
>  16157

  3. Re: [PATCHES] Big number of "unused" pages as reported by

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-09-06T06:56:52Z

    Hello!

On Fri, 6 Sep 2002, Christopher Kings-Lynne wrote:

> This question should not be posted to -patches, changed accordingly.
>
> What happens if you go 'VACUUM VERBOSE FULL goods;'?

Oh, big thanx!
But 'VACUUM VERBOSE FULL goods;' didn't work, only 'VACUUM FULL VERBOSE
goods;' did.:)

I make a guess I've got this due to parallel running of a program making
bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big
number of unused pages in such a case. LOCK TABLE?

>
> Your on-disk files won't shrink or have unused tuples removed unless you
> VACUUM FULL.  The problem with doing VACUUM FULL is that it totally locks
> the whole table while it's running, meaning no-one can use the table.  This

This can't scare people whom had dealt with 6.x.;)
Only if "We scare because we care"...=)

> is bad in production environments, so it's not the default.  Bear in mind
> that postgres will re-use the unused portion of the table as you add new
> tuples...

Yes, as an ole MUMPSter I did catch this very well some times ago.=)

>
> Chris
>
> > Some time ago I've got troubles with performance of my PG.
> > After investigation I had found that the most probable reason was the big
> > number of "unused" pages. Below follows what VACUUM reported:
> >
> > =======================
> > vacuum verbose goods;
> > NOTICE:  --Relation goods--
> > NOTICE:  Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep
> > 0, UnUsed 465938.
> > =======================
> > select count(*) from goods;
> >  count
> > -------
> >  16157
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Yep! Suggest to add this as well as that typical mistake with
LANGUAGE/HANDLER (plpgsql.so I mean).:-)

-- 
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

  4. Re: [PATCHES] Big number of "unused" pages as reported by VACUUM

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-09-06T07:20:43Z

    > I make a guess I've got this due to parallel running of a program making
> bulk INSERTs/UPDATEs into that table. Mmm...I need a way to avoid the big
> number of unused pages in such a case. LOCK TABLE?

Well, I suggest doing a normal vacuum analyze ('VACUUM ANALYZE goods') after
every bulk insert/update.  This will go through the table and mark all new
outdated tuples as re-usable.  That way, when you do your next bulk
insert/update it will be able to reuse the unused tuples.  Give that a
try...

Chris

  5. Re: Big number of "unused" pages as reported by VACUUM

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-06T13:56:17Z

    I think you want to use VACUUM FULL to actually shrink the table.  In
7.2.X, VACUUM only records free space for later reuse.

---------------------------------------------------------------------------

Yury Bokhoncovich wrote:
> Hello!
> 
> Some time ago I've got troubles with performance of my PG.
> After investigation I had found that the most probable reason was the big
> number of "unused" pages. Below follows what VACUUM reported:
> 
> =======================
> vacuum verbose goods;
> NOTICE:  --Relation goods--
> NOTICE:  Pages 15068: Changed 0, Empty 0; Tup 16157: Vac 0, Keep 0, UnUsed 465938.
> =======================
> select count(*) from goods;
>  count
> -------
>  16157
> =======================
> 
> The same schema with the almost identical number of rows gives completely
> different result on another table:
> =======================
> vacuum verbose goods;
> NOTICE:  --Relation goods--
> NOTICE:  Pages 912: Changed 0, Empty 0; Tup 11209: Vac 0, Keep 0, UnUsed
> 19778.
> =======================
> select count(*) from goods;
>  count
> -------
>  11209
> =======================
> 
> Two questions:
> 
> 1) Where to seek the real source of the enormous big number of unused
> pages?
> 
> 2) How to shrink the table (i.e. how can I get rid those unused pages)?
> 
> PG: was 7.2.1, now 7.2.2.
> 
> -- 
> WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
> Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
> Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073