pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAfter 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>
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:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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:31 Ellen Cyran <ellen@urban.csuohio.edu>
parent: 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:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 17:11 Ellen Cyran <ellen@urban.csuohio.edu>
parent: 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 17:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 18:07 Ellen Cyran <ellen@urban.csuohio.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-14 13:17 Ellen Cyran <ellen@urban.csuohio.edu>
parent: 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-14 14:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ellen Cyran <ellen@urban.csuohio.edu>
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