[PERFORM] Multiple Order By Criteria

[J]

I'm trying to query a table with 250,000+ rows. My 
query requires I provide 5 colums in my "order by" clause:
 
select   column
from table 
where 
 column >= '2004-3-22 0:0:0'order by 

    ds.receipt desc,
    ds.carrier_id asc,
    ds.batchnum asc,
    encounternum asc,
    ds.encounter_id ASC
limit 100 offset 0
 
I have an index built for each of these columns in 
my order by clause. This query takes an unacceptable amount of time to execute. 
Here are the results of the explain:
 
Limit  (cost=229610.78..229611.03 rows=100 
width=717)  ->  Sort  (cost=229610.78..230132.37 
rows=208636 width=717)    Sort Key: 
receipt, carrier_id, batchnum, encounternum, 
encounter_id    ->  Seq Scan 
on detail_summary ds  (cost=0.00..22647.13 rows=208636 
width=717)  
Filter: (receipt >= '2004-03-22'::date)
 
When I have the order by just have 1 criteria, it's 
fine (just ds.receipt DESC)
 
Limit  (cost=0.00..177.71 rows=100 
width=717)  ->  Index Scan Backward using 
detail_summary_receipt_id_idx on detail_summary ds  (cost=0.00..370756.84 
rows=208636 width=717)    Index Cond: 
(receipt >= '2004-03-22'::date)
 
I've increased my work_mem to up to 256meg with no 
speed increase. I think there's something here I just don't 
understand.
 
How do I make this go fast ?

 
 
 
 

Re: [PERFORM] Multiple Order By Criteria

[J]

I created the index, in order. Did a vacuum analyze on the table and my 
explain still says:


Limit  (cost=229610.78..229611.03 rows=100 width=717)
 ->  Sort  (cost=229610.78..230132.37 rows=208636 width=717)
   Sort Key: receipt, carrier_id, batchnum, encounternum, encounter_id
   ->  Seq Scan on detail_summary ds  (cost=0.00..22647.13 rows=208636 
width=717)

 Filter: (receipt >= '2004-03-22'::date)


So, for fun I did
set enable_seqscan to off

But that didn't help. For some reason, the sort wants to do a seq scan and 
not use my super new index.


Am I doing something wrong ?

- Original Message - 
From: "Josh Berkus" 

To: 
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, January 17, 2006 5:25 PM
Subject: Re: [PERFORM] Multiple Order By Criteria



J,


I have an index built for each of these columns in my order by clause.
This query takes an unacceptable amount of time to execute. Here are the
results of the explain:


You need a single index which has all five columns, in order.




--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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




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

  http://archives.postgresql.org

Re: [PERFORM] Multiple Order By Criteria

[J]

I created the index like this:

CREATE INDEX rcbee_idx
 ON detail_summary
 USING btree
 (receipt, carrier_id, batchnum, encounternum, encounter_id);

Is this correct ?

How do I make a reverse opclass ?

- Original Message - 
From: "Stephan Szabo" <[EMAIL PROTECTED]>

To: "Josh Berkus" 
Cc: ; <[EMAIL PROTECTED]>
Sent: Tuesday, January 17, 2006 5:40 PM
Subject: Re: [PERFORM] Multiple Order By Criteria




On Tue, 17 Jan 2006, Josh Berkus wrote:


J,

> I have an index built for each of these columns in my order by clause.
> This query takes an unacceptable amount of time to execute. Here are 
> the

> results of the explain:

You need a single index which has all five columns, in order.


I think he'll also need a reverse opclass for the first column in the
index or for the others since he's doing desc, asc, asc, asc, asc.

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




---(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] Multiple Order By Criteria

[J]

I've read all of this info, closely. I wish when I was searching for an
answer for my problem these pages came up. Oh well.

I am getting an idea of what I need to do to make this work well. I was
wondering if there is more information to read on how to implement this
solution in a more simple way. Much of what's written seems to be towards an
audience that should understand certain things automatically.
- Original Message - 
From: "Stephan Szabo" <[EMAIL PROTECTED]>

To: <[EMAIL PROTECTED]>
Cc: "Josh Berkus" ; 
Sent: Tuesday, January 17, 2006 6:39 PM
Subject: Re: [PERFORM] Multiple Order By Criteria




On Tue, 17 Jan 2006 [EMAIL PROTECTED] wrote:


I created the index like this:

CREATE INDEX rcbee_idx
  ON detail_summary
  USING btree
  (receipt, carrier_id, batchnum, encounternum, encounter_id);

Is this correct ?


That would work if you were asking for all the columns ascending or
descending, but we don't currently use it for mixed orders.


How do I make a reverse opclass ?


There's some information at the following:
http://archives.postgresql.org/pgsql-novice/2005-10/msg00254.php
http://archives.postgresql.org/pgsql-general/2005-01/msg00121.php
http://archives.postgresql.org/pgsql-general/2004-06/msg00565.php

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




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

Re: [PERFORM] Multiple Order By Criteria

[J]

I have the answer I've been looking for and I'd like to share with all. 
After help from you guys, it appeared that the real issue was using an index 
for my order by X DESC clauses. For some reason that doesn't make good 
sense, postgres doesn't support this, when it kinda should automatically.


Take the following end of an SQL statement.

order by
   col1 DESC
   col2 ASC
   col3 ASC

The first thing I learned is that you need an index that contains all these 
columns in it, in this order. If one of them has DESC then you have to 
create a function / operator class for each data type, in this case let's 
assume it's an int4. So, first thing you do is create a function that you're 
going to use in your operator:


create function
   int4_revcmp(int4,int4)  // --> cal the function whatever you want
   returns int4
   as 'select $2 - $1'
language sql;

Then you make your operator class.
CREATE OPERATOR CLASS int4_revop
 FOR TYPE int4 USING btree AS
 OPERATOR1   > ,
 OPERATOR2   >= ,
 OPERATOR3   = ,
 OPERATOR4   <= ,
 OPERATOR5   < ,
 FUNCTION1   int4_revcmp(int4, int4); // --> must be 
the name of your function you created.


Then when you make your index

create index rev_idx on table
   using btree(
   col1 int4_revop,// --> must be name of operator class you 
defined.

   col2,
   col3
);

What I don't understand is how to make this function / operator class work 
with a text datatype. I tried interchanging the int4 with char and text and 
postgres didn't like the (as 'select $2 - $1') in the function, which I can 
kinda understand. Since I'm slighlty above my head at this point, I don't 
really know how to do it. Does any smart people here know how ?



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

Re: [PERFORM] Multiple Order By Criteria

[J]

Here's some C to use to create the operator classes, seems to work ok.
---

#include "postgres.h"
#include 
#include "fmgr.h"
#include "utils/date.h"

/* For date sorts */

PG_FUNCTION_INFO_V1(ddd_date_revcmp);

Datum   ddd_date_revcmp(PG_FUNCTION_ARGS){
   DateADT arg1=PG_GETARG_DATEADT(0);
   DateADT arg2=PG_GETARG_DATEADT(1);

   PG_RETURN_INT32(arg2 - arg1);
}

/* For integer sorts */

PG_FUNCTION_INFO_V1(ddd_int_revcmp);

Datum   ddd_int_revcmp(PG_FUNCTION_ARGS){
   int32   arg1=PG_GETARG_INT32(0);
   int32   arg2=PG_GETARG_INT32(1);

   PG_RETURN_INT32(arg2 - arg1);
}

/* For string sorts */

PG_FUNCTION_INFO_V1(ddd_text_revcmp);

Datum   ddd_text_revcmp(PG_FUNCTION_ARGS){
   text*   arg1=PG_GETARG_TEXT_P(0);
   text*   arg2=PG_GETARG_TEXT_P(1);

   PG_RETURN_INT32(strcmp((char*)VARDATA(arg2),(char*)VARDATA(arg1)));
}


/*
create function ddd_date_revcmp(date,date) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_date_revcmp' LANGUAGE C STRICT;
create function ddd_int_revcmp(int4,int4) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_int_revcmp' LANGUAGE C STRICT;
create function ddd_text_revcmp(text,text) returns int4 as 
'/data/postgres/contrib/cmplib.so', 'ddd_text_revcmp' LANGUAGE C STRICT;

*/

- Original Message - 
From: "Stephan Szabo" <[EMAIL PROTECTED]>

To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 18, 2006 2:24 PM
Subject: Re: [PERFORM] Multiple Order By Criteria



On Wed, 18 Jan 2006 [EMAIL PROTECTED] wrote:


Could you explain to me how do create this operator class for a text data
type ? I think it will give me more of an understanding of what's going 
on

if I could see this example.


Using an SQL function (mostly because I'm too lazy to look up the C call
syntax) I think it'd be something like:

create function bttextrevcmp(text, text) returns int4 as
'select bttextcmp($2, $1)' language 'sql';

CREATE OPERATOR CLASS text_revop
 FOR TYPE text USING btree AS
 OPERATOR1   > ,
 OPERATOR2   >= ,
 OPERATOR3   = ,
 OPERATOR4   <= ,
 OPERATOR5   < ,
 FUNCTION1   bttextrevcmp(text,text);

I believe bttextcmp is the standard text btree operator class helper
function, so we call it with reverse arguments to try to flip its results
(I think -bttextcmp($1,$2) would also work).




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

[PERFORM] Query optimization with X Y JOIN

[J]

Hey guys, how u been. This is quite a newbie 
question, but I need to ask it. I'm trying to wrap my mind around the syntax of 
join and why and when to use it. I understand the concept of making a query go 
faster by creating indexes, but it seems that when I want data from multiple 
tables that link together the query goes slow. The slow is typically due to 
expensive nested loops. The reason is, all my brain understands is:
 
select
    tablea.data
    tableb.data
    tablec.data
from
    tablea
    tableb
    tablec
where
    tablea.pri_key = 
tableb.foreign_key AND
    tableb.pri_key = 
tablec.foreign_key AND...
 
From what I read, it seems you can use inner/outer 
right/left join on (bla) but when I see syntax examples I see that sometimes 
tables are omitted from the 'from' section of the query and other times, no. 
Sometimes I see that the join commands are nested and others, no and sometimes I 
see joins syntax that only applies to one table. From what I understand join can 
be used to tell the database the fast way to murge table data together to get 
results by specifiying the table that has the primary keys and the table that 
has the foreign keys.
 
I've read all through the postgres docs on this 
command and I'm still left lost. Can someone please explain to me in simple 
language how to use these commands or provide me with a link. I need it to live 
right now. Thanx.
 
    

Re: [PERFORM] Query optimization with X Y JOIN

[J]

If I want my database to go faster, due to X then I would think that the 
issue is about performance. I wasn't aware of a paticular constraint on X.


I have more that a rudementary understanding of what's going on here, I was 
just hoping that someone could shed some light on the basic principal of 
this JOIN command and its syntax. Most people I ask, don't give me straight 
answers and what I have already read on the web is not very helpful thus 
far.
- Original Message - 
From: "Craig A. James" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, January 26, 2006 11:12 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN


First, this isn't really the right place to ask -- this forum is about 
performance, not SQL syntax.


Second, this isn't a question anyone can answer in a reasonable length of 
time.  What you're asking for usually is taught in a class on relational 
database theory, which is typically a semester or two in college.


If you really need a crash course, dig around on the web for terms like 
"SQL Tutorial".


Good luck,
Craig


[EMAIL PROTECTED] wrote:
Hey guys, how u been. This is quite a newbie question, but I need to ask 
it. I'm trying to wrap my mind around the syntax of join and why and when 
to use it. I understand the concept of making a query go faster by 
creating indexes, but it seems that when I want data from multiple tables 
that link together the query goes slow. The slow is typically due to 
expensive nested loops. The reason is, all my brain understands is:

 select
tablea.data
tableb.data
tablec.data
from
tablea
tableb
tablec
where
tablea.pri_key = tableb.foreign_key AND
tableb.pri_key = tablec.foreign_key AND...
 From what I read, it seems you can use inner/outer right/left join on 
(bla) but when I see syntax examples I see that sometimes tables are 
omitted from the 'from' section of the query and other times, no. 
Sometimes I see that the join commands are nested and others, no and 
sometimes I see joins syntax that only applies to one table. From what I 
understand join can be used to tell the database the fast way to murge 
table data together to get results by specifiying the table that has the 
primary keys and the table that has the foreign keys.
 I've read all through the postgres docs on this command and I'm still 
left lost. Can someone please explain to me in simple language how to use 
these commands or provide me with a link. I need it to live right now. 
Thanx.




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




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

  http://archives.postgresql.org

Re: [PERFORM] Query optimization with X Y JOIN

[J]

Yes, that helps a great deal. Thank you so much.

- Original Message - 
From: "Richard Huxton" 

To: <[EMAIL PROTECTED]>
Cc: 
Sent: Thursday, January 26, 2006 11:47 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN



[EMAIL PROTECTED] wrote:
If I want my database to go faster, due to X then I would think that the 
issue is about performance. I wasn't aware of a paticular constraint on 
X.


You haven't asked a performance question yet though.

I have more that a rudementary understanding of what's going on here, I 
was just hoping that someone could shed some light on the basic principal 
of this JOIN command and its syntax. Most people I ask, don't give me 
straight answers and what I have already read on the web is not very 
helpful thus far.


OK - firstly it's not a JOIN command. It's a SELECT query that happens to 
join (in your example) three tables together. The syntax is specified in 
the SQL reference section of the manuals, and I don't think it's different 
from the standard SQL spec here.


A query that joins two or more tables (be they real base-tables, views or 
sub-query result-sets) produces the product of both. Normally you don't 
want this so you apply constraints to that join (table_a.col1 = 
table_b.col2).


In some cases you want all the rows from one side of a join, whether or 
not you get a match on the other side of the join. This is called an outer 
join and results in NULLs for all the columns on the "outside" of the 
join. A left-join returns all rows from the table on the left of the join, 
a right-join from the table on the right of it.


When planning a join, the planner will try to estimate how many matches it 
will see on each side, taking into account any extra constraints (you 
might want only some of the rows in table_a anyway). It then decides 
whether to use any indexes on the relevant column(s).


Now, if you think the planner is making a mistake we'll need to see the 
output of EXPLAIN ANALYSE for the query and will want to know that you've 
vacuumed and analysed the tables in question.


Does that help at all?
--
  Richard Huxton
  Archonet Ltd

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




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

Re: [PERFORM] Overusing 1 CPU

[J. Roeleveld]

On Wednesday 02 June 2010 13:37:37 Mozzi wrote:
> Hi
> 
> Thanx mate Create Index seems to be the culprit.
> Is it normal to just use 1 cpu tho?

If it is a single-threaded process, then yes.
And a "Create index" on a single table will probably be single-threaded.

If you now start a "create index" on a different table, a different CPU should 
be used for that.

> 
> Mozzi
> 
> On Wed, 2010-06-02 at 12:24 +0100, Matthew Wakeling wrote:
> > On Wed, 2 Jun 2010, Mozzi wrote:
> > > This box is basically adle @ the moment as it is still in testing yet
> > > top shows high usage on just 1 of the cores.
> >
> > First port of call: What process is using the CPU? Run top on a fairly
> > wide terminal and use the "c" button to show the full command line.
> >
> > Matthew
> 

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How filesystems matter with PostgreSQL

[J. Roeleveld]

On Friday 04 June 2010 14:17:35 Jon Schewe wrote:
> Some interesting data about different filesystems I tried with
> PostgreSQL and how it came out.
> 
> I have an application that is backed in postgres using Java JDBC to
> access it. The tests were all done on an opensuse 11.2 64-bit machine,
> on the same hard drive (just ran mkfs between each test) on the same
> input with the same code base. All filesystems were created with the
> default options.
> 
> XFS (logbufs=8): ~4 hours to finish
> ext4: ~1 hour 50 minutes to finish
> ext3: 15 minutes to finish
> ext3 on LVM: 15 minutes to finish
> 

Hi Jon,

Any chance you can do the same test with reiserfs?

Thanks,

Joost

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Slow function in queries SELECT clause.

[Davor J.]

I think I have read what is to be read about queries being prepared in 
plpgsql functions, but I still can not explain the following, so I thought 
to post it here:

Suppose 2 functions: factor(int,int) and offset(int, int).
Suppose a third function: convert(float,int,int) which simply returns 
$1*factor($2,$3)+offset($2,$3)
All three functions are IMMUTABLE.

Very simple, right? Now I have very fast AND very slow executing queries on 
some 150k records:

VERY FAST (half a second):

SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;

VERY SLOW (a minute):

SELECT convert(data, 1, 2) FROM tbl_data;

The slowness cannot be due to calling a function 150k times. If I define 
convert2(float,int,int) to return a constant value, then it executes in 
about a second. (still half as slow as the VERY FAST query).

I assume that factor and offset are cached in the VERY FAST query, and not 
in the slow one? If so, why not and how can I "force" it? Currently I need 
only one function for conversions.

Regards,
Davor 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow function in queries SELECT clause.

[Davor J.]

M 
(SELECT sens_chan_data_timestamp AS timestamp, sens_chan_data_data AS 
data_from_tree_id_70 FROM tbl_sensor_channel_data WHERE tree_id = 70 AND 
sens_chan_data_timestamp >= '2008-06-11T00:00:00' AND sens_chan_data_timestamp 
<= '2008-06-18T00:00:00' ) AS "70" 

 ORDER BY timestamp;

EXPLAIN ANALYSE SELECT timestamp,

"Sort  (cost=176058.28..176321.92 rows=105456 width=12) (actual 
time=630.350..669.843 rows=150678 loops=1)"
"  Sort Key: tbl_sensor_channel_data.sens_chan_data_timestamp"
"  Sort Method:  external sort  Disk: 3240kB"
"  ->  Bitmap Heap Scan on tbl_sensor_channel_data  (cost=3005.29..167259.94 
rows=105456 width=12) (actual time=35.498..399.726 rows=150678 loops=1)"
"Recheck Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= 
'2008-06-11 00:00:00'::timestamp without time zone) AND 
(sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time 
zone))"
"->  Bitmap Index Scan on tbl_sensor_channel_data_pkey  
(cost=0.00..2978.92 rows=105456 width=0) (actual time=27.433..27.433 
rows=150678 loops=1)"
"  Index Cond: ((tree_id = 70) AND (sens_chan_data_timestamp >= 
'2008-06-11 00:00:00'::timestamp without time zone) AND 
(sens_chan_data_timestamp <= '2008-06-18 00:00:00'::timestamp without time 
zone))"
"Total runtime: 694.968 ms"





"Szymon Guz"  wrote in message 
news:aanlktimb8-0kzrrbddqgxnz5tjdgf2t3ffbu2lvx-...@mail.gmail.com...



  2010/6/19 Davor J. 

I think I have read what is to be read about queries being prepared in
plpgsql functions, but I still can not explain the following, so I thought
to post it here:

Suppose 2 functions: factor(int,int) and offset(int, int).
Suppose a third function: convert(float,int,int) which simply returns
$1*factor($2,$3)+offset($2,$3)
All three functions are IMMUTABLE.

Very simple, right? Now I have very fast AND very slow executing queries on
some 150k records:

VERY FAST (half a second):

SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;

VERY SLOW (a minute):

SELECT convert(data, 1, 2) FROM tbl_data;

The slowness cannot be due to calling a function 150k times. If I define
convert2(float,int,int) to return a constant value, then it executes in
about a second. (still half as slow as the VERY FAST query).

I assume that factor and offset are cached in the VERY FAST query, and not
in the slow one? If so, why not and how can I "force" it? Currently I need
only one function for conversions.

Regards,
Davor






  Hi,
  show us the code of those two functions and explain analyze of those queries.


  regards
  Szymon Guz

Re: [PERFORM] Slow function in queries SELECT clause.

[Davor J.]

Thanks Tom,

Your concepts of "inlining" and "black box" really cleared things up for me. 
With fnc_unit_convert() written in SQL and declared as STABLE I indeed have 
fast performance now.

I appreciate the note on the IMMUTABLE part. The table contents should not 
change in a way to affect the functions. So, as far as I understand the 
Postgres workings, this shouldn't pose a problem.

Regards,
Davor

"Tom Lane"  wrote in message 
news:25116.1277047...@sss.pgh.pa.us...
> "Davor J."  writes:
>> Suppose 2 functions: factor(int,int) and offset(int, int).
>> Suppose a third function: convert(float,int,int) which simply returns
>> $1*factor($2,$3)+offset($2,$3)
>> All three functions are IMMUTABLE.
>
> You should write the third function as a SQL function, which'd allow it
> to be inlined.
>
>> VERY FAST (half a second):
>> 
>> SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
>
> In this case both factor() calls are folded to constants, hence executed
> only once.
>
>> VERY SLOW (a minute):
>> 
>> SELECT convert(data, 1, 2) FROM tbl_data;
>
> Without inlining, there's no hope of any constant-folding here.
> The optimizer just sees the plpgsql function as a black box and
> can't do anything with it.
>
> BTW, your later mail shows that the factor() functions are not really
> IMMUTABLE, since they select from tables that presumably are subject to
> change.  The "correct" declaration would be STABLE.  If you're relying
> on constant-folding to get reasonable application performance, you're
> going to have to continue to mislabel them as IMMUTABLE; but be aware
> that you're likely to have issues any time you do change the table
> contents.  The changes won't get reflected into existing query plans.
>
> regards, tom lane
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow function in queries SELECT clause.

[Davor J.]

> "Tom Lane"  wrote in message 
> news:25116.1277047...@sss.pgh.pa.us...
>> "Davor J."  writes:
>>> Suppose 2 functions: factor(int,int) and offset(int, int).
>>> Suppose a third function: convert(float,int,int) which simply returns
>>> $1*factor($2,$3)+offset($2,$3)
>>> All three functions are IMMUTABLE.
>>
>> You should write the third function as a SQL function, which'd allow it
>> to be inlined.
>>
>>> VERY FAST (half a second):
>>> 
>>> SELECT data*factor(1,2)+offset(1,2) FROM tbl_data;
>>
>> In this case both factor() calls are folded to constants, hence executed
>> only once.
>>
>>> VERY SLOW (a minute):
>>> 
>>> SELECT convert(data, 1, 2) FROM tbl_data;
>>
>> Without inlining, there's no hope of any constant-folding here.
>> The optimizer just sees the plpgsql function as a black box and
>> can't do anything with it.
>>
> Your concepts of "inlining" and "black box" really cleared things up for 
> me. With fnc_unit_convert() written in SQL and declared as STABLE I indeed 
> have fast performance now.

A note on performance here: If I declare the fast SQL function 
fnc_unit_convert() as STRICT or as SECURITY DEFINER, then I suddenly get 
slow performance again (i.e. no apparent inlining). 



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to achieve sustained disk performance of 1.25 GB write for 5 mins

[J. Roeleveld]

On Wednesday 17 November 2010 15:26:56 Eric Comeau wrote:
> This is not directly a PostgreSQL performance question but I'm hoping
> some of the chaps that build high IO PostgreSQL servers on here can help.
> 
> We build file transfer acceleration s/w (and use PostgreSQL as our
> database) but we need to build a test server that can handle a sustained
> write throughput of 1,25 GB for 5 mins.
> 
> Why this number, because we want to push a 10 Gbps network link for 5-8
> mins, 10Gbps = 1.25 GB write, and would like to drive it for 5-8 mins
> which would be 400-500 GB.
> 
> Note this is just a "test" server therefore it does not need fault
> tolerance.
> 
> Thanks in advance,
> Eric

I'm sure there are others with more experience on this, but if you don't need 
failt tolerance, a bunch of fast disks in striping-mode (so-called RAID-0) on 
seperated channels (eg. different PCI-Express channels) would be my first step.

Alternatively, if you don't care if the data is actually stored, couldn't you 
process it with a program that does a checksum over the data transmitted and 
then ignores/forgets it? (eg. forget about disk-storage and do it all in 
memory?)

--
Joost

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Postgres 9.0 has a bias against indexes

[J Sisson]

Odds are that a table of 14 rows will more likely be cached in RAM
than a table of 14 million rows.  PostgreSQL would certainly be more
"openminded" to using an index if chances are low that the table is
cached.  If the table *is* cached, though, what point would there be
in reading an index?

Also, if random_page_cost is set to default (4.0), the planner will
tend towards sequential scans.  You can drop this number a bit to
"help" the planner be more selective of indexes...and there's also
cpu_tuple_* settings that can be modified to pursuade the planner to
use indexes.

Doubtful that any prodding will force an index scan with a cached
table of 14 rows, though...

On 1/27/11, Mladen Gogala  wrote:
> I have a table EMP, with 14 rows and a description like this:
> scott=> \d+ emp
>   Table "public.emp"
>Column  |Type | Modifiers | Storage  |
> Description
> --+-+---+--+-
>   empno| smallint| not null  | plain|
>   ename| character varying(10)   |   | extended |
>   job  | character varying(9)|   | extended |
>   mgr  | smallint|   | plain|
>   hiredate | timestamp without time zone |   | plain|
>   sal  | double precision|   | plain|
>   comm | double precision|   | plain|
>   deptno   | smallint|   | plain|
> Indexes:
>  "emp_pkey" PRIMARY KEY, btree (empno)
>  "emp_mgr_i" btree (mgr)
> Foreign-key constraints:
>  "fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
> Has OIDs: no
>
> scott=>
>
> A recursive query doesn't use existing index on mgr:
> scott=> explain analyze
> with recursive e(empno,ename,mgr,bossname,level) as (
> select empno,ename,mgr,NULL::varchar,0 from emp where empno=7839
> union
> select emp.empno,emp.ename,emp.mgr,e.ename,e.level+1
> from emp,e
> where emp.mgr=e.empno)
> select * from e;
>   QUERY PLAN
> -
>   CTE Scan on e  (cost=20.59..23.21 rows=131 width=78) (actual
> time=0.020..0.143 rows=14 loops=1)
> CTE e
>   ->  Recursive Union  (cost=0.00..20.59 rows=131 width=52) (actual
> time=0.018..0.128 rows=14 loops=1)
> ->  Seq Scan on emp  (cost=0.00..1.18 rows=1 width=10)
> (actual time=0.013..0.015 rows=1 loops=1)
>   Filter: (empno = 7839)
> ->  Hash Join  (cost=0.33..1.68 rows=13 width=52) (actual
> time=0.016..0.021 rows=3 loops=4)
>   Hash Cond: (public.emp.mgr = e.empno)
>   ->  Seq Scan on emp  (cost=0.00..1.14 rows=14
> width=10) (actual time=0.001..0.004 rows=14 loops=4)
>   ->  Hash  (cost=0.20..0.20 rows=10 width=44) (actual
> time=0.004..0.004 rows=4 loops=4)
> Buckets: 1024  Batches: 1  Memory Usage: 1kB
> ->  WorkTable Scan on e  (cost=0.00..0.20
> rows=10 width=44) (actual time=0.001..0.002 rows=4 loops=4)
>   Total runtime: 0.218 ms
> (12 rows)
>
> scott=>
>
> The optimizer will not use index, not even when I turn off both hash and
> merge joins. This is not particularly important for a table with 14
> rows, but for a larger table, this is a problem. The
> only way to actually force the use of index is by disabling seqscan, but
> that chooses a wrong path
> again, because it reads the "outer" table by primary key, which will be
> very slow. Full table scan,
> done by the primary key is probably the slowest thing around. I know
> about the PostgreSQL philosophy
> which says "hints are bad", and I deeply disagree with it, but would it
> be possible to have at
> least one parameter that would change calculations in such a way that
> indexes are favored, where they exist?
>
> --
> Mladen Gogala
> Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> www.vmsinfo.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


-- 
Computers are like air conditioners...
They quit working when you open Windows.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Xeon twice the performance of opteron

[J Sisson]

On Thu, Mar 17, 2011 at 10:13 AM, Jeff  wrote:
> hey folks,
>
> Running into some odd performance issues between a few of our db boxes.

We've noticed similar results both in OLTP and data warehousing conditions here.

Opteron machines just seem to lag behind *especially* in data
warehousing.  Smaller
cache for sorting/etc... is what I'd always chalked it up to, but I'm
open to other theories
if they exist.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Bulk Insert and Index use

[Jim J]

If the bulk load has the possibility of duplicating data, then you need 
to change methods.  Try bulk loading into a temp table,  index it like 
the original, eliminate the dups and merge the tables.

It is also possible to do an insert from the temp table into the final 
table like:
insert into original (x,x,x)  (select temp.1, temp.2, etc from temp left 
join original on temp.street=original.street where original.street is null)

Good Luck
Jim
Rudi Starcevic wrote:
Hi,
I have a question on bulk checking, inserting into a table and
how best to use an index for performance.
The data I have to work with is a monthly CD Rom csv data dump of
300,000 property owners from one area/shire.
So every CD has 300,000 odd lines, each line of data which fills the 
'property' table.

Beginning with the first CD each line should require one SELECT and
one INSERT as it will be the first property with this address.
The SELECT uses fields like 'street' and 'suburb', to check for an 
existing property,
so I have built an index on those fields.

My question is does each INSERT rebuild the index on the 'street' and 
'suburb' fields?
I believe it does but I'm asking to be sure.

If this is the case I guess performance will suffer when I have, say, 
200,000
rows in the table.

Would it be like:
a) Use index to search on 'street' and 'suburb'
b) No result? Insert new record
c) Rebuild index on 'street' and 'suburb'
for each row?
Would this mean that after 200,000 rows each INSERT will require
the index of 000's of rows to be re-indexed?
So far I believe my only options are to use either and index
or sequential scan and see which is faster.
A minute for your thoughts and/or suggestions would be great.
Thanks.
Regards,
Rudi.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] Performance Question

[J Sisson]

There are a few things you didn't mention...

First off, what is the context this database is being used in?  Is it the
backend for a web server?  Data warehouse?  Etc?

Second, you didn't mention the use of indexes.  Do you have any indexes on
the table in question, and if so, does EXPLAIN ANALYZE show the planner
utilizing the index(es)?

Third, you have 8 GB of RAM on a dedicated machine.  Consider upping the
memory settings in postgresql.conf.  For instance, on my data warehouse
machines (8 GB RAM each) I have shared_buffers set to almost 2 GB and
effective_cache_size set to nearly 5.5 GB.  (This is dependent on how you're
utilizing this database, so don't blindly set these values!)

Last, you didn't mention what RAID level the other server you tested this on
was running.

On Wed, Nov 12, 2008 at 10:27 AM, - - <[EMAIL PROTECTED]> wrote:

> I've been searching for performance metrics and tweaks for a few weeks now.
> I'm trying to determine if the length of time to process my queries is
> accurate or not and I'm having a difficult time determining that. I know
> postgres performance is very dependent on hardware and settings and I
> understand how difficult it is to tackle. However, I was wondering if I
> could get some feedback based on my results please.
>
> The database is running on a dual-core 2GHz Opteron processor with 8GB of
> RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad
> for Postgres, but moving the database to another server didn't change
> performance at all). Some of the key parameters from postgresql.conf are:
>
> max_connections = 100
> shared_buffers = 16MB
> work_mem = 64MB
> everything else is set to the default
>
> One of my tables has 660,000 records and doing a SELECT * from that table
> (without any joins or sorts) takes 72 seconds. Ordering the table based on 3
> columns almost doubles that time to an average of 123 seconds. To me, those
> numbers are crazy slow and I don't understand why the queries are taking so
> long. The tables are UTF-8 encode and contain a mix of languages (English,
> Spanish, etc). I'm running the query from pgadmin3 on a remote host. The
> server has nothing else running on it except the database.
>
> As a test I tried splitting up the data across a number of other tables. I
> ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join
> the results together. This was even slower, taking an average of 103 seconds
> to complete the generic select all query.
>
> I'm convinced something is wrong, I just can't pinpoint where it is. I can
> provide any other information necessary. If anyone has any suggestions it
> would be greatly appreciated.
>
>


-- 
Computers are like air conditioners...
They quit working when you open Windows.

[PERFORM] queries with subquery constraints on partitioned tables not optimized?

[Davor J.]

 Let's say you have one partitioned table, "tbl_p", partitioned according to 
the PK "p_pk". I have made something similar with triggers, basing myself on 
the manual for making partitioned tables.
According to the manual, optimizer searches the CHECKs of the partitions to 
determine which table(s) to use (if applicable).

So if one has CHECKs of kind "p_pk = some number", queries like "SELECT * 
from tbl_p where p_pk = 1" will only be searched in the appropriate table. 
One can check this with EXPLAIN. So far so good.

