Re: [PERFORM] Bgwriter and pg_stat_bgwriter.buffers_clean aspects

2009-01-06 Thread Greg Smith
On Tue, 6 Jan 2009, Dmitry Koterov wrote: But why buffers_backend is so high? As I understood from your article, buffers_backend shows the number of writes immediately caused by any write operations, e.g. when an INSERT has to flush something on disk, because it has no space left for a new data

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread M. Edward (Ed) Borasky
David Rees wrote: > On Tue, Jan 6, 2009 at 11:02 AM, Stefano Nichele > wrote: >> BTW, why did you said I/O bound ? Which are the parameters that highlight >> that ? Sorry for my ignorance > > In addition to the percentage of time spent in wait as Scott said, you > can also see the number of p

Re: [PERFORM] Memory Allocation (8 GB shared buffer limit on Ubuntu Hardy)

2009-01-06 Thread Frank Joerdens
On Wed, Jan 7, 2009 at 3:23 AM, Tom Lane wrote: > "Frank Joerdens" writes: >> then I take the request size value from the error and do >> echo 8810725376 > /proc/sys/kernel/shmmax >> and get the same error again. > > What about shmall? Yes that works, it was set to r...@db04:~# cat /proc/sys/ke

Re: [PERFORM] Memory Allocation (8 GB shared buffer limit on Ubuntu Hardy)

2009-01-06 Thread Tom Lane
"Frank Joerdens" writes: > then I take the request size value from the error and do > echo 8810725376 > /proc/sys/kernel/shmmax > and get the same error again. What about shmall? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.

Re: [PERFORM] Memory Allocation (8 GB shared buffer limit on Ubuntu Hardy)

2009-01-06 Thread Frank Joerdens
Tom Lane wrote: > "Ryan Hansen" writes: [...] >> but when I set the shared buffer in PG and restart >> the service, it fails if it's above about 8 GB. > > Fails how? And what PG version is that? The thread seems to end here as far as the specific question was concerned. I just ran into the same

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread David Rees
On Tue, Jan 6, 2009 at 11:02 AM, Stefano Nichele wrote: > BTW, why did you said I/O bound ? Which are the parameters that highlight > that ? Sorry for my ignorance In addition to the percentage of time spent in wait as Scott said, you can also see the number of processes which are blocked (b

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread Scott Marlowe
On Tue, Jan 6, 2009 at 12:02 PM, Stefano Nichele wrote: > Thanks for your help. I'll give you the info you asked as soon as I'll have > it (i have also to install iostat but I don't have enough privilege to do > that). > > BTW, why did you said I/O bound ? Which are the parameters that highlight >

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread Stefano Nichele
Thanks for your help. I'll give you the info you asked as soon as I'll have it (i have also to install iostat but I don't have enough privilege to do that). BTW, why did you said I/O bound ? Which are the parameters that highlight that ? Sorry for my ignorance ste Merlin Moncure wrote:

Re: [PERFORM] failure notice

2009-01-06 Thread Scott Marlowe
I got this bounce message from your account, stefano.nich...@gmail.com. I'm on gmail too, but don't get a lot of -perform messages into my spam folder. Just in case you've got eliminatecc turned on on the mailing list server, I'm resending it through the mail server without your email address in

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread Scott Marlowe
I concur with Merlin you're I/O bound. Adding to his post, what RAID controller are you running, does it have cache, does the cache have battery backup, is the cache set to write back or write through? Also, what do you get for this (need contrib module pgbench installed) pgbench -i -s 100 pgben

Re: [PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread Merlin Moncure
On Tue, Jan 6, 2009 at 11:50 AM, Stefano Nichele wrote: > Hi list, > I would like to ask your help in order to understand if my postgresql server > (ver. 8.2.9) is well configured. > It's a quad-proc system (32 bit) with a 6 disk 1+0 RAID array and 2 separate > disks for the OS and write-ahead log

[PERFORM] understanding postgres issues/bottlenecks

2009-01-06 Thread Stefano Nichele
Hi list, I would like to ask your help in order to understand if my postgresql server (ver. 8.2.9) is well configured. It's a quad-proc system (32 bit) with a 6 disk 1+0 RAID array and 2 separate disks for the OS and write-ahead logs with 4GB of RAM. I don't know what is the best info to help

Re: [PERFORM] bad selectivity estimates for CASE

2009-01-06 Thread Tom Lane
"Robert Haas" writes: > Well, presumably CASE WHEN THEN WHEN THEN > WHEN THEN ... END = could > be simplified to . Not without breaking the order-of-evaluation guarantees. Consider case when x=0 then 0 when 1/x = 42 then 1 end = 1 This expression should not suffer a divide-by-ze

Re: [PERFORM] bad selectivity estimates for CASE

2009-01-06 Thread Robert Haas
On Mon, Jan 5, 2009 at 11:40 PM, Tom Lane wrote: > "Robert Haas" writes: >> While looking at a complex query that is being poorly planned by >> PostgreSQL 8.2.9, I discovered that any non-trivial CASE...WHEN >> expression seems to produce a selectivity estimate of 0.005. > > If you have an idea f

Re: [PERFORM] Bgwriter and pg_stat_bgwriter.buffers_clean aspects

2009-01-06 Thread Dmitry Koterov
Thank you. But why buffers_backend is so high? As I understood from your article, buffers_backend shows the number of writes immediately caused by any write operations, e.g. when an INSERT has to flush something on disk, because it has no space left for a new data in shared buffers. I suppose thes