Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B471F633ACE for ; Tue, 12 Jan 2010 14:01:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 47640-04 for ; Tue, 12 Jan 2010 18:01:41 +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 DF366633A6B for ; Tue, 12 Jan 2010 14:01:41 -0400 (AST) Received: by ewy19 with SMTP id 19so16170899ewy.21 for ; Tue, 12 Jan 2010 10:01:40 -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:cc:content-type; bh=ZatTrarxgfQGjfgLKL2GM4r2TQ+QQuBkMvZKnSlFnx8=; b=A1cp54XZh5okGZJbEMjuQIIjBn2WVbYVdZjjnSnujJmGKvovMPPcvXRov0zhiPg26U 93KzPBu0rhfPIxK0U4REdAMWLCHMwjqYZxIHorokwxKTnAJSI3emUYD+ljij34CElGwO +VflO691ks7eckOHUykCnIoctOuKvYm7TH3H0= 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 :cc:content-type; b=RduNV2cgXVJcTVejEiCGtseyOTDFcTiV7ww80IGmgiByEbRtYjRe33mbP279TFlJpX okTPb/N9A26niZZ41Ku9xhKcOv+klPCTHqc+JSZcKGljBNs7SmbiOUjRM/ZIfJYtE0Nf qgNg2XZlF4AzXE4hkzKHrrU8SAsfkusoD4Pp4= MIME-Version: 1.0 Received: by 10.216.87.134 with SMTP id y6mr122654wee.20.1263319300101; Tue, 12 Jan 2010 10:01:40 -0800 (PST) In-Reply-To: References: <4B4B177B020000250002E194@gw.wicourts.gov> <4B4B3BF6020000250002E1B1@gw.wicourts.gov> <61039b861001111323g531a5d68q1f59171d926aefef@mail.gmail.com> Date: Tue, 12 Jan 2010 13:01:39 -0500 Message-ID: <61039b861001121001p37dc7698i48651189edcee9d0@mail.gmail.com> Subject: Re: performance config help From: Bob Dusek To: Matthew Wakeling Cc: Kevin Grittner , Scott Marlowe , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d77e881ed92f047cfb72ae X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/233 X-Sequence-Number: 37113 --0016e6d77e881ed92f047cfb72ae Content-Type: text/plain; charset=ISO-8859-1 On Tue, Jan 12, 2010 at 12:12 PM, Matthew Wakeling wrote: > On Mon, 11 Jan 2010, Bob Dusek wrote: > >> How do I learn more about the actual lock contention in my db? Lock >> contention makes >> some sense. Each of the 256 requests are relatively similar. So, I don't >> doubt that >> lock contention could be an issue. I just don't know how to observe it or >> correct it. >> It seems like if we have processes that are contending for locks, there's >> not much we can >> do about it. >> > > To me: > > 1. This doesn't look like an IO bandwidth issue, as the database is small. > 2. This doesn't look like a CPU speed issue, as usage is low. > 3. This doesn't look like a memory bandwidth issue, as that would count as > CPU active in top. > 4. This doesn't look like a memory size problem either. > > So, what's left? It could be a network bandwidth problem, if your client is > on a separate server. You haven't really given much detail about the nature > of the queries, so it is difficult for us to tell if the size of the results > means that you are maxing out your network. However, it doesn't sound like > it is likely to me that this is the problem. > > The connections to postgres are happening on the localhost. Our application server accepts connections from the network, and the application queries Postgres using a standard pg_pconnect on the localhost. > It could be a client bottleneck problem - maybe your server is performing > really well, but your client can't keep up. You may be able to determine > this by switching on logging of long-running queries in Postgres, and > comparing that with what your client says. Also, look at the resource usage > on the client machine. > We've been logging long-running queries (200 ms). That's how we know Postgres is degrading. We don't see any queries showing up when we have 40 clients running. But, we start seeing quite a bit show up after that. > > It could be a lock contention problem. To me, this feels like the most > likely. You say that the queries are similar. If you are reading and writing > from a small set of the same objects in each of the transactions, then you > will suffer badly from lock contention, as only one backend can be in a > read-modify-write cycle on a given object at a time. We don't know enough > about the data and queries to say whether this is the case. However, if you > have a common object that every request touches (like a status line or > something), then try to engineer that out of the system. > > Hope this helps. Synchronising forty processes around accessing a single > object for high performance is really hard, and Postgres does it incredibly > well, but it is still by far the best option to avoid contention completely > if possible. > Each of the concurrent clients does a series of selects, inserts, updates, and deletes. The requests would generally never update or delete the same rows in a table. However, the requests do generally write to the same tables. And, they are all reading from the same tables that they're writing to. For the inserts, I imagine they are blocking on access to the sequence that controls the primary keys for the insert tables. But, I'm not sure about locking beyond that. When we delete from the tables, we generally delete where "clientid=X", which deletes all of the rows that a particular client inserted (each client cleans up its own rows after it finishes what its doing). Would that be blocking inserts on that table for other clients? > > -Kevin >> > > It'd really help us reading your emails if you could make sure that it is > easy to distinguish your words from words you are quoting. It can be very > confusing reading some of your emails, trying to remember which bits I have > seen before written by someone else. This is one of the few lines that I > know you didn't write - you're a Bob, not a Kevin. A few ">" characters at > the beginning of lines, which most mail readers will add automatically, make > all the difference. > > I'm really sorry. I'm using gmail's interface. I just saw the "<< Plain Text" formatter at the top of this compose message. But, if I convert it to Plain Text now, I may lose my portion of the message. I'll use the Plain Text when posting future messages. Sorry for the hassel. Matthew > > -- > Me... a skeptic? I trust you have proof? --0016e6d77e881ed92f047cfb72ae Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Tue, Jan 12, 2010 at 12:12 PM, Matthew Wa= keling <matthew= @flymine.org> wrote:
On Mon, 11 Jan 2010, Bob Dusek wrote:
How do I learn more about the actual lock contention in my db?=A0=A0 Lock c= ontention makes
some sense.=A0 Each of the 256 requests are relatively similar.=A0 So, I do= n't doubt that
lock contention could be an issue.=A0 I just don't know how to observe = it or correct it.=A0
It seems like if we have processes that are contending for locks, there'= ;s not much we can
do about it.=A0

