Обсуждение: After how many updates should a vacuum be performed?
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
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
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
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
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
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
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
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
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