Re: performance config help - Mailing list pgsql-performance
| From | Bob Dusek |
|---|---|
| Subject | Re: performance config help |
| Date | |
| Msg-id | 61039b861001121001p37dc7698i48651189edcee9d0@mail.gmail.com Whole thread Raw |
| In response to | Re: performance config help (Matthew Wakeling <matthew@flymine.org>) |
| Responses |
Re: performance config help
Re: performance config help |
| List | pgsql-performance |
On Tue, Jan 12, 2010 at 12:12 PM, Matthew Wakeling <matthew@flymine.org> wrote:
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.
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.
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?
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.
On Mon, 11 Jan 2010, Bob Dusek wrote:To me: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.
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?
pgsql-performance by date: