date:20060615

Sorry about that, I was in a slight panic :) 

I am using postgresql 8.1.4. I will install 8.1.3 and see if the same behavior 
exists.. we 
may have started seeing this in 8.1.3, but I dont think before. I will check 
some stability 
machines for similar bloating.

The query (calling a store proc) which is always running when the spiral begins 
is below. It simply performs 
bulk linking of two objects.  Depending on what the application is detecting, 
it could be called to insert
40 - 50k records, 500 at a time. When the box is healthy, this is a 200 - 500 
ms op, but this starts to become 
a 2+ ms op. I guess this makes sense considering the paging.

Jun 14 12:50:18 xxx postgres[5649]: [3-1] LOG:  duration: 20117.984 ms  
statement: EXECUTE unnamed  [PREPARE:  select * from 
link_attacker_targets($1, $2, $3)  as

CREATE OR REPLACE FUNCTION link_attacker_targets (p_attacker bigint, p_targets 
varchar, p_targets_size integer) 
returns bigint[] as
$body$
DECLARE
v_targets  bigint[];
v_target   bigint;
v_returns  bigint[];
v_returns_size integer := 0;
BEGIN
v_targets := convert_string2bigint_array (p_targets, p_targets_size);

FOR i IN 1..p_targets_size LOOP
v_target := v_targets[i];

BEGIN
  INSERT into attacker_target_link (attacker_id, target_id) values 
(p_attacker, v_target);
  v_returns_size := v_returns_size + 1;
  v_returns[v_returns_size] := v_target;
  
EXCEPTION WHEN unique_violation THEN
-- do nothing... app cache may be out of date.
END;
END LOOP;
RETURN v_returns;
END;
$body$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

On Wednesday 14 June 2006 17:03, you wrote:
 jody brownell [EMAIL PROTECTED] writes:
  27116 postgres  15   0 1515m 901m  91m S  0.0 22.9  18:33.96 postgres: 
  qradar qradar :::x.x.x.x(51149) idle
 
 This looks like a memory leak, but you haven't provided enough info to
 let someone else reproduce it.  Can you log what your application is
 doing and extract a test case?  What PG version is this, anyway?
 
   regards, tom lane
 
 
 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Postgres consuming way too much memory???

The last version of postgres we had in production was 8.1.1 actually, not 
8.1.3. 

So far, on my stability box and older production stability boxes I dont see the 
same behavior. 

I will install 8.1.1 on these boxes and see what I see.

On Thursday 15 June 2006 09:01, jody brownell wrote:
 Sorry about that, I was in a slight panic :) 
 
 I am using postgresql 8.1.4. I will install 8.1.3 and see if the same 
 behavior exists.. we 
 may have started seeing this in 8.1.3, but I dont think before. I will check 
 some stability 
 machines for similar bloating.
 
 The query (calling a store proc) which is always running when the spiral 
 begins is below. It simply performs 
 bulk linking of two objects.  Depending on what the application is detecting, 
 it could be called to insert
 40 - 50k records, 500 at a time. When the box is healthy, this is a 200 - 500 
 ms op, but this starts to become 
 a 2+ ms op. I guess this makes sense considering the paging.
 
 Jun 14 12:50:18 xxx postgres[5649]: [3-1] LOG:  duration: 20117.984 ms  
 statement: EXECUTE unnamed  [PREPARE:  select * from 
 link_attacker_targets($1, $2, $3)  as
 
 CREATE OR REPLACE FUNCTION link_attacker_targets (p_attacker bigint, 
 p_targets varchar, p_targets_size integer) 
   returns bigint[] as
 $body$
 DECLARE
 v_targets  bigint[];
 v_target   bigint;
 v_returns  bigint[];
 v_returns_size integer := 0;
 BEGIN
 v_targets := convert_string2bigint_array (p_targets, p_targets_size);
 
 FOR i IN 1..p_targets_size LOOP
   v_target := v_targets[i];
 
   BEGIN
   INSERT into attacker_target_link (attacker_id, target_id) values 
 (p_attacker, v_target);
   v_returns_size := v_returns_size + 1;
   v_returns[v_returns_size] := v_target;
   
   EXCEPTION WHEN unique_violation THEN
   -- do nothing... app cache may be out of date.
   END;
 END LOOP;
 RETURN v_returns;
 END;
 $body$
 LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 
 On Wednesday 14 June 2006 17:03, you wrote:
  jody brownell [EMAIL PROTECTED] writes:
   27116 postgres  15   0 1515m 901m  91m S  0.0 22.9  18:33.96 postgres: 
   qradar qradar :::x.x.x.x(51149) idle
  
  This looks like a memory leak, but you haven't provided enough info to
  let someone else reproduce it.  Can you log what your application is
  doing and extract a test case?  What PG version is this, anyway?
  
  regards, tom lane
  
  
  
 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend
 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[PERFORM] How to analyze function performance

2006-06-15 Thread Mindaugas


  Hello,

  Is it possible to somehow analyze function performance? E.g.
we are using function cleanup() which takes obviously too much time
to execute but I have problems trying to figure what is slowing things
down.

  When I explain analyze function lines step by step it show quite
acceptable performance.

  PostgreSQL 8.0 is running on two dual core Opterons.

  Thanks,

  Mindaugas


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] How to analyze function performance

2006-06-15 Thread Tomas Vondra

It depends what is the purpose of the function. If it's mainly a
container for a heap of SQL queries along with some simple IF, ELSE
etc. then I use two simple ways to analyze the performance (or lack
of performance):

1) I use a lot of debug messages

2) I print out all SQL and the execute EXPLAIN / EXPLAIN ANALYZE on them

If the function is mainly a computation of something, it's usually nice
to try to use for example C language, as it's much faster than PL/pgSQL
for this type of functions.

But it depends on what you are trying to do in that function ...

Tomas

   Hello,
 
   Is it possible to somehow analyze function performance? E.g.
 we are using function cleanup() which takes obviously too much time
 to execute but I have problems trying to figure what is slowing things
 down.
 
   When I explain analyze function lines step by step it show quite
 acceptable performance.
 
   PostgreSQL 8.0 is running on two dual core Opterons.
 
   Thanks,
 
   Mindaugas

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] How to analyze function performance

Mindaugas [EMAIL PROTECTED] writes:
   Is it possible to somehow analyze function performance? E.g.
 we are using function cleanup() which takes obviously too much time
 to execute but I have problems trying to figure what is slowing things
 down.

   When I explain analyze function lines step by step it show quite
 acceptable performance.

Are you sure you are explain analyzeing the same queries the function
is really doing?  You have to account for the fact that what plpgsql is
issuing is parameterized queries, and sometimes that limits the
planner's ability to pick a good plan.  For instance, if you have

declare x int;
begin
...
for r in select * from foo where key = x loop ...

then what is really getting planned and executed is select * from foo
where key = $1 --- every plpgsql variable gets replaced by a parameter
symbol $n.  You can model this for EXPLAIN purposes with a prepared
statement:

prepare p1(int) as select * from foo where key = $1;
explain analyze execute p1(42);

If you find out that a particular query really sucks when parameterized,
you can work around this by using EXECUTE to force the query to be
planned afresh on each use with literal constants instead of parameters:

for r in execute 'select * from foo where key = ' || x loop ...

The replanning takes extra time, though, so don't do this except where
you've specifically proved there's a need.

BTW, be careful to use quote_literal() when needed in queries built as
strings, else you'll have bugs and maybe even security problems.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Postgres consuming way too much memory???

Some more information...

When postgresql starts to go into this bloating state, I can only make it 
happen from my java app.
If I simultaneously perform insert of 10million rows into another table, it 
behaves as expected, but 
the postgresql process handling the java connection slows down and bloats.

This leads me to think it has something to do with either the long lived 
connection. I am using 
dbcp jdbc pool from jakarta OR I am trigger this behavior with something I am 
doing in the link 
routine I sent earlier.

I am going to try closing the connection after each TX to see if this resolves 
it for now. If not, I will write
a java app, stored procedure (table etc) reproduce it without our application. 

Oh yeah, it is when I use about have of my swap, dstat starts reporting heavy 
paging, memory climbs very quickly.



On Thursday 15 June 2006 09:15, jody brownell wrote:
 The last version of postgres we had in production was 8.1.1 actually, not 
 8.1.3. 
 
 So far, on my stability box and older production stability boxes I dont see 
 the same behavior. 
 
 I will install 8.1.1 on these boxes and see what I see.
 
 On Thursday 15 June 2006 09:01, jody brownell wrote:
  Sorry about that, I was in a slight panic :) 
  
  I am using postgresql 8.1.4. I will install 8.1.3 and see if the same 
  behavior exists.. we 
  may have started seeing this in 8.1.3, but I dont think before. I will 
  check some stability 
  machines for similar bloating.
  
  The query (calling a store proc) which is always running when the spiral 
  begins is below. It simply performs 
  bulk linking of two objects.  Depending on what the application is 
  detecting, it could be called to insert
  40 - 50k records, 500 at a time. When the box is healthy, this is a 200 - 
  500 ms op, but this starts to become 
  a 2+ ms op. I guess this makes sense considering the paging.
  
  Jun 14 12:50:18 xxx postgres[5649]: [3-1] LOG:  duration: 20117.984 ms  
  statement: EXECUTE unnamed  [PREPARE:  select * from 
  link_attacker_targets($1, $2, $3)  as
  
  CREATE OR REPLACE FUNCTION link_attacker_targets (p_attacker bigint, 
  p_targets varchar, p_targets_size integer) 
  returns bigint[] as
  $body$
  DECLARE
  v_targets  bigint[];
  v_target   bigint;
  v_returns  bigint[];
  v_returns_size integer := 0;
  BEGIN
  v_targets := convert_string2bigint_array (p_targets, p_targets_size);
  
  FOR i IN 1..p_targets_size LOOP
  v_target := v_targets[i];
  
  BEGIN
INSERT into attacker_target_link (attacker_id, target_id) values 
  (p_attacker, v_target);
v_returns_size := v_returns_size + 1;
v_returns[v_returns_size] := v_target;

  EXCEPTION WHEN unique_violation THEN
  -- do nothing... app cache may be out of date.
  END;
  END LOOP;
  RETURN v_returns;
  END;
  $body$
  LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
  
  On Wednesday 14 June 2006 17:03, you wrote:
   jody brownell [EMAIL PROTECTED] writes:
27116 postgres  15   0 1515m 901m  91m S  0.0 22.9  18:33.96 postgres: 
qradar qradar :::x.x.x.x(51149) idle
   
   This looks like a memory leak, but you haven't provided enough info to
   let someone else reproduce it.  Can you log what your application is
   doing and extract a test case?  What PG version is this, anyway?
   
 regards, tom lane
   
   
   
  
  ---(end of broadcast)---
  TIP 6: explain analyze is your friend
  
 
 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?
 
http://www.postgresql.org/docs/faq
 

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Is it possible to start two instances of postgresql?

2006-06-15 Thread Chris Browne

[EMAIL PROTECTED] writes:
 Is it possible to start two instances of postgresql with different port and
 directory which run simultaneously?

Certainly.  We have one HACMP cluster which hosts 14 PostgreSQL
instances across two physical boxes.  (If one went down, they'd all
migrate to the survivor...)

 If can then will this cause any problem or performance drop down?

There certainly can be; the databases will be sharing disks, memory,
and CPUs, so if they are avidly competing for resources, the
competition is sure to have some impact on performance.

Flip side: That 14 database cluster has several databases that are
known to be very lightly used; they *aren't* competing, and aren't a
problem.

Consider it obvious that if you haven't enough memory or I/O bandwidth
to cover your two PG instances, you'll find performance sucks...  If
you have enough, then it can work fine...
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/linuxxian.html
At  Microsoft, it doesn't  matter which  file you're  compiling, only
which flags you #define.  -- Colin Plumb

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Postgres consuming way too much memory???

jody brownell [EMAIL PROTECTED] writes:
 When postgresql starts to go into this bloating state, I can only make it 
 happen from my java app.

That's interesting.  The JDBC driver uses protocol features that aren't
used by psql, so it's possible that the leak is triggered by one of
those features.  I wouldn't worry too much about duplicating the problem
from psql anyway --- a Java test case will do fine.

 I am going to try closing the connection after each TX to see if this
 resolves it for now. If not, I will write a java app, stored procedure
 (table etc) reproduce it without our application.

Even if that works around it for you, please pursue getting a test case
together so we can find and fix the underlying problem.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Postgres consuming way too much memory???

jody brownell [EMAIL PROTECTED] writes:
   BEGIN
   INSERT into attacker_target_link (attacker_id, target_id) values 
 (p_attacker, v_target);
   v_returns_size := v_returns_size + 1;
   v_returns[v_returns_size] := v_target;
  
   EXCEPTION WHEN unique_violation THEN
   -- do nothing... app cache may be out of date.
   END;

Hmm.  There is a known problem that plpgsql leaks some memory when
catching an exception:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00885.php

So if your problem case involves a whole lot of duplicates then that
could explain the initial bloat.  However, AFAIK that leakage is in
a transaction-local memory context, so the space ought to be freed at
transaction end.  And Linux's malloc does know about giving space back
to the kernel (unlike some platforms).  So I'm not sure why you're
seeing persistent bloat.

Can you rewrite the function to not use an EXCEPTION block (perhaps
a separate SELECT probe for each row --- note this won't be reliable
if there are concurrent processes making insertions)?  If so, does
that fix the problem?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Confirmation of bad query plan generated by 7.4

On Wed, Jun 14, 2006 at 10:36:55PM -0400, Tom Lane wrote:
 Jim Nasby [EMAIL PROTECTED] writes:
  On Jun 13, 2006, at 8:50 PM, Tom Lane wrote:
  Hmm ... worksforme.  Could you provide a complete test case?
 
  decibel=# create table date_test(d date not null, i int not null);
  [etc]
 
 Not sure what you are driving at.  The estimates are clearly not
 defaults (the default estimate would be 1/3rd of the table, or
 about 100mil rows).  Are you expecting them to be the same?  If so why?
 The comparison values are slightly different after all.

Yes... I was expecting that since we're looking at a date field that the
timestamp would get cast to a date. Sorry I wasn't clear on that...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Postgres fsync off (not needed) with NetApp

On Thu, Jun 15, 2006 at 01:14:26AM -0400, Jonah H. Harris wrote:
 On 14 Jun 2006 23:33:53 -0400, Greg Stark [EMAIL PROTECTED] wrote:
 In fact the benefit of the NVRAM is precisely that it makes sure you 
 *don't*
 have any reason to turn fsync off. It should make the fsync essentially 
 free.
 
 Having run PostgreSQL on a NetApp with input from NetApp, this is
 correct.  fsync should be turned on, but you will not incur the *real*
 direct-to-disk cost of the sync, it will be direct-to-NVRAM.

Just so there's no confusion... this applies to any caching RAID
controller as well. You just need to ensure that the cache in the
controller absolutely will not be lost in the event of a power failure
or what-have-you. On most controllers this is accomplished with a simple
battery backup; I don't know if the higher-end stuff takes further steps
(such as flashing the cache contents to flash memory on a power
failure).
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Precomputed constants?

On Thu, Jun 15, 2006 at 06:31:02AM +0200, Zoltan Boszormenyi wrote:
 Jim C. Nasby ?rta:
 On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
   
 Replacing random() with a true constant gives me index scan
 even if it's hidden inside other function calls. E.g.:
 
 
 The database has no choice but to compute random() for every row; it's
 marked VOLATILE.
   
 
 I see now, docs about CREATE FUNCTION mentions random(),
 currval() and timeofday() as examples for VOLATILE.
 But where in the documentation can I find this info about all
 built-in functions? Thanks.

No, but you can query pg_proc for that info. The docs should have info
about that table.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Postgres consuming way too much memory???

Tom - that make sense... and fits the timeline of when the instability may have 
been introduced.

I use soft references in java to track these relationships. When the GC needs 
memory it will collect
objects referenced by soft references so I need to have this exception caught 
where my caches may get cleaned.

When the system is under load as it would be in this case, there references 
would be cleaned causing a large 
number of exceptions in the pgplsql, subsequently causing the leak... hence the 
swift downward spiral.

The previous version of these routines used selects but due to volume of 
selects, performance suffered quite 
a bit. I dont think I could revert now for production use... closing the 
connection maybe the workaround for 
us for this release IF this is in fact what the problem is. Unfortunatly, I use 
the catch in about 20 similar 
routines to reset sequences etc this may be painful :(

I will modify the routine to help isolate the problem. stay tuned.

BTW - the fix you mentioned  is that targeted for 8.2? Is there a timeline 
for 8.2?

On Thursday 15 June 2006 12:44, Tom Lane wrote:
 jody brownell [EMAIL PROTECTED] writes:
  BEGIN
INSERT into attacker_target_link (attacker_id, target_id) values 
  (p_attacker, v_target);
v_returns_size := v_returns_size + 1;
v_returns[v_returns_size] := v_target;
   
  EXCEPTION WHEN unique_violation THEN
  -- do nothing... app cache may be out of date.
  END;
 
 Hmm.  There is a known problem that plpgsql leaks some memory when
 catching an exception:
 http://archives.postgresql.org/pgsql-hackers/2006-02/msg00885.php
 
 So if your problem case involves a whole lot of duplicates then that
 could explain the initial bloat.  However, AFAIK that leakage is in
 a transaction-local memory context, so the space ought to be freed at
 transaction end.  And Linux's malloc does know about giving space back
 to the kernel (unlike some platforms).  So I'm not sure why you're
 seeing persistent bloat.
 
 Can you rewrite the function to not use an EXCEPTION block (perhaps
 a separate SELECT probe for each row --- note this won't be reliable
 if there are concurrent processes making insertions)?  If so, does
 that fix the problem?
 
   regards, tom lane
 
 
 

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Postgres consuming way too much memory???

jody brownell [EMAIL PROTECTED] writes:
 BTW - the fix you mentioned  is that targeted for 8.2? Is there a 
 timeline for 8.2?

There is no fix as yet, but it's on the radar screen to fix for 8.2.

We expect 8.2 will go beta towards the end of summer (I forget whether
Aug 1 or Sep 1 is the target).

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Which processor runs better for Postgresql?

2006-06-15 Thread Vivek Khera



On Jun 13, 2006, at 2:02 PM, Steve Poe wrote:



Can anyone share what their experience has been with Intel's dual core
CPUs and/or Dell's new servers?


I'm one of the few Dell fans around here... but I must say that I  
don't buy them for my big DB servers specifically since they don't  
currently ship Opteron based systems.  (I did call and thank my sales  
rep for pushing my case for them to do Opterons, though, since I'm  
sure they are doing it as a personal favor to me :-) )


I just put up a pentium-D dual-core based system and it is pretty  
wickedly fast.  it only has a pair of SATA drives on it and is used  
for pre-production testing.




I am hoping the client is willing to wait for Dell to ship a AMD
Opeteron-based server.


Don't wait.  It will be *months* before that happens. Go get a Sun  
X4100 and an external RAID array and be happy.  These boxes are an  
amazing work of engineering.




smime.p7s
Description: S/MIME cryptographic signature

Re: [PERFORM] Postgres consuming way too much memory???

On Thu, 2006-06-15 at 11:34 -0400, Tom Lane wrote:
 jody brownell [EMAIL PROTECTED] writes:
  When postgresql starts to go into this bloating state, I can only make it 
  happen from my java app.
 
 That's interesting.  The JDBC driver uses protocol features that aren't
 used by psql, so it's possible that the leak is triggered by one of
 those features.  I wouldn't worry too much about duplicating the problem
 from psql anyway --- a Java test case will do fine.
 
  I am going to try closing the connection after each TX to see if this
  resolves it for now. If not, I will write a java app, stored procedure
  (table etc) reproduce it without our application.


Just to mention another possible culprit; this one doesn't seem all that
likely to me, but at least it's easy to investigate.

With DBCP and non-ancient versions of the JDBC driver that use v3
protocol and real prepared statements, it is possible to (mis)configure
the system to create an unbounded number of cached prepared statements
on any particular connection.  Older versions of DBCP were also known to
have bugs which aggravated this issue when prepared statement caching
was enabled, IIRC.

-- Mark Lewis

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Which processor runs better for Postgresql?

2006-06-15 Thread Steve Poe

Vivek,

Thanks for your feedback.  Which Dell server did you purchase?  

The client has a PowerEdge 2600 and they STILL want Dell. Again, if it
were my pocketbook, Dell would not be there. 

The client has a 30GB DB. This is large for me, but probably not with
you. Also, I am advising the client to go to a 10+ disc array (from 3)
and enough RAM to load half the DB into memory. 

Steve




On Thu, 2006-06-15 at 12:22 -0400, Vivek Khera wrote:
 On Jun 13, 2006, at 2:02 PM, Steve Poe wrote:
 
 
  Can anyone share what their experience has been with Intel's dual core
  CPUs and/or Dell's new servers?
 
 I'm one of the few Dell fans around here... but I must say that I  
 don't buy them for my big DB servers specifically since they don't  
 currently ship Opteron based systems.  (I did call and thank my sales  
 rep for pushing my case for them to do Opterons, though, since I'm  
 sure they are doing it as a personal favor to me :-) )
 
 I just put up a pentium-D dual-core based system and it is pretty  
 wickedly fast.  it only has a pair of SATA drives on it and is used  
 for pre-production testing.
 
 
  I am hoping the client is willing to wait for Dell to ship a AMD
  Opeteron-based server.
 
 Don't wait.  It will be *months* before that happens. Go get a Sun  
 X4100 and an external RAID array and be happy.  These boxes are an  
 amazing work of engineering.
 


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Postgres consuming way too much memory???

