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



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org