Re: performance config help - Mailing list pgsql-performance

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.

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.

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.

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

Matthew

--
 Me... a skeptic?  I trust you have proof?