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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx ICQ: 99-312-438 > (910) 405-47-18 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Regards Pandu
