Re: [PERFORM] performance config help

So, pgBouncer is pretty good. It doesn't appear to be as good as limiting TCON and using pconnect, but since we can't limit TCON in a production environment, we may not have a choice. Actually, you can : use lighttpd and php/fastcgi. Lighttpd handles the network stuff, and funnels/q

Re: [PERFORM] performance config help

Bob Dusek writes: > So, pgBouncer is pretty good. It doesn't appear to be as good as > limiting TCON and using pconnect, but since we can't limit TCON in a > production environment, we may not have a choice. You can still use pconnect() with pgbouncer, in transaction mode, if your application is

Re: [PERFORM] performance config help

Bob Dusek wrote: >>> The problem with our "cheap" connection pool is that the persistent >>> connections don't seem to be available immediately after they're >>> released by the previous process. pg_close doesn't seem to help the >>> situation. We understand that pg_close doesn't really close a

Re: [PERFORM] performance config help

Bob Dusek wrote: > So, pgBouncer is pretty good. It doesn't appear to be as good as > limiting TCON and using pconnect, but since we can't limit TCON in a > production environment, we may not have a choice. It may be worth looking into pgpool, as well. If you have a very cheap-to-connect-to loca

Re: [PERFORM] performance config help

>> The problem with our "cheap" connection pool is that the persistent >> connections don't seem to be available immediately after they're >> released by the previous process.   pg_close doesn't seem to help the >> situation.  We understand that pg_close doesn't really close a >> persistent connect

Re: [PERFORM] performance config help

On Wed, Jan 13, 2010 at 1:10 PM, Bob Dusek wrote: > And, we pretty much doubled our capacity... from approx 40 "requests" > per second to approx 80. Excellent! > The problem with our "cheap" connection pool is that the persistent > connections don't seem to be available immediately after they're

Re: [PERFORM] performance config help

FYI - We have implemented a number of changes... a) some query and application optimizations b) connection pool (on the cheap: set max number of clients on Postgres server and created a blocking wrapper to pg_pconnect that will block until it gets a connection) c) moved the application server to a

Re: [PERFORM] performance config help

On 13/01/2010 2:01 AM, Bob Dusek 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. Well, that's a good reason to have all those CPU

Re: [PERFORM] performance config help

On Tue, 12 Jan 2010, Bob Dusek wrote: 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 fro

Re: [PERFORM] performance config help

> Bob, you might want to just send plain text, to avoid such problems. Will do. Looks like gmail's interface does it nicely. > > -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-p

Re: [PERFORM] performance config help

On Tue, Jan 12, 2010 at 12:12 PM, Matthew Wakeling 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 conte

Re: [PERFORM] performance config help

Matthew Wakeling wrote: >> -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 b

Re: [PERFORM] performance config help

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

Re: [PERFORM] performance config help

Each of the 256 requests was being processed by a php process. So, it could certainly be faster. But, the fact that we're seeing the db performance degrade would seem to indicate that our application is fast enough to punish the db. Isn't that true? Not necessarily. Your DB still has

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 4:17 PM, Greg Smith wrote: > Scott Marlowe wrote: >> >> The DDR3 Nehalem and DDR2 AMD are both actually pretty close in real >> world use on 4 or more socket machines.  Most benchmarks on memory >> bandwidth give no huge advantage to either one or the other.  They >> both m

Re: [PERFORM] performance config help

Scott Marlowe wrote: The DDR3 Nehalem and DDR2 AMD are both actually pretty close in real world use on 4 or more socket machines. Most benchmarks on memory bandwidth give no huge advantage to either one or the other. They both max out at about 25GB/s. The most fair comparison I've seen so fa

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 3:04 PM, Greg Smith wrote: > Scott Marlowe wrote: >> >> So, I took a break from writing and searched for some more info on the >> 74xx series CPUs, and from reading lots of articles, including this >> one: >> http://www.anandtech.com/cpuchipsets/intel/showdoc.aspx?i=3414 >>

Re: [PERFORM] performance config help

Bob Dusek wrote: How do I learn more about the actual lock contention in my db? There's a page with a sample query and links to more info at http://wiki.postgresql.org/wiki/Lock_Monitoring One other little thing: when you're running "top", try using "top -c" instead. That should show you

Re: [PERFORM] performance config help

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

Re: [PERFORM] performance config help

Scott Marlowe wrote: So, I took a break from writing and searched for some more info on the 74xx series CPUs, and from reading lots of articles, including this one: http://www.anandtech.com/cpuchipsets/intel/showdoc.aspx?i=3414 It seems apparent that the 74xx series if a great CPU, as long as you

Re: [PERFORM] performance config help

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

Re: [PERFORM] performance config help

> > > I haven't been keeping up on the hardware, so I defer to you on > that. It certainly seems like it would fit with the symptoms. On > the other hand, I haven't seen anything yet to convince me that it > *couldn't* be a client-side or network bottleneck, or the sort of > lock contention bottle

Re: [PERFORM] performance config help

Scott Marlowe wrote: > FYI, on an 8 or 16 core machine, 10k to 30k context switches per > second aren't that much. Yeah, on our 16 core machines under heavy load we hover around 30k. He was around 50k, which is why I said it looked like it was "becoming a problem." > If you're climbing past

Re: [PERFORM] performance config help

Scott Marlowe writes: > On Mon, Jan 11, 2010 at 1:13 PM, Dusek, Bob wrote: >> I haven't been involved in any benchmarking of PG8 with fsync=off, but we >> certainly did it with PG 7.4.  fsync=0ff, for our purposes, was MUCH faster. > And many changes have been made since then to make fsyncing m

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 1:13 PM, Dusek, Bob wrote: > I haven't been involved in any benchmarking of PG8 with fsync=off, but we > certainly did it with PG 7.4.  fsync=0ff, for our purposes, was MUCH faster. And many changes have been made since then to make fsyncing much faster. You may be grind

