Обсуждение: 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-----
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...*)
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
-----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-----
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
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
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
> 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