2006-06-15 Thread Bruce Momjian


Added to TODO:

   o Fix memory leak from exceptions

http://archives.postgresql.org/pgsql-performance/2006-06/msg0$


---

Tom Lane wrote:
 jody brownell [EMAIL PROTECTED] writes:
  BEGIN
INSERT into attacker_target_link (attacker_id, target_id) values 
  (p_attacker, v_target);
v_returns_size := v_returns_size + 1;
v_returns[v_returns_size] := v_target;
   
  EXCEPTION WHEN unique_violation THEN
  -- do nothing... app cache may be out of date.
  END;
 
 Hmm.  There is a known problem that plpgsql leaks some memory when
 catching an exception:
 http://archives.postgresql.org/pgsql-hackers/2006-02/msg00885.php
 
 So if your problem case involves a whole lot of duplicates then that
 could explain the initial bloat.  However, AFAIK that leakage is in
 a transaction-local memory context, so the space ought to be freed at
 transaction end.  And Linux's malloc does know about giving space back
 to the kernel (unlike some platforms).  So I'm not sure why you're
 seeing persistent bloat.
 
 Can you rewrite the function to not use an EXCEPTION block (perhaps
 a separate SELECT probe for each row --- note this won't be reliable
 if there are concurrent processes making insertions)?  If so, does
 that fix the problem?
 
   regards, tom lane
 
 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?
 
http://www.postgresql.org/docs/faq
 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM]Is it possible to start two instances of postgresql?

2006-06-15 Thread Dan Harris




[EMAIL PROTECTED] wrote:

  

both of the two database are live but use for two different web app.
my company don't want to spend more to buy a new server, so then I think of
to implement both under the same server and one instance..


Just as an anecdote, I am running 30 databases on a single instance and 
it's working quite well.   There may be reasons to run multiple 
instances but it seems like tuning them to cooperate for memory would 
pose some problems - e.g. effective_cache_size.


-Dan


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Which processor runs better for Postgresql?

2006-06-15 Thread Vivek Khera



On Jun 15, 2006, at 1:10 PM, Steve Poe wrote:


Vivek,

Thanks for your feedback.  Which Dell server did you purchase?


I have many many dell rackmounts: 1550, 1650, 1750, 1850, and SC1425  
and throw in a couple of 2450.


I *really* like the 1850 with built-in SCSI RAID.  It is fast enough  
to be a replica of my primary bread and butter database running on a  
beefy opteron system (using Slony-1 replication).


The SC1425 boxes make for good, cheap web front end servers.  We buy  
'em in pairs and load balance them at the network layer using CARP.


At the office we have mostly SC400 series (400, 420, and 430) for our  
servers.  The latest box is an SC430 with dual core pentium D and  
dual SATA drives running software mirror.  It pushes over 20MB/s on  
the disks, which is pretty impressive for the hardware.





The client has a PowerEdge 2600 and they STILL want Dell. Again, if it
were my pocketbook, Dell would not be there.


I lucked out and skipped the 2650 line, apparently :-)

