Re: Need to run CLUSTER to keep performance - Mailing list pgsql-performance

Rafael Martinez wrote:
>
> We have more information about this 'problem'.
>

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.
0 pages are entirely empty.
CPU 0.05s/0.03u sec elapsed 0.94 sec.
INFO:  vacuuming "pg_toast.pg_toast_376272"
INFO:  index "pg_toast_376272_index" now contains 260 row versions in 2
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_376272": found 0 removable, 260 nonremovable row
versions in 65 pages
DETAIL:  129 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  analyzing "public.hosts"
INFO:  "hosts": scanned 3848 of 3848 pages, containing 16323 live rows
and 94563 dead rows; 16323 rows in sample, 16323 estimated total rows
VACUUM

###############################################
2007-11-11_0301.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 16782 row versions in 556 pages
DETAIL:  94551 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
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
DETAIL:  372 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.08s/0.16u sec elapsed 590.99 sec.
INFO:  vacuuming "pg_toast.pg_toast_376272"
INFO:  index "pg_toast_376272_index" now contains 131 row versions in 2
pages
DETAIL:  129 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_376272": removed 129 row versions in 33 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 32.05 sec.
INFO:  "pg_toast_376272": found 129 removable, 131 nonremovable row
versions in 65 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 51.96 sec.
INFO:  analyzing "public.hosts"
INFO:  "hosts": scanned 3875 of 3875 pages, containing 16323 live rows
and 576 dead rows; 16323 rows in sample, 16323 estimated total rows
VACUUM

###############################################
2007-11-11_0315.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 17363 row versions in 556 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 1.39 sec.
INFO:  "hosts": found 0 removable, 17362 nonremovable row versions in
3875 pages
DETAIL:  1039 dead row versions cannot be removed yet.
There were 94074 unused item pointers.
0 pages are entirely empty.
CPU 0.02s/0.02u sec elapsed 1.43 sec.
INFO:  vacuuming "pg_toast.pg_toast_376272"
INFO:  index "pg_toast_376272_index" now contains 131 row versions in 2
pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_376272": found 0 removable, 131 nonremovable row
versions in 65 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 129 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.05 sec.
INFO:  analyzing "public.hosts"
INFO:  "hosts": scanned 3875 of 3875 pages, containing 16323 live rows
and 1040 dead rows; 16323 rows in sample, 16323 estimated total rows
VACUUM



After this last job the amount of dead rows just continued growing until
 today.

--
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/