Thread: VACUUM problems with 7.4

VACUUM problems with 7.4

From
Rajesh Kumar Mallah
Date:
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.

Can any one please help?

Regds
mallah

tradein_clients=# VACUUM FULL verbose banner_stats2 ;
INFO:  vacuuming "public.banner_stats2"
INFO:  "banner_stats2": found 0 removable, 741912 nonremovable row versions in
6710 pages
DETAIL:  737900 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 246672 bytes.
0 pages are or will become empty, including 0 at the end of the table.
557 pages containing 61344 free bytes are potential move destinations.
CPU 0.15s/1.23u sec elapsed 1.38 sec.
INFO:  index "banner_stats_pkey" now contains 741912 row versions in 2173
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.05u sec elapsed 0.09 sec.
INFO:  "banner_stats2": moved 0 row versions, truncated 6710 to 6710 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
tradein_clients=#


Re: VACUUM problems with 7.4

From
Tom Lane
Date:
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.

            regards, tom lane

Re: VACUUM problems with 7.4

From
Date:
> 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/



Re: VACUUM problems with 7.4

From
Neil Conway
Date:
<mallah@trade-india.com> writes:
> But it was not this bad in 7.3 as far as i understand.

No, I believe this behavior is present in any recent release of
PostgreSQL.

-Neil