Now, if one takes a subquery for "1", the optimizer evaluates it first 
(let's say to "1"), but then searches for it (sequentially) in every 
partition, which, for large partitions, can be very time-consuming and goes 
beyond the point of partitioning.

Is this normal, or am I missing something?

Kind regards,
Davor




-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] the jokes for pg concurrency write performance

[J Sisson]

2010/2/1  :
> * joke 1: insert operation would use a excluse lock on reference row by the
> foreign key . a big big big performance killer , i think this is a stupid
> design .
>
> * joke 2: concurrency update on same row would lead to that other
> transaction must wait the earlier transaction complete , this would kill the
> concurrency performance in some long time transaction situation . a stupid
> design to ,

I hear that MySQL can work wonders in performance by bypassing the
checks you're concerned about...don't count on the data being
consistent, but by golly it'll get to the client FAAAST...

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] oom_killer

[J Sisson]

On Thu, Apr 21, 2011 at 3:04 PM, Scott Marlowe  wrote:
> Just because you've been walking around with a gun pointing at your
> head without it going off does not mean walking around with a gun
> pointing at your head is a good idea.

+1

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Time to put theory to the test?

[J Sisson]

Not sure if this is the right list...but:

Disclaimer:  I realize this is comparing apples to oranges.  I'm not
trying to start a database flame-war.  I just want to say thanks to
the PostgreSQL developers who make my life easier.

I manage thousands of databases (PostgreSQL, SQL Server, and MySQL),
and this past weekend we had a massive power surge that knocked out
two APC cabinets.  Quite a few machines rebooted (and management is
taking a new look at the request for newer power cabinets heh).
Talking theory is one thing, predicting results is another...and yet
the only thing that counts is "what happens when 'worst-case-scenario'
becomes reality?"

Long story short, every single PostgreSQL machine survived the failure
with *zero* data corruption.  I had a few issues with SQL Server
machines, and virtually every MySQL machine has required data cleanup
and table scans and tweaks to get it back to "production" status.

I was really impressed...you guys do amazing work.  Thank you.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Time to put theory to the test?

[J Sisson]

On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch  wrote:
> Tip from someone that manages thousands of MySQL servers: Use InnoDB
> when using MySQL.

Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
knowledge of MySQL, but if InnoDB has such amazing benefits as being
crash safe, and even speed increases in some instances, why isn't
InnoDB default?  I suppose the real issue is that I prefer software
that gives me safe defaults that I can adjust towards the "unsafe" end
as far as I'm comfortable with, rather than starting off in la-la land
and working back towards sanity.

I'll concede that the issues we had with MySQL were self-inflicted for
using MyISAM.  Thanks for pointing this out.  Time to go get my
knowledge of MySQL up to par with my knowledge of PostgreSQL...

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++

[J Sisson]

On Wed, Aug 17, 2011 at 1:55 PM, Ogden  wrote:

>
>
> What about the OS itself? I put the Debian linux sysem also on XFS but
> haven't played around with it too much. Is it better to put the OS itself on
> ext4 and the /var/lib/pgsql partition on XFS?
>
>
We've always put the OS on whatever default filesystem it uses, and then put
PGDATA on a RAID 10/XFS and PGXLOG on RAID 1/XFS (and for our larger
installations, we setup another RAID 10/XFS for heavily accessed indexes or
tables).  If you have a battery-backed cache on your controller (and it's
been tested to work), you can increase performance by mounting the XFS
partitions with "nobarrier"...just make sure your battery backup works.

I don't know how current this information is for 9.x (we're still on 8.4),
but there is (used to be?) a threshold above which more shared_buffers
didn't help.  The numbers vary, but somewhere between 8 and 16 GB is
typically quoted.  We set ours to 25% RAM, but no more than 12 GB (even for
our machines with 128+ GB of RAM) because that seems to be a breaking point
for our workload.

Of course, no advice will take the place of testing with your workload, so
be sure to test =)

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

[J Sisson]

On Sun, Sep 11, 2011 at 1:36 PM, Ogden  wrote:

> As someone who migrated a RAID 5 installation to RAID 10, I am getting far
> better read and write performance on heavy calculation queries. Writing on
> the RAID 5 really made things crawl. For lots of writing, I think RAID 10 is
> the best. It should also be noted that I changed my filesystem from ext3 to
> XFS - this is something you can look into as well.
>
> Ogden
>
> RAID 10 on XFS here, too, both in OLTP and Data-warehousing scenarios.  Our
largest OLTP is ~375 GB, and PostgreSQL performs admirably (we converted
from MSSQL to PostgreSQL, and we've had more issues with network bottlenecks
since converting (where MSSQL was always the bottleneck before)).  Now that
we have fiber interconnects between our two main datacenters, I'm actually
having to work again haha.

But yeah, we tried quite a few file systems, and XFS **for our workloads**
performed better than everything else we tested, and RAID 10 is a given if
you do any significant writing.

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

[J Sisson]

Sorry, meant to send this to the list.

For really big data-warehousing, this document really helped us:

http://pgexperts.com/document.html?id=49

Re: [PERFORM] Databases optimization

[J Sisson]

On Sun, Sep 11, 2011 at 5:22 PM, Maciek Sakrejda wrote:

> performance guidelines, I recommend Greg Smith's "PostgreSQL 9.0 High
> Performance" [1] (disclaimer: I used to work with Greg and got a free
> copy)
>
> I'll second that.  "PostgreSQL 9.0 High Performance" is an excellent
resource
(I recommend it even for non-PostgreSQL admins because it goes so in-depth
on Linux tuning) so whether you get it for free or not, it's worth the time
it takes
to read and absorb the info.

I've never run PostgreSQL virtualized, but I can say that if it's anything
like
running SQL Server virtualized, it's not a terribly good idea.

[PERFORM] memory allocation

[nijam J]

we are using cloud server

*this are memory info*

free -h
 total   used   free sharedbuffers cached
Mem:   15G15G   197M   194M   121M14G
-/+ buffers/cache:   926M14G
Swap:  15G32M15G

*this are disk info:*
 df -h

FilesystemSize  Used Avail Use% Mounted on
/dev/vda1  20G  1.7G   17G  10% /
devtmpfs  7.9G 0  7.9G   0% /dev
tmpfs 7.9G  4.0K  7.9G   1% /dev/shm
tmpfs 7.9G   17M  7.9G   1% /run
tmpfs 7.9G 0  7.9G   0% /sys/fs/cgroup
/dev/mapper/vgzero-lvhome  99G  189M   94G   1% /home
/dev/mapper/vgzero-lvdata 1.2T   75G  1.1T   7% /data
/dev/mapper/vgzero-lvbackup   296G  6.2G  274G   3% /backup
/dev/mapper/vgzero-lvxlog 197G   61M  187G   1% /pg_xlog
/dev/mapper/vgzero-lvarchive  197G   67G  121G  36% /archive



i allocated memory as per following list:
shared_buffers = 2GB  (10-30 %)
effective_cache_size =7GB (70-75 %)   >>(shared_buffers+page cache) for
dedicated server only
work_mem = 128MB (0.3-1 %)
maintenance_work_mem = 512MB (0.5-4 % )
temp_Buffer =  8MB >>default is better( setting can
be changed within individual sessions)

checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 3.5
cpu_tuple_cost = 0.05
wal_buffers = 32MB  leave this default 3% of shared buffer is better



is it better or do i want to modify any thing

our server is getting too slow again and again

please give me a suggestion

Re: [PERFORM] How to improve db performance with $7K?

[Douglas J. Trainor]

You asked for it!  ;-)
If you want cheap, get SATA.  If you want fast under
*load* conditions, get SCSI.  Everything else at this
time is marketing hype, either intentional or learned.
Ignoring dollars, expect to see SCSI beat SATA by 40%.
* * * What I tell you three times is true * * *
Also, compare the warranty you get with any SATA
drive with any SCSI drive.  Yes, you still have some
change leftover to buy more SATA drives when they
fail, but... it fundamentally comes down to some
actual implementation and not what is printed on
the cardboard box.  Disk systems are bound by the
rules of queueing theory.  You can hit the sales rep
over the head with your queueing theory book.
Ultra320 SCSI is king of the hill for high concurrency
databases.  If you're only streaming or serving files,
save some money and get a bunch of SATA drives.
But if you're reading/writing all over the disk, the
simple first-come-first-serve SATA heuristic will
hose your performance under load conditions.
Next year, they will *try* bring out some SATA cards
that improve on first-come-first-serve, but they ain't
here now.  There are a lot of rigged performance tests
out there...  Maybe by the time they fix the queueing
problems, serial Attached SCSI (a/k/a SAS) will be out.
Looks like Ultra320 is the end of the line for parallel
SCSI, as Ultra640 SCSI (a/k/a SPI-5) is dead in the
water.
Ultra320 SCSI.
Ultra320 SCSI.
Ultra320 SCSI.
Serial Attached SCSI.
Serial Attached SCSI.
Serial Attached SCSI.
For future trends, see:
http://www.incits.org/archive/2003/in031163/in031163.htm
   douglas
p.s. For extra credit, try comparing SATA and SCSI drives
when they're 90% full.
On Apr 6, 2005, at 8:32 PM, Alex Turner wrote:
I guess I'm setting myself up here, and I'm really not being ignorant,
but can someone explain exactly how is SCSI is supposed to better than
SATA?
Both systems use drives with platters.  Each drive can physically only
read one thing at a time.
SATA gives each drive it's own channel, but you have to share in SCSI.
 A SATA controller typicaly can do 3Gb/sec (384MB/sec) per drive, but
SCSI can only do 320MB/sec across the entire array.
What am I missing here?
Alex Turner
netEconomist

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

Re: [PERFORM] How to improve db performance with $7K?

[Douglas J. Trainor]

A good one page discussion on the future of SCSI and SATA can
be found in the latest CHIPS (The Department of the Navy Information
Technology Magazine, formerly CHIPS AHOY) in an article by
Patrick G.  Koehler and Lt. Cmdr. Stan Bush.
Click below if you don't mind being logged visiting Space and Naval
Warfare Systems Center Charleston:
http://www.chips.navy.mil/archives/05_Jan/web_pages/scuzzy.htm
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Opteron vs Xeon (Was: What to do with 6 disks?)

[J. Andrew Rogers]

I've seen quite a few folks touting the Opteron as 2.5x 
faster with postgres than a Xeon box.  What makes the 
Opteron so quick?  Is it that Postgres really prefers to 
run in 64-bit mode?

I don't know about 2.5x faster (perhaps on specific types 
of loads), but the reason Opterons rock for database 
applications is their insanely good memory bandwidth and 
latency that scales much better than the Xeon.  Opterons 
also have a ccNUMA-esque I/O fabric and two dedicated 
on-die memory channels *per processor* -- no shared bus 
there, closer to real UNIX server iron than a glorified 
PC.

We run a large Postgres database on a dual Opteron in 
32-bit mode that crushes Xeons running at higher clock 
speeds.  It has little to do with bitness or theoretical 
instruction dispatch, and everything to do with the 
superior memory controller and I/O fabric.  Databases are 
all about moving chunks of data around and the Opteron 
systems were engineered to do this very well and in a very 
scalable fashion.  For the money, it is hard to argue with 
the price/performance of Opteron based servers.  We 
started with one dual Opteron postgres server just over a 
year ago (with an equivalent uptime) and have considered 
nothing but Opterons for database servers since.  Opterons 
really are clearly superior to Xeons for this application. 
I don't work for AMD, just a satisfied customer. :-)

re: 6 disks.  Unless you are tight on disk space, a hot 
spare might be nice as well depending on your needs.

Cheers,
J. Andrew Rogers
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Adaptec/LSI/?? RAID

[J. Andrew Rogers]

I've got a bunch of mission-critical Postgres servers on 
Opterons, all with no less than 4GB RAM,  running Linux + 
XFS, and most with LSI MegaRAID cards. We've never had a 
single system crash or failure on our postgres servers, 
and some of them are well-used and with uptimes in excess 
of a year.


It may be anecdotal, but LSI MegaRAID cards generally seem 
to work pretty well with Linux.  The only problem I've 
ever seen was a BIOS problem between the LSI and the 
motherboard, which was solved by flashing the BIOS on the 
motherboard with the latest version (it was grossly out of 
date anyway).



J. Andrew Rogers

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

  http://archives.postgresql.org

Re: [PERFORM] Filesystem

[J. Andrew Rogers]

On Fri, 3 Jun 2005 09:06:41 +0200
 "Martin Fandel" <[EMAIL PROTECTED]> wrote:
i have only a little question. Which filesystem is 
preferred for postgresql? I'm plan to use xfs

(before i used reiserfs). The reason
is the xfs_freeze Tool to make filesystem-snapshots. 



XFS has worked great for us, and has been both reliable 
and fast.  Zero problems and currently our standard server 
filesystem.  Reiser, on the other hand, has on rare 
occasion eaten itself on the few systems where someone was 
running a Reiser partition, though none were running 
Postgres at the time.  We have deprecated the use of 
Reiser on all systems where it is not already running.


In terms of performance for Postgres, the rumor is that 
XFS and JFS are at the top of the heap, definitely better 
than ext3 and somewhat better than Reiser.  I've never 
used JFS, but I've seen a few benchmarks that suggest it 
is at least as fast as XFS for Postgres.


Since XFS is more mature than JFS on Linux, I go with XFS 
by default.  If some tragically bad problems develop with 
XFS I may reconsider that position, but we've been very 
happy with it so far.  YMMV.


cheers,

J. Andrew Rogers

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

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

Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore)

[J. Andrew Rogers]

On 7/29/05 10:46 AM, "Josh Berkus"  wrote:
>> does anybody have expierence with this machine (4x 875 dual core Opteron
>> CPUs)?
> 
> Nope.   I suspect that you may be the first person to report in on
> dual-cores.  There may be special compile issues with dual-cores that
> we've not yet encountered.


There was recently a discussion of similar types of problems on a couple of
the supercomputing lists, regarding surprisingly substandard performance
from large dual-core opteron installations.

The problem as I remember it boiled down to the Linux kernel handling
memory/process management very badly on large dual core systems --
pathological NUMA behavior.  However, this problem has apparently been fixed
in Linux v2.6.12+, and using the more recent kernel on large dual core
systems generated *massive* performance improvements on these systems for
the individuals with this issue.  Using the patched kernel, one gets the
performance most people were expecting.

The v2.6.12+ kernels are a bit new, but they contain a very important
performance patch for systems like the one above.  It would definitely be
worth testing if possible.


J. Andrew Rogers



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

Re: [PERFORM] Performance problems on 4/8way Opteron (dualcore)

[J. Andrew Rogers]

On 7/30/05 12:57 AM, "William Yu" <[EMAIL PROTECTED]> wrote:
> I haven't investigated the 2.6.12+ kernel updates yet -- I probably will
> do our development servers first to give it a test.


The kernel updates make the NUMA code dual-core aware, which apparently
makes a big difference in some cases but not in others.  It makes some
sense, since multi-processor multi-core machines will have two different
types of non-locality instead of just one that need to be managed.  Prior to
the v2.6.12 patches, a dual-core dual-proc machine was viewed as a quad-proc
machine.

The closest thing to a supported v2.6.12 kernel that I know of is FC4, which
is not really supported in the enterprise sense of course.


J. Andrew Rogers



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

Re: [PERFORM] sustained update load of 1-2k/sec

[J. Andrew Rogers]

On 8/19/05 1:24 AM, "Mark Cotner" <[EMAIL PROTECTED]> wrote:
> I'm currently working on an application that will poll
> thousands of cable modems per minute and I would like
> to use PostgreSQL to maintain state between polls of
> each device.  This requires a very heavy amount of
> updates in place on a reasonably large table(100k-500k
> rows, ~7 columns mostly integers/bigint).  Each row
> will be refreshed every 15 minutes, or at least that's
> how fast I can poll via SNMP.  I hope I can tune the
> DB to keep up.
> 
> The app is threaded and will likely have well over 100
> concurrent db connections.  Temp tables for storage
> aren't a preferred option since this is designed to be
> a shared nothing approach and I will likely have
> several polling processes.


Mark,

We have PostgreSQL databases on modest hardware doing exactly what you are
attempting to (massive scalable SNMP monitoring system).  The monitoring
volume for a single database server appears to exceed what you are trying to
do by a few orders of magnitude with no scaling or performance issues, so I
can state without reservation that PostgreSQL can easily handle your
application in theory.

However, that is predicated on having a well-architected system that
minimizes resource contention and unnecessary blocking, and based on your
description you may be going about it a bit wrong.

The biggest obvious bottleneck is the use of threads and massive
process-level parallelization.  As others have pointed out, async queues are
your friends, as is partitioning the workload horizontally rather than
vertically through the app stack.  A very scalable high-throughput engine
for SNMP polling only requires two or three threads handling different parts
of the workload to saturate the network, and by choosing what each thread
does carefully you can all but eliminate blocking when there is work to be
done.

We only use a single database connection to insert all the data into
PostgreSQL, and that process/thread receives its data from a work queue.
Depending on how you design your system, you can batch many records in your
queue as a single transaction.  In our case, we also use very few updates,
mostly just inserts, which is probably advantageous in terms of throughput
if you have the disk for it.  The insert I/O load is easily handled, and our
disk array is a modest 10k SCSI rig.  The only thing that really hammers the
server is when multiple reporting processes are running, which frequently
touch several million rows each (the database is much larger than the system
memory), and even this is manageable with clever database design.


In short, what you are trying to do is easily doable on PostgreSQL in
theory.  However, restrictions on design choices may pose significant
hurdles.  We did not start out with an ideal system either; it took a fair
amount of re-engineering to solve all the bottlenecks and problems that pop
up.

Good luck,

J. Andrew Rogers
[EMAIL PROTECTED]



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

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

Re: [PERFORM] Postgresql Hardware - Recommendations

[J. Andrew Rogers]

On 9/5/05 6:50 AM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> The questions we are asking us now are:
> 
> 1) Intel or AMD (or alternate Platform)
> Are we better of with Xeons or Opterons? Should we consider the IBM
> OpenPower platform?


Opteron spanks Xeon for database loads.  Advantage AMD, and you generally
won't have to spend much extra money for the privilege.  I've never used
Postgres on the IBM OpenPower platform, but I would expect that it would
perform quite well, certainly better than the Xeons and probably competitive
with the Opterons in many respects -- I am not sufficiently knowledgeable to
make a definitive recommendation.

 
> 2) CPUs vs cache
> Would you rather have more CPUs or more cache? Eg: 4x Xeon 1MB vs 2x
> Xeon 8MB


I would expect that cache sizes are relatively unimportant compared to
number of processors, but it would depend on the specifics of your load.
Cache coherence is a significant issue for high concurrency database
applications, and a few megabytes of cache here and there will likely make
little difference for a 60GB database.  Databases spend most of their time
playing in main memory, not in cache.  The biggest advantage I can see to
bigger cache would be connection scaling, in which case you'll probably buy
more mileage with more processors.

There are a lot of architecture dependencies here.  Xeons scale badly to 4
processors, Opterons scale just fine.


 
> 3) CPUs vs Memory
> Would you rather have 4x CPUs and 8GB of memory, or 2x CPUs with 16GB of
> memory?


Uh, for what purpose?  CPU and memory are not fungible, so how you
distribute them depends very much on your application.  You can never have
too much memory for a large database, but having extra processors on a
scalable architecture is pretty nice too.  What they both buy you is not
really related.  

The amount of memory you need is determined by the size of your cache-able
working set and the nature of your queries.  Spend whatever money is left on
the processors; if your database spends all its time waiting for disks, no
quantity of processors will help you unless you are doing a lot of math on
the results.


YMMV, as always.  Recommendations more specific than "Opterons rule, Xeons
suck" depend greatly on what you plan on doing with the database.


Cheers,

J. Andrew Rogers




---(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 There Any Way ....

[Douglas J. Trainor]

Ron Peacetree sounds like someone talking out of his _AZZ_.
He can save his unreferenced flapdoodle for his SQL Server
clients.  Maybe he will post references so that we may all
learn at the feet of Master Peacetree.  :-)

douglas

On Oct 4, 2005, at 7:33 PM, Ron Peacetree wrote:


pg is _very_ stupid about caching.  Almost all of the caching is left
to the OS, and it's that way by design (as post after post by TL has
pointed out).

That means pg has almost no ability to take application domain
specific knowledge into account when deciding what to cache.
There's plenty of papers on caching out there that show that
context dependent knowledge leads to more effective caching
algorithms than context independent ones are capable of.

(Which means said design choice is a Mistake, but unfortunately
one with too much inertia behind it currentyl to change easily.)

Under these circumstances, it is quite possible that an expert class
human could optimize memory usage better than the OS + pg.

If one is _sure_ they know what they are doing, I'd suggest using
tmpfs or the equivalent for critical read-only tables.  For "hot"
tables that are rarely written to and where data loss would not be
a disaster, "tmpfs" can be combined with an asyncronous writer
process push updates to HD.  Just remember that a power hit
means that

The (much) more expensive alternative is to buy SSD(s) and put
the critical tables on it at load time.

Ron


-Original Message-
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
Sent: Oct 4, 2005 4:57 PM
To: Stefan Weiss <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is There Any Way 

On Tue, Oct 04, 2005 at 12:31:42PM +0200, Stefan Weiss wrote:

On 2005-09-30 01:21, Lane Van Ingen wrote:
  (3) Assure that a disk-based table is always in memory (outside of 
keeping

it in
  memory buffers as a result of frequent activity which would 
prevent

LRU
  operations from taking it out) ?


I was wondering about this too. IMO it would be useful to have a way 
to tell

PG that some tables were needed frequently, and should be cached if
possible. This would allow application developers to consider joins 
with
these tables as "cheap", even when querying on columns that are not 
indexed.
I'm thinking about smallish tables like users, groups, *types, etc 
which
would be needed every 2-3 queries, but might be swept out of RAM by 
one
large query in between. Keeping a table like "users" on a RAM fs 
would not

be an option, because the information is not volatile.


Why do you think you'll know better than the database how frequently
something is used? At best, your guess will be correct and PostgreSQL
(or the kernel) will keep the table in memory. Or, your guess is wrong
and you end up wasting memory that could have been used for something
else.

It would probably be better if you describe why you want to force this
table (or tables) into memory, so we can point you at more appropriate
solutions.



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

Re: [PERFORM] Is There Any Way ....

[Douglas J. Trainor]

Hey, you can say what you want about my style, but you
still haven't pointed to even one article from the vast literature
that you claim supports your argument.  And I did include a
smiley.  Your original email that PostgreSQL is wrong and
that you are right led me to believe that you, like others making
such statements, would not post your references.  You remind
me of Ted Nelson, who wanted the computing center at
the University of Illinois at Chicago to change their systems
just for him.  BTW, I'm a scientist -- I haven't made my mind
up about anything.  I really am interested in what you say,
if there is any real work backing up your claims such that
it would impact average cases.

Any app designer can conceive of many ways to game the
server to their app's advantage -- I'm not interested in that
potboiler.

douglas

On Oct 4, 2005, at 11:06 PM, Ron Peacetree wrote:


Unfortunately, no matter what I say or do, I'm not going to please
or convince anyone who has already have made their minds up
to the extent that they post comments like Mr Trainor's below.
His response style pretty much proves my earlier point that this
is presently a religious issue within the pg community.

The absolute best proof would be to build a version of pg that does
what Oracle and DB2 have done and implement it's own DB
specific memory manager and then compare the performance
between the two versions on the same HW, OS, and schema.

The second best proof would be to set up either DB2 or Oracle so
that they _don't_ use their memory managers and compare their
performance to a set up that _does_ use said memory managers
on the same HW, OS, and schema.

I don't currently have the resources for either experiment.

Some might even argue that IBM (where Codd and Date worked)
and Oracle just _might_ have had justification for the huge effort
they put into developing such infrastructure.

Then there's the large library of research on caching strategies
in just about every HW and SW domain, including DB theory,
that points put that the more context dependent, ie application
or domain specific awareness, caching strategies are the better
they are.

Maybe after we do all we can about physical IO and sorting
performance I'll take on the religious fanatics on this one.

One problem set at a time.
Ron



---(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] Is There Any Way ....

[Douglas J. Trainor]

A blast from the past is forwarded below.

douglas

Begin forwarded message:

From: Tom Lane <[EMAIL PROTECTED]>
Date: August 23, 2005 3:23:43 PM EDT
To: Donald Courtney <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org, Frank Wiles <[EMAIL PROTECTED]>, gokulnathbabu manoharan <[EMAIL PROTECTED]>
Subject: Re: [PERFORM] Caching by Postgres 

Donald Courtney <[EMAIL PROTECTED]> writes:
I am not alone in having the *expectation* that a database should have
some cache size parameter and the option to skip the file system.  If
I use oracle, sybase, mysql and maxdb they all have the ability to
size a data cache and move to 64 bits.

And you're not alone in holding that opinion despite having no shred
of evidence that it's worthwhile expanding the cache that far.

However, since we've gotten tired of hearing this FUD over and over,
8.1 will have the ability to set shared_buffers as high as you want.
I expect next we'll be hearing from people complaining that they
set shared_buffers to use all of RAM and performance went into the
tank ...

regards, tom lane


On Oct 4, 2005, at 11:06 PM, Ron Peacetree wrote:

Unfortunately, no matter what I say or do, I'm not going to please
or convince anyone who has already have made their minds up
to the extent that they post comments like Mr Trainor's below.
His response style pretty much proves my earlier point that this
is presently a religious issue within the pg community.

The absolute best proof would be to build a version of pg that does
what Oracle and DB2 have done and implement it's own DB
specific memory manager and then compare the performance
between the two versions on the same HW, OS, and schema.

The second best proof would be to set up either DB2 or Oracle so
that they _don't_ use their memory managers and compare their
performance to a set up that _does_ use said memory managers
on the same HW, OS, and schema.

I don't currently have the resources for either experiment.

Some might even argue that IBM (where Codd and Date worked)
and Oracle just _might_ have had justification for the huge effort
they put into developing such infrastructure. 

Then there's the large library of research on caching strategies
in just about every HW and SW domain, including DB theory,
that points put that the more context dependent, ie application
or domain specific awareness, caching strategies are the better
they are.

Maybe after we do all we can about physical IO and sorting
performance I'll take on the religious fanatics on this one.

One problem set at a time.
Ron   

OT Re: [PERFORM] Hardware/OS recommendations for large databases (

[Douglas J. Trainor]

AMD added quad-core processors to their public roadmap for 2007.

Beyond 2007, the quad-cores will scale up to 32 sockets
(using Direct Connect Architecture 2.0)

Expect Intel to follow.

douglas

On Nov 16, 2005, at 9:38 AM, Steve Wampler wrote:


[...]

Got it - the cpu is only acting on one query in any instant but may be
switching between many 'simultaneous' queries.  PG isn't really  
involved

in the decision.  That makes sense.


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

Re: [PERFORM] opinion on disk speed

[J. Andrew Rogers]

On Dec 12, 2005, at 1:59 PM, Vivek Khera wrote:
From where did you get LSI MegaRAID controller with 512MB?  The  
320-2X doesn't seem to come with more than 128 from the factory.


Can you just swap out the DIMM card for higher capacity?



We've swapped out the DIMMs on MegaRAID controllers.  Given the cost  
of a standard low-end DIMM these days (which is what the LSI  
controllers use last I checked), it is a very cheap upgrade.


Admittedly I've never actually run benchmarks to see if it made a  
significant difference in practice, but it certainly seems like it  
should in theory and the upgrade cost is below the noise floor for  
most database servers.


J. Andrew Rogers


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

Re: [PERFORM] opinion on disk speed

[J. Andrew Rogers]

On Dec 12, 2005, at 2:19 PM, Vivek Khera wrote:

On Dec 12, 2005, at 5:16 PM, J. Andrew Rogers wrote:

We've swapped out the DIMMs on MegaRAID controllers.  Given the  
cost of a standard low-end DIMM these days (which is what the LSI  
controllers use last I checked), it is a very cheap upgrade.


What's the max you can put into one of these cards? I haven't been  
able to find docs on which specific DIMM type they use...



Table 3.7 in the MegaRAID Adapter User's Guide has the specs and  
limits for various controllers.  For the 320-2x, the limit is 512MB  
of PC100 ECC RAM.



J. Andrew Rogers


---(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] Postgres scalability and performance on windows

[J. Andrew Rogers]

On Nov 28, 2006, at 8:24 AM, Tom Lane wrote:

"Gopal" <[EMAIL PROTECTED]> writes:

This is the query and the schema
...
select
sum(area(intersection(snaptogrid(chunkgeometry,0.0001),
GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148  
50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892  
50.8201477285483,-0.140030845589332

50.8208343077265))',4326))) * 100/ (0.00114901195862628)) as
percentCover,


So evidently area(intersection(snaptogrid(...))) takes about 300
microsec per row.  The PostGIS hackers would have to comment on  
whether

that seems out-of-line or not, and whether you can make it faster.



This is consistent with the typical cost for GIS geometry ops -- they  
are relatively expensive.  When running queries against PostGIS  
fields for our apps, about half the CPU time will be spent inside the  
geometry ops.  Fortunately, there is significant opportunity for  
improvement in the performance of the underlying code if anyone found  
the time to optimize (and uglify) it for raw speed.



Cheers,

J. Andrew Rogers


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

[PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

Hello great gurus of performance:
Our 'esteemed' Engr group recently informed a customer that in their testing, 
upgrading to 8.2.x improved the performance of our J2EE 
application "approximately 20%", so of course, the customer then tasked me 
with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 
rpms from postgresql.org, did an initdb, and the pg_restored their data. It's 
been about a week now, and the customer is complaining that in their testing, 
they are seeing a 30% /decrease/ in general performance. Of course, our Engr 
group is being less than responsive, and I have a feeling all they're doing 
is googling for answers, so I'm turning to this group for actual 
assistance :)
I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had 
spent the better part of their 2 years as a customer tuning and tweaking 
setting. I've attached the file that was in place at the time of upgrade. I 
did some cursory googling of my own, and quickly realized that enough has 
changed in v8 that I'm not comfortable making the exact same modification to 
their new config file as some options are new, some have gone away, etc. I've 
attached the existing v8 conf file as well. 
I'd really like it if someone could assist me in determining which of the v8 
options need adjusted to be 'functionally equivalent' to the v7 file. Right 
now, my goal is to get the customer back to the previous level of 
performance, and only then pursue further optimization. I can provide any and 
all information needed, but didn't know what to include initially, so I've 
opted to include the minimal :)
The DB server in question does nothing else, is running CentOS 4.5, kernel 
2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon 
3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.

Thank you in advance for any and all assistance you can provide.
-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Handy Guide to Modern Science:
1. If it's green or it wiggles, it's biology.
2. If it stinks, it's chemistry.
3. If it doesn't work, it's physics.
# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have 
# to SIGHUP the postmaster for the changes to take effect, or use 
# "pg_ctl reload".


#---
# CONNECTIONS AND AUTHENTICATION
#---

# - Connection Settings -

tcpip_socket = true
max_connections = 220
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''  # what interface to listen on; defaults to any
#rendezvous_name = ''   # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60# 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---
# RESOURCE USAGE (except WAL)
#---

# - Memory -

# Needs an actual restart to change!!
shared_buffers = 25000  # min 16, at least max_connections*2, 8KB each
#shared_buffers = 75000 # min 16, at least max_connections*2, 8KB each
#shared_buffers = 20# min 16, at least max_connections*2, 
8KB each
sort_mem = 15000# min 64, size in KB
vacuum_mem = 10 # min 1024, size in KB
#vacuum_mem = 32768 # min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 20  # min max_fsm_relations*16, 6 bytes each
#max_fsm_pa

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote:
> After the restore, did you ANALYZE the entire database to update
> the planner's statistics?  Have you enabled autovacuum or are you
> otherwise vacuuming and analyzing regularly?  What kind of queries
> are slower than desired?  If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.

I forgot to mention that. Yes, we did:
vacuumdb -a -f -v -z

We have not yet turned on autovacuum. That was next on our list, and then 
customer started in w/ the performance. We are doing an 'analyze table' 
followed by 'vacuum table' on a periodic basis, but I'll have to wait till 
I'm in the office on Monday to see what that schedule is (customer only 
allows us to VPN from work)

