Thread: Some strange issues with VACUUM on one table.

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@rambler-co.ru   ICQ: 99-312-438
(910) 405-47-18

But why i should 'vacuum full' each day? All what i read suggest use vacuum
analyze once per day for such tables.

Again: i doing vacuum analyze each time after update table (once per 24
hour) (all other time table is read only really)
(and i see no reason for vacuum the table more frequently if i doing only
one package update per 24 hour)

I trying understand why vacuum analyze do not work at all. Somehow vacuum
without full see no free pages even if only 1% of pages really used in file.


-----Original Message-----
From: Gourish Singbal [mailto:gourish@gmail.com]
Sent: Tuesday, December 20, 2005 3:52 PM
To: Boguk Maxim
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Some strange issues with VACUUM on one table.



The table events_extra needs to be full vacuumed to free the disk space with
respect to this table and vacuum the table more frequently .

You need to reindex the table once in a week ... (it depends on growth of
the index size )



 12/20/05, Boguk Maxim <astar@rambler-co.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@rambler-co.ru   ICQ: 99-312-438
(910) 405-47-18


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster




--
Best,
Gourish Singbal

Boguk Maxim <astar@rambler-co.ru> writes:
> I trying understand why vacuum analyze do not work at all.

I think either you're not actually applying vacuum analyze at all
(better double-check your daily maintenance script), or you're somehow
managing to apply it at a time when it can't remove old rows because
there's still an open transaction that might be able to see those rows.
The latter theory doesn't really explain continuing table growth over
time, though, unless you've got a transaction that's been sitting open
the entire time.

            regards, tom lane

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


--
Regards
Pandu