I used the 2450's as my DB servers and they were barely adequate once  
we got beyond our startup phase, and moving them over to Opteron was  
a godsend.   I tried some small opteron systems vendor but had QC  
issues (1 of 5 systems stable), so went with Sun and have not looked  
back.  I still buy Dell's for all other server purposes mainly  
because it is convenient in terms of purchasing and getting support  
(ie, business reasons).


And I don't spend all my time babysitting these boxes, like others  
imply.





The client has a 30GB DB. This is large for me, but probably not with
you. Also, I am advising the client to go to a 10+ disc array (from 3)
and enough RAM to load half the DB into memory.


30GB DB on a 10 disk array seems overkill, considering that the  
smallest disks you're going to get will be 36GB (or perhaps 72Gb by  
now).





smime.p7s
Description: S/MIME cryptographic signature

Re: [PERFORM]Is it possible to start two instances of postgresql?

2006-06-15 Thread Bill Moran

In response to Dan Harris [EMAIL PROTECTED]:
 
  [EMAIL PROTECTED] wrote:
 
  both of the two database are live but use for two different web app.
  my company don't want to spend more to buy a new server, so then I think of
  to implement both under the same server and one instance..
 
 Just as an anecdote, I am running 30 databases on a single instance and 
 it's working quite well.   There may be reasons to run multiple 
 instances but it seems like tuning them to cooperate for memory would 
 pose some problems - e.g. effective_cache_size.

The only reason I can see for doing this is when you need to run two
different versions of PostgreSQL.  Which is what I've been forced to
do on one of our servers.

It works, but it's a pain to admin.  If you can just put all the databases
in one db cluster (is that terminology still correct?) it'll be much
easier.

-- 
Bill Moran
Collaborative Fusion Inc.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[PERFORM] Optimizer internals

I'm not a programmer so understanding the optimizer code is WAY beyond my limits.My question, that I haven't seen answered elsewhere, is WHAT things can affect the choice of an index scan over a sequence scan. I understand that sometimes a sequence scan is faster and that you still have to get the data from the disk but my question relates to an issue we had pop up today.
We have 2 tables, which we'll refer to as laaf and laaf_new. The first table has 220M rows and the second table has 4M rows. What were basically doing is aggregating the records from the first table into the second one at which point we're going to drop the first one. This is the same table I mentioned previously in my post about pg_dump.
laaf_new has one less column than laaf and both were freshly vacuum analyzed after having an index added on a single column (other than the primary key). The query we were doing was as follows:select main_account_status_dim_id, count(*)
from cla_dw.loan_account_agg_fact_newgroup by main_account_status_dim_idorder by main_account_status_dim_id; One of our problems is that we don't have any PGSQL dbas here. All of our guys are DB2 (we're still looking though).
Now I've been told by our DBA that we should have been able to wholy satisfy that query via the indexes.We did regular EXPLAINS on the query with seqscan enabled and disabled and even in our own tests actually running the queries, the results WERE faster with a seq scan than an index scan but the question we were discussing is WHY did it choose the index scan and why is the index scan slower than the sequence scan? He's telling me that DB2 would have been able to do the whole thing with indexes.
EXPLAINS:(the reason for the random_page_cost was that we had the default of 4 in the .conf file and were planning on changing it to 2 anyway to match our other server)set random_page_cost=2;set enable_seqscan=on;
explain select main_account_status_dim_id, count(*)from cla_dw.loan_account_agg_factgroup by main_account_status_dim_idorder by main_account_status_dim_id; Sort (cost=8774054.54..8774054.66 rows=48 width=4)
 Sort Key: main_account_status_dim_id - HashAggregate (cost=8774052.60..8774053.20 rows=48 width=4) - Seq Scan on loan_account_agg_fact (cost=0.00..7609745.40
 rows=232861440 width=4)set random_page_cost=2;set enable_seqscan=off;explain select main_account_status_dim_id, count(*)from cla_dw.loan_account_agg_factgroup by main_account_status_dim_id
order by main_account_status_dim_id; Sort (cost=108774054.54..108774054.66 rows=48 width=4) Sort Key: main_account_status_dim_id - HashAggregate (cost=108774052.60..108774053.20
 rows=48 width=4) - Seq Scan on loan_account_agg_fact (cost=1.00..107609745.40 rows=232861440 width=4)Here's the DDL for the table laaf:When the system is not busy again, I'll run a verbose version. The query was run against each of the tables to compare the results of aggregation change with the new table.
CREATE TABLE cla_dw.loan_account_agg_fact( loan_account_agg_fact_id int8 NOT NULL DEFAULT nextval('loan_account_agg_fact_loan_account_agg_fact_id_seq'::regclass), dw_load_date_id int4 NOT NULL DEFAULT 0,
 servicer_branch_dim_id int4 NOT NULL DEFAULT 0, main_account_status_dim_id int4 NOT NULL DEFAULT 0, product_dim_id int4 NOT NULL DEFAULT 0, next_due_date_id int4 NOT NULL DEFAULT 0, account_balance numeric(15,6) NOT NULL DEFAULT 0,
 loan_count int4 NOT NULL DEFAULT 0, principal numeric(15,6) NOT NULL DEFAULT 0, interest numeric(15,6) NOT NULL DEFAULT 0, fees numeric(15,6) NOT NULL DEFAULT 0, gl_principal numeric(15,6) NOT NULL DEFAULT 0,
 gl_interest numeric(15,6) NOT NULL DEFAULT 0, accruable_principal numeric(15,6) NOT NULL DEFAULT 0, unaccruable_principal numeric(15,6) NOT NULL DEFAULT 0, calculated_principal numeric(15,6) DEFAULT 0,
 current_interest numeric(15,6) NOT NULL DEFAULT 0, past_due_interest numeric(16,5) NOT NULL DEFAULT 0, cash_available numeric(15,6) DEFAULT 0, cash_collected numeric(15,6) DEFAULT 0, cash_collected_date_id int4 DEFAULT 0,
 dw_agg_load_dt timestamp(0) DEFAULT ('now'::text)::timestamp(6) with time zone, cash_available_principal numeric(15,6) DEFAULT 0, cash_available_current numeric(15,6) DEFAULT 0, cash_available_last numeric(15,6) DEFAULT 0,
 cash_available_interest numeric(15,6) DEFAULT 0, cash_available_fees numeric(15,6) DEFAULT 0, cash_not_collected numeric(15,6) DEFAULT 0, number_contacts_total int4 DEFAULT 0, number_broken_commitments int4 DEFAULT 0,
 loc_current_due_total numeric(15,6) DEFAULT 0, loc_current_due_principal numeric(15,6) DEFAULT 0, loc_current_due_interest numeric(15,6) DEFAULT 0, loc_current_due_fees numeric(15,6) DEFAULT 0, loc_past_due_last numeric(15,6) DEFAULT 0,
 loc_past_due_total numeric(15,6) DEFAULT 0, number_made_commitments int4 DEFAULT 0, CONSTRAINT loan_account_agg_fact_pkey PRIMARY KEY (loan_account_agg_fact_id)) WITH OIDS;CREATE INDEX loan_account_agg_fact_main_account_status_dim_id
 ON cla_dw.loan_account_agg_fact USING btree

Re: [PERFORM] Precomputed constants?

2006-06-15 Thread Zoltan Boszormenyi


Jim C. Nasby írta:

On Thu, Jun 15, 2006 at 06:31:02AM +0200, Zoltan Boszormenyi wrote:
  

Jim C. Nasby ?rta:


On Wed, Jun 14, 2006 at 01:30:10PM +0200, B?sz?rm?nyi Zolt?n wrote:
 
  

Replacing random() with a true constant gives me index scan
even if it's hidden inside other function calls. E.g.:
   


The database has no choice but to compute random() for every row; it's
marked VOLATILE.
 
  

I see now, docs about CREATE FUNCTION mentions random(),
currval() and timeofday() as examples for VOLATILE.
But where in the documentation can I find this info about all
built-in functions? Thanks.



No, but you can query pg_proc for that info. The docs should have info
about that table.
  


Thanks!

# select proname,provolatile from pg_proc where proname='random';
proname | provolatile
-+-
random  | v
(1 sor)

# select distinct provolatile from pg_proc;
provolatile
-
i
s
v
(3 sor)

If I get this right, IMMUTABLE/STABLE/VOLATILE
are indicated with their initials.

Best regards,
Zoltán Böszörményi


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] Optimizer internals

On Thu, 2006-06-15 at 14:05 -0400, John Vincent wrote:
 Now I've been told by our DBA that we should have been able to wholy
 satisfy that query via the indexes.

DB2 can satisfy the query using only indexes because DB2 doesn't do
MVCC.

Although MVCC is generally a win in terms of making the database easier
to use and applications less brittle, it also means that the database
must inspect the visibility information for each row before it can
answer a query.  For most types of queries this isn't a big deal, but
for count(*) type queries, it slows things down.

Since adding the visibility information to indexes would make them
significantly more expensive to use and maintain, it isn't done.
Therefore, each row has to be fetched from the main table anyway.

Since in this particular query you are counting all rows of the
database, PG must fetch each row from the main table regardless, so the
sequential scan is much faster because it avoids traversing the index
and performing random read operations.

-- Mark Lewis

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Optimizer internals

On 6/15/06, Mark Lewis [EMAIL PROTECTED] wrote:
DB2 can satisfy the query using only indexes because DB2 doesn't doMVCC.Although MVCC is generally a win in terms of making the database easierto use and applications less brittle, it also means that the database
must inspect the visibility information for each row before it cananswer a query.For most types of queries this isn't a big deal, butfor count(*) type queries, it slows things down.
Mark,Thanks for the answer. My DBAs just got this look on thier face when I showed. It's not like the couldn't have investigated this information themselves but I think the light finally came on.One question that we came up with is how does this affect other aggregate functions like MAX,MIN,SUM and whatnot? Being that this is our data warehouse, we use these all the time. As I've said previously, I didn't know a human could generate some of the queries we've passed through this system.
Since adding the visibility information to indexes would make themsignificantly more expensive to use and maintain, it isn't done.
Therefore, each row has to be fetched from the main table anyway.Since in this particular query you are counting all rows of thedatabase, PG must fetch each row from the main table regardless, so thesequential scan is much faster because it avoids traversing the index
and performing random read operations.-- Mark Lewis

Re: [PERFORM] Optimizer internals

On Thu, 2006-06-15 at 14:46 -0400, John Vincent wrote:

 One question that we came up with is how does this affect other
 aggregate functions like MAX,MIN,SUM and whatnot? Being that this is
 our data warehouse, we use these all the time. As I've said
 previously, I didn't know a human could generate some of the queries
 we've passed through this system. 

Previously, MIN and MAX would also run slowly, for the same reason as
COUNT(*).  But there really isn't a need for that, since you can still
get a big speedup by scanning the index in order, looking up each row
and stopping as soon as you find a visible one.

This has been fixed so newer versions of PG will run quickly and use the
index for MIN and MAX.  I don't remember which version had that change;
it might not be until 8.2.  You can dig the archives to find out for
sure. 

For older versions of PG before the fix, you can make MIN and MAX run
quickly by rewriting them in the following form:

SELECT column FROM table ORDER BY column LIMIT 1;

Unfortunately SUM is in the same boat as COUNT; in order for it to
return a meaningful result it must inspect visibility information for
all of the rows.

-- Mark

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Optimizer internals