>
> A few differences between the configuration files stand out.  The
> 7.4 file has the following settings:
>
>   shared_buffers = 25000
>   sort_mem = 15000
>   effective_cache_size = 196608
>
> The 8.2 config has:
>
>   #shared_buffers = 32MB
>   #work_mem = 1MB
>   #effective_cache_size = 128MB
>
> To be equivalent to the 7.4 config the 8.2 config would need:
>
>   shared_buffers = 195MB
>   work_mem = 15000kB
>   effective_cache_size = 1536MB
>
> With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
> (less if the entire database isn't that big) and effective_cache_size
> to 5GB - 6GB.  You might have to increase the kernel's shared memory
> settings before increasing shared_buffers.
>

We have the following in sysctl.conf:
kernel.shmmax=2147483648
kernal.shmall=2097152
kernel.sem = 250 32000 100 128

which should be sufficient, no?

> Some of the other settings are the same between the configurations
> but deserve discussion:
>
>   fsync = off
>
> Disabling fsync is dangerous -- are all parties aware of the risk
> and willing to accept it?  Has the risk been weighed against the
> cost of upgrading to a faster I/O subsystem?  How much performance
> benefit are you realizing by disabling fsync?  What kind of activity
> led to the decision to disable fynsc?  Are applications doing
> anything like executing large numbers of insert/update/delete
> statements outside of a transaction block when they could be done
> in a single transaction?

Yes, they're aware. This is a temporary setting while they order upgraded SAN 
devices. Currently, the I/O on the boxes is horrific.

>
>   commit_delay = 2
>   commit_siblings = 3
>
> What kind of activity led to the above settings?  Are they a guess
> or were they determined empirically?  How much benefit are they
> providing and how did you measure that?

Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly 
willing to discount him if so advised.

>
>   enable_mergejoin = off
>   geqo = off
>
> I've occasionally had to tweak planner settings but I prefer to do
> so for specific queries instead of changing them server-wide.

I concur. Unfortunately, our Engr group don't actually write the SQL for the 
app. It's generated, and is done in such a fashion as to work on all our 
supported dbs (pgsql, oracle, mysql). 

Thanks a ton for the input thus far

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Anything worth shooting is worth shooting twice. Ammo is cheap.  Life is 
expensive.

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

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
> Another thing that seems strange is that the 8.2 config file does not
> seem to have been processed by initdb --- or did you explicitly comment
> out the settings it made?

I don't understand this comment. You are saying 'initdb' will make changes to 
the file? The file I sent is the working copy from the machine in question.

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

"Does it worry you that you don't talk any kind of sense?"

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

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

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote:
> When you initdb, a config file is edited from the template by initdb to
> reflect your machine config.

I didn't realize that. I'll have to harass the rest of the team to see if 
someone overwrote that file or not. In the interim, I did an 'initdb' to 
another location on the same box and then copied those values into the config 
file. That's cool to do, I assume?

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Cowering in a closet is starting to seem like a reasonable plan.

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

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Sunday 03 June 2007 18:30:17 Greg Smith wrote:
> To be equivalent to the 7.4 config the 8.2 config would need:

I've taken all the wonderful advise offered thus far, and put the attached 
into use. Our initial testing shows a 66% improvement in page load times for 
our app. I have the customer beating on things and noting anything that is 
still slow.

On a side note, is there any real benefit to using autovacuum over a 
periodically scheduled vacuum? I ask because we have the latter already coded 
up and cron'd and it seems to keep things fairly optimized.

BTW, I'm on the list, so there's no need to reply direct. I can get the 
replies from the list

Thanks again for everyone's assistance thus far. Y'all rock!
-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

I feel like I'm diagonally parked in a parallel universe...
# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.)  White space may be used.  Comments are introduced
# with '#' anywhere on a line.  The complete list of option names and
# allowed values can be found in the PostgreSQL documentation.  The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'.  Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units:  kB = kilobytes MB = megabytes GB = gigabytes
# Time units:ms = milliseconds s = seconds min = minutes h = hours d = days


#---
# FILE LOCATIONS
#---

# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'   # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'   # write an extra PID file
# (change requires restart)


#---
# CONNECTIONS AND AUTHENTICATION
#---

# - Connection Settings -

listen_addresses = '*'  # what IP address(es) to listen on; 
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432# (change requires restart)
max_connections = 200   # (change requires restart)
# Note: increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).  You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # octal
# (change requires restart)
#bonjour_name = ''  # defaults to the computer name
# (change requires restart)

# - Security & Authentication -

#authentication_timeout = 1min  # 1s-600s
#ssl = off  # (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos
#krb_server_keyfile = ''# (change requires restart)
#krb_srvname = 'postgres'   # (change requires restart)
#krb_server_hostname = ''   # emp

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

le,
   folder.project_id AS projectId,
   folder.path AS folderPathString,
   folder.title AS folderTitle,
   item.folder_id AS folderId,
   item.title AS title,
   item.name AS name,
   artifact.description AS description,
   field_value.value AS artifactGroup,
   field_value2.value AS status,
   field_value2.value_class AS statusClass,
   field_value3.value AS category,
   field_value4.value AS customer,
   sfuser.username AS submittedByUsername,
   sfuser.full_name AS submittedByFullname,
   item.date_created AS submittedDate,
   artifact.close_date AS closeDate,
   sfuser2.username AS assignedToUsername,
   sfuser2.full_name AS assignedToFullname,
   item.date_last_modified AS lastModifiedDate,
   artifact.estimated_hours AS estimatedHours,
   artifact.actual_hours AS actualHours,
   item.version AS version
FROM
   relationship relationship,
   sfuser sfuser,
   sfuser sfuser2,
   field_value field_value3,
   item item,
 project project,
   field_value field_value2,
   field_value field_value,
   artifact artifact,
   folder folder,
   field_value field_value4
WHERE
   artifact.id=item.id
AND item.folder_id=folder.id
AND folder.project_id=project.id
AND artifact.group_fv=field_value.id
AND artifact.status_fv=field_value2.id
AND artifact.category_fv=field_value3.id
AND artifact.customer_fv=field_value4.id
AND item.created_by_id=sfuser.id
AND relationship.is_deleted=false
AND relationship.relationship_type_name='ArtifactAssignment'
AND relationship.origin_id=sfuser2.id
AND artifact.id=relationship.target_id
AND item.is_deleted=false
AND ((project.path='projects.union_gas_gdar_ebt' AND ((folder.path IN 
('tracker.cutover_tasks', 'tracker.peer_review_tracker', 'tracker.tars_0', 
'tracker.reviews', 'tracker.defects', 'tracker.tars', 
'tracker.database_change_requests')) 
OR folder.path LIKE 'tracker.cutover_tasks.%' OR folder.path 
LIKE 'tracker.peer_review_tracker.%' OR folder.path LIKE 'tracker.tars_0.%' 
OR folder.path LIKE 'tracker.reviews.%' OR folder.path LIKE 'tracker.defects.
%' OR folder.path LIKE 'tracker.tars.%' OR folder.path 
LIKE 'tracker.database_change_requests.%')))
AND folder.project_id='proj1775'
AND item.folder_id='tracker11923'
AND folder.path='tracker.defects'
AND (sfuser2.username='nobody' AND field_value2.value_class='Open');

takes 0m9.506s according to time.. it's attached as explain2

TIA, again

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

It's not the pace of life that concerns me, it's the sudden stop at the end.

   QUERY PLAN   


 Unique  (cost=101.98..102.03 rows=5 width=56) (actual time=3049.469..3049.479 
rows=1 loops=1)
   ->  Sort  (cost=101.98..101.99 rows=5 width=56) (actual 
time=3049.461..3049.465 rows=1 loops=1)
 Sort Key: rbac_project_path_string, rbac_resource_name, 
rbac_resource_value
 ->  Append  (cost=0.00..101.92 rows=5 width=56) (actual 
time=2101.795..3049.325 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..13.35 rows=1 width=56) (actual 
time=505.275..505.275 rows=0 loops=1)
 ->  Nested Loop  (cost=0.00..12.97 rows=1 width=30) 
(actual time=505.268..505.268 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..8.68 rows=1 width=42) 
(actual time=0.347..346.759 rows=7294 loops=1)
 ->  Index Scan using role_oper_obj_oper on 
role_operation  (cost=0.00..4.40 rows=1 width=30) (actual time=0.235..71.840 
rows=7294 loops=1)
   Index Cond: (((object_type_id)::text = 
'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND 
((operation_name)::text = 'access'::text))
 ->  Index Scan using role_pk on "role"  
(cost=0.00..4.27 rows=1 width=12) (actual time=0.020..0.024 rows=1 loops=7294)
   Index Cond: (("role".id)::text = 
(role_operation.role_id)::text)
   Filter: (NOT is_deleted)

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Monday 04 June 2007 17:17:03 Heikki Linnakangas wrote:
> And did you use the same encoding and locale? Text operations on
> multibyte encodings are much more expensive.

The db was created as:
createdb -E UNICODE -O  

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Linux is the answer, now what was your question?

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

   http://archives.postgresql.org

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
> Those plans look like they have a lot of casts to text in them. How have
> you defined your indexes? Are your id columns really text?

project table:
Indexes:
"project_pk" PRIMARY KEY, btree (id)
"project_path" UNIQUE, btree (path)

role table:
Indexes:
"role_pk" PRIMARY KEY, btree (id)

role_default_user table:
Indexes:
"role_def_user_pk" PRIMARY KEY, btree (id)
"role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id, 
project_id)

role_operation table:
Indexes:
"role_operation_pk" PRIMARY KEY, btree (id)
"role_oper_obj_oper" btree (object_type_id, operation_category, 
operation_name)
"role_oper_role_id" btree (role_id)

sfuser table:
Indexes:
"sfuser_pk" PRIMARY KEY, btree (id)
"sfuser_username" UNIQUE, btree (username)

projectmembership table:
Indexes:
"pjmb_pk" PRIMARY KEY, btree (id)
"pjmb_projmember" UNIQUE, btree (project_id, member_id)
"pjmb_member" btree (member_id)

relationship table:
Indexes:
"relationship_pk" PRIMARY KEY, btree (id)
"relation_origin" btree (origin_id)
"relation_target" btree (target_id)
"relation_type" btree (relationship_type_name)

field_value table:
Indexes:
"field_value_pk" PRIMARY KEY, btree (id)
"f_val_fid_val_idx" UNIQUE, btree (field_id, value)
"field_class_idx" btree (value_class)
"field_value_idx" btree (value)

item table:
Indexes:
"item_pk" PRIMARY KEY, btree (id)
"item_created_by_id" btree (created_by_id)
"item_folder" btree (folder_id)
"item_name" btree (name)

and yes, the 'id' column is always: character varying type

> And you don't have a 7.4 install around to compare the plans do you?

I have a 7.3.19 db, if that would be useful

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Whose cruel idea was it for the word "lisp" to have an "s" in it?

---(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] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

[Douglas J Hunley]

On Tuesday 05 June 2007 10:34:04 Douglas J Hunley wrote:
> On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
> > Those plans look like they have a lot of casts to text in them. How have
> > you defined your indexes? Are your id columns really text?
>
> project table:
> Indexes:
> "project_pk" PRIMARY KEY, btree (id)
> "project_path" UNIQUE, btree (path)
>
> role table:
> Indexes:
> "role_pk" PRIMARY KEY, btree (id)
>
> role_default_user table:
> Indexes:
> "role_def_user_pk" PRIMARY KEY, btree (id)
> "role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id,
> project_id)
>
> role_operation table:
> Indexes:
> "role_operation_pk" PRIMARY KEY, btree (id)
> "role_oper_obj_oper" btree (object_type_id, operation_category,
> operation_name)
> "role_oper_role_id" btree (role_id)
>
> sfuser table:
> Indexes:
> "sfuser_pk" PRIMARY KEY, btree (id)
> "sfuser_username" UNIQUE, btree (username)
>
> projectmembership table:
> Indexes:
> "pjmb_pk" PRIMARY KEY, btree (id)
> "pjmb_projmember" UNIQUE, btree (project_id, member_id)
> "pjmb_member" btree (member_id)
>
> relationship table:
> Indexes:
> "relationship_pk" PRIMARY KEY, btree (id)
> "relation_origin" btree (origin_id)
> "relation_target" btree (target_id)
> "relation_type" btree (relationship_type_name)
>
> field_value table:
> Indexes:
> "field_value_pk" PRIMARY KEY, btree (id)
> "f_val_fid_val_idx" UNIQUE, btree (field_id, value)
> "field_class_idx" btree (value_class)
> "field_value_idx" btree (value)
>
> item table:
> Indexes:
> "item_pk" PRIMARY KEY, btree (id)
> "item_created_by_id" btree (created_by_id)
> "item_folder" btree (folder_id)
> "item_name" btree (name)
>
> and yes, the 'id' column is always: character varying type
>
> > And you don't have a 7.4 install around to compare the plans do you?
>
> I have a 7.3.19 db, if that would be useful

Any insight given the above?

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

"It is our moral duty to corrupt the young"

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [PERFORM] mysql to postgresql, performance questions

[Ross J. Reedstrom]

On Sat, Mar 20, 2010 at 10:47:30PM -0500, Andy Colson wrote:
> 
> I guess, for me, once I started using PG and learned enough about it (all 
> db have their own quirks and dark corners) I was in love.  It wasnt 
> important which db was fastest at xyz, it was which tool do I know, and 
> trust, that can solve problem xyz.
> 
> (I added the "and trust" as an after thought, because I do have one very 
> important 100% uptime required mysql database that is running.  Its my 
> MythTV box at home, and I have to ask permission from my GF before I take 
> the box down to upgrade anything.  And heaven forbid if it crashes or 
> anything.  So I do have experience with care and feeding of mysql.  And no, 
> I'm not kidding.)
> 
> And I choose PG.
> 

Andy, you are so me! I have the exact same one-and-only-one mission
critical mysql DB, but the gatekeeper is my wife. And experience with
that instance has made me love and trust PostgreSQL even more.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] mysql to postgresql, performance questions

[Ross J. Reedstrom]

On Tue, Mar 23, 2010 at 03:22:01PM -0400, Tom Lane wrote:
> "Ross J. Reedstrom"  writes:
> 
> > Andy, you are so me! I have the exact same one-and-only-one mission
> > critical mysql DB, but the gatekeeper is my wife. And experience with
> > that instance has made me love and trust PostgreSQL even more.
> 
> So has anyone looked at porting MythTV to PG?
> 
My understanding from perusing mailing list archives is that there have
been multiple attempts to provide a database neutral layer and support
different backend databases (mostly w/ PG as the driver) but the lead
developer has been something between disintrested and actively hostile
to the idea. I think this page http://www.mythtv.org/wiki/PostgreSQL_Support 
say it all:
   deleted "PostgreSQL Support" (Outdated, messy and unsupported)

And the Wayback machine version:

http://web.archive.org/web/20080521003224/http://mythtv.org/wiki/index.php/PostgreSQL_Support

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgres 9 query performance

[Ross J. Reedstrom]

On Sun, Jan 30, 2011 at 05:18:15PM -0500, Tom Lane wrote:
> Andres Freund  writes:
> > What happens if you change the
> > left join event.origin on event.id = origin.eventid
> > into
> > join event.origin on event.id = origin.eventid
> > ?
> 
> > The EXISTS() requires that origin is not null anyway. (Not sure why the 
> > planner doesn't recognize that though).
> 
> Sloppy thinking in reduce_outer_joins() is why.  Fixed now:
> http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=1df57f63f3f60c684aa8918910ac410e9c780713
> 
>   regards, tom lane

This is one of the reasons I love open source in general, and PostgreSQL
in particular: Tom has the bandwidth to notice these kinds of
workarounds being discussed on support lists, and turn them immediately
into improvements in the planner. Partly because (I assume, based on
the commit message) Andres's parenthetical comment red-flagged it for
him, since he knew he could trust Andres's opinion that there was
probably a planner improvement hiding here. Amazing!

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] [PERFORM] Slow count(*) again...

[Ross J. Reedstrom]

