Home > mailing lists

Thread: Zero dead tuples, when significant apparent bloat

Zero dead tuples, when significant apparent bloat

From

John Melesky

Date:

11 December 2013, 01:27:07

Here's the situation:

relation_size | indexes_size | total_relation_size

----------------+----------------+---------------------

997 MB | 2073 MB | 3070 MB

(1 row)

If I select n_dead_tup from pg_stat_user_tables, I get:

n_dead_tup

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

Okay, so I run ANALYZE table, then check again:

n_dead_tup

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

Finally, I run VACUUM FULL ANALYZE table. Now I see:

relation_size | indexes_size | total_relation_size

----------------+----------------+---------------------

118 MB | 200 MB | 319 MB

(1 row)

.... ?

It seems clear that there were dead tuples, since the table size shrank to an eighth of its previous size. Why did analyze not pick that up?

Am I missing something?

This is a very large database, so we want to introspect against live/dead tuple percentage to minimize the tables we run a VACUUM FULL against.

I've been staring at this for days.

-john

John Melesky | Sr Database Administrator

503.284.7581 x204 | john.melesky@rentrak.com
RENTRAK | www.rentrak.com | NASDAQ: RENT

Notice: This message is confidential and is intended only for the
recipient(s) named above. If you have received this message in error,
or are not the named recipient(s), please immediately notify the
sender and delete this message.

Re: Zero dead tuples, when significant apparent bloat

From

bricklen

Date:

11 December 2013, 01:57:33

On Tue, Dec 10, 2013 at 5:05 PM, John Melesky <john.melesky@rentrakmail.com> wrote:

It seems clear that there were dead tuples, since the table size shrank to an eighth of its previous size. Why did analyze not pick that up?
Am I missing something?
This is a very large database, so we want to introspect against live/dead tuple percentage to minimize the tables we run a VACUUM FULL against.

If you willing to install the pgstattuple[1] extension, what does the output say? Note, there is some overhead on larger tables (disk I/O primarily)

select * from pgstattuple('your table');

Also, check the output from bloat query at https://wiki.postgresql.org/wiki/Show_database_bloat

[1] http://www.postgresql.org/docs/current/static/pgstattuple.html

Re: Zero dead tuples, when significant apparent bloat

From

Tom Lane

Date:

11 December 2013, 02:05:45

John Melesky <john.melesky@rentrakmail.com> writes:
> It seems clear that there were dead tuples, since the table size shrank to
> an eighth of its previous size. Why did analyze not pick that up?

Dead tuples and empty space are not the same thing.

            regards, tom lane

Re: Zero dead tuples, when significant apparent bloat

From

John Melesky

Date:

12 December 2013, 21:04:38

On Tue, Dec 10, 2013 at 5:57 PM, bricklen <bricklen@gmail.com> wrote:

If you willing to install the pgstattuple[1] extension, what does the output say? Note, there is some overhead on larger tables (disk I/O primarily)

Yeah, this is a prod database that sees nontrivial traffic, so I'm not yet ready to install pgstattuple.

Also, check the output from bloat query at https://wiki.postgresql.org/wiki/Show_database_bloat

Interestingly, that does return results that include the tables which are exhibiting the zero dead tuple behavior. Thanks.

On Tue, Dec 10, 2013 at 6:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Dead tuples and empty space are not the same thing.

Can you elaborate on that?

What I'm trying to do is efficiently choose tables which are displaying significant space bloat to schedule them for a manual vacuum full.

My understanding was that percentage of dead tuples would be a reasonable metric, since dead tuples in the table would also be reflected in the indexes, and since vacuum and autovacuum attempt to return pages which are entirely empty of live tuples. Actually, I assumed that dead tuples were also the triggering metric for autovacuum and autoanalyze.

If that method is not feasible, can you suggest a different method for determining candidate tables to reclaim space from?

-john

John Melesky | Sr Database Administrator