pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
After how many updates should a vacuum be performed?
9+ messages / 2 participants
[nested] [flat]

* After how many updates should a vacuum be performed?
@ 2006-09-13 16:06 Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Ellen Cyran @ 2006-09-13 16:06 UTC (permalink / raw)
  To: pgsql-admin

We have a database that periodically we perform large updates, around
a million records, after that the vacuum will run for 12 hours without
completing.  After that, I typically remove the 2 indexes and 1 
constraint on the largest table, 7 million records, and the vacuum will 
complete in a couple of hours and the indexes can be recreated in a half 
hour.

After how many updates should a vacuum be performed?  Do indexes need to 
be recreated periodically?  Would auto vacuuming help in this case?  Any 
suggestions on tuning?

The database is in PostgreSQL 8.0.7.

Thanks.

Ellen





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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
@ 2006-09-13 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-09-13 16:19 UTC (permalink / raw)
  To: Ellen Cyran <ellen@urban.csuohio.edu>; +Cc: pgsql-admin

Ellen Cyran <ellen@urban.csuohio.edu> writes:
> We have a database that periodically we perform large updates, around
> a million records, after that the vacuum will run for 12 hours without
> completing.  After that, I typically remove the 2 indexes and 1 
> constraint on the largest table, 7 million records, and the vacuum will 
> complete in a couple of hours and the indexes can be recreated in a half 
> hour.

By "vacuum" do you mean VACUUM FULL?  My advice is not to use that,
just plain VACUUM.  Don't forget to make sure your FSM is large enough,
too.

			regards, tom lane



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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-13 16:31   ` Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Ellen Cyran @ 2006-09-13 16:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

No, I mean vacuum analyze.  I'll vacuum verbose and see about adjusting 
the fsm.  Thanks.

Tom Lane wrote:

> Ellen Cyran <ellen@urban.csuohio.edu> writes:
> 
>>We have a database that periodically we perform large updates, around
>>a million records, after that the vacuum will run for 12 hours without
>>completing.  After that, I typically remove the 2 indexes and 1 
>>constraint on the largest table, 7 million records, and the vacuum will 
>>complete in a couple of hours and the indexes can be recreated in a half 
>>hour.
> 
> 
> By "vacuum" do you mean VACUUM FULL?  My advice is not to use that,
> just plain VACUUM.  Don't forget to make sure your FSM is large enough,
> too.
> 
> 			regards, tom lane





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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
@ 2006-09-13 16:54     ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 17:11       ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2006-09-13 16:54 UTC (permalink / raw)
  To: Ellen Cyran <ellen@urban.csuohio.edu>; +Cc: pgsql-admin

Ellen Cyran <ellen@urban.csuohio.edu> writes:
> Tom Lane wrote:
>> By "vacuum" do you mean VACUUM FULL?

> No, I mean vacuum analyze.

Hm, that should be OK.  What do you have maintenance_work_mem set to?

			regards, tom lane



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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-13 17:11       ` Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 17:27         ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Ellen Cyran @ 2006-09-13 17:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

It's set at the default 16384.

Tom Lane wrote:

> Ellen Cyran <ellen@urban.csuohio.edu> writes:
> 
>>Tom Lane wrote:
>>
>>>By "vacuum" do you mean VACUUM FULL?
> 
> 
>>No, I mean vacuum analyze.
> 
> 
> Hm, that should be OK.  What do you have maintenance_work_mem set to?
> 
> 			regards, tom lane





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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 17:11       ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
@ 2006-09-13 17:27         ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 18:07           ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-14 13:17           ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  0 siblings, 2 replies; 9+ messages in thread

From: Tom Lane @ 2006-09-13 17:27 UTC (permalink / raw)
  To: Ellen Cyran <ellen@urban.csuohio.edu>; +Cc: pgsql-admin

Ellen Cyran <ellen@urban.csuohio.edu> writes:
>> Hm, that should be OK.  What do you have maintenance_work_mem set to?

> It's set at the default 16384.

