From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mike White <mikewhite22@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?
Date: Fri, 4 Jan 2008 10:56:43 -0600
Message-ID: <dcc563d10801040856o55047d3tff0df911e491c83f@mail.gmail.com> (raw)
In-Reply-To: <715193.26887.qm@web34711.mail.mud.yahoo.com>
References: <715193.26887.qm@web34711.mail.mud.yahoo.com>
On Jan 3, 2008 5:12 PM, Mike White <mikewhite22@yahoo.com> wrote:
> I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge
> at any given time, but which has a lot of churn. Just the kind of
> usage pattern that 7.4 loves. :) This is something that we have to do
> every 6 months or so, and it always takes a long time.
I wonder why you have to do it every 6 months... Is this one of those
things where slowly the table bloats because fsm is too small or
vacuum is too infrequent? Or is this a case of some kind of
fragmentation? I've got plenty of heavily updated tables that USED to
live in a 7.4 and I never needed to run vacuum full on it, because
autovacuum (with slightly more aggressive settings than default) was
plenty on that db to keep the table clean. So I'm wondering if
there's some preventative maintenance that would stop you needing to
do this.
> Today, however,
> I've noticed that the pg_lock table shows two AccessExclusiveLock
> entries that are completely identical. Is this a sign of internal
> deadlock, or is this behavior expected during VACUUM FULL operation?
> Also, please don't tell me to upgrade.
OK. I will tell you to update. 7.4.7 had many data eating bugs that
were fixed by 7.4.18.
> We're happily running 8.2 on
> some production machines, but I am tied to 7.4 on this particular box
> for reasons beyond my control. :(
Been there, done that, got the t-shirt :-(
>
> <dbname>=# select * from pg_locks where mode = 'AccessExclusiveLock';
> relation | database | transaction | pid | mode |
> granted
> ------------+------------+-------------+-------+---------------------+---------
> 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t
> 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock |
> t
I'm guessing that the richer view that exists in 8.2 might show
differences, but the simpler view in 7.4 doesn't. You might try
adapting parts of the 8.2 view into 7.4 and seeing if that helps.
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-admin@postgresql.org
Cc: scott.marlowe@gmail.com, mikewhite22@yahoo.com
Subject: Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?
In-Reply-To: <dcc563d10801040856o55047d3tff0df911e491c83f@mail.gmail.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