Re: server performance issues - suggestions for tuning

On 8/27/07, Kevin Kempter <kevin(at)kevinkempterllc(dot)com> 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.

What pg version are you working with, and on what OS / OS version?

> 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.

way too big for work_mem as mentioned before. Set it to something
reasonable, like 8M or so. Then, if you've got one query that really
needs lots of memory to run well, you can set it higher for that
connection / query only. You can even set work_mem to a particular
number for a particular user with alter user command.

Oh, and 200 concurrent queries is a LOT.

> 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.

> The servers are 4-CPU intel boxes (not dual-core) with 4Gig of memory and
> attached to raid-10 array's

It sounds to me like your systems are I/O bound, at least when vacuum
is running. If you want to get good performance and have vacuum run
in a reasonable amount of time, you might need to upgrade your RAID
subsystems. Do you have battery backed caching controllers? Which
exact model controller are you using? How many drives in your RAID10
array? What types of queries are typical (OLAP versus OLTP really)?

> Any thoughts on where to start?

The vacuum cost settings to reduce the impact vacuum has.

Increasing fsm settings as needed.

Vacuum verbose to see if you've blown out your fsm settings and to see
what fsm settings you might need.

reindexing particularly bloated tables / indexes.

hardware upgrades if needed.