Re: performance config help

On Tue, Jan 12, 2010 at 12:12 PM, Matthew Wakeling <matthew(at)flymine(dot)org>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?