Re: VACUUM problems with 7.4 - Mailing list pgsql-performance

> Rajesh Kumar Mallah <mallah@trade-india.com> writes:
>> I am sure there is no transaction open with the table banner_stats2. Still VACUUM FULL does
>> not  seems to effective in removing the
>> dead rows.
>
> That is not the issue --- the limiting factor is what is your oldest open transaction, period.
> Whether it has yet looked at this table is not relevant, because the system has no way to know
> whether it might decide to do so later.


Ok , shutting down the database and vacumming immediatly after starting
helped .

But it was not this  bad in 7.3 as far as i understand. Is it something that
has come up in 7.4 only , if so any solution to this issue?

BTW can you please tell me if its safe to upgrade from  RC2 to 7.4 final
without initdb? [ i am still on RC2 :(  ]


Regds
Mallah.





AFTER RESTARTING DATABASE:

tradein_clients=# VACUUM FULL verbose banner_stats2 ;
INFO:  vacuuming "public.banner_stats2"
INFO:  "banner_stats2": found 737900 removable, 4012 nonremovable row versions in 6710 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 61 to 72 bytes long.
There were 120 unused item pointers.
Total free space (including removable row versions) is 51579272 bytes.
6387 pages are or will become empty, including 0 at the end of the table.
6686 pages containing 51578312 free bytes are potential move destinations.
CPU 0.17s/0.09u sec elapsed 0.26 sec.
INFO:  index "banner_stats_pkey" now contains 4012 row versions in 2165 pages
DETAIL:  737900 index row versions were removed.
1813 index pages have been deleted, 1813 are currently reusable.
CPU 0.16s/1.58u sec elapsed 1.97 sec.
INFO:  "banner_stats2": moved 785 row versions, truncated 6710 to 38 pages
DETAIL:  CPU 0.17s/0.54u sec elapsed 8.30 sec.
INFO:  index "banner_stats_pkey" now contains 4012 row versions in 2165 pages
DETAIL:  785 index row versions were removed.
1821 index pages have been deleted, 1821 are currently reusable.
CPU 0.00s/0.02u sec elapsed 0.50 sec.
VACUUM
tradein_clients=#



tradein_clients=# VACUUM FULL verbose banner_stats2 ;
INFO:  vacuuming "public.banner_stats2"
INFO:  "banner_stats2": found 0 removable, 4012 nonremovable row versions in 38 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 61 to 72 bytes long.
There were 100 unused item pointers.
Total free space (including removable row versions) is 7368 bytes.
0 pages are or will become empty, including 0 at the end of the table.
2 pages containing 5984 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index "banner_stats_pkey" now contains 4012 row versions in 2165 pages
DETAIL:  0 index row versions were removed.
1821 index pages have been deleted, 1821 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "banner_stats2": moved 0 row versions, truncated 38 to 38 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
tradein_clients=#



>
>             regards, tom lane
>
> ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off
> all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/