Re: [PERFORM] performance config help

> I have slave dbs running on four 7200RPM SATA drives with fsync off. > They only get updated from the master db so if they go boom, I just > recreate their node. There's times fsync off is ok, you just have to > know that that db is now considered "disposable". > > However, I'd suggest doing s

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 12:34 PM, Bob Dusek wrote: > 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?  O

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 12:36 PM, Scott Marlowe wrote: > FYI, on an 8 or 16 core machine, 10k to 30k context switches per > second aren't that much.  If you're climbing past 100k you might want > to look out. > > The more I read up on the 74xx CPUs and look at the numbers here the > more I think i

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 11:20 AM, Kevin Grittner wrote: > Bob Dusek wrote: >> Kevin Grittner wrote: >>> Bob Dusek 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, effect

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 1:20 PM, Kevin Grittner wrote: > Bob Dusek wrote: > > Kevin Grittner wrote: > >> Bob Dusek 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, e

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 10:54 AM, Bob Dusek wrote: >> You want to use some connection pooling which queues requests when >> more than some configurable number of connections is already active >> with a request.  You probably want to run that on the server side. >> As for the postgresql.conf, could

Re: [PERFORM] performance config help

> >> RAID-0 >> > > And how many drives? > > Just two. We have an application server that is processing requests. Each request >> consists of a combination of selects, inserts, and deletes. We actually see >> degredation when we get more than 40 concurrent requests. The exact number >> of querie

Re: [PERFORM] performance config help

Bob Dusek wrote: > Kevin Grittner wrote: >> Bob Dusek 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 performanc

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 10:49 AM, Bob Dusek wrote: >> Depends, is that the first iteration of output?  if so, ignore it and >> show me the second and further on.  Same for vmstat...  In fact let >> them run for a minute or two and attach the results...  OTOH, if that >> is the second or later set

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 12:17 PM, Kevin Grittner < kevin.gritt...@wicourts.gov> wrote: > Bob Dusek wrote: > > Scott Marlowe wrote: > >> Bob Dusek wrote: > > >>> 4X E7420 Xeon, Four cores (for a total of 16 cores) > > >> What method of striped RAID? > > > > RAID-0 > > I hope you have a plan for w

Re: [PERFORM] performance config help

> > > > This is to be expected, to some extent, as we would expect some > perfromance > > degradation with higher utilization. But, the hardware doesn't appear to > be > > very busy, and that's where we're hoping for some help. > > It's likely in io wait. > > >> What do the following commands tell

Re: [PERFORM] performance config help

We may want to start looking at query plans for the slowest queries. Use explain analyze to find them and attach them here. I kinda have a feeling you're running into a limit on the speed of your memory though, and there's no real cure for that. You can buy a little time with some query or db tun

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 9:42 AM, Bob Dusek wrote: >> What method of striped RAID?  RAID-5?  RAID-10?  RAID-4?  RAID-0? > > RAID-0 Just wondering how many drives? > To be more specific about the degradation, we've set the > "log_min_duration_statement=200", and when we run with 40 concurrent > re

Re: [PERFORM] performance config help

Ivan Voras wrote: Yes, but you are issuing 133 write operations per seconds per drive(s) - this is nearly the limit of what you can get with 15k RPM drives (actually, the limit should be somewhere around 200..250 IOPS but 133 isn't that far). I saw in your other post you have fsync turned of

Re: [PERFORM] performance config help

Bob Dusek wrote: > Scott Marlowe wrote: >> Bob Dusek wrote: >>> 4X E7420 Xeon, Four cores (for a total of 16 cores) >> What method of striped RAID? > > RAID-0 I hope you have a plan for what to do when any one drive in this array fails, and the entire array is unusable. Anyway, my benchm

Re: [PERFORM] performance config help

Bob Dusek wrote: On Mon, Jan 11, 2010 at 8:50 AM, Scott Marlowe > wrote: On Mon, Jan 11, 2010 at 6:44 AM, Bob Dusek mailto:redu...@gmail.com>> wrote: > Hello, > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... >

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 9:07 AM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to Bob Dusek : > > Hello, > > > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... > > > > 4X E7420 Xeon, Four cores (for a total of 16 cores) > > 2.13 GHz, 8M Cache, 1066

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 8:50 AM, Scott Marlowe wrote: > On Mon, Jan 11, 2010 at 6:44 AM, Bob Dusek wrote: > > Hello, > > > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... > > > > 4X E7420 Xeon, Four cores (for a total of 16 cores) > > 2.13 GHz, 8M Cache, 1066 Mhz FSB > >

Re: [PERFORM] performance config help

In response to Bob Dusek : > Hello, > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... > > 4X E7420 Xeon, Four cores (for a total of 16 cores) > 2.13 GHz, 8M Cache, 1066 Mhz FSB > 32 Gigs of RAM > 15 K RPM drives in striped raid > > Things run fine, but when we get a lot

Re: [PERFORM] performance config help

On Mon, Jan 11, 2010 at 6:44 AM, Bob Dusek wrote: > Hello, > > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... > > 4X E7420 Xeon, Four cores (for a total of 16 cores) > 2.13 GHz, 8M Cache, 1066 Mhz FSB > 32 Gigs of RAM > 15 K RPM drives in striped raid What method of stripe

[PERFORM] performance config help

Hello, We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware... 4X E7420 Xeon, Four cores (for a total of 16 cores) 2.13 GHz, 8M Cache, 1066 Mhz FSB 32 Gigs of RAM 15 K RPM drives in striped raid Things run fine, but when we get a lot of concurrent queries running, we see a pretty