On 6/15/06, Mark Lewis [EMAIL PROTECTED] wrote:
Unfortunately SUM is in the same boat as COUNT; in order for it toreturn a meaningful result it must inspect visibility information forall of the rows.-- MarkWe'll this is interesting news to say the least. We went with PostgreSQL for our warehouse because we needed the advanced features that MySQL didn't have at the time (views/sprocs).
It sounds like we almost need another fact table for the places that we do SUM (which is not a problem just an additional map. If I'm interpreting this all correctly, we can't force PG to bypass a sequence scan even if we know our data is stable because of the MVCC aspect. In our case, as with most warehouses (except those that do rolling loads during the day), we only write data to it for about 5 hours at night in batch. 
Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible.
If anyone from the bizgres team is watching, have they done any work in this area? Thanks.John

Re: [PERFORM] Optimizer internals

2006-06-15 Thread Scott Marlowe

On Thu, 2006-06-15 at 14:21, John Vincent wrote:
 On 6/15/06, Mark Lewis [EMAIL PROTECTED] wrote:
 Unfortunately SUM is in the same boat as COUNT; in order for
 it to
 return a meaningful result it must inspect visibility
 information for
 all of the rows.
 
 -- Mark
 
 We'll this is interesting news to say the least. We went with
 PostgreSQL for our warehouse because we needed the advanced features
 that MySQL didn't have at the time (views/sprocs). 
 
 It sounds like we almost need another fact table for the places that
 we do SUM (which is not a problem just an additional map. If I'm
 interpreting this all correctly, we can't force PG to bypass a
 sequence scan even if we know our data is stable because of the MVCC
 aspect. In our case, as with most warehouses (except those that do
 rolling loads during the day), we only write data to it for about 5
 hours at night in batch. 
 
 Any suggestions? FYI the original question wasn't meant as a poke at
 comparing PG to MySQL to DB2. I'm not making an yvalue judgements
 either way. I'm just trying to understand how we can use it the best
 way possible. 
 
 If anyone from the bizgres team is watching, have they done any work
 in this area? 

This might help:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

Since you're doing a data warehouse, I would think materialized views
would be a natural addition anyway.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Optimizer internals

Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible.
If anyone from the bizgres team is watching, have they done any work in this area? Thanks.John

Actually we just thought about something. With PG, we can create
an index that is a SUM of the column where indexing, no? We're going to
test this in a few hours. Would that be able to be satisfied by an
index scan?
Also, we're looking at the link provided for the materialized views in PG.Thanks.

[PERFORM] SAN performance mystery

2006-06-15 Thread Tim Allen

We have a customer who are having performance problems. They have a 
large (36G+) postgres 8.1.3 database installed on an 8-way opteron with 
8G RAM, attached to an EMC SAN via fibre-channel (I don't have details 
of the EMC SAN model, or the type of fibre-channel card at the moment). 
They're running RedHat ES3 (which means a 2.4.something Linux kernel).


They are unhappy about their query performance. We've been doing various 
things to try to work out what we can do. One thing that has been 
apparent is that autovacuum has not been able to keep the database 
sufficiently tamed. A pg_dump/pg_restore cycle reduced the total 
database size from 81G to 36G. Performing the restore took about 23 hours.


We tried restoring the pg_dump output to one of our machines, a 
dual-core pentium D with a single SATA disk, no raid, I forget how much 
RAM but definitely much less than 8G. The restore took five hours. So it 
would seem that our machine, which on paper should be far less 
impressive than the customer's box, does more than four times the I/O 
performance.


To simplify greatly - single local SATA disk beats EMC SAN by factor of 
four.


Is that expected performance, anyone? It doesn't sound right to me. Does 
anyone have any clues about what might be going on? Buggy kernel 
drivers? Buggy kernel, come to think of it? Does a SAN just not provide 
adequate performance for a large database?


I'd be grateful for any clues anyone can offer,

Tim


begin:vcard
fn:Tim Allen
n:Allen;Tim
email;internet:[EMAIL PROTECTED]
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] SAN performance mystery

2006-06-15 Thread Scott Marlowe

On Thu, 2006-06-15 at 16:50, Tim Allen wrote:
 We have a customer who are having performance problems. They have a 
 large (36G+) postgres 8.1.3 database installed on an 8-way opteron with 
 8G RAM, attached to an EMC SAN via fibre-channel (I don't have details 
 of the EMC SAN model, or the type of fibre-channel card at the moment). 
 They're running RedHat ES3 (which means a 2.4.something Linux kernel).
 
 They are unhappy about their query performance. We've been doing various 
 things to try to work out what we can do. One thing that has been 
 apparent is that autovacuum has not been able to keep the database 
 sufficiently tamed. A pg_dump/pg_restore cycle reduced the total 
 database size from 81G to 36G. Performing the restore took about 23 hours.

Do you have the ability to do any simple IO performance testing, like
with bonnie++ (the old bonnie is not really capable of properly testing
modern equipment, but bonnie++ will give you some idea of the throughput
of the SAN)  Or even just timing a dd write to the SAN?

 We tried restoring the pg_dump output to one of our machines, a 
 dual-core pentium D with a single SATA disk, no raid, I forget how much 
 RAM but definitely much less than 8G. The restore took five hours. So it 
 would seem that our machine, which on paper should be far less 
 impressive than the customer's box, does more than four times the I/O 
 performance.
 
 To simplify greatly - single local SATA disk beats EMC SAN by factor of 
 four.
 
 Is that expected performance, anyone? It doesn't sound right to me. Does 
 anyone have any clues about what might be going on? Buggy kernel 
 drivers? Buggy kernel, come to think of it? Does a SAN just not provide 
 adequate performance for a large database?

Yes, this is not uncommon.  It is very likely that your SATA disk is
lying about fsync.

What kind of backup are you using?  insert statements or copy
statements?  If insert statements, then the difference is quite
believable.  If copy statements, less so.

Next time, on their big server, see if you can try a restore with fsync
turned off and see if that makes the restore faster.  Note you should
turn fsync back on after the restore, as running without it is quite
dangerous should you suffer a power outage.

How are you mounting to the EMC SAN?  NFS, iSCSI? Other?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] SAN performance mystery

2006-06-15 Thread Brian Hurt


Tim Allen wrote:

We have a customer who are having performance problems. They have a 
large (36G+) postgres 8.1.3 database installed on an 8-way opteron 
with 8G RAM, attached to an EMC SAN via fibre-channel (I don't have 
details of the EMC SAN model, or the type of fibre-channel card at the 
moment). They're running RedHat ES3 (which means a 2.4.something Linux 
kernel).


They are unhappy about their query performance. We've been doing 
various things to try to work out what we can do. One thing that has 
been apparent is that autovacuum has not been able to keep the 
database sufficiently tamed. A pg_dump/pg_restore cycle reduced the 
total database size from 81G to 36G. Performing the restore took about 
23 hours.


We tried restoring the pg_dump output to one of our machines, a 
dual-core pentium D with a single SATA disk, no raid, I forget how 
much RAM but definitely much less than 8G. The restore took five 
hours. So it would seem that our machine, which on paper should be far 
less impressive than the customer's box, does more than four times the 
I/O performance.


To simplify greatly - single local SATA disk beats EMC SAN by factor 
of four.


