Re: Vacuum taking an age

Hi,
thanks for your reply.
The number of rows per second has been increasing rapidly, but its
averaging about 1 row per second, and a far smaller number of updates.
So maybe there are not such a huge number of dead rows. I hope that a
normal vacuum will clean it up.
Total number of rows is about 3 million.

Last night before I got your reply, I noticed that the number of
shared memory buffers was only 1000, so I increased shmmax and when I
restart the server next, its number of buffers will be 10000. The
server has 8GB of memory, so that will only be a small proportion of
its total memory.
I have not restarted postgres yet because a vacuum is still running.
Maybe I should kill that and restart postgres?

The reason I increased this is because I noticed if I did a partial
count of rows (e.g. those inserted with a timestamp after midnight
last night), then the first time takes about 17 seconds, and the
second time 1/4 second.

I started a vacuum on the table yesterday, and its still running. I
guess thats because the table is live. I am pretty sure that if I take
it offline, then the vacuum will complete relatively quickly. Am I
right? (I don't want to take it offline unless I really need to.)

On 04/01/2008, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Jan 3, 2008 6:48 AM, Brian Modra <epailty(at)googlemail(dot)com> wrote:
> > Hi,
> >
> >
> > I have a pretty "live" table: rows being inserted and updated more
> > than once 1 per second, though far, far more inserts than updates.
> >
> > There are currently over 3 million rows.
> >
> > It has not been vacuumed for months.
>
> How many rows per second? 1? all of them? Kinda makes a difference.
>
> If it was 1 a second updated for 3 months that's about 7million dead
> rows. If it was all 3million, then that's 7million * 3million dead
> rows, also know as a whole bunch of rows.
>
> Either way, you probably have a table so terribly bloated that a
> regular vacuum will not help you in terms of speeding it up. Regular
> vacuums are like brushing your teeth three times a day. If you've
> forgotten for three months, brushing them once isn't likely to fix all
> the cavities you've got. Same thing here. You'll either need a
> vacuum full or a cluster. Cluster is often faster. Or you can try
> selecting everything into a temp table, truncating the real table, and
> inserting the data back in. Truncation will remove all rows, dead or
> otherwise. The advantage is that it's often faster to truncate /
> reload than it is to vacuum full. If you have indexes, you might want
> to drop them while re-inserting and then recreated them.
>

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 183 8059
6 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa