Scott Marlowe wrote:
On Nov 12, 2007 10:11 AM, Rafael Martinez <r.m.guerrero@xxxxxxxxxxx> wrote:
Sending this just in case it can help ....
Checking all the log files from these vacuum jobs we have been running,
we found one that looks difference from the rest, specially on the
amount of removed pages.
We are sending also the output before and after the one we are talking
about:
###############################################
2007-11-11_0245.log
###############################################
COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien
-p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts'
CODE: 0
OUTPUT:
INFO: vacuuming "public.hosts"
INFO: index "hosts_pkey" now contains 110886 row versions in 554 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.00u sec elapsed 0.87 sec.
INFO: "hosts": found 0 removable, 110886 nonremovable row versions in
3848 pages
DETAIL: 94563 dead row versions cannot be removed yet.
There were 0 unused item pointers.
You see that right there? You've got 94k dead rows that cannot be removed.
Then, later on, they can:
CPU 0.04s/0.09u sec elapsed 590.48 sec.
INFO: "hosts": removed 94551 row versions in 3835 pages
DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec.
INFO: "hosts": found 94551 removable, 16695 nonremovable row versions
in 3865 pages
So, between the first and second vacuum you had a long running
transaction that finally ended and let you clean up the dead rows.
No, before 8.3, CLUSTER throws away non-removable dead tuples. So the
long running transaction might still be there.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
