VACUUM FULL hangs on ordinary table

Поиск
Список
Период
Сортировка
От Ivan Zolotukhin
Тема VACUUM FULL hangs on ordinary table
Дата
Msg-id 751e56400605190458i232c3010me53f1e8562ed92a6@mail.gmail.com
обсуждение исходный текст
Ответы Re: VACUUM FULL hangs on ordinary table  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: VACUUM FULL hangs on ordinary table  (Joachim Wieland <joe@mcknight.de>)
Список pgsql-general
Hello,

I have a production database which have not been full vacuumed for ~2
months (autovacuum worked fine with default settings all this time).
When I run VACUUM FULL VERBOSE ANALYZE it processes several
tables/indexes and than hangs (at least I tried to wait for 30 mins
and nothing happened) on one particular table "education" which is
quite ordinary I think. When it hangs I see in `ps auxww` process with
"VACUUM waiting" in its status. There are no clients connected to the
database except me in that moment since I switched application to
another DB. pg_locks says that there are several AccessShareLocks and
one RowExclusiveLock but no one related with "education" table (yes,
also how is it possible to have several locks on unused database with
only clean operations and several postmaster stop/starts in the past?
how should I clean them if it is essential?).

I tried dump/restore of this database to the new one -- it works
perfectly now under production load and VACUUM FULL on it runs only 1
minute. I know that proper settings for autovacuum can eliminate need
of VACUUM FULL but anyway it looks somewhat strange, doesn't it?

PostgreSQL version is

PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-52)

Below I pasted last lines concerning above table from VACUUM output
(it stops after the last line):

[...]
INFO:  vacuuming "public.education"
INFO:  "education": found 0 removable, 41764 nonremovable row versions
in 674 pages
DETAIL:  1111 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 1968 bytes long.
There were 458 unused item pointers.
Total free space (including removable row versions) is 38540 bytes.
0 pages are or will become empty, including 0 at the end of the table.
206 pages containing 25740 free bytes are potential move destinations.
CPU 0.04s/0.00u sec elapsed 0.55 sec.
INFO:  index "pk_education" now contains 41764 row versions in 121 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.15 sec.
INFO:  "education": moved 0 row versions, truncated 674 to 674 pages
DETAIL:  CPU 0.00s/0.02u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_1519428"
INFO:  "pg_toast_1519428": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "pg_toast_1519428_index" now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.education"
INFO:  "education": scanned 674 of 674 pages, containing 40653 live
rows and 1111 dead rows; 3000 rows in sample, 40653 estimated total
rows

Hope that someone explains me how's that possible.

Thanks,
Ivan Zolotukhin

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joe Kramer"
Дата:
Сообщение: Re: GUI Interface
Следующее
От: Robert Treat
Дата:
Сообщение: Re: RES: Add column and specify the column position in