To me:

1. This doesn't look like an IO bandwidth issue, as the database is sma= ll.
2. This doesn't look like a CPU speed issue, as usage is low.
3. This doesn't look like a memory bandwidth issue, as that would count= as
=A0 CPU active in top.
4. This doesn't look like a memory size problem either.

So, what's left? It could be a network bandwidth problem, if your clien= t is on a separate server. You haven't really given much detail about t= he nature of the queries, so it is difficult for us to tell if the size of = the results means that you are maxing out your network. However, it doesn&#= 39;t sound like it is likely to me that this is the problem.


The connections to postgres are happening on the = localhost.=A0 Our application server accepts connections from the network, = and the application queries Postgres using a standard pg_pconnect on the lo= calhost.=A0
=A0
It could be a client bottleneck problem - maybe your server is performing r= eally well, but your client can't keep up. You may be able to determine= this by switching on logging of long-running queries in Postgres, and comp= aring that with what your client says. Also, look at the resource usage on = the client machine.

We've been logging long-running queries (200 ms).= =A0 That's how we know Postgres is degrading.=A0 We don't see any q= ueries showing up when we have 40 clients running.=A0 But, we start seeing = quite a bit show up after that.=A0
=A0

It could be a lock contention problem. To me, this feels like the most like= ly. You say that the queries are similar. If you are reading and writing fr= om a small set of the same objects in each of the transactions, then you wi= ll suffer badly from lock contention, as only one backend can be in a read-= modify-write cycle on a given object at a time. We don't know enough ab= out the data and queries to say whether this is the case. However, if you h= ave a common object that every request touches (like a status line or somet= hing), then try to engineer that out of the system.

Hope this helps. Synchronising forty processes around accessing a single ob= ject for high performance is really hard, and Postgres does it incredibly w= ell, but it is still by far the best option to avoid contention completely = if possible.

Each of the concurrent clients does a series of selec= ts, inserts, updates, and deletes.=A0 The requests would generally never up= date or delete the same rows in a table.=A0 However, the requests do genera= lly write to the same tables.=A0 And, they are all reading from the same ta= bles that they're writing to.=A0 For the inserts, I imagine they are bl= ocking on access to the sequence that controls the primary keys for the ins= ert tables.=A0

But, I'm not sure about locking beyond that.=A0 When we delete from= the tables, we generally delete where "clientid=3DX", which dele= tes all of the rows that a particular client inserted (each client cleans u= p its own rows after it finishes what its doing).=A0 Would that be blocking= inserts on that table for other clients?
=A0

=A0 =A0 =A0-Kevin

It'd really help us reading your emails if you could make sure that it = is easy to distinguish your words from words you are quoting. It can be ver= y confusing reading some of your emails, trying to remember which bits I ha= ve seen before written by someone else. This is one of the few lines that I= know you didn't write - you're a Bob, not a Kevin. A few ">= ;" characters at the beginning of lines, which most mail readers will = add automatically, make all the difference.


I'm really sorry.=A0 I'm using gmail'= s interface. =A0 I just saw the "<< Plain Text" formatter a= t the top of this compose message.=A0 But, if I convert it to Plain Text no= w, I may lose my portion of the message.=A0 I'll use the Plain Text whe= n posting future messages.=A0

Sorry for the hassel.=A0

Matthew

--
Me... a skeptic? =A0I trust you have proof?

--0016e6d77e881ed92f047cfb72ae--