That should be plenty for getting rid of a million or so tuples.  I'm
wondering if you are seeing some weird locking effect.  Is the VACUUM
constantly busy with I/O or does it sit and wait at points?  Do you have
other queries actively accessing the table during the VACUUM?

			regards, tom lane



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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 17:11       ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 17:27         ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-13 18:07           ` Ellen Cyran <ellen@urban.csuohio.edu>
  1 sibling, 0 replies; 9+ messages in thread

From: Ellen Cyran @ 2006-09-13 18:07 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Someone else was doing the vacuum that didn't complete this last time 
and they started it at night so no other queries were running. I wasn't 
monitoring I/O usage at the time and in the past I just always removed 
the indexes and vacuumed when this happened.

This is on a Solaris server, would you suggest any additional commands 
besides iostat to monitor the i/o?

Ellen


Tom Lane wrote:

> Ellen Cyran <ellen@urban.csuohio.edu> writes:
> 
>>>Hm, that should be OK.  What do you have maintenance_work_mem set to?
> 
> 
>>It's set at the default 16384.
> 
> 
> That should be plenty for getting rid of a million or so tuples.  I'm
> wondering if you are seeing some weird locking effect.  Is the VACUUM
> constantly busy with I/O or does it sit and wait at points?  Do you have
> other queries actively accessing the table during the VACUUM?
> 
> 			regards, tom lane





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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 17:11       ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 17:27         ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-14 13:17           ` Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-14 14:11             ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

From: Ellen Cyran @ 2006-09-14 13:17 UTC (permalink / raw)
  To: pgsql-admin

The FSM seems to be large enough.  The verbose indicated
   39 relations, 5090 pages, and 3952 total pages needed.
Allocated FSM size is 1000 relations & 20000 pages = 186KB shared memory.

Also, the vacuum of all the database only took 1 hour and 20 minutes so
is there anything I should look at the next time this happens besides i/o?

Thanks.

Ellen


Tom Lane wrote:

> Ellen Cyran <ellen@urban.csuohio.edu> writes:
> 
>>>Hm, that should be OK.  What do you have maintenance_work_mem set to?
> 
> 
>>It's set at the default 16384.
> 
> 
> That should be plenty for getting rid of a million or so tuples.  I'm
> wondering if you are seeing some weird locking effect.  Is the VACUUM
> constantly busy with I/O or does it sit and wait at points?  Do you have
> other queries actively accessing the table during the VACUUM?
> 
> 			regards, tom lane





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

* Re: After how many updates should a vacuum be performed?
  2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:19 ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 16:31   ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 16:54     ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-13 17:11       ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
  2006-09-13 17:27         ` Re: After how many updates should a vacuum be performed? Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-14 13:17           ` Re: After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
@ 2006-09-14 14:11             ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2006-09-14 14:11 UTC (permalink / raw)
  To: Ellen Cyran <ellen@urban.csuohio.edu>; +Cc: pgsql-admin

Ellen Cyran <ellen@urban.csuohio.edu> writes:
> Also, the vacuum of all the database only took 1 hour and 20 minutes so
> is there anything I should look at the next time this happens besides i/o?

I'm still wondering about locks.  If the VACUUM seems to be just sitting
and not doing I/O, look in the pg_locks view to see if it's blocked on a
lock.

			regards, tom lane



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


end of thread, other threads:[~2006-09-14 14:11 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-13 16:06 After how many updates should a vacuum be performed? Ellen Cyran <ellen@urban.csuohio.edu>
2006-09-13 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 16:31   ` Ellen Cyran <ellen@urban.csuohio.edu>
2006-09-13 16:54     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 17:11       ` Ellen Cyran <ellen@urban.csuohio.edu>
2006-09-13 17:27         ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-13 18:07           ` Ellen Cyran <ellen@urban.csuohio.edu>
2006-09-14 13:17           ` Ellen Cyran <ellen@urban.csuohio.edu>
2006-09-14 14:11             ` Tom Lane <tgl@sss.pgh.pa.us>

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