Обсуждение: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

Поиск
Список
Период
Сортировка

Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

От
Mike White
Дата:
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.  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.  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.  :(

<dbname>=# select * from pg_locks where mode = 'AccessExclusiveLock';
  relation  |  database  | transaction |  pid  |        mode         |
granted
------------+------------+-------------+-------+---------------------+---------
 2434930929 | 2434930890 |             | 18356 | AccessExclusiveLock |
t
 2434930929 | 2434930890 |             | 18356 | AccessExclusiveLock |
t
(2 rows)

miro_stats=# VACUUM FULL VERBOSE tsdb_archive_state;
INFO:  vacuuming "public.tsdb_archive_state"
INFO:  "tsdb_archive_state": found 0 removable, 59138 nonremovable row
versions in 4597419 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 60 to 60 bytes long.
There were 583813020 unused item pointers.
Total free space (including removable row versions) is 35231071156
bytes.
4596598 pages are or will become empty, including 0 at the end of the
table.
4597196 pages containing 35231061344 free bytes are potential move
destinations.
CPU 99.57s/20.04u sec elapsed 3948.67 sec.
INFO:  index "tsdb_archive_state_pkey" now contains 59138 row versions
in 132 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.02 sec.
(Hanging here for about 4 hours.)

Thanks,
Mike White


Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

От
"Scott Marlowe"
Дата:
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.

Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks?

От
Tom Lane
Дата:
Mike White <mikewhite22@yahoo.com> writes:
> 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?

I think it's just the regular and session versions of the lock.  Don't
recall at the moment whether it's easier to tell them apart in newer
releases.

> Also, please don't tell me to upgrade.

You should *definitely* be on 7.4.x where x is considerably greater than
7.  This is a painless update and failing to do it borders on negligence.
Read the release notes at
http://www.postgresql.org/docs/7.4/static/release.html

            regards, tom lane