On Thu, Feb 03, 2011 at 12:44:23PM -0500, Chris Browne wrote:
> mladen.gog...@vmsinfo.com (Mladen Gogala) writes:
> > Hints are not even that complicated to program. The SQL parser should
> > compile the list of hints into a table and optimizer should check
> > whether any of the applicable access methods exist in the table. If it
> > does - use it. If not, ignore it. This looks to me like a
> > philosophical issue, not a programming issue.
> 
> It's worth looking back to what has already been elaborated on in the
> ToDo.
> 
> http://wiki.postgresql.org/wiki/Todo
> ---
> Optimizer hints (not wanted)
> 
> Optimizer hints are used to work around problems in the optimizer and
> introduce upgrade and maintenance issues. We would rather have the
> problems reported and fixed. We have discussed a more sophisticated
> system of per-class cost adjustment instead, but a specification remains
> to be developed.

And as to the 'wait around for a new version to fix that': there are
constantly excellent examples of exactly this happening, all the time
with PostgreSQL - most recent example I've seen -
http://archives.postgresql.org/pgsql-performance/2011-01/msg00337.php

The wait often isn't long, at all.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Really really slow select count(*)

[Ross J. Reedstrom]

On Tue, Feb 08, 2011 at 03:52:31PM -0600, Kevin Grittner wrote:
> Scott Marlowe  wrote:
> > Greg Smith  wrote:
>  
> >> Kevin and I both suggested a "fast plus timeout then immediate"
> >> behavior is what many users seem to want.
>  
> > Are there any settings in postgresql.conf that would make it
> > unsafe to use -m immediate?
>  
> I don't think so.  There could definitely be problems if someone
> cuts power before your shutdown completes, though.  (I hear that
> those firefighters like to cut power to a building before they grab
> those big brass nozzles to spray a stream of water into a building. 
> Go figure...)

Following you off topic, I know of one admin type who has stated "I don't
care what sort of fine the power company wants to give me, if my
property's on fire, I'm going to pull the meter, in order to hand it to
the first responder, rather than have them sit there waiting for the
power tech to arrive while my house burns."

Back on topic, I like the the idea of a timed escalation. That means
there's two things to configure though, timeout(s?) and the set of
states to escalate through. I can see different use cases for different
sets. Hmmm:

pg_ctl -m s:10:f:5:i restart

for smart, 5 sec. timeout, escalate to fast, 5 sec., then immediate?
Not sure how rhat would interact w/ -t.

Perhaps:

pg_ctl -t 10 -m s -t 5 -m f -m i restart

Some video-processing tools do things like that: the order of options
impacts their interaction.

Ross
-- 
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
Connexions  http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE




-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

I have a system that currently inserts ~ 250 million rows per day (I  
have about 10k more raw data than that, but I'm at the limit of my  
ability to get useful insert performance out of postgres).


Things I've already done that have made a big difference:
- modified postgresql.conf shared_buffers value
- converted to COPY from individual insert statements
- changed BLCKSZ to 32768

I currently get ~35k/sec inserts on a table with one index (~70k/sec  
inserts if I don't have any indexes).


The indexed field is basically a time_t (seconds since the epoch),  
autovacuum is running (or postgres would stop choosing to use the  
index). The other fields have relatively lower cardinality.


Each days worth of data gets inserted into its own table so that I  
can expire the data without too much effort (since drop table is much  
faster than running a delete and then vacuum).


I would really like to be able to have 1 (or 2) more indexes on the  
table since it takes a while for a sequential scan of 250million rows  
to complete, but CPU time goes way up.


In fact, it looks like I'm not currently IO bound, but CPU-bound. I  
think some sort of lazy-index generation (especially if it could be  
parallelized to use the other processors/cores that currently sit  
mostly idle) would be a solution. Is anyone working on something like  
this? Any other ideas? Where should I look if I want to start to  
think about creating a new index that would work this way (or am I  
just crazy)?


Thanks for any insight!

--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 24, 2006, at 4:02 PM, Dave Dutcher wrote:
If you can live with possible database corruption, you could try  
turning

Fsync off.  For example if you could just reinsert the data on the off
chance a hardware failure corrupts the database, you might get a  
decent

improvement.


I tried, but I didn't see much of an improvement (and it's not really  
acceptable for this application).


Also have you tried creating the index after you have inserted all  
your

data?  (Or maybe copy already disables the indexes while inserting?)


The data gets inserted in batches every 5 minutes and I potentially  
have people querying it constantly, so I can't remove and re-create  
the index.


--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 24, 2006, at 4:03 PM, Steinar H. Gunderson wrote:
Have you tried fiddling with the checkpointing settings? Check your  
logs --
if you get a warning about checkpoints being too close together,  
that should

give you quite some boost.


no warnings in the log (I did change the checkpoint settings when I  
set up the database, but didn't notice an appreciable difference in  
insert performance).


Apart from that, you should have quite a bit to go on -- somebody  
on this
list reported 2 billion rows/day earlier, but it might have been on  
beefier

hardware, of course. :-)


Probably :) I'll keep searching the list archives and see if I find  
anything else (I did some searching and didn't find anything that I  
hadn't already tried).


Thanks!

--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 24, 2006, at 4:13 PM, Steinar H. Gunderson wrote:

On Wed, May 24, 2006 at 04:09:54PM -0400, Daniel J. Luke wrote:

no warnings in the log (I did change the checkpoint settings when I
set up the database, but didn't notice an appreciable difference in
insert performance).


How about wal_buffers? Upping it might not help all that much if  
only one

thread is writing, but you might give it a try...


I tried, but I didn't notice a difference.

I should probably emphasize that I appear to be CPU bound (and I can  
double my # of rows inserted per second by removing the index on the  
table, or half it by adding another index).


I really should run gprof just to verify.

--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 24, 2006, at 4:12 PM, Larry Rosenman wrote:

are the batches single insert's, or within a big transaction?
If the former, the latter is a big win.


One big transaction every 5 minutes using 'COPY FROM' (instead of  
inserts).



Also, what release(s) are you running?


8.1.x (I think we're upgrading from 8.1.3 to 8.1.4 today).

--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 24, 2006, at 4:24 PM, Larry Rosenman wrote:
Also, is pg_xlog on the same or different spindles from the rest of  
the

PG Data directory?


It's sitting on the same disk array (but I'm doing 1 transaction  
every 5 minutes, and I'm not near the array's sustained write  
capacity, so I don't think that's currently limiting performance).


--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 29, 2006, at 7:11 AM, Markus Schaber wrote:

One big transaction every 5 minutes using 'COPY FROM' (instead of
inserts).


Are you using "COPY table FROM '/path/to/file'", having the file  
sitting

on the server, or "COPY table FROM STDIN" or psql "/copy", having the
file sitting on the client?


COPY table FROM STDIN using psql on the server

I should have gprof numbers on a similarly set up test machine soon ...
--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

[Daniel J. Luke]

On May 30, 2006, at 3:59 PM, Daniel J. Luke wrote:
I should have gprof numbers on a similarly set up test machine  
soon ...


gprof output is available at http://geeklair.net/~dluke/ 
postgres_profiles/


(generated from CVS HEAD as of today).

Any ideas are welcome.

Thanks!
--
Daniel J. Luke
++
| * [EMAIL PROTECTED] * |
| *-- http://www.geeklair.net -* |
++
|   Opinions expressed are mine and do not necessarily   |
|  reflect the opinions of my employer.  |
++




PGP.sig
Description: This is a digitally signed message part

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[J. Andrew Rogers]

On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:
Empirically... postgresql built for 64 bits is marginally slower  
than that built

for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested  
on any

of those architectures.


Really? All of our customers are migrating to Opteron and I have  
many that have been using Opteron for over 12 months happily.




We have been using PostgreSQL on Opteron servers almost since the  
Opteron was first released, running both 32-bit and 64-bit versions  
of Linux.  Both 32-bit and 64-bit versions have been bulletproof for  
us, with the usual stability I've become accustomed to with both  
PostgreSQL and Linux.  We have been running nothing but 64-bit  
versions on mission-critical systems for the last year with zero  
problems.


The short story is that for us 64-bit PostgreSQL on Opterons is  
typically something like 20% faster than 32-bit on the same, and  
*much* faster than P4 Xeon systems they nominally compete with.   
AMD64 is a more efficient architecture than x86 in a number of ways,  
and the Opteron has enviable memory latency and bandwidth that make  
it an extremely efficient database workhorse.  x86->AMD64 is not a  
word-width migration, it is a different architecture cleverly  
designed to be efficiently compatible with x86.  In addition to  
things like a more RISC-like register set, AMD64 uses a different  
floating point architecture that is more efficient than the old x87.


In terms of bang for the buck in a bulletproof database server, it is  
really hard to argue with 64-bit Opterons.  They are damn fast, and  
in my experience problem free.  We run databases on other  
architectures, but they are all getting replaced with 64-bit Linux on  
Opterons because the AMD64 systems tend to be both faster and  
cheaper.  Architectures like Sparc have never given us problems, but  
they have not exactly thrilled us with their performance either.



Cheers,

J. Andrew Rogers


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

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[J. Andrew Rogers]

On Jun 13, 2006, at 1:40 AM, Nis Jorgensen wrote:

Since you sound like you have done extensive testing:

Do you have any data regarding whether to enable hyperthreading or  
not?

I realize that this may be highly dependant on the OS, application and
number of CPUs, but I would be interested in hearing your
recommendations (or others').



Hyperthreading never made much of a difference for our database  
loads.  Since we've retired all non-dev P4 database servers, I am not  
too worried about it.  We will probably re-test the new "Core 2" CPUs  
that are coming out, since those differ significantly from the P4 in  
capability.



J. Andrew Rogers


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

Re: [PERFORM] XFS filessystem for Datawarehousing

[J. Andrew Rogers]

On Aug 1, 2006, at 2:49 PM, Milen Kulev wrote:
Is anyone using XFS for storing/retrieving relatively large amount  
of data  (~ 200GB)?



Yes, we've been using it on Linux since v2.4 (currently v2.6) and it  
has been rock solid on our database servers (Opterons, running in  
both 32-bit and 64-bit mode).  Our databases are not quite 200GB  
(maybe 75GB for a big one currently), but ballpark enough that the  
experience is probably valid.  We also have a few terabyte+ non- 
database XFS file servers too.


Performance has been very good even with nearly full file systems,  
and reliability has been perfect so far. Some of those file systems  
get used pretty hard for months or years non-stop.  Comparatively, I  
can only tell you that XFS tends to be significantly faster than  
Ext3, but we never did any serious file system tuning either.


Knowing nothing else, my experience would suggest that XFS is a fine  
and safe choice for your application.



J. Andrew Rogers


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

Re: [PERFORM] linux distro for better pg performance

[J. Andrew Rogers]

On Thu, 2004-04-15 at 06:39, Gavin M. Roy wrote:
> Your IDE drive is the biggest hardward bottleneck here.  RPM's and bus 
> transfers are slower than SCSI or SATA.


Individual disk throughput generally has very little bearing on database
performance compared to other factors.  In fact, IDE bandwidth
performance is perfectly adequate for databases, and for database
purposes indistinguishable from SATA.  I would say that average access
and read/write completion times, especially under load, are by far the
most limiting factors, and disk RPM is only one component of this.  In
fact, disk RPM is a very expensive way to get marginally better
throughput in this regard, and I would suggest 10k rather than 15k
drives for the money.

There are really only two features that are worth buying in your disk
subsystem which many people ignore: TCQ and independently managed I/O
with a large battery-backed write-back cache.  Currently, the only place
to really get this is with SCSI RAID.  You can get 10k SATA drives, so
when you are buying SCSI you are really buying these features.

Do these features make a difference?  Far more than you would imagine. 
On one postgres server I just upgraded, we went from a 3Ware 8x7200-RPM
RAID-10 configuration to an LSI 320-2 SCSI 3x10k RAID-5, with 256M
cache, and got a 3-5x performance improvement in the disk subsystem
under full database load.  SCSI RAID can service a lot of I/O requests
far more efficiently than current IDE/SATA RAID controllers, and it
shows in the stats.  Under these types of loads, the actually bandwidth
utilized by the disks doesn't come anywhere close to even their rated
performance, never mind the theoretical performance of the bus.  Service
times for IDE/SATA RAID increases dramatically under load, whereas SCSI
tends not to under the same load.

Considering that very good SCSI RAID controllers (e.g. the LSI 320-2
that I mention above) are only marginally more expensive than nominally
equivalent IDE/SATA controller solutions, using SCSI RAID with 10k
drives is pretty much the price-performance sweet spot if you use your
disk system hard (like we do).  For databases with low disk I/O
intensity, stay with IDE/SATA and save a little money.  For databases
that have high disk I/O intensity, use SCSI.  The price premium for SCSI
is about 50%, but the performance difference is an integer factor under
load.


j. andrew rogers






---(end of broadcast)---
TIP 3: 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] Wierd context-switching issue on Xeon

[J. Andrew Rogers]

I decided to check the context-switching behavior here for baseline
since we have a rather diverse set of postgres server hardware, though
nothing using Xeon MP that is also running a postgres instance, and
everything looks normal under load.  Some platforms are better than
others, but nothing is outside of what I would consider normal bounds.

Our biggest database servers are Opteron SMP systems, and these servers
are particularly well-behaved under load with Postgres 7.4.2.  If there
is a problem with the locking code and context-switching, it sure isn't
manifesting on our Opteron SMP systems.  Under rare confluences of
process interaction, we occasionally see short spikes in the 2-3,000
cs/sec range.  It typically peaks at a couple hundred cs/sec under load.
Obviously this is going to be a function of our load profile a certain
extent.

The Opterons have proven to be very good database hardware in general
for us.


j. andrew rogers








---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Wierd context-switching issue on Xeon

[J. Andrew Rogers]

I verified problem on a Dual Opteron server.  I temporarily killed the
normal load, so the server was largely idle when the test was run.

Hardware:
2x Opteron 242
Rioworks HDAMA server board
4Gb RAM

OS Kernel:
RedHat9 + XFS


1 proc: 10-15 cs/sec
2 proc: 400,000-420,000 cs/sec



j. andrew rogers




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

Re: [PERFORM] Quad processor options

[J. Andrew Rogers]

On Tue, 2004-05-11 at 12:06, Bjoern Metzdorf wrote:
> Has anyone experiences with quad Xeon or quad Opteron setups? I am 
> looking at the appropriate boards from Tyan, which would be the only 
> option for us to buy such a beast. The 30k+ setups from Dell etc. don't 
> fit our budget.
> 
> I am thinking of the following:
> 
> Quad processor (xeon or opteron)
> 5 x SCSI 15K RPM for Raid 10 + spare drive
> 2 x IDE for system
> ICP-Vortex battery backed U320 Hardware Raid
> 4-8 GB Ram


Just to add my two cents to the fray:

We use dual Opterons around here and prefer them to the Xeons for
database servers.  As others have pointed out, the Opteron systems will
scale well to more than two processors unlike the Xeon.  I know a couple
people with quad Opterons and it apparently scales very nicely, unlike
quad Xeons which don't give you much more.  On some supercomputing
hardware lists I'm on, they seem to be of the opinion that the current
Opteron fabric won't really show saturation until you have 6-8 CPUs
connected to it.

Like the other folks said, skip the 15k drives.  Those will only give
you a marginal improvement for an integer factor price increase over 10k
drives.  Instead spend your money on a nice RAID controller with a fat
cache and a backup battery, and maybe some extra spindles for your
array.  I personally like the LSI MegaRAID 320-2, which I always max out
to 256Mb of cache RAM and the required battery.  A maxed out LSI 320-2
should set you back <$1k.  Properly configured, you will notice large
improvements in the performance of your disk subsystem, especially if
you have a lot of writing going on.

I would recommend getting the Opterons, and spending the relatively
modest amount of money to get nice RAID controller with a large
write-back cache while sticking with 10k drives.

Depending on precisely how you configure it, this should cost you no
more than $10-12k.  We just built a very similar configuration, but with
dual Opterons on an HDAMA motherboard rather than a quad Tyan, and it
cost <$6k inclusive of everything.  Add the money for 4 of the 8xx
processors and the Tyan quad motherboard, and the sum comes out to a
very reasonable number for what you are getting.


j. andrew rogers




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

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] Configuring PostgreSQL to minimize impact of

[J. Andrew Rogers]

On Tue, 2004-05-11 at 14:52, Paul Tuckfield wrote:
> Love that froogle.
> 
> It looks like a nice card. One thing I didn't get straight is if 
> the cache is writethru or write back.


The LSI MegaRAID reading/writing/caching behavior is user configurable.
It will support both write-back and write-through, and IIRC, three
different algorithms for reading (none, read-ahead, adaptive).  Plenty
of configuration options.

It is a pretty mature and feature complete hardware RAID implementation.


j. andrew rogers


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

   http://archives.postgresql.org

Re: [PERFORM] [OFF-TOPIC] - Known maximum size of the PostgreSQL

[J. Andrew Rogers]

On Sat, 2004-06-05 at 11:55, Carlos Eduardo Smanioto wrote:
> What's the case of bigger database PostgreSQL (so greate and amount of
> registers) that they know???


You might want to fix the month on your system time.

With respect to how big PostgreSQL databases can get in practice, these
are our two biggest implementations:

- 0.5 Tb GIS database (this maybe upwards of 600-700Gb now, I didn't
check)

- 10 Gb OLTP system with 70 million rows and a typical working set of
2-3 Gb.


Postgres is definitely capable of handling large pretty databases with
ease.  There are some narrow types of workloads that it doesn't do so
well on, but for many normal DBMS loads it scales quite well.


j. andrew rogers



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

[PERFORM] arrays and indexes

[Ross J. Reedstrom]

Hi all - 
I've got a schema I'm working on modifying, nad I need some help getting
the best performance out. The orginal schema has a many to many linkage
between a couple tables, using a two column linkage table. This is used
to represent groups of people and their relationship to an object
(authors, copyrightholders, maintainers) This worked fine, and, with the
right indixes, is quite zippy. Approximate schems:

table content (
contentid serial,
name text,
<...>
authorgroupid int,
cpholdergroupid int,
maintgroupid int)

table groups (
personid text,
groupid int)

Note that neither grouid nor personid are unique.

Now the users want not just groups, but ordered lists. Well, that's just
fine: we could do it with another column in the groups linkage table,
and some additional logic in the middleware for detecting identical
groups, but it occured to me that PG's array types are just the ticket
for ordered lists like this.

So, by dropping arrays of personids (authors, copyrightholders,
maintainers, ...) into the content table, I can do everything I need.

Only one problem. Retreiving all the content for a particular
person/role is fairly common. Queries of the form:

SELECT * from content c join groups g on c.authorgroupid = g.personid
where personid = 'ross';

work fine and use the index on groups.personid.

In the new schema, the same thing is:

SELECT * from content where 42 = ANY (authors);

Works fine, but for the life of me I can't find nor figure out how to
build an index that will be used to speed this along. Any ideas?

I'm using 7.4.3, BTW.

Ross
-- 
Ross Reedstrom, Ph.D. [EMAIL PROTECTED]
Research Scientist  phone: 713-348-6166
The Connexions Project  http://cnx.rice.edu   fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE



---(end of broadcast)---
TIP 3: 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] arrays and indexes

[Ross J. Reedstrom]

On Mon, Jul 26, 2004 at 02:27:20AM -0400, Greg Stark wrote:
> 
> "Ross J. Reedstrom" <[EMAIL PROTECTED]> writes:
> 
> > In the new schema, the same thing is:
> > 
> > SELECT * from content where 42 = ANY (authors);
> > 
> > Works fine, but for the life of me I can't find nor figure out how to
> > build an index that will be used to speed this along. Any ideas?
> 
> Well that's basically the problem with denormalized data like this.
> 
> Have you resolved what you're going to do if two sessions try to add a user to
> the same group at the same time? Or how you'll go about removing a user from
> all his groups in one shot?

We've got plenty of interlocks in the middleware to handle the first
(mainly because this is an authoring system where everyone has to agree
to participate, and acknowledge the open license on the materials)

Second, they _can't_ be removed: we're effectively a write only archive.
Even if we weren't it would be a rare event and could go slowly (loop
over groups in the middleware, probably)

> 
> Basically, if you denormalize in this fashion it becomes hard to use the
> groups as anything but single monolithic objects. Whereas normalized data can
> be queried and updated from other points of view like in the case you name
> above.

These groups _really are_ ideal for Joe Conway's work on arrays: we need
ordered vectors, so we'd be sorting all the time, otherwise. They're
static, and they're read only. The one thing they're not is fixed, known
size (Sorry Merlin). They work fine for the query as shown: the only
issue is performance.

> Postgres does have a way to do what you ask, though. It involves GiST
> indexes and the operators from the contrib/intarray directory from the
> Postgres source.

Well, yes, that's how it used to be done. I figured the new array
support should be able to handle it without the addon, however.

> However I warn you in advance that this is fairly esoteric stuff and
> will take some time to get used to. And at least in my case I found
> the indexes didn't actually help much for my data sets, probably
> because they just weren't big enough to benefit.

I know that they should help in  this case: we've got lots of content.
Any particular author or maintainter will be in a small fraction of
those. i.e.: it's ideal for an index. And the current joined case uses
an index, when it's available. I'll take a look at the GiST/contrib work,
anyway. 

Thanks - 

Ross 
-- 
Ross Reedstrom, Ph.D. [EMAIL PROTECTED]
Research Scientist  phone: 713-348-6166
The Connexions Project  http://cnx.rice.edu   fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

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

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

[J. Andrew Rogers]

On Tue, 2004-08-24 at 22:28, Mischa Sandberg wrote:
> I see that PG has a one-shot CLUSTER command, but doesn't support
> continuously-updated clustered indexes.
> 
> What I infer from newsgroup browsing is, such an index is impossible,
> given the MVCC versioning of records (happy to learn I'm wrong).


It is possible to have MVCC and ordered/indexed heaps, but it isn't
something you can just tack onto the currently supported types -- I
looked into this myself.  It would take substantial additional code
infrastructure to support it, basically an alternative heap system and
adding support for tables with odd properties to many parts of the
system.  Pretty non-trivial.

This is probably my #1 "I wish postgres had this feature" feature.  It
is a serious scalability enhancer for big systems and a pain to work
around not having.


> I'd be curious to know what other people, who've crossed this same
> bridge from MSSQL or Oracle or Sybase to PG, have devised,
> faced with the same kind of desired performance gain for retrieving
> blocks of rows with the same partial key.


The CLUSTER command is often virtually useless for precisely the kinds
of tables that need to be clustered.  My databases are on-line 24x7, and
the tables that are ideal candidates for clustering are in the range of
50-100 million rows. I can afford to lock these tables up for no more
than 5-10 minutes during off-peak in the hopes that no one notices, and
CLUSTER does not work remotely in the ballpark of that fast for tables
of that size.  People who can run CLUSTER in a cron job must either have
relatively small tables or regular large maintenance windows.


My solution, which may or may not work for you, was to write a table
partitioning system using the natural flexibility and programmability of
postgresql (e.g. table inheritance).  From this I automatically get a
roughly ordered heap according to the index I would cluster on, with
only slightly funky SQL access.  The end result works much better with
CLUSTER too, though CLUSTER is much less necessary at that point
because, at least for my particular purposes, the rows are mostly
ordered due to how the data was partitioned.

So there are ways to work around CLUSTER, but you'll have to be clever
and it will require tailoring the solution to your particular
requirements.


J. Andrew Rogers




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

   http://archives.postgresql.org

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

[J. Andrew Rogers]

On Thu, 2004-08-26 at 11:18, Bruce Momjian wrote:
> How do vendors actually implement auto-clustering?  I assume they move
> rows around during quiet periods or have lots of empty space in each
> value bucket.


As far as I know, Oracle does it by having a B-Tree organized heap (a
feature introduced around v8 IIRC), basically making the primary key
index and the heap the same physical structure.  Any non-index columns
are stored in the index along with the index columns.  Implementing it
is slightly weird because searching the index and selecting the rows
from the heap are not separate operations.

The major caveat to having tables of this type is that you can only have
a primary key index.  No other indexes are possible because the "heap"
constantly undergoes local reorganizations if you have a lot of write
traffic, the same kind of reorganization you would normally expect in a
BTree index.

The performance improvements come from two optimizations.  First, you
have to touch significantly fewer blocks to get all the rows, even
compared to a CLUSTERed heap.  Second, the footprint is smaller and
plays nicely with the buffer cache.

When I've used these types of heaps in Oracle 8 on heavily used tables
with tens of millions of rows, we frequently got a 10x or better
performance improvement on queries against those tables.  It is only
really useful for tables with vast quantities of relatively small rows,
but it can be a lifesaver in those cases.


J. Andrew Rogers



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

Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?

[J. Andrew Rogers]

On Thu, 2004-08-26 at 12:30, Magnus Hagander wrote:
> Almost the same for MSSQL. The clustered index is always forced unique.
> If you create a non-unique clustered index, SQLServer will internally
> pad it with random (or is it sequential? Can't remember right now) data
> to make each key unique. The clustered index contains all the data
> fields - both the index key and the other columns from the database.
> 
> It does support non-clustered indexes as well on the same table. Any
> "secondary index" will then contain the index key and the primary key
> value. This means a lookup in a non-clustered index means a two-step
> index lookup: First look in the non-clustered index for the clustered
> key. Then look in the clustered index for the rest of the data.


Ah, okay.  I see how that would work for a secondary index, though it
would make for a slow secondary index.  Neat workaround.  For all I
know, current versions of Oracle may support secondary indexes on
index-organized tables; all this Postgres usage over the last couple
years has made my Oracle knowledge rusty.


> IIRC, SQL Server always creates clustered indexes by default for primary
> keys.


That would surprise me actually.  For some types of tables, e.g. ones
with multiple well-used indexes or large rows, index-organizing the heap
could easily give worse performance than a normal index/heap pair
depending on access patterns.  It also tends to be more prone to having
locking contention under some access patterns.  This is one of those
options that needs to be used knowledgeably; it is not a general
architectural improvement that you would want to apply to every table
all the time.


J. Andrew Rogers




---(end of broadcast)---
TIP 3: 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] Table UPDATE is too slow

[Marinos J. Yannikos]

Ron St-Pierre wrote:
We have a web based application with data that is updated daily. The 
biggest bottleneck occurs when we try to update
one of the tables. This table contains 58,000 rows and 62 columns, and 
EVERY column is indexed.
Have you thought of / tried using 2 separate databases or tables and 
switching between them? Since you seem to be updating all the values, it 
might be a lot faster to re-create the table from scratch without 
indexes and add those later (maybe followed by a VACUUM ANALYZE) ...

That said, I'm not entirely sure how well postgres' client libraries can 
deal with tables being renamed while in use, perhaps someone can shed 
some light on this.

Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[PERFORM] Partitioning

[J. Andrew Rogers]

On Tue, 2004-09-14 at 21:30, Joe Conway wrote:
> That's exactly what we're doing, but using inherited tables instead of a 
> union view. With inheritance, there is no need to rebuild the view each 
> time a table is added or removed. Basically, in our application, tables 
> are partitioned by either month or week, depending on the type of data 
> involved, and queries are normally date qualified.



We do something very similar, also using table inheritance and a lot of 
triggers to automatically generate partitions and so forth.  It works
pretty well, but it is a custom job every time I want to implement a
partitioned table.  You can save a lot on speed and space if you use it
to break up large tables with composite indexes, since you can drop
columns from the table depending on how you use it.  A big part of
performance gain is that the resulting partitions end up being more
well-ordered than the non-partitioned version, since inserts are hashed
to different partition according to the key and hash function.  It is
kind of like a cheap and dirty real-time CLUSTER operation.  It also
lets you truncate, lock, and generally be heavy-handed with subsets of
the table without affecting the rest of the table.


I think generic table partitioning could pretty much be built on top of
existing capabilities with a small number of tweaks.

The main difference would be the ability to associate a partitioning
hash function with a table (probably defined inline at CREATE TABLE
time).  Something with syntax like:

...PARTITION ON 'date_trunc(''hour'',ts)'...

There would also probably need to be some type of metadata table to
associate specific hashes with partition table names.  Other than that,
the capabilities largely already exist, and managing the partition
hashing and association is the ugly part when rolling your own. 
Intercepting DML when necessary and making it behave correctly is
already pretty easy, but could probably be streamlined.


j. andrew rogers




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

Re: [PERFORM] Partitioning

[J. Andrew Rogers]

On Thu, 2004-09-16 at 13:39, Jim C. Nasby wrote:
> Forgive my ignorance, but I didn't think you could have a table that
> inherits from a parent not have all the columns. Or is that not what you
> mean by 'you can drop columns from the table...'?
> 
> This is one advantage I see to a big UNION ALL view; if you're doing
> partitioning based on unique values, you don't actually have to store
> that value in the partition tables. For example,
> http://stats.distributed.net has a table that details how much work each
> participant did each day for each project. Storing project_id in that
> table is an extra 4 bytes... doesn't sound like much until you consider
> that the table has over 130M rows right now. So it would be nice to have
> an easy way to partition the table based on unique project_id's and not
> waste space in the partition tables on a field that will be the same for
> every row (in each partition).


Yeah, it is harder to do this automagically, though in theory it should
be possible.  Since we have to roll our own partitioning anyway, we've
broken up composite primary keys so that one of the key columns hashes
to a partition, using the key itself in the partition table name rather
than replicating that value several million times. Ugly as sin, but you
can make it work in some cases.

I do just enough work for our queries to behave correctly, and a lot of
times I actually hide the base table and its descendents underneath a
sort of metadata table that is grafted to the base tables by a lot of
rules/triggers/functions/etc, and then do queries against that or a view
of that. As I said, ugly as sin and probably not universal, but you need
a lot of abstraction to make it look halfway normal.  I'm going to think
about this some more and see if I can't construct a generic solution.


cheers,

j. andrew rogers



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

Re: [PERFORM] First set of OSDL Shared Mem scalability results,

[J. Andrew Rogers]

I have an idea that makes some assumptions about internals that I think
are correct.

When you have a huge number of buffers in a list that has to be
traversed to look for things in cache, e.g. 100k, you will generate an
almost equivalent number of cache line misses on the processor to jump
through all those buffers.  As I understand it (and I haven't looked so
I could be wrong), the buffer cache is searched by traversing it
sequentially.  OTOH, it seems reasonable to me that the OS disk cache
may actually be using a tree structure that would generate vastly fewer
cache misses by comparison to find a buffer.  This could mean a
substantial linear search cost as a function of the number of buffers,
big enough to rise above the noise floor when you have hundreds of
thousands of buffers.

Cache misses start to really add up when a code path generates many,
many thousands of them, and differences in the access path between the
buffer cache and disk cache would be reflected when you have that many
buffers.  I've seen these types of unexpected performance anomalies
before that got traced back to code patterns and cache efficiency and
gotten integer factors improvements by making some seemingly irrelevant
code changes.

So I guess my question would be 1) are my assumptions about the
internals correct, and 2) if they are, is there a way to optimize
searching the buffer cache so that a search doesn't iterate over a
really long buffer list that is bottlenecked on cache line replacement. 

My random thought of the day,

j. andrew rogers



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

   http://archives.postgresql.org

Re: [PERFORM] Insertion puzzles

[J. Andrew Rogers]

On Sat, 2004-11-13 at 18:00, [EMAIL PROTECTED] wrote:
> I ran into the exact same problem you did.  I tried many, many changes to
> the conf file, I tried O.S. tuning but performance stunk.  I had a fairly
> simple job that had a lot of updates and inserts that was taking 4 1/2
> hours.  I re-wrote it to be more "Postgres friendly" - meaning less
> database updates  and got it down under 2 1/2 hours (still horrible). 
> Understand, the legacy non-postgres ISAM db took about 15 minutes to
> perform the same task.  I assumed it was a system problem that would go
> away when we upgraded servers but it did not.  I converted to MySQL and the
> exact same java process takes 5  minutes! Postgres is a great DB for some,
> for our application it was not - you may want to consider other products
> that are a bit faster and do not require the vacuuming of stale data.


I have to wonder if the difference is in how your job is being chopped
up by the different connection mechanisms.  The only time I've had
performance problems like this, it was the result of pathological and
unwelcome behaviors in the way things were being handled in the
connector or database design.

We have a 15GB OLTP/OLAP database on five spindles with a large
insert/update load and >100M rows, and I don't think it takes 2.5 hours
to do *anything*.  This includes inserts/updates of hundreds of
thousands of rows at a shot, which takes very little time.

I've gotten really bad performance before under postgres, but once I
isolated the reason I've always gotten performance that was comparable
to any other commercial RDBMS on the same hardware.  


J. Andrew Rogers



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

[PERFORM] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Hi,
according to 
http://www.postgresql.org/docs/8.0/interactive/limitations.html , 
concurrent access to GiST indexes isn't possible at the moment. I 
haven't read the thesis mentioned there, but I presume that concurrent 
read access is also impossible. Is there any workaround for this, esp. 
if the index is usually only read and not written to?

It seems to be a big problem with tsearch2, when multiple clients are 
hammering the db (we have a quad opteron box here that stays 75% idle 
despite an apachebench with concurrency 10 stressing the php script that 
uses tsearch2, with practically no disk accesses)

Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Oleg Bartunov wrote:
On Thu, 3 Feb 2005, Marinos J. Yannikos wrote:
concurrent access to GiST indexes isn't possible at the moment. I [...]
there are should no problem with READ access.
OK, thanks everyone (perhaps it would make sense to clarify this in the 
manual).

I'm willing to see some details: version, query, explain analyze.
8.0.0
Query while the box is idle:
explain analyze select count(*) from fr_offer o, fr_merchant m where 
idxfti @@ to_tsquery('ranz & mc') and eur >= 70 and m.m_id=o.m_id;

Aggregate  (cost=2197.48..2197.48 rows=1 width=0) (actual 
time=88.052..88.054 rows=1 loops=1)
   ->  Merge Join  (cost=2157.42..2196.32 rows=461 width=0) (actual 
time=88.012..88.033 rows=3 loops=1)
 Merge Cond: ("outer".m_id = "inner".m_id)
 ->  Index Scan using fr_merchant_pkey on fr_merchant m 
(cost=0.00..29.97 rows=810 width=4) (actual time=0.041..1.233 rows=523 
loops=1)
 ->  Sort  (cost=2157.42..2158.57 rows=461 width=4) (actual 
time=85.779..85.783 rows=3 loops=1)
   Sort Key: o.m_id
   ->  Index Scan using idxfti_idx on fr_offer o 
(cost=0.00..2137.02 rows=461 width=4) (actual time=77.957..85.754 rows=3 
loops=1)
 Index Cond: (idxfti @@ '\'ranz\' & \'mc\''::tsquery)
 Filter: (eur >= 70::double precision)

 Total runtime: 88.131 ms
now, while using apachebench (-c10), "top" says this:
Cpu0  : 15.3% us, 10.0% sy,  0.0% ni, 74.7% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu1  : 13.3% us, 11.6% sy,  0.0% ni, 75.1% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu2  : 16.9% us,  9.6% sy,  0.0% ni, 73.4% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu3  : 18.7% us, 14.0% sy,  0.0% ni, 67.0% id,  0.0% wa,  0.0% hi,  0.3% si
(this is with shared_buffers = 2000; a larger setting makes almost no 
difference for overall performance: although according to "top" system 
time goes to ~0 and user time to ~25%, the system still stays 70-75% idle)

vmstat:
 r  b   swpd   free   buff  cache   si   sobibo   incs us 
sy id wa
 2  0  0 8654316  64908 4177136005635  279   286  5 
 1 94  0
 2  0  0 8646188  64908 417713600 0 0 1156  2982 15 
10 75  0
 2  0  0 8658412  64908 417713600 0 0 1358  3098 19 
11 70  0
 1  0  0 8646508  64908 417713600 0   104 1145  2070 13 
12 75  0

so the script's execution speed is apparently not limited by the CPUs.
The query execution times go up like this while apachebench is running 
(and the system is 75% idle):

 Aggregate  (cost=2197.48..2197.48 rows=1 width=0) (actual 
time=952.661..952.663 rows=1 loops=1)
   ->  Merge Join  (cost=2157.42..2196.32 rows=461 width=0) (actual 
time=952.621..952.641 rows=3 loops=1)
 Merge Cond: ("outer".m_id = "inner".m_id)
 ->  Index Scan using fr_merchant_pkey on fr_merchant m 
(cost=0.00..29.97 rows=810 width=4) (actual time=2.078..3.338 rows=523 
loops=1)
 ->  Sort  (cost=2157.42..2158.57 rows=461 width=4) (actual 
time=948.345..948.348 rows=3 loops=1)
   Sort Key: o.m_id
   ->  Index Scan using idxfti_idx on fr_offer o 
(cost=0.00..2137.02 rows=461 width=4) (actual time=875.643..948.301 
rows=3 loops=1)
 Index Cond: (idxfti @@ '\'ranz\' & \'mc\''::tsquery)
 Filter: (eur >= 70::double precision)
 Total runtime: 952.764 ms

I can't seem to find out where the bottleneck is, but it doesn't seem to 
be CPU or disk. "top" shows that postgres processes are frequently in 
this state:

  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  WCHAN 
COMMAND
 6701 postgres  16   0  204m  58m  56m S  9.3  0.2   0:06.96 semtimedo
 ^
postmaste

Any hints are appreciated...
Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Oleg Bartunov wrote:
Marinos,
what if you construct "apachebench & Co" free  script and see if
the issue still exists. There are could be many issues doesn't
connected to postgresql and tsearch2.
Yes, the problem persists - I wrote a small perl script that forks 10 
chils processes and executes the same queries in parallel without any 
php/apachebench involved:

--- 8< ---
#!/usr/bin/perl
use DBI;
$n=10;
$nq=100;
$sql="select count(*) from fr_offer o, fr_merchant m where idxfti @@ 
to_tsquery('ranz & mc') and eur >= 70 and m.m_id=o.m_id;";

sub reaper { my $waitedpid = wait; $running--; $SIG{CHLD} = \&reaper; }
$SIG{CHLD} = \&reaper;
for $i (1..$n)
{
if (fork() > 0) { $running++; }
else
{
my 
$dbh=DBI->connect('dbi:Pg:host=daedalus;dbname=','root','',{
 AutoCommit => 1 }) || die "!db";
for my $j (1..$nq)
{
my $sth=$dbh->prepare($sql);
$r=$sth->execute() or print STDERR $dbh->errstr();
}
exit 0;
}
}
while ($running > 0)
{
sleep 1;
print "Running: $running\n";
}
--- >8 ---

Result (now with shared_buffers = 2, hence less system and more user 
time):

Cpu0  : 25.1% us,  0.0% sy,  0.0% ni, 74.9% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu1  : 18.3% us,  0.0% sy,  0.0% ni, 81.7% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu2  : 27.8% us,  0.3% sy,  0.0% ni, 71.9% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu3  : 23.5% us,  0.3% sy,  0.0% ni, 75.9% id,  0.0% wa,  0.0% hi,  0.3% si
  PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  WCHAN 
COMMAND
 7571 postgres  16   0  204m  62m  61m R 10.6  0.2   0:01.97 - 
postmaste
 7583 postgres  16   0  204m  62m  61m S  9.6  0.2   0:02.06 semtimedo 
postmaste
 7586 postgres  16   0  204m  62m  61m S  9.6  0.2   0:02.00 semtimedo 
postmaste
 7575 postgres  16   0  204m  62m  61m S  9.3  0.2   0:02.12 semtimedo 
postmaste
 7578 postgres  16   0  204m  62m  61m R  9.3  0.2   0:02.05 - 
postmaste

i.e., virtually no difference. With 1000 queries and 10 in parallel, the 
apachebench run takes 60.674 seconds and the perl script 59.392 seconds.

Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 3: 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] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Tom Lane schrieb:
What's the platform exactly (hardware and OS)?
Hardware: http://www.appro.com/product/server_1142h.asp
- SCSI version, 2 x 146GB 10k rpm disks in software RAID-1
- 32GB RAM
OS: Linux 2.6.10-rc3, x86_64, debian GNU/Linux distribution
- CONFIG_K8_NUMA is currently turned off (no change, but now all CPUs 
have ~25% load, previously one was 100% busy and the others idle)

- CONFIG_GART_IOMMU=y (but no change, tried both settings)
[other kernel options didn't seem to be relevant for tweaking at the 
moment, mostly they're "safe defaults"]

The PostgreSQL data directory is on an ext2 filesystem.
Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustrasse 63, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 3: 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] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Tom Lane wrote:
You might try the attached patch (which I just applied to HEAD).
It cuts down the number of acquisitions of the BufMgrLock by merging
adjacent bufmgr calls during a GIST index search.  [...]
Thanks - I applied it successfully against 8.0.0, but it didn't seem to 
have a noticeable effect. I'm still seeing more or less exactly 25% CPU 
usage by postgres processes and identical query times (measured with the 
Perl script I posted earlier).

Regards,
 Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustrasse 63, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] GiST indexes and concurrency (tsearch2)

[Marinos J. Yannikos]

Tom Lane wrote:
I'm not completely convinced that you're seeing the same thing,
but if you're seeing a whole lot of semops then it could well be.
I'm seeing ~280 semops/second with spinlocks enabled and ~80k 
semops/second (> 4 mil. for 100 queries) with --disable-spinlocks, which 
increases total run time by ~20% only. In both cases, cpu usage stays 
around 25%, which is a bit odd.

[...]You said
you're testing a quad-processor machine, so it could be that you're
seeing the same lock contention issues that we've been trying to figure
out for the past year ...
Are those issues specific to a particular platform (only x86/Linux?) or 
is it a problem with SMP systems in general? I guess I'll be following 
the current discussion on -hackers closely...

Regards,
 Marinos
---(end of broadcast)---
TIP 3: 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

[PERFORM] 7 hrs for a pg_restore?

[Douglas J Hunley]

I spent a whopping seven hours restoring a database late Fri nite for a 
client. We stopped the application, ran pg_dump -v -Ft -b -o $db > 
~/pre_8.3.tar on the 8.2.x db, and then upgrading the software to 8.3. I then 
did a pg_restore -v -d $db ./pre_8.3.tar and watched it positively crawl.
I'll grant you that it's a 5.1G tar file, but 7 hours seems excessive. 

Is that kind of timeframe 'abnormal' or am I just impatient? :) If the former, 
I can provide whatever you need, just ask for it. 
Thanks!
-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

I've been dying to hit something since I pressed "1" to join your conference.

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

Re: [PERFORM] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 13:12:54 Joshua D. Drake wrote:
> > I spent a whopping seven hours restoring a database late Fri nite for
> > a client. We stopped the application, ran pg_dump -v -Ft -b -o $db >
> > ~/pre_8.3.tar on the 8.2.x db, and then upgrading the software to
> > 8.3. I then did a pg_restore -v -d $db ./pre_8.3.tar and watched it
> > positively crawl. I'll grant you that it's a 5.1G tar file, but 7
> > hours seems excessive.
> >
> > Is that kind of timeframe 'abnormal' or am I just impatient? :) If
> > the former, I can provide whatever you need, just ask for it.
> > Thanks!
>
> 7 hours for 5.1 G is excessive. It took me 11 hours to do 220G :). It
> would be helpful if we knew what the machine was doing. Was it IO
> bound? How much ram does it have? Is it just a single HD drive? What
> are your settings for postgresql?

It wasn't doing anything but the restore. Dedicated DB box

postgresql.conf attached

system specs:
Intel(R) Xeon(TM) CPU 3.40GHz (dual, so shows 4 in Linux)

MemTotal:  8245524 kB

The db resides on a HP Modular Storage Array 500 G2. 4x72.8Gb 15k rpm disks. 1 
raid 6 logical volume. Compaq Smart Array 6404 controller

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

We do nothing *FOR* users. We do things *TO* users. It's a fine distinction, 
but an important one all the same.
# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some paramters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes MB = megabytes GB = gigabytes
# Time units:ms = milliseconds s = seconds min = minutes h = hours d = days


#--
# FILE LOCATIONS
#--

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'   # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'   # write an extra PID file
# (change requires restart)


#--
# CONNECTIONS AND AUTHENTICATION
#--

# - Connection Settings -

listen_addresses = '*'  # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432# (change requires restart)
max_connections = 200   # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).  You might
# also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
  

Re: [PERFORM] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 13:13:37 Richard Huxton wrote:
> Douglas J Hunley wrote:
> > I spent a whopping seven hours restoring a database late Fri nite for a
> > client. We stopped the application, ran pg_dump -v -Ft -b -o $db >
> > ~/pre_8.3.tar on the 8.2.x db, and then upgrading the software to 8.3. I
> > then did a pg_restore -v -d $db ./pre_8.3.tar and watched it positively
> > crawl. I'll grant you that it's a 5.1G tar file, but 7 hours seems
> > excessive.
>
> Depends, both on the machine and the database.
>
> What sort of disk i/o are you seeing, what's the cpu(s) doing, and
> what's the restore taking so long over (since you have -v)?

The I/O didn't seem abnormal to me for this customer, so I didn't record it. 
It wasn't excessive though. It took the longest on a couple of our highest 
volume tables. By far index creation took the longest of the entire process

>
> Oh, and have you tweaked the configuration settings for the restore?
> Lots of work_mem, turn fsync off, that sort of thing.

I didn't tweak anything for the restore specifically. Used the postgresql.conf 
as attached in another reply


-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

One item could not be deleted because it was missing.  -- Mac System 7.0b1 
error message

---(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] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 13:22:58 Tom Lane wrote:
> Richard Huxton <[EMAIL PROTECTED]> writes:
> > Douglas J Hunley wrote:
> >> I spent a whopping seven hours restoring a database late Fri nite for a
> >
> > Oh, and have you tweaked the configuration settings for the restore?
> > Lots of work_mem, turn fsync off, that sort of thing.
>
> maintenance_work_mem, to be more specific.  If that's too small it will
> definitely cripple restore speed.  I'm not sure fsync would make much
> difference, but checkpoint_segments would.  See
> http://www.postgresql.org/docs/8.3/static/populate.html#POPULATE-PG-DUMP

from the postgresql.conf i posted:
~ $ grep maint postgresql.conf 
maintenance_work_mem = 256MB# min 1MB

thx for the pointer to the URL. I've made note of the recommendations therein 
for next time.

>
> Also: why did you choose -o ... was there a real need to?  I can see
> that being pretty expensive.
>

I was under the impression our application made reference to OIDs. I'm now 
doubting that heavily  and am seeking confirmation.

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

I've got trouble with the wife again - she came into the bar looking for me 
and I asked her for her number.

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

Re: [PERFORM] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 13:23:23 Jeff Davis wrote:
> On Tue, 2008-02-19 at 13:03 -0500, Douglas J Hunley wrote:
> > I spent a whopping seven hours restoring a database late Fri nite for a
> > client. We stopped the application, ran pg_dump -v -Ft -b -o $db >
> > ~/pre_8.3.tar on the 8.2.x db, and then upgrading the software to 8.3. I
> > then did a pg_restore -v -d $db ./pre_8.3.tar and watched it positively
> > crawl. I'll grant you that it's a 5.1G tar file, but 7 hours seems
> > excessive.
>
> Are there lots of indexes on localized text attributes? If you have a
> big table with localized text (e.g. en_US.UTF-8), it can take a long
> time to build the indexes. If the file is 5GB compressed, I wouldn't be
> surprised if it took a long time to restore.
>
> Keep in mind, if you have several GB worth of indexes, they take up
> basically no space in the logical dump (just the "CREATE INDEX" command,
> and that's it). But they can take a lot of processor time to build up
> again, especially with localized text.
>

that could be a factor here. It is a UNICODE db, and we do a lot of text-based 
indexing for the application

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Be courteous to everyone, friendly to no one.

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

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

Re: [PERFORM] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 14:28:54 Dave Cramer wrote:
> shared buffers is *way* too small as is effective cache
> set them to 2G/6G respectively.
>
> Dave

pardon my ignorance, but is this in the context of a restore only? or 'in 
general'?

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Don't let Kirk show you what he affectionately calls the "Captain's Log"

---(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] 7 hrs for a pg_restore?

[Douglas J Hunley]

On Tuesday 19 February 2008 15:16:42 Dave Cramer wrote:
> On 19-Feb-08, at 2:35 PM, Douglas J Hunley wrote:
> > On Tuesday 19 February 2008 14:28:54 Dave Cramer wrote:
> >> shared buffers is *way* too small as is effective cache
> >> set them to 2G/6G respectively.
> >>
> >> Dave
> >
> > pardon my ignorance, but is this in the context of a restore only?  
> > or 'in
> > general'?
>
> This is the "generally accepted" starting point for a pg db for  
> production.

fair enough. I have scheduled this change for the next outage

-- 
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

"The internet has had no impact on my life whatsoever.com" - anon

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate