vacuum question

From: Stefano Nichele <stefano(dot)nichele(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: vacuum question
Date: 2009-01-22 14:45:31
Message-ID: 4978868B.6090700@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi All,
I would like to ask your help in understanding vacuum activities.
I have a heavily-updated table with this structure:

colA bigint not null
colB character varying(128) not null
colC character varying(200) not null
colD character varying(200) not null
colE character varying(20)

Indexes:
"pk_tableA" PRIMARY KEY, btree (colA, colB, colC, colD)

Additional info:
pg_relation_size= 3453 MB
pg_total_relation_size= 7596 MB

I ran vacuum full verbose and that was the output:
----------------------------------------------------------------------------------------------------------
INFO: vacuuming "public.tableA"
INFO: "tableA": found 1284708 removable, 28569007 nonremovable row
versions in 441967 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 59 to 361 bytes long.
There were 8024509 unused item pointers.
Total free space (including removable row versions) is 466596580 bytes.
21297 pages are or will become empty, including 0 at the end of the table.
326363 pages containing 463153220 free bytes are potential move
destinations.
CPU 6.35s/2.90u sec elapsed 78.09 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 530257 pages
DETAIL: 328056 index row versions were removed.
19516 index pages have been deleted, 19516 are currently reusable.
CPU 7.45s/12.99u sec elapsed 108.42 sec.
INFO: "tableA": moved 1739094 row versions, truncated 441967 to 383291
pages
DETAIL: CPU 38.12s/958.74u sec elapsed 2414.47 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 532924 pages
DETAIL: 1739094 index row versions were removed.
13949 index pages have been deleted, 13949 are currently reusable.
CPU 8.46s/4.97u sec elapsed 132.59 sec.
----------------------------------------------------------------------------------------------------------

Then I still ran vacuum full and it was the output:
----------------------------------------------------------------------------------------------------------
INFO: vacuuming "public.tableA"
INFO: "tableA": found 0 removable, 28569007 nonremovable row versions
in 383291 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 59 to 361 bytes long.
There were 3496277 unused item pointers.
Total free space (including removable row versions) is 10348068 bytes.
0 pages are or will become empty, including 0 at the end of the table.
28326 pages containing 1795632 free bytes are potential move destinations.
CPU 5.05s/1.99u sec elapsed 53.20 sec.
INFO: index "pk_tableA" now contains 28569007 row versions in 532924 pages
DETAIL: 0 index row versions were removed.
13949 index pages have been deleted, 13949 are currently reusable.
CPU 7.00s/10.82u sec elapsed 91.17 sec.
INFO: "tableA": moved 0 row versions, truncated 383291 to 383291 pages
DETAIL: CPU 7.50s/10.93u sec elapsed 198.59 sec.
----------------------------------------------------------------------------------------------------------

At the end:
pg_relation_size= 3010 MB
pg_total_relation_size= 7173 MB

I saw that the second time it says:
There were 3496277 unused item pointers

Is this correct ? Should it not be 0 the second time ?
Do you see anything strange in the output ? (of course I'm asking since
i have a lot of queries that go in timeout)

Do you know any useful documentation that could help me in understanding
all those numbers ?

Cheers and thanks in advance.
ste

Browse pgsql-admin by date

  From Date Subject
Next Message Jessica Richard 2009-01-22 14:50:44 Postgres 8.3.x upgrade
Previous Message Grzegorz Jaśkiewicz 2009-01-22 13:19:16 Re: [GENERAL] bytea size limit?