On 8/21/07, Sachchida Ojha <sojha@xxxxxxxxxxxxxxxxxxx> wrote: > Thanks to all of you. I have changed the settings and reloaded the > config. Let me run this system overnight. I will update this forum if > new settings works for me. I am also asking management to upgrade the > hardware. You need to run vacuum verbose on the database (not an individual table) and note the output at the end. It will tell you how bloated your current db is. If vacuums have been delayed for too long, you may need to vacuum full and / or reindex the bloated tables and indexes to reclaim the lost space. Assuming that there's not too much dead space, or that if there is you've used vacuum full / reindexdb to reclaim it, then vacuum running regularly and in the background should fix this issue... The output of vacuum verbose you're looking for is like this: DETAIL: A total of 2096 page slots are in use (including overhead). 2096 page slots are required to track all free space. Current limits are: 20000 page slots, 1000 relations, using 182 KB. If it comes back with some huge number for page slots (like in the millions) needed to track all the dead tuples you'll need that vacuum full / reindex. A certain amount of dead space is ok, even a good thing, since you don't have to extend your table / index files to insert. 10-30% dead space is normal. anything around 100% or heading up from there is bad. You'll also want to look through the rest of the vacuum verbose output for things like this: INFO: vacuuming "abc.zip_test" INFO: index "zip_test_pkey" now contains 1000000 row versions in 3076 pages DETAIL: 8589 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.37s/0.23u sec elapsed 28.23 sec. INFO: "zip_test": removed 8589 row versions in 55 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "zip_test": found 8589 removable, 1000000 nonremovable row versions in 6425 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 1.36s/0.34u sec elapsed 100.52 sec. If the number of rows removed and the pages they held were a large percentage of the table, then you'll likely need to reindex them to get the space back. Or cluster on an index. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
