agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Stefano Nichele <stefano.nichele@gmail.com>
To: pgsql-admin@postgresql.org
Subject: vacuum question
Date: Thu, 22 Jan 2009 15:45:31 +0100
Message-ID: <4978868B.6090700@gmail.com> (raw)
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
view thread (11+ messages) latest in thread
Message-ID: <4978868B.6090700@gmail.com>
Permalink: ../4978868B.6090700@gmail.com/
Also on: postgresql.org/message-id/4978868B.6090700@gmail.com
reply
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: stefano.nichele@gmail.com
Subject: Re: vacuum question
In-Reply-To: <4978868B.6090700@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox