On Thu, 7 Aug 2003, Yaroslav Mazurak wrote:
> Hi All!
>
> Shridhar Daithankar wrote:
>
> > On 7 Aug 2003 at 10:05, Yaroslav Mazurak wrote:
>
> >>>It needs to reflect how much cache the system is using - try the "free"
> >>>command to see figures.
>
> >> I'm not found "free" utility on FreeBSD 4.7. :(
>
> > <rant>
> > Grr.. I don't like freeBSD for it's top output.Active/inactive/Wired.. Grr..
> > why can't it be shared buffered and cached? Same goes for HP-UX top. Looking at
> > it one gets hardly any real information.. Anyway that's just me..
> > </rant>
>
> Grr... I don't like PostgreSQL for it's memory usage parameters. In
> Sybase ASA, I say for example: "use 64Mb RAM for cache". I don't worry
> about data in this cache - this may be queries, sort areas, results etc.
> I think that server know better about it's memory requirements. I know
> that Sybase *use*, and use *only this* memory and don't trap with
> "Memory exhausted" error.
> I'm not remember 700 minutes queries (more complex that my query),
> following with "memory exhausted" error, on Sybase.
> Advertising, he? :(
>
> > Top on freeBSD seems pretty unintuituive em but if you find any documentation
> > on that, that would help you. (Haven't booted in freeBSD in ages so no data
> > out of my head..)
>
> > You can try various sysctls on freeBSD. Basicalyl idea is to find out how much
> > of memory is used and how much is cached. FreeBSD must be providing that one in
> > some form..
>
> > IIRC there is a limit on filesystem cache on freeBSD. 300MB by default. If that
> > is the case, you might have to raise it to make effective_cache_size really
> > effective..
>
> "Try various sysctls" says nothing for me. I want use *all available
> RAM* (of course, without needed for OS use) for PostgreSQL.
That's a nice theory, but it doesn't work out that way. About every two
months someone shows up wanting postgresql to use all the memory in their
box for caching and we wind up explaining that the kernel is better at
caching than postgresql is, and how it's better not to push the usage of
the memory right up to the limit.
The reason you don't want to use every bit for postgresql is that, if you
use add load after that you may make the machine start to swap out and
slow down considerably.
My guess is that this is exactly what's happening to you, you're using so
much memory that the machine is running out and slowing down.
Drop shared_buffers to 1000 to 4000, sort_mem to 8192 and start over from
there. Then, increase them each one at a time until there's no increase
in speed, or stop if it starts getting slower and back off.
bigger is NOT always better.
