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