Is that expected performance, anyone? It doesn't sound right to me. 
Does anyone have any clues about what might be going on? Buggy kernel 
drivers? Buggy kernel, come to think of it? Does a SAN just not 
provide adequate performance for a large database?


I'd be grateful for any clues anyone can offer,



I'm actually in a not dissimiliar position here- I was seeing the 
performance of Postgres going to an EMC Raid over iSCSI running at about 
1/2 the speed of a lesser machine hitting a local SATA drive.  That was, 
until I noticed that the SATA drive Postgres installation had fsync 
turned off, and the EMC version had fsync turned on.  Turning fsync on 
on the SATA drive dropped it's performance to being about 1/4th that of EMC.


Moral of the story: make sure you're comparing apples to apples.

Brian


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] SAN performance mystery

On 6/15/06, Tim Allen [EMAIL PROTECTED] wrote:
snippedIs that expected performance, anyone? It doesn't sound right to me. Doesanyone have any clues about what might be going on? Buggy kerneldrivers? Buggy kernel, come to think of it? Does a SAN just not provide
adequate performance for a large database?I'd be grateful for any clues anyone can offer,TimTim,Here are the areas I would look at first if we're considering hardware to be the problem:
HBA and driver: Since this is a Intel/Linux system, the HBA is PROBABLY a qlogic. I would need to know the SAN model to see what the backend of the SAN is itself. EMC has some FC-attach models that actually have SATA disks underneath. You also might want to look at the cache size of the controllers on the SAN.
- Something also to note is that EMC provides a add-on called PowerPath for load balancing multiple HBAs. If they don't have this, it might be worth investigating. - As with anything, disk layout is important. With the lower end IBM SAN (DS4000) you actually have to operate on physical spindle level. On our 4300, when I create a LUN, I select the exact disks I want and which of the two controllers are the preferred path. On our DS6800, I just ask for storage. I THINK all the EMC models are the ask for storage type of scenario. However with the 6800, you select your storage across extent pools.
Have they done any benchmarking of the SAN outside of postgres? Before we settle on a new LUN configuration, we always do the dd,umount,mount,dd routine. It's not a perfect test for databases but it will help you catch GROSS performance issues.
SAN itself: - Could the SAN be oversubscribed? How many hosts and LUNs total do they have and what are the queue_depths for those hosts? With the qlogic card, you can set the queue depth in the BIOS of the adapter when the system is booting up. CTRL-Q I think. If the system has enough local DASD to relocate the database internally, it might be a valid test to do so and see if you can isolate the problem to the SAN itself.
PG itself: If you think it's a pgsql configuration, I'm guessing you already
configured postgresql.conf to match thiers (or at least a fraction of
thiers since the memory isn't the same?). What about loading a from-scratch config file and restarting the tuning process?
Just a dump of my thought process from someone who's been spending too much time tuning his SAN and postgres lately.

Re: [PERFORM] Optimizer internals

On Thu, Jun 15, 2006 at 03:43:09PM -0400, John Vincent wrote:
 Any suggestions? FYI the original question wasn't meant as a poke at
 comparing PG to MySQL to DB2. I'm not making an yvalue judgements either
 way. I'm just trying to understand how we can use it the best way possible.
 
 If anyone from the bizgres team is watching, have they done any work in
 this area?
 
 Thanks.
 John
 
 
 Actually we just thought about something. With PG, we can create an index
 that is a SUM of the column where indexing, no? We're going to test this in
 a few hours. Would that be able to be satisfied by an index scan?
 
 Also, we're looking at the link provided for the materialized views in PG.
 
 Thanks.

decibel=# create index test on i ( sum(i) );
ERROR:  cannot use aggregate function in index expression
decibel=# 

BTW, there have been a number of proposals to negate the effect of not
having visibility info in indexes. Unfortunately, none of them have come
to fruition yet, mostly because it's a very difficult problem to solve.
But it is something that the community would like to see happen.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] SAN performance mystery

Brian Hurt [EMAIL PROTECTED] writes:
 Tim Allen wrote:
 To simplify greatly - single local SATA disk beats EMC SAN by factor 
 of four.

 I'm actually in a not dissimiliar position here- I was seeing the 
 performance of Postgres going to an EMC Raid over iSCSI running at about 
 1/2 the speed of a lesser machine hitting a local SATA drive.  That was, 
 until I noticed that the SATA drive Postgres installation had fsync 
 turned off, and the EMC version had fsync turned on.  Turning fsync on 
 on the SATA drive dropped it's performance to being about 1/4th that of EMC.

And that's assuming that the SATA drive isn't configured to lie about
write completion ...

I agree with Brian's suspicion that the SATA drive isn't properly
fsync'ing to disk, resulting in bogusly high throughput.  However,
ISTM a well-configured SAN ought to be able to match even the bogus
throughput, because it should be able to rely on battery-backed
cache to hold written blocks across a power failure, and hence should
be able to report write-complete as soon as it's got the page in cache
rather than having to wait till it's really down on magnetic platter.
Which is what the SATA drive is doing ... only it can't keep the promise
it's making for lack of any battery backup on its on-board cache.

So I'm thinking *both* setups may be misconfigured.  Or else you forgot
to buy the battery-backed-cache option on the SAN hardware.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] Optimizer internals

decibel=# create index test on i ( sum(i) );ERROR:cannot use aggregate function in index _expression_
decibel=#BTW, there have been a number of proposals to negate the effect of nothaving visibility info in indexes. Unfortunately, none of them have cometo fruition yet, mostly because it's a very difficult problem to solve.
But it is something that the community would like to see happen.--Jim C. Nasby, Sr. Engineering Consultant
[EMAIL PROTECTED]Pervasive Software
http://pervasive.comwork: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Yeah we got the same thing when we tried it.
I thought about the whole thing on the way home and the downside is that we might have to ditch pgsql.As far as implementing it, it might make sense to translate READ UNCOMMITTED to that new functionality. If the default isolation level stays the current level, the people who need it can use it via WITH UR or somesuch.
I know it's not that easy but it's an idea. I'm also thinking that the table inheritance we're going to be taking advantage of in 8.1 on the new server might make the sequence scan less of an issue. The only reason the sequence scan really blows is that we have a single table with 220M rows and growing.

Re: [PERFORM] SAN performance mystery