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@xxxxxxxxxxx> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
