Bob Dusek <redusek@xxxxxxxxx> wrote: > Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> wrote: >> Bob Dusek <redusek@xxxxxxxxx> 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. >> 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.) > 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. 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.... -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
