pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Smith <greg@2ndquadrant.com>
To: Dimitri <dimitrik.fr@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Alexandre de Arruda Paes <adaldeia@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Date: Sun, 22 Aug 2010 13:53:04 -0400
Message-ID: <4C716400.5030900@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTimP3W5yK3Rr0UOkw8CywQ-5V_p5oBg2BSs63cTt@mail.gmail.com>
References: <AANLkTi=3XOw+6_gpR5c9c-OZaVa3AoDeVVOkcR4zA8V7@mail.gmail.com>
	<AANLkTi=865eVqoej58M8W1afxHZXVzx1uqnvxb--LmgG@mail.gmail.com>
	<AANLkTikSM75f-GksqBt-CwZJxiDF0mFEH3T7UDUiZ9=3@mail.gmail.com>
	<AANLkTimS9cjYedVmC0UrwYU42ivd_TAw_=nFZEggDXP6@mail.gmail.com>
	<AANLkTi=kTvCFWX4Zgkw=s-JRrv_+caEu57KUJEeyufgy@mail.gmail.com>
	<AANLkTimwDa+kJZaLuoOa=6ve_O9Ka5sHiwFVXud9rOku@mail.gmail.com>
	<AANLkTikz1VQFLw0dm54cUtQ2_eWmg=R6HVKu2nUUA5+b@mail.gmail.com>
	<14512.1282137722@sss.pgh.pa.us>
	<AANLkTik=uHxUbUEzfZJWDRyxhdLemytsAdtzUpyn6kF-@mail.gmail.com>
	<4C6CEE4202000025000348D6@gw.wicourts.gov>
	<AANLkTinpa+932QM7c8SxhkAY4Gr7RHFb_F_HHX+T3J64@mail.gmail.com>
	<AANLkTikvRrKxehYBZ6vD5MbWGPGANX4mPwe1-vcFQz_b@mail.gmail.com>
	<AANLkTikDbe+j_JUNgH61aREdwm01+eChCy89eVyUaeR4@mail.gmail.com>
	<AANLkTi=wP4pEnuFCx529qWCwPuSMpbKc1RUEh3Lhugwr@mail.gmail.com>
	<AANLkTikKmWDWNxzmFNGa_b7e+HXN5bh-Z1-PVxxeNs9x@mail.gmail.com>
	<AANLkTimP3W5yK3Rr0UOkw8CywQ-5V_p5oBg2BSs63cTt@mail.gmail.com>

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




view thread (15+ messages)  latest in thread

Message-ID: <4C716400.5030900@2ndquadrant.com>
Permalink:  ../4C716400.5030900@2ndquadrant.com/
Also on:    postgresql.org/message-id/4C716400.5030900@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: greg@2ndquadrant.com, dimitrik.fr@gmail.com, robertmhaas@gmail.com, adaldeia@gmail.com
  Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
  In-Reply-To: <4C716400.5030900@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox