Re: postgreSQL performance 8.2.6 vs 8.3.3

On Mon, Feb 23, 2009 at 2:02 PM, <david(at)lang(dot)hm> wrote:
> On Fri, 20 Feb 2009, David Rees wrote:
>
>> On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage <battlemage(at)gmail(dot)com> wrote:
>>>
>>> The amount of tps almost doubled, which is good, but i'm worried about
>>> the
>>> load. For my application, a load increase is bad and I'd like to keep it
>>> just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters
>>> should I work with to decrease the resulting load average at the expense
>>> of
>>> tps?
>>
>> Why is it bad? High load can mean a number of things.
>>
>> The only way to reduce the load is to get the client to submit
>> requests slower. I don't think you'll be successful in tuning the
>> database to run slower. I think you're headed in the wrong direction.
>
> note that on linux the loadave includes processes that are stalled waiting
> for I/O to complete. as a result loadave isn't the entire picture. you need
> to also look to see what the cpu idle time looks like.
>
> that being said, I am generally very happy with loadave <= # cores and
> consider loadave <= 2x # cores to be acceptable
>
> it's nowhere near perfect, but it seems to serve me well as a rule of thumb.

And it's very dependent on type of load. For our primary customer
data database a load of 80 to 120 is not uncommon during certain
operations (like adding a slave back to the fark and it gets a ton of
requests while it's loading up its cache) and it stays responsive.
OTOH, a load of 20 on a reporting server doing tons of sequential
scans and allocating a lot of memory is way overloaded for the same
server type.

I had responsive behaviour into the 300 or 400 load range running
pgbench in "destroy all servers mode (-c 500 -t 10000000 or something
like that) on that machine. Sure, it wasn't exactly peppy or
anything, but most small queries were still running in well under a
second.