Re: Postgres 8.2 memory weirdness

On Jan 24, 2008 10:49 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> 8.2.1 has a nasty bug related to statistics collection that causes
> performance issues exactly in the kind of heavy update situation you're
> in. That's actually why i asked for the exact 8.2 version. You should
> plan an upgrade as soon as feasible to the current release just to
> eliminate this as a possible influence on your problems. No need to dump
> the database or do anything fancy, just get the new version going and
> point it at the existing database.

Not seeing any excessive cpu from the stats collector process.. So
maybe not being hit with this bug.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ P SWAP
TIME COMMAND
28445 postgres 15 0 7432 828 408 S 0 0.0 4:15.47 3 6604
4:15 postgres: stats collector process

With the above said, we had started sometime ago to move 8.2.5 into
our environments, so that should be on these servers next week (the
push is a slow process, but we are really liking what we are seeing
for 8.3, so I'm hoping once blessed, i'll push it thru quickly)..

> > checkpoint_segments = 50
> > checkpoint_timeout = 300
> > checkpoint_warning = 3600s <--- set this last night and
>
> If you're getting checkpoints every 89 seconds it's no wonder your system
> is dying. You may need to consider a large increase to
> checkpoint_segments to get the interval between checkpoints to increase.
> It should at least be a few minutes between them if you want any
> reasonable performance level.

I doubled the checkpoint segments yesterday and have not seen any
warnings. Will run with segments of 100 for a while and see how things
look.. Anyway to make sure that there is not a number between 50 and
100 that makes more sense?

> > effective_cache_size = 330000 <-- This appears totally wrong and
> > something I noticed last night. left over from previous versions of
> > postgres on different hardware. (thinking to set this to 6-7G)
>
> Right, that's where it should be.

We have started some performance analysis and this numvber is sure
affecting performance in good ways by having it set semi correctly.
This has not been pushed (too many changes), but we will continue
performance testing and it will probably make it to prod next week.

Thanks for some sanity checks here Greg, it's truly appreciated.

Tory