Re: transaction timeout - Mailing list pgsql-general

On Wed, 2005-07-27 at 10:31, Dr NoName wrote:
> > Sure.  Like this:
> >
> > Client A accesses table T, and "hangs."
> > Client B attempts to get an ACCESS EXCLUSIVE lock on
> > table T in
> > preparation for VACUUM FULL.
> > Client C connects to the database and waits for
> > client B to get and
> > release his lock on table T.
> > Client D connects to the database and waits for
> > client B to get and
> > release his lock on table T.
> > Client E connects to the database and waits for
> > client B to get and
> > release his lock on table T.
> > etc...
>
> oh! my! gawd!
> Finally a clear explanation that makes perfect sense.
> Now why did it take so long?

Because your initial definition of the problem kinda led us all in the
wrong direction for 24 hours?  :)  Remember, it took like three times of
folks asking "what's happening that locks your database" before the
vacuum full issue came up.  From there, 24 more hours.  Actually not
bad.

And don't forget, the docs on vacuum pretty clearly state:

"The second form is the VACUUM FULL command. This uses a more aggressive
algorithm for reclaiming the space consumed by expired row versions. Any
space that is freed by VACUUM FULL is immediately returned to the
operating system. Unfortunately, this variant of the VACUUM command
acquires an exclusive lock on each table while VACUUM FULL is processing
it. Therefore, frequently using VACUUM FULL can have an extremely
negative effect on the performance of concurrent database queries."

And then later on:

"VACUUM FULL is recommended for cases where you know you have deleted
the majority of rows in a table, so that the steady-state size of the
table can be shrunk substantially with VACUUM FULL's more aggressive
approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for
space recovery."

So, daily vacuum fulls are not recommended.

> So all I need to do is take out the FULL? Is regular
> VACUUM sufficient? How often do we need FULL? (I know
> it's a stupid question without providing some more
> context, but how can I estimate it?)

Please read up on vacuuming in the docs, at:

http://www.postgresql.org/docs/8.0/static/maintenance.html#ROUTINE-VACUUMING

It's quite enlightening about this.  Basically, assuming your fsm
settings are high enough for your update/delete load, yes, plain vacuums
should be enough.

>
> I suppose the ultimate solution would be a wrapper
> script that works as follows:
>
> check if there are any waiting/idle in transaction
> processes
> if such processes exist, do a regular VACUUM and send
> out a warning email
> otherwise, do VACUUM FULL.

Nah, that's probably overkill.  I'd rather just run plain vacuum
verboses and check them by hand once a week or so to make sure I'm
reclaiming all the space.

> I like this solution a lot more than getting support
> calls on weekends.

Amen brother, amen...

> Out of curiousity, how is lock acquisition implemented
> in postgresql? All the processes have to go through
> some sort of queue, so that locks are granted in FIFO
> order, as you described. Just trying to understand it
> better.

See here:

http://www.postgresql.org/docs/8.0/static/mvcc.html

PostgreSQL's locking system is quite impression.  I kinda giggle when
someone says "Well, not MySQL has feature Y, so why bother with
PostgreSQL?" It's pretty obvious they haven't really read up on pgsql
when they say things like that.