hi, i have a postgresql-8.2.4 db, and vacuuming it does not remove the dead rows basically, the problem is this part of the vacuum-output: " HINT: Close open transactions soon to avoid wraparound problems. INFO: vacuuming "public.sessions" INFO: scanned index "sessions_pkey" to remove 2 row versions DETAIL: CPU 0.60s/0.25u sec elapsed 61.57 sec. INFO: "sessions": removed 2 row versions in 2 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "sessions_pkey" now contains 6157654 row versions in 52923 pages DETAIL: 0 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: "sessions": found 2 removable, 6157654 nonremovable row versions in 478069 pages DETAIL: 6155746 dead row versions cannot be removed yet. There were 8735 unused item pointers. 107 pages contain useful free space. 0 pages are entirely empty. CPU 6.02s/1.58u sec elapsed 598.05 sec. WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. INFO: vacuuming "pg_toast.pg_toast_5525738" INFO: index "pg_toast_5525738_index" now contains 13957669 row versions in 38328 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.40s/0.04u sec elapsed 22.26 sec. INFO: "pg_toast_5525738": found 0 removable, 13957669 nonremovable row versions in 3461686 pages DETAIL: 13938280 dead row versions cannot be removed yet. There were 154 unused item pointers. 69 pages contain useful free space. 0 pages are entirely empty. CPU 39.95s/6.19u sec elapsed 1139.50 sec. INFO: analyzing "public.sessions" INFO: "sessions": scanned 3000 of 478438 pages, containing 12 live rows and 38419 dead rows; 12 rows in sample, 1914 estimated total rows INFO: free space map contains 26849 pages in 444 relations DETAIL: A total of 30736 page slots are in use (including overhead). 30736 page slots are required to track all free space. Current limits are: 153600 page slots, 1000 relations, using 1005 kB. " (the full vacuum-log is at http://www.nekomancer.net/tmp/vacuum.txt) the "sessions" table hold session-data for a web-application (the code uses the perl Apache::Session module btw.), so it changes very often, and is vacuumed every hour (using a cronjob). previously we were running this application with postgresql-7.4, and there the vacuuming worked fine. now we migrated this to postgresql-8.2.4, and it does not want to vacuum it properly. the migration to 8.2.4 happened approx. one month ago, and this dead-row count has been growing since then. what i tried: ps aux | grep postgres on the db-server, and found some connections that were quite old. i restarted the applications that "caused" those connections, so right now there are no too old connections. pg_stat_activity: the query_start of every entry is on today, for the entries with null query_start the postgres processes are not older than 2 days. on the db-server, 4 postgres processes are "idle in transaction", but none is older than 2 days. in pg_locks, all the locks that are for the "sessions" table are from "young" (today-created) connections, and their locks are RowShareLock or AccessShareLock. so currently i am out of ideas what to check... well, actually there is one more idea: maybe the autovacuuming process somehow "conflicts" with the manual-vacuuming cronjob? is that possible? any other ideas? thanks, gabor ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
