Re: VACUUM ANALYSE...

"Thilo Hille" <thilo(at)resourcery(dot)de> writes:
>> BTW, what *is* the amount of RAM in the box? I'm eyeing the
>> shared_buffers setting with suspicion. It may be too high.
>> 500Mb in shared buffers would very likely be more usefully spent
>> elsewhere.

> Amount of RAM is 1GB. At the moment postgres runs nearly alone on the
> machine.
> I believed it would be a good idea to set the shared buffers as high as
> possible....
> i lowered them to 50000.

Nope. There is a faction that thinks shared buffers should be as high
as possible, and there is another faction that favors keeping them
relatively small (I belong to the latter camp). But both factions agree
that shared buffers near 50% of physical RAM is the worst scenario.
When you do that, what really happens is that most disk pages end up
being buffered twice: once in Postgres shared buffers and once in kernel
disk cache. That's just wasting RAM. You either give shared buffers
the bulk of RAM (and squeeze out kernel caching) or trim them down and
rely on the kernel to do most of the disk caching.

I'd cut shared_buffers to 10000, or maybe even less. I think you get
to the point of diminishing returns with more than a few thousand of 'em.
IMHO it's better to give the kernel the flexibility of assigning memory
to processes or kernel disk cache as needed. You'll cope better with
load spikes if the kernel has memory to spare. I suspect part of the
reason your performance is going into the ground is the kernel is being
forced to resort to swapping (you could check this with iostat or vmstat).

> # VACUUM VERBOSE user_log;
> NOTICE: --Relation user_log--
> NOTICE: Pages 232907: Changed 0, Empty 0; Tup 2841267: Vac 0, Keep 0,
> UnUsed 18478387

That doesn't seem too horrible: roughly 12 tuples per page. However, if
they're short rows then maybe there is a lot of free space there.

> # VACUUM VERBOSE fullstatistic;
> NOTICE: --Relation fullstatistic--
> NOTICE: Pages 118815: Changed 895, Empty 0; Tup 90646: Vac 87611, Keep 167,
> UnUsed 8777533.

Here you are hurting: less than one tuple per page. This table
desperately needs a VACUUM FULL.

> Could you be more specific about the term 'painful'? *fear!*
> VACUUM FULL will completely lock the tables?

Yes, it will.

Now, if you expect the amount of stored data to grow over time, maybe
you could just sit tight and wait for the tables to fill up. But if you
want to reduce the amount of disk space occupied today, you need a
VACUUM FULL.

regards, tom lane