From: Richard Huxton <dev@archonet.com>
To: Kevin Kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: server performance issues - suggestions for tuning
Date: Tue, 28 Aug 2007 08:50:54 +0100
Message-ID: <46D3D3DE.8050003@archonet.com> (raw)
In-Reply-To: <200708272213.14277.kevin@kevinkempterllc.com>
References: <200708272213.14277.kevin@kevinkempterllc.com>
Kevin Kempter wrote:
> Hi List;
>
> I've just inherited multiple postgres database servers in multiple data
> centers across the US and Europe via a new contract I've just started.
>
> Each night during the nightly batch processing several of the servers (2 in
> particular) slow to a crawl - they are dedicated postgres database servers.
> There is a lot of database activity going on sometimes upwards of 200
> concurrent queries however I just dont think that the machines should be this
> pegged. I am in the process of cleaning up dead space - their #1 fix for
> performance issues in the past is to kill the current vacuum process.
> Likewise I've just bumped shared_buffers to 150000 and work_mem to 250000.
Well, allowing vacuum to do its job can clearly only help matters. I'm
not sure about setting work_mem so high though. That's the memory you're
using per-sort, so you can use multiples of that in a single query. With
200 concurrent queries I'd worry about running into swap. If you're
doing it just for the batch processes that might make sense.
You might well want to set maintenance_work_mem quite high though, for
any overnight maintenance.
A shared_buffers of 1.2GB isn't outrageous, but again with 200 backend
processes you'll want to consider how much memory each process will
consume. It could be that you're better off with a smaller
shared_buffers and relying more on the OS doing its disk caching.
> Even at that I still see slow processing/high system loads at nite.I have
> noticed that killing the current vacuum process (autovacuum is turned on)
> speeds up the entire machine significantly.
If it's disk i/o that's the limiting factor you might want to look at
the "Cost-Based Vacuum Delay" section in the configuration settings.
> The servers are 4-CPU intel boxes (not dual-core) with 4Gig of memory and
> attached to raid-10 array's
>
> Any thoughts on where to start?
Make sure you are gathering stats and at least stats_block_level stuff.
Then have a cron-job make copies of the stats tables, but adding a
timestamp column. That way you can run diffs against different time periods.
Pair this up with top/vmstat/iostat activity.
Use log_min_duration_statement to catch any long-running queries so you
can see if you're getting bad plans that push activity up.
Try and make only one change at a time, otherwise it's difficult to tell
what's helping/hurting.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, kevin@kevinkempterllc.com
Subject: Re: server performance issues - suggestions for tuning
In-Reply-To: <46D3D3DE.8050003@archonet.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