Thread: Vacuum Full + Cluster + Vacuum full = non removable dead rows

On Tue, Aug 17, 2010 at 1:19 PM, Alexandre de Arruda Paes
<adaldeia@gmail.com> wrote:
> Hi,
>
> PG 8.4.4
> I did't see any transactions locking this table and I think that CLUSTER
> will recreate the table.

Prepared transactions?

> This is a temporary table, with one DELETE, Some INSERTs and a lot of
> UPDATES. And the UPDATES become slow and slow every time.
> The only way to correct, is truncating the table.

And you're sure there aren't any "idle in transaction" connections/

Alexandre de Arruda Paes <adaldeia@gmail.com> writes:
> I know the problem with VACUUM FULL and bloated Indexes, but I don't
> understand why the table that is not in use by nobody, cant be vacuumed or
> clustered to avoid dead tuples.

There's an open transaction somewhere that VACUUM is preserving the
tuples for.  This transaction need not ever have touched the table,
or ever intend to touch the table --- but VACUUM cannot know that,
so it saves any tuples that the transaction might be entitled to see
if it looked.

> carmen=# select * from vlocks where relname='tp93t'; select * from
> pg_stat_activity where usename='webpa';

You keep on showing us only subsets of pg_stat_activity :-(

Also, if you don't see anything in pg_stat_activity, try pg_prepared_xacts.

            regards, tom lane

Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.us>

>> There's an open transaction somewhere that VACUUM is preserving
>> the tuples for.  This transaction need not ever have touched the
>> table, or ever intend to touch the table --- but VACUUM cannot
>> know that, so it saves any tuples that the transaction might be
>> entitled to see if it looked.
>>
>> > carmen=# select * from vlocks where relname='tp93t'; select *
>> > from pg_stat_activity where usename='webpa';
>>
>> You keep on showing us only subsets of pg_stat_activity :-(

> select * from pg_stat_activity where usename='webpa';

You keep on showing us only subsets of pg_stat_activity :-(

*ANY* open transaction, including "idle in transaction" including
transactions by other users in other databases will prevent vacuum
from cleaning up rows, for the reasons Tom already gave you.

What do you get from?:

select * from pg_stat_activity where current_query <> '<IDLE>'
  order by xact_start limit 10;

-Kevin

Alexandre de Arruda Paes <adaldeia@gmail.com> writes:
> Below, the pg_prepared_xacts result.

OK, so you don't have any prepared transactions, but you're still not
showing us the full content of pg_stat_activity.

Just out of curiosity, how many rows does "select count(*) from tp93t"
think there are?

            regards, tom lane

So, does it mean that VACUUM will never clean dead rows if you have a
non-stop transactional activity in your PG database???... (24/7 OLTP
for ex.)

Rgds,
-Dimitri


On 8/19/10, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.us>
>
>>> There's an open transaction somewhere that VACUUM is preserving
>>> the tuples for.  This transaction need not ever have touched the
>>> table, or ever intend to touch the table --- but VACUUM cannot
>>> know that, so it saves any tuples that the transaction might be
>>> entitled to see if it looked.
>>>
>>> > carmen=# select * from vlocks where relname='tp93t'; select *
>>> > from pg_stat_activity where usename='webpa';
>>>
>>> You keep on showing us only subsets of pg_stat_activity :-(
>
>> select * from pg_stat_activity where usename='webpa';
>
> You keep on showing us only subsets of pg_stat_activity :-(
>
> *ANY* open transaction, including "idle in transaction" including
> transactions by other users in other databases will prevent vacuum
> from cleaning up rows, for the reasons Tom already gave you.
>
> What do you get from?:
>
> select * from pg_stat_activity where current_query <> '<IDLE>'
>   order by xact_start limit 10;
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

No, it means it can't clean rows that are younger than the oldest
transaction currently in progress.  if you started a transaction 5
hours ago, then all the dead tuples created in the last 5 hours are
not recoverable.  Dead tuples created before that transaction are
recoverable.  If you run transactions for days or weeks, then you're
gonna have issues.

On Sat, Aug 21, 2010 at 2:25 AM, Dimitri <dimitrik.fr@gmail.com> wrote:
> So, does it mean that VACUUM will never clean dead rows if you have a
> non-stop transactional activity in your PG database???... (24/7 OLTP
> for ex.)
>
> Rgds,
> -Dimitri
>
>
> On 8/19/10, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>>> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.us>
>>
>>>> There's an open transaction somewhere that VACUUM is preserving
>>>> the tuples for.  This transaction need not ever have touched the
>>>> table, or ever intend to touch the table --- but VACUUM cannot
>>>> know that, so it saves any tuples that the transaction might be
>>>> entitled to see if it looked.
>>>>
>>>> > carmen=# select * from vlocks where relname='tp93t'; select *
>>>> > from pg_stat_activity where usename='webpa';
>>>>
>>>> You keep on showing us only subsets of pg_stat_activity :-(
>>
>>> select * from pg_stat_activity where usename='webpa';
>>
>> You keep on showing us only subsets of pg_stat_activity :-(
>>
>> *ANY* open transaction, including "idle in transaction" including
>> transactions by other users in other databases will prevent vacuum
>> from cleaning up rows, for the reasons Tom already gave you.
>>
>> What do you get from?:
>>
>> select * from pg_stat_activity where current_query <> '<IDLE>'
>>   order by xact_start limit 10;
>>
>> -Kevin
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



--
To understand recursion, one must first understand recursion.

Great! - it's what I expected until now :-)
but discussion in this thread put my mind in trouble :-))

So, the advice for Alexandre here is just to check the age of the
oldest running transaction and the last time when the table in
question was modified.. - if modification time is older than the
oldest transaction = we have a problem in PG.. Otherwise it works as
expected to match MVCC.

Rgds,
-Dimitri


On 8/21/10, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> No, it means it can't clean rows that are younger than the oldest
> transaction currently in progress.  if you started a transaction 5
> hours ago, then all the dead tuples created in the last 5 hours are
> not recoverable.  Dead tuples created before that transaction are
> recoverable.  If you run transactions for days or weeks, then you're
> gonna have issues.
>
> On Sat, Aug 21, 2010 at 2:25 AM, Dimitri <dimitrik.fr@gmail.com> wrote:
>> So, does it mean that VACUUM will never clean dead rows if you have a
>> non-stop transactional activity in your PG database???... (24/7 OLTP
>> for ex.)
>>
>> Rgds,
>> -Dimitri
>>
>>
>> On 8/19/10, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
>>> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>>>> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.us>
>>>
>>>>> There's an open transaction somewhere that VACUUM is preserving
>>>>> the tuples for.  This transaction need not ever have touched the
>>>>> table, or ever intend to touch the table --- but VACUUM cannot
>>>>> know that, so it saves any tuples that the transaction might be
>>>>> entitled to see if it looked.
>>>>>
>>>>> > carmen=# select * from vlocks where relname='tp93t'; select *
>>>>> > from pg_stat_activity where usename='webpa';
>>>>>
>>>>> You keep on showing us only subsets of pg_stat_activity :-(
>>>
>>>> select * from pg_stat_activity where usename='webpa';
>>>
>>> You keep on showing us only subsets of pg_stat_activity :-(
>>>
>>> *ANY* open transaction, including "idle in transaction" including
>>> transactions by other users in other databases will prevent vacuum
>>> from cleaning up rows, for the reasons Tom already gave you.
>>>
>>> What do you get from?:
>>>
>>> select * from pg_stat_activity where current_query <> '<IDLE>'
>>>   order by xact_start limit 10;
>>>
>>> -Kevin
>>>
>>> --
>>> Sent via pgsql-performance mailing list
>>> (pgsql-performance@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
> --
> To understand recursion, one must first understand recursion.
>

On Sat, Aug 21, 2010 at 9:49 AM, Alexandre de Arruda Paes
<adaldeia@gmail.com> wrote:
> Only for discussion: the CLUSTER command, in my little knowledge, is a
> intrusive command that's cannot recover the dead tuples too.
>
> Only TRUNCATE can do this job, but obviously is not applicable all the time.

Either VACUUM or CLUSTER will recover *dead* tuples.  What you can't
recover are tuples that are still visible to some running transaction.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

The problem here is that we're trying to keep an image of a whole
world for any transaction which is in most cases will need to get a
look on few streets around.. ;-)
I understand well that it's respecting the standard and so on, but the
background problem that you may see your table bloated just because
there is a long running transaction appeared in another database, and
if it's maintained/used/etc by another team - the problem very quickly
may become human rather technical :-))

So, why simply don't add a FORCE option to VACUUM?.. - In this case if
one executes "VACUUM FORCE TABLE" will be just aware about what he's
doing and be sure no one of the active transactions will be ever
access this table.

What do you think?.. ;-)

Rgds,
-Dimitri


On 8/22/10, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Aug 21, 2010 at 9:49 AM, Alexandre de Arruda Paes
> <adaldeia@gmail.com> wrote:
>> Only for discussion: the CLUSTER command, in my little knowledge, is a
>> intrusive command that's cannot recover the dead tuples too.
>>
>> Only TRUNCATE can do this job, but obviously is not applicable all the
>> time.
>
> Either VACUUM or CLUSTER will recover *dead* tuples.  What you can't
> recover are tuples that are still visible to some running transaction.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Dimitri wrote:
> I understand well that it's respecting the standard and so on, but the
> background problem that you may see your table bloated just because
> there is a long running transaction appeared in another database, and
> if it's maintained/used/etc by another team - the problem very quickly
> may become human rather technical :-))
>

The way VACUUM and autovacuum work by default, it's OK to expect just
over 20% of the database rows to be bloat from dead rows.  On some
systems that much overhead is still too much, but on others the system
continues to operate just fine with that quantity of bloat.  It's not
unreasonable, and is recoverable once the long running transaction finishes.

If your application has a component to it that allows a transaction to
run for so long that more than 20% of a table can be dead before it
completes, you have a technical problem.  The technical solution may not
be simple or obvious, but you need to find one--not say "the person
shouldn't have done that".  Users should never have gotten an API
exposed to them where it's possible for them to screw things up that
badly.  The usual first round of refactoring here is to figuring out how
to break transactions into smaller chunks usefully, which tends to
improve other performance issues too, and then they don't run for so
long either.

> So, why simply don't add a FORCE option to VACUUM?.. - In this case if
> one executes "VACUUM FORCE TABLE" will be just aware about what he's
> doing and be sure no one of the active transactions will be ever
> access this table.
>

See above.  If you've gotten into this situation, you do not need a
better hammer to smack the part of the server that is stuck.  One would
be almost impossible to build, and have all sorts of side effects it's
complicated to explain.  It's far simpler to just avoid to known and
common design patterns that lead to this class of problem in the first
place.  This is a database application coding problem, not really a
database internals one.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

Alexandre de Arruda Paes wrote:
> Unfortunately, the customer can't wait for the solution and the
> programmer eliminated the
> use of this table by using a in-memory array.

Well that will be fun.  Now they've traded their old problem for a new
one--cache inconsistency between the data in memory and what sitting in
the database.  The fun apart about that is that the cache mismatch bugs
you'll run into are even more subtle, frustrating, and difficult to
replicate on demand than the VACUUM ones.

> Only for discussion: the CLUSTER command, in my little knowledge, is a
> intrusive command that's cannot recover the dead tuples too.
> Only TRUNCATE can do this job, but obviously is not applicable all the
> time.

Yes, CLUSTER takes a full lock on the table and rewrites a new one with
all the inactive data removed.  The table is unavailable to anyone else
while that's happening.

Some designs separate their data into partitions in a way that it's
possible to TRUNCATE/DROP the ones that are no longer relevant (and are
possibly filled with lots of dead rows) in order to clean them up
without using VACUUM.  This won't necessarily help with long-running
transactions though.  If those are still referring to do data in those
old partitions, removing them will be blocked for the same reason VACUUM
can't clean up inside of them--they data is still being used by an
active transaction.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us