Re: Some strange issues with VACUUM on one table.

I faced a similar situation sometime back in postgres 7.3.4 and i did
the following steps and got the problem fixed.

1. Increase max_fsm_pages (may need to increase max_fsm_relations also
if you have many objects)
2. Reindex the indexes which have grown very big (find them using
pg_class table)
3 .Perfom a vaccuum full on the database.
4. Restart the postmaster.

http://www.postgresql.org/docs/7.3/static/runtime-config.html
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

On 12/20/05, Boguk Maxim <astar(at)rambler-co(dot)ru> wrote:
> I have one small table (like 35000 records) in my database.
> Table updated once per day (1%-5% records updated 0.1%-2% new records
> inserted).
> (parent table updating at same time... no other updates/deletes on tables
> events and events_extra ever doing).
> For such usage mode i choose 'vacuum analyze' table once per day after
> update is enough for keep table clean and fast.
> After like 3 month use i found table become hella slow and use like 5Gb on
> HDD (and still have only ~35000 records).
> I looked my cron mails about vacuum and found no errors at all.
>
> Then i start research situation:
>
> Here collected data:
> (sorry for long post i tried add all info what i collected):
> No server crashes/reboots last year.
>
> postmaster --version
> postmaster (PostgreSQL) 8.0.3
>
> afisha2=# \d events_extra
> Table "public.events_extra"
> Column | Type | Modifiers
> --------+---------+-----------
> id | integer | not null
> data | text |
> Indexes:
> "events_extra_id" UNIQUE, btree (id)
> Foreign-key constraints:
> "$1" FOREIGN KEY (id) REFERENCES events(id) ON DELETE CASCADE
>
> afisha2=# SELECT avg(bit_length(data)) from events_extra;
> avg
> -----------------------
> 5188.2716594952901500
> (1 row)
>
> afisha2=# select count(*) from events_extra;
> count
> -------
> 34396
> (1 row)
>
> Now i doing usual vacuum analyze events_extra:
>
> afisha2=# VACUUM VERBOSE ANALYZE events_extra;
> INFO: vacuuming "public.events_extra"
> INFO: index "events_extra_id" now contains 35562 row versions in 859 pages
> DETAIL: 121759 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.05s/0.20u sec elapsed 6.33 sec.
> INFO: "events_extra": removed 121759 row versions in 12005 pages
> DETAIL: CPU 0.63s/0.67u sec elapsed 97.62 sec.
> INFO: "events_extra": found 121759 removable, 34575 nonremovable row
> versions in 325150 pages
> DETAIL: 1100 dead row versions cannot be removed yet.
> There were 3475521 unused item pointers.
> 0 pages are entirely empty.
> CPU 7.81s/2.83u sec elapsed 616.91 sec.
> INFO: vacuuming "pg_toast.pg_toast_50664"
> INFO: index "pg_toast_50664_index" now contains 1985 row versions in 1694
> pages
> DETAIL: 35157 index row versions were removed.
> 1627 index pages have been deleted, 1496 are currently reusable.
> CPU 0.04s/0.03u sec elapsed 10.41 sec.
> INFO: "pg_toast_50664": removed 35157 row versions in 6721 pages
> DETAIL: CPU 0.33s/0.36u sec elapsed 55.81 sec.
> INFO: "pg_toast_50664": found 35157 removable, 1752 nonremovable row
> versions in 158420 pages
> DETAIL: 252 dead row versions cannot be removed yet.
> There were 829348 unused item pointers.
> 0 pages are entirely empty.
> CPU 4.04s/1.42u sec elapsed 494.67 sec.
> INFO: analyzing "public.events_extra"
> INFO: "events_extra": scanned 3000 of 325330 pages, containing 349 live
> rows and 7 dead rows; 349 rows in sample, 37847 estimated total rows
> VACUUM
>
> Hella strange DB growth like 100x normal size and still vacuum see '0 pages
> are entirely empty.'
> And table use huge disk space.
>
> but 'vacuum full analyze' help:
>
>
> afisha2=# VACUUM FULL VERBOSE ANALYZE events_extra;
> INFO: vacuuming "public.events_extra"
> INFO: "events_extra": found 4430 removable, 34396 nonremovable row versions
> in 325330 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 56 to 2036 bytes long.
> There were 3595534 unused item pointers.
> Total free space (including removable row versions) is 2623671076 bytes.
> 320774 pages are or will become empty, including 0 at the end of the table.
> 325220 pages containing 2623668204 free bytes are potential move
> destinations.
> CPU 6.11s/1.75u sec elapsed 243.67 sec.
> INFO: index "events_extra_id" now contains 34396 row versions in 859 pages
> DETAIL: 4430 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.07s/0.07u sec elapsed 5.07 sec.
> INFO: "events_extra": moved 26037 row versions, truncated 325330 to 2574
> pages
> DETAIL: CPU 20.88s/20.47u sec elapsed 1873.01 sec.
> INFO: index "events_extra_id" now contains 34396 row versions in 859 pages
> DETAIL: 26037 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.01s/0.08u sec elapsed 0.11 sec.
> INFO: vacuuming "pg_toast.pg_toast_50664"
> INFO: "pg_toast_50664": found 733 removable, 1733 nonremovable row versions
> in 158468 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> Nonremovable row versions range from 43 to 2034 bytes long.
> There were 864070 unused item pointers.
> Total free space (including removable row versions) is 1289157700 bytes.
> 158004 pages are or will become empty, including 0 at the end of the table.
> 158463 pages containing 1289157632 free bytes are potential move
> destinations.
> CPU 3.26s/1.03u sec elapsed 180.28 sec.
> INFO: index "pg_toast_50664_index" now contains 1733 row versions in 861
> pages
> DETAIL: 733 index row versions were removed.
> 796 index pages have been deleted, 796 are currently reusable.
> CPU 0.03s/0.01u sec elapsed 3.73 sec.
> INFO: "pg_toast_50664": moved 1595 row versions, truncated 158468 to 315
> pages
> DETAIL: CPU 8.74s/9.15u sec elapsed 763.01 sec.
> INFO: index "pg_toast_50664_index" now contains 1733 row versions in 861
> pages
> DETAIL: 1595 index row versions were removed.
> 793 index pages have been deleted, 793 are currently reusable.
> CPU 0.01s/0.01u sec elapsed 2.55 sec.
> INFO: analyzing "public.events_extra"
> INFO: "events_extra": scanned 2574 of 2574 pages, containing 34396 live
> rows and 0 dead rows; 3000 rows in sample, 34396 estimated total rows
> VACUUM
>
> Ok here question:
> What i doing wrong? Or better why vacuum analyze dont work?
> Bug? Some unknow for me feature on such tables? FOREIGN KEY issues?
> Parent table 'event' staying clear with vacuum anylyze all time.
>
> PS: sorry for bad english.
>
> astar(at)rambler-co(dot)ru ICQ: 99-312-438
> (910) 405-47-18
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Regards
Pandu