From: Tory M Blue <tmblue@gmail.com>
To: Greg Smith <gsmith@gregsmith.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres 8.2 memory weirdness
Date: Fri, 25 Jan 2008 09:36:18 -0800
Message-ID: <8a547c840801250936w59aad548w268f24a5fcc89ccf@mail.gmail.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0801241335180.7217@westnet.com>
References: <8a547c840801231023q55796128nb2e88b8c8cfe86e@mail.gmail.com>
<Pine.GSO.4.64.0801231923420.12679@westnet.com>
<8a547c840801240946n37c7fad6ycf62187f82b2027c@mail.gmail.com>
<Pine.GSO.4.64.0801241335180.7217@westnet.com>
On Jan 24, 2008 10:49 AM, Greg Smith <gsmith@gregsmith.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tmblue@gmail.com, gsmith@gregsmith.com
Subject: Re: Postgres 8.2 memory weirdness
In-Reply-To: <8a547c840801250936w59aad548w268f24a5fcc89ccf@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox