Обсуждение: pgstattuple output?

Hi,

 I have an aging 7.3 database on Solaris 9/Sparc.  We are on the verge of
upgrading Postgresql, but we first need to reclaim some disk space.  I was
looking for tables that may have become fragmented when I saw this
pgstattuple output that confused me:

table_len             21773516800
tuple_count              69244287
tuple_len             13058755529
tuple_percent               59.98
dead_tuple_count                0
dead_tuple_len                  0
dead_tuple_percent              0
free_space             8013437220
free_percent                 36.8

Why is there so much free space with no dead tuples?  This table has likely
had serveral columns added over time, is that part of the problem? Indexes?

Thanks,

 Michael

On Wed, Jan 25, 2006 at 03:40:38PM -0800, Michael Crozier wrote:
> Why is there so much free space with no dead tuples?  This table has likely
> had serveral columns added over time, is that part of the problem? Indexes?

An ordinary vacuum frees space for PostgreSQL's use but it doesn't
shrink the table's file(s) and return space to the operating system;
this remains true in the latest versions.  If the table was ever
as large as you're seeing then it won't shrink unless you do a
vacuum full, cluster, dump/drop/create/restore, etc.  Here's an
example in 8.1.2:

test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> INSERT INTO foo SELECT 1 FROM generate_series(1, 100000);
INSERT 0 100000
test=> UPDATE foo SET x = 2;
UPDATE 100000
test=> VACUUM foo;
VACUUM
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len          | 7225344
tuple_count        | 100000
tuple_len          | 3200000
tuple_percent      | 44.29
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 3205936
free_percent       | 44.37

This example shows the same thing you're seeing: lots of free space
but no dead tuples.  The table *did* have a lot of dead tuples due
to the update but the vacuum freed that space.  If we do another
update the table length doesn't change (not by much, anyway) because
the database is able to reuse the free space without having to
allocate more pages via the operating system:

test=> UPDATE foo SET x = 3;
UPDATE 100000
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len          | 7233536
tuple_count        | 100000
tuple_len          | 3200000
tuple_percent      | 44.24
dead_tuple_count   | 100000
dead_tuple_len     | 3200000
dead_tuple_percent | 44.24
free_space         | 12348
free_percent       | 0.17

--
Michael Fuhr

> > Why is there so much free space with no dead tuples?  This table has
> > likely had serveral columns added over time, is that part of the problem?
> > Indexes?
>
> An ordinary vacuum frees space for PostgreSQL's use but it doesn't
> shrink the table's file(s) and return space to the operating system;
> this remains true in the latest versions.

I think I see now, "dead" tuples are the tuples that have yet to be reclaimed
by vacuum, not tuples that are ready to be used. I'm still rather confused,
as this table is only modified via inserts.  No deletes or update operations
are ever performed.  Logically (ie "I don't really know the truth") this
table should have no free tuples or free space except for the remainder of
the last allocated page.

What I need to see is the free space map, but I don't think those
patches/functions exist for 7.3.  I'll go look around.

Thanks,

 Michael

Michael Crozier wrote:

> I think I see now, "dead" tuples are the tuples that have yet to be
> reclaimed
> by vacuum, not tuples that are ready to be used. I'm still rather
> confused,
> as this table is only modified via inserts.  No deletes or update
> operations
> are ever performed.  Logically (ie "I don't really know the truth")
> this
> table should have no free tuples or free space except for the
> remainder of
> the last allocated page.

I suspect that if a transaction rolls back, any inserts done in the
interim turn into dead tuples.

- John Burger
   MITRE

On Thu, Jan 26, 2006 at 10:13:52AM -0500, John D. Burger wrote:
> I suspect that if a transaction rolls back, any inserts done in the
> interim turn into dead tuples.

Yep:

test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> BEGIN; INSERT INTO foo SELECT 1 FROM generate_series(1, 10000); ROLLBACK;
BEGIN
INSERT 0 10000
ROLLBACK
test=> INSERT INTO foo SELECT 1 FROM generate_series(1, 20000);
INSERT 0 20000
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len          | 1089536
tuple_count        | 20000
tuple_len          | 640000
tuple_percent      | 58.74
dead_tuple_count   | 10000
dead_tuple_len     | 320000
dead_tuple_percent | 29.37
free_space         | 6872
free_percent       | 0.63

test=> VACUUM foo;
VACUUM
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+--------
table_len          | 1089536
tuple_count        | 20000
tuple_len          | 640000
tuple_percent      | 58.74
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 326692
free_percent       | 29.98

--
Michael Fuhr