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] Choice of bitmap scan over index scan

On Mon, Jan 11, 2010 at 2:41 PM, Jeremy Harris wrote: > On 01/11/2010 02:53 AM, Robert Haas wrote: >> >> On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris  wrote: >>> >>> Needing to use an external (on-disk) sort method, when taking >>> only 90MB, looks odd. > > [...] >> >> Well, you'd need to have w

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] work_mem vs temp files issue

Jaime Casanova writes: > the temp files shoul be coming from hash operations but AFAICS the > files are small and every hash operation should be using until > work_mem memory, right? No, when a hash spills to disk the code has to guess the partition sizes (number of buckets per partition) in adva

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] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 3:18 PM, Tom Lane wrote: > Jaime Casanova writes: >> LOG:  begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f >> LOG:  switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec >> elapsed 0.25 sec >> LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp85

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] work_mem vs temp files issue

Jaime Casanova writes: > LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f > LOG: switching to bounded heapsort at 641 tuples: CPU 0.08s/0.13u sec > elapsed 0.25 sec > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp8507.5", size 471010 > LOG: temporary file: path "base/pg

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] Choice of bitmap scan over index scan

On 01/11/2010 02:53 AM, Robert Haas wrote: On Sun, Jan 10, 2010 at 9:04 AM, Jeremy Harris wrote: Needing to use an external (on-disk) sort method, when taking only 90MB, looks odd. [...] Well, you'd need to have work_mem> 90 MB for that not to happen, and very few people can afford to set th

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] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 2:14 PM, Jaime Casanova wrote: > On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas wrote: >> On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova >> wrote: >>> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote: It might be useful to turn on trace_sort to see if the small f

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 2:07 PM, Robert Haas wrote: > On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova > wrote: >> On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote: >>> >>> It might be useful to turn on trace_sort to see if the small files >>> are coming from sorts.  If they're from hashes I'm afr

Re: [PERFORM] [PERFORMANCE] work_mem vs temp files issue

On Mon, Jan 11, 2010 at 1:15 PM, Jaime Casanova wrote: > On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote: >> >> It might be useful to turn on trace_sort to see if the small files >> are coming from sorts.  If they're from hashes I'm afraid there's >> no handy instrumentation ... >> > > yes they a

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] work_mem vs temp files issue

On Sun, Sep 13, 2009 at 5:37 PM, Tom Lane wrote: > > It might be useful to turn on trace_sort to see if the small files > are coming from sorts.  If they're from hashes I'm afraid there's > no handy instrumentation ... > yes they are, this is the log (i deleted the STATEMENT lines because they we

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

[PERFORM] cache false-sharing in lwlocks

Hi, LWLockPadded is either 16 or 32 bytes, so modern systems (e.g. Core2 or AMD Opteron [1]) with cacheline size of 64 bytes can get false-sharing in lwlocks. I changed LWLOCK_PADDED_SIZE in src/backend/storage/lmgr/lwlock.c to 64, and ran sysbench OLTP read-only benchmark, and got a slight impro

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] Choice of bitmap scan over index scan

Mathieu De Zutter wrote: > So if this query usually does *not* hit the cache, it will be > probably faster if I leave it like that? While testing a query I > execute it that much that it's always getting into the cache. > However, since other applications run on the same server, I think > that i

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

Re: [PERFORM] PG optimization question

On Sun, 10 Jan 2010 19:45:32 +0100, Robert Haas wrote: 2010/1/10 Pierre Frédéric Caillaud : If you transfer (delete from staging, insert into archive) in one transaction , then it will be always visible in exactly one of them, and exatly once in a view over both staging and archive(s).  

Re: [PERFORM] Choice of bitmap scan over index scan

Postgres is being conservative. The plan it uses (bitmap index scan) will perform much better than an index scan when the data is not in the cache, by maybe an order of magnitude, depending on your hardware setup. The index scan may perform better at the moment, but the bitmap index scan is

Re: [PERFORM] Choice of bitmap scan over index scan

On Mon, 11 Jan 2010, Mathieu De Zutter wrote: > seq_page_cost = 0.1 > random_page_cost = 0.1 So if this query usually does *not* hit the cache, it will be probably faster if I leave it like that? While testing a query I execute it that much that it's always getting into the cache. However, s

Re: [PERFORM] Choice of bitmap scan over index scan

On Mon, Jan 11, 2010 at 3:52 AM, Robert Haas wrote: > On Sun, Jan 10, 2010 at 10:53 AM, Kevin Grittner > wrote: > > seq_page_cost = 0.1 > > random_page_cost = 0.1 > > These might not even be low enough. The reason why bitmap index scans > win over plain index scans, in general, is because you m