Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A1FFC633BB3 for ; Mon, 11 Jan 2010 14:26:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18340-01-6 for ; Mon, 11 Jan 2010 18:26:28 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f219.google.com (mail-ew0-f219.google.com [209.85.219.219]) by mail.postgresql.org (Postfix) with ESMTP id EFEEF634F96 for ; Mon, 11 Jan 2010 13:54:49 -0400 (AST) Received: by ewy19 with SMTP id 19so14858063ewy.21 for ; Mon, 11 Jan 2010 09:54:48 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=Y6IZdYoMX9GdgOjrhwvFvjDkKlOTpmdQ03KXfuZoA3M=; b=n6aWs0cmR/Hs1ZkBE4tSwhpSIalzRBXhLQck29OZWqyjA0mGFl/s4lJCG+BEb0MmG7 R9o5/QmUgOss7U/2xcFzDJJvCEWieJXfTeegHu0KD/8w5j04g7ki5rb69EPDBPkKmpMH ZbCtNfo2ldGfkFIcIVhzd0zNl9yLmr1CHogrI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=nkZmZf9vjyXAdkdunP15nw1KJmT2D8JTUKsddeJz2GSnDU0o1ue12Ahgk9uyb1wdJp JJB9Wef/ldB59W88wr8sTFqGkCRLD28TteGfysep48ivXJ0ckYzrevHqZUZgnuXnyAMD SdLMF4AICNpWR095/7mNkgvo87S7sZECJGolA= MIME-Version: 1.0 Received: by 10.216.87.67 with SMTP id x45mr847753wee.18.1263232488310; Mon, 11 Jan 2010 09:54:48 -0800 (PST) In-Reply-To: <4B4B08E1020000250002E185@gw.wicourts.gov> References: <61039b861001110544o2c352763q13dcf9d78a156714@mail.gmail.com> <61039b861001110842g15bb1f26jc50b6e6762e01427@mail.gmail.com> <4B4B08E1020000250002E185@gw.wicourts.gov> Date: Mon, 11 Jan 2010 12:54:48 -0500 Message-ID: <61039b861001110954j67bd0efex500a91e7c2b0fd74@mail.gmail.com> Subject: Re: performance config help From: Bob Dusek To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d99eadbc064c047ce73b5e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/200 X-Sequence-Number: 37080 --0016e6d99eadbc064c047ce73b5e Content-Type: text/plain; charset=ISO-8859-1 On Mon, Jan 11, 2010 at 12:17 PM, Kevin Grittner < Kevin.Grittner@wicourts.gov> wrote: > Bob Dusek wrote: > > Scott Marlowe wrote: > >> Bob Dusek wrote: > > >>> 4X E7420 Xeon, Four cores (for a total of 16 cores) > > >> What method of striped RAID? > > > > RAID-0 > > I hope you have a plan for what to do when any one drive in this > array fails, and the entire array is unusable. > Point noted. > Anyway, my benchmarks tend to show that best throughput occurs at > about (CPU_count * 2) plus effective_spindle_count. Since you seem > to be fully cached, effective_spindle_count would be zero, so I > would expect performance to start to degrade when you have more than > about 32 sessions active. > That's a little disheartening for a single or dual CPU system. > > We actually see degredation when we get more than 40 concurrent > > requests. > > Close enough. > > > when we run 200+ requests, we see multi-second queries. > > No surprise there. Your vmstat output suggests that context > switches are becoming a problem, and I wouldn't be surprised if I > heard that the network is an issue. You might want to have someone > take a look at the network side to check. > This is all happening on a LAN, and network throughput doesn't seem to be an issue. It may be a busy network, but I'm not sure about a problem. Can you elaborate on your suspicion, based on the vmstat? I haven't used vmstat much. > > You want to use some connection pooling which queues requests when > more than some configurable number of connections is already active > with a request. You probably want to run that on the server side. > As for the postgresql.conf, could you show what you have right now, > excluding all comments? > The problem with connection pooling is that we actually have to achieve more than 40 per second, which happens to be the sweet spot with our current config. I posted our changes from the default in a reply to another message. I don't want to duplicate. Can you check those out? > > -Kevin > --0016e6d99eadbc064c047ce73b5e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Mon, Jan 11, 2010 at 12:17 PM, Kevin = Grittner <Kevin.Grittner@wicourts.gov> wrote:
Bob Dusek <reduse= k@gmail.com> wrote:
> Scott Marlowe <scott.marlowe@gmail.com>wrote:
>> Bob Dusek <redusek@gmail.com> wrote:

>>> 4X E7420 Xeon, Four cores (for a total= of 16 cores)

>> What method of striped RAID?
>
> RAID-0

I hope you have a plan for what to do when any one drive in this
array fails, and the entire array is unusable.

Poi= nt noted.
=A0
Anyway, my benchmarks tend to show that best throughput occurs at
about (CPU_count * 2) plus effective_spindle_count. =A0Since you seem
to be fully cached, effective_spindle_count would be zero, so I
would expect performance to start to degrade when you have more than
about 32 sessions active.

That's a little dish= eartening for a single or dual CPU system.=A0


> We actually see degredation when we get more than 40 concurrent
> requests.

Close enough.

> when we run 200+ requests, we see multi-second queries.

No surprise there. =A0Your vmstat output suggests that context
switches are becoming a problem, and I wouldn't be surprised if I
heard that the network is an issue. =A0You might want to have someone
take a look at the network side to check.

This is = all happening on a LAN, and network throughput doesn't seem to be an issue.=A0 It may be a busy network, but I'm not sure about a problem.=A0 Can you elaborate on your suspicion, based on the vmstat?=A0 I haven't used vmstat much.

You want to use some connection pooling which queues requests when
more than some configurable number of connections is already active
with a request. =A0You probably want to run that on the server side.
As for the postgresql.conf, could you show what you have right now,
excluding all comments?

The problem with connectio= n pooling is that we actually have to achieve more than 40 per second, which happens to be the sweet spot with our current config.=A0

I posted our changes from the default in a reply= to another message.=A0 I don't want to duplicate.=A0 Can you check tho= se out?=A0

-Kevin

--0016e6d99eadbc064c047ce73b5e--