pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACUUM problems with 7.4
4+ messages / 4 participants
[nested] [flat]

* VACUUM problems with 7.4
@ 2003-11-24 17:13 Rajesh Kumar Mallah <mallah@trade-india.com>
  2003-11-24 17:44 ` Re: VACUUM problems with 7.4 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Rajesh Kumar Mallah @ 2003-11-24 17:13 UTC (permalink / raw)
  To: pgsql-performance


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=#




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM problems with 7.4
  2003-11-24 17:13 VACUUM problems with 7.4 Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-11-24 17:44 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-24 18:04   ` Re: VACUUM problems with 7.4 mallah@trade-india.com
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2003-11-24 17:44 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: 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.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM problems with 7.4
  2003-11-24 17:13 VACUUM problems with 7.4 Rajesh Kumar Mallah <mallah@trade-india.com>
  2003-11-24 17:44 ` Re: VACUUM problems with 7.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-11-24 18:04   ` mallah@trade-india.com
  2003-11-25 02:27     ` Re: VACUUM problems with 7.4 Neil Conway <neilc@samurai.com>
  0 siblings, 1 reply; 4+ messages in thread

From: mallah@trade-india.com @ 2003-11-24 18:04 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: 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/





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: VACUUM problems with 7.4
  2003-11-24 17:13 VACUUM problems with 7.4 Rajesh Kumar Mallah <mallah@trade-india.com>
  2003-11-24 17:44 ` Re: VACUUM problems with 7.4 Tom Lane <tgl@sss.pgh.pa.us>
  2003-11-24 18:04   ` Re: VACUUM problems with 7.4 mallah@trade-india.com
@ 2003-11-25 02:27     ` Neil Conway <neilc@samurai.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Neil Conway @ 2003-11-25 02:27 UTC (permalink / raw)
  To: mallah@trade-india.com; +Cc: tgl@sss.pgh.pa.us; pgsql-performance

<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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-11-25 02:27 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-24 17:13 VACUUM problems with 7.4 Rajesh Kumar Mallah <mallah@trade-india.com>
2003-11-24 17:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-11-24 18:04   ` mallah@trade-india.com
2003-11-25 02:27     ` Neil Conway <neilc@samurai.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox