pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum full on a big table
8+ messages / 6 participants
[nested] [flat]

* Vacuum full on a big table
@ 2005-03-17 21:10  Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Gaetano Mendola @ 2005-03-17 21:10 UTC (permalink / raw)
  To: pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,
is there a way to vacuum full a table but working only
a part of the table ? I have a table with 6 milion rows
and vacuum full it will send out of line for hours my
server, so I'll like to vacuum that table multiple times
in order to not block that table for a long period.

It will work decresing the FSM settings ?


Regards
Gaetano Mendola



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCOfIs7UpzwH2SGd4RAsXaAKDIgcNZLqsYULjnNVNhTktXvWmJTgCg4zcK
V/gFNRTCu0y99HLbTtGm610=
=0SF3
-----END PGP SIGNATURE-----




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

* Re: Vacuum full on a big table
@ 2005-03-17 21:20  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Scott Marlowe @ 2005-03-17 21:20 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-admin

On Thu, 2005-03-17 at 15:10, Gaetano Mendola wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hi all,
> is there a way to vacuum full a table but working only
> a part of the table ? I have a table with 6 milion rows
> and vacuum full it will send out of line for hours my
> server, so I'll like to vacuum that table multiple times
> in order to not block that table for a long period.
> 
> It will work decresing the FSM settings ?

Vacuum full doesn't use fsm, lazy vacuum does (i.e. plain vacuum).  Is
there a reason you're doing a full vacuum?  Like you forgot to vacuum
for a while and now it needs it?  If not, then a full vacuum is
generally not necessary, and you're better off with regular vacuum and
large enough fsm settings.  Use vacuum analyze to see if you need to
increase them.

and no, you can't vacuum parts at a time.  it's all or nothing. 
(*Unless that changes in 8.0...*)



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

* Re: Vacuum full on a big table
@ 2005-03-17 21:28  Lee Wu <Lwu@mxlogic.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Lee Wu @ 2005-03-17 21:28 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin

I wish.

I am having a table, size of more than 60G, with 2.04412e+08 rows.
Vacuum full and reindex it just kill me.

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gaetano Mendola
Sent: Thursday, March 17, 2005 2:10 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Vacuum full on a big table

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,
is there a way to vacuum full a table but working only
a part of the table ? I have a table with 6 milion rows
and vacuum full it will send out of line for hours my
server, so I'll like to vacuum that table multiple times
in order to not block that table for a long period.

It will work decresing the FSM settings ?


Regards
Gaetano Mendola



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCOfIs7UpzwH2SGd4RAsXaAKDIgcNZLqsYULjnNVNhTktXvWmJTgCg4zcK
V/gFNRTCu0y99HLbTtGm610=
=0SF3
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend



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

* Re: Vacuum full on a big table
@ 2005-03-17 21:53  Gaetano Mendola <mendola@bigfoot.com>
  parent: Scott Marlowe <smarlowe@g2switchworks.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Gaetano Mendola @ 2005-03-17 21:53 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Scott Marlowe wrote:

> 
> Vacuum full doesn't use fsm, lazy vacuum does (i.e. plain vacuum).  

Are you sure? Why then after a vacuum full verbose the FSM settings
are displayed ?



> Is there a reason you're doing a full vacuum? 

Because I'm only running pg_autovacuum since one month now, but I see
that for same table is a disaster do not vacuum full once in a day.


> and no, you can't vacuum parts at a time.  it's all or nothing.
> (*Unless that changes in 8.0...*)

I wish that this happen.


Regards
Gaetano Mendola









-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCOfxN7UpzwH2SGd4RArg0AKDn4hDw6aiaQgHW18xBfUsCNWqurgCgtaVj
fmDWcXtK+kZsrdSbY6rw3LA=
=zZEC
-----END PGP SIGNATURE-----




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

* Re: Vacuum full on a big table
@ 2005-03-17 22:00  Oleg Bartunov <oleg@sai.msu.su>
  parent: Lee Wu <Lwu@mxlogic.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Oleg Bartunov @ 2005-03-17 22:00 UTC (permalink / raw)
  To: Lee Wu <Lwu@mxlogic.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin

On Thu, 17 Mar 2005, Lee Wu wrote:

> I wish.
>
> I am having a table, size of more than 60G, with 2.04412e+08 rows.
> Vacuum full and reindex it just kill me.

In my case ( I have more than 500,000,000 rows) I had to 
'select * into new_big_table from big_table'
it was faster and didn't kill server.
As a bonus, you could 'CLUSTER' your big table if add
'order by somekey';

After that, dont' forget to recreate indices and then you could 
drop big_table and 'alter table new_big_table rename to big_table'.


Oleg


>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gaetano Mendola
> Sent: Thursday, March 17, 2005 2:10 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Vacuum full on a big table
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi all,
> is there a way to vacuum full a table but working only
> a part of the table ? I have a table with 6 milion rows
> and vacuum full it will send out of line for hours my
> server, so I'll like to vacuum that table multiple times
> in order to not block that table for a long period.
>
> It will work decresing the FSM settings ?
>
>
> Regards
> Gaetano Mendola
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.5 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFCOfIs7UpzwH2SGd4RAsXaAKDIgcNZLqsYULjnNVNhTktXvWmJTgCg4zcK
> V/gFNRTCu0y99HLbTtGm610=
> =0SF3
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



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

* Re: Vacuum full on a big table
@ 2005-03-17 22:21  Oleg Bartunov <oleg@sai.msu.su>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  1 sibling, 0 replies; 8+ messages in thread

From: Oleg Bartunov @ 2005-03-17 22:21 UTC (permalink / raw)
  To: Lee Wu <Lwu@mxlogic.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin

Also,

just speculating, is't possible to create new table (select * into) in 
different tablespace if there is no space on disk ?
I didn't find this.

 	Oleg

On Fri, 18 Mar 2005, Oleg Bartunov wrote:

> On Thu, 17 Mar 2005, Lee Wu wrote:
>
>> I wish.
>> 
>> I am having a table, size of more than 60G, with 2.04412e+08 rows.
>> Vacuum full and reindex it just kill me.
>
> In my case ( I have more than 500,000,000 rows) I had to 'select * into 
> new_big_table from big_table'
> it was faster and didn't kill server.
> As a bonus, you could 'CLUSTER' your big table if add
> 'order by somekey';
>
> After that, dont' forget to recreate indices and then you could drop 
> big_table and 'alter table new_big_table rename to big_table'.
>
>
> Oleg
>
>
>> 
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gaetano Mendola
>> Sent: Thursday, March 17, 2005 2:10 PM
>> To: pgsql-admin@postgresql.org
>> Subject: [ADMIN] Vacuum full on a big table
>> 
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>> 
>> Hi all,
>> is there a way to vacuum full a table but working only
>> a part of the table ? I have a table with 6 milion rows
>> and vacuum full it will send out of line for hours my
>> server, so I'll like to vacuum that table multiple times
>> in order to not block that table for a long period.
>> 
>> It will work decresing the FSM settings ?
>> 
>> 
>> Regards
>> Gaetano Mendola
>> 
>> 
>> 
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.2.5 (MingW32)
>> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>> 
>> iD8DBQFCOfIs7UpzwH2SGd4RAsXaAKDIgcNZLqsYULjnNVNhTktXvWmJTgCg4zcK
>> V/gFNRTCu0y99HLbTtGm610=
>> =0SF3
>> -----END PGP SIGNATURE-----
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>> 
>
> 	Regards,
> 		Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83



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

* Re: Vacuum full on a big table
@ 2005-03-17 22:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2005-03-17 22:35 UTC (permalink / raw)
  To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-admin

Gaetano Mendola <mendola@bigfoot.com> writes:
> Scott Marlowe wrote:
>> Is there a reason you're doing a full vacuum? 

> Because I'm only running pg_autovacuum since one month now, but I see
> that for same table is a disaster do not vacuum full once in a day.

You need to find out why regular vacuum isn't getting the job done,
not look for a bigger hammer.  What is growing, exactly --- the table,
its indexes (all of them, or only some), the TOAST table or index?
Do you have adequate FSM space according to what VACUUM VERBOSE says?

It's much more likely that we'd remove VACUUM FULL entirely than do
major work on it (like try to make it work on part of a table, which
is something I don't believe could work anyway).  That entire approach
is fundamentally broken when it comes to major rearrangements of huge
tables: it requires exclusive lock, it takes forever, and it bloats the
table's indexes.

			regards, tom lane



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

* Re: Vacuum full on a big table
@ 2005-03-17 23:47  Steve Crawford <scrawford@pinpointresearch.com>
  parent: Oleg Bartunov <oleg@sai.msu.su>
  1 sibling, 0 replies; 8+ messages in thread

From: Steve Crawford @ 2005-03-17 23:47 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; Lee Wu <Lwu@mxlogic.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin

> In my case ( I have more than 500,000,000 rows) I had to
> 'select * into new_big_table from big_table'
> it was faster and didn't kill server.
> As a bonus, you could 'CLUSTER' your big table if add
> 'order by somekey';
>
> After that, dont' forget to recreate indices and then you could
> drop big_table and 'alter table new_big_table rename to big_table'.

That's OK under certain circumstances. If you have lots of 
dependencies (views, rules, triggers, etc.) it can turn into a royal 
pain.

For example if you have big_table_view which refers to big_table you 
might expect to be able to:
alter table big_table rename to big_table_old;
create table big_table as select * from big_table_old;
drop table big_table_old;

But you would be wrong. Instead you will find that big_table_view now 
refers to big_table_old so you can't drop the old table till you drop 
the view. You will then have to recreate the view. Of course there 
can be numerous views referring to the table and other views 
referring to some of those views and pretty soon the whole thing can 
become a terrible mess.

Cheers,
Steve




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


end of thread, other threads:[~2005-03-17 23:47 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-17 21:10 Vacuum full on a big table Gaetano Mendola <mendola@bigfoot.com>
2005-03-17 21:20 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-03-17 21:53   ` Gaetano Mendola <mendola@bigfoot.com>
2005-03-17 22:35     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-17 21:28 Re: Vacuum full on a big table Lee Wu <Lwu@mxlogic.com>
2005-03-17 22:00 ` Oleg Bartunov <oleg@sai.msu.su>
2005-03-17 22:21   ` Oleg Bartunov <oleg@sai.msu.su>
2005-03-17 23:47   ` Steve Crawford <scrawford@pinpointresearch.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