On Mon, Jan 11, 2010 at 1:20 PM, Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:
I appreciate the explanation. We were thinking that since we have so much CPU available, we weren't hitting Postgres' peak and that maybe a config change would help. But, thus far, it sounds like we're hardware-bound, and an application connection pool seems inevitable.
I'm curious if it would be worth our effort to enable the pg_stat stuff and try to analyze the system that way. We don't have a lot of experience with that, but if we could learn something critical from it, we will do it.
I'm thinking we'll have to roll our own. In a way, we have already done the connection pooling. We're experimenting with a new architecture with much more demanding performance requirements. We were emboldened by the hardware specs.
Yeah :) We haven't run into much trouble. But, we cut our teeth doing performance analysis of our app using PG 7.4. And, people on this list seem to be adamantly against this config these days. Is this safer in older versions of PG? Or, are the risks the same?
We have some asynchronous communications processes that communicate permanent db changes to an enterprise-level data warehouse. And, we can recover that data back down to the server if the server goes belly-up. If something does go belly up, we really only lose the bit of data that hasn't been communicated yet. It's true, that this data is important. However, it's also true that it's very costly to guarantee this that very small amount of data isn't lost. And, practically speaking (for our purposes) it seems that the data's not worth the cost.
Bob Dusek <redusek@xxxxxxxxx> wrote:
> Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote:
>> 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.
Not at all. You only have so many resources to keep busy at any one
moment. It is generally more efficient to only context switch
between as many processes as can keep those resources relatively
busy; otherwise valuable resources are spent switching among the
various processes rather than doing useful work.
[Regular readers of this list might want to skip ahead while I run
through my usual "thought experiment on the topic. ;-) ]
Imagine this hypothetical environment -- you have one CPU running
requests. There are no other resources to worry about and no
latency to the clients. Let's say that the requests take one second
each. The client suddenly has 100 requests to run. Assuming
context switching is free, you could submit all at once, and 100
seconds later, you get 100 responses, with an average response time
of 100 seconds. Let's put a (again free) connection pooler in
there. You submit those 100 requests, but they are fed to the
database one at a time. You get one response back in one second,
the next in two seconds, the last in 100 seconds. No request took
any longer, and the average response time was 50.5 seconds -- almost
a 50% reduction.
Now context switching is not free, and you had tens of thousands of
them per second. Besides the hit on CPU availability during each
switch, you're reducing the value of the L1 and L2 caches. So in
reality, you could expect your "request storm" to perform
significantly worse in comparison to the connection pooled
configuration. In reality, you have more than one resource to keep
busy, so the pool should be sized greater than one; but it's still
true that there is some point at which getting a request to the
database server delays the response to that request more than
queuing it for later execution would. Some database products build
in a way to manage this; in PostgreSQL it's on you to do so.
I appreciate the explanation. We were thinking that since we have so much CPU available, we weren't hitting Postgres' peak and that maybe a config change would help. But, thus far, it sounds like we're hardware-bound, and an application connection pool seems inevitable.
>> 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.
It was simply this: all that CPU idle time while it was swamped with
requests suggests that there might be a bottleneck outside the
database server. That could be, as another post suggests, the
client software. It could also be the network. (It could also be
contention on locks within PostgreSQL from the large number of
requests, but that's covered by the connection pooling suggestion.)
I'm curious if it would be worth our effort to enable the pg_stat stuff and try to analyze the system that way. We don't have a lot of experience with that, but if we could learn something critical from it, we will do it.
> 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.
Well, if you're considering a connection pool which can only submit
one request per second, you're looking at the wrong technology. We
use a custom connection pool built into our software, so I'm not
very familiar with the "drop in" packages out there, but we start
the next queued request based on the completion of a request --
there's no polling involved.
I'm thinking we'll have to roll our own. In a way, we have already done the connection pooling. We're experimenting with a new architecture with much more demanding performance requirements. We were emboldened by the hardware specs.
Between the RAID 0, fsync = off, and full_page_writes = off -- you
really had better not be staking anything important on this data.
This configuration would make The Flying Wallendas break out in a
sweat. It suggests to me that you might want to look into a better
RAID controller -- a high quality controller with battery-backup
(BBU) cache, configured for write-back, might allow you to change
all these to safe settings. If you also switch to a RAID
configuration with some redundancy, you'll be much safer....
Yeah :) We haven't run into much trouble. But, we cut our teeth doing performance analysis of our app using PG 7.4. And, people on this list seem to be adamantly against this config these days. Is this safer in older versions of PG? Or, are the risks the same?
We have some asynchronous communications processes that communicate permanent db changes to an enterprise-level data warehouse. And, we can recover that data back down to the server if the server goes belly-up. If something does go belly up, we really only lose the bit of data that hasn't been communicated yet. It's true, that this data is important. However, it's also true that it's very costly to guarantee this that very small amount of data isn't lost. And, practically speaking (for our purposes) it seems that the data's not worth the cost.
-Kevin
