[PERFORM] Scaling up PostgreSQL in Multiple CPU / Dual Core Powered Servers

[Jojo Paderes]

I'd like to know if the latest PostgreSQL release can scale up by
utilizing multiple cpu or dual core cpu to boost up the sql
executions.

I already do a research on the PostgreSQL mailing archives and only
found old threads dating back 2000. A lot of things have improved with
PostgreSQL and hopefully the support for multiple cpu or dual cores is
already provided.

--
http://jojopaderes.multiply.com
http://jojopaderes.wordpress.com

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Qingqing Zhou]

"Simon Riggs" <[EMAIL PROTECTED]> wrote
> On Tue, 2006-03-21 at 06:22 -0600, Jim C. Nasby wrote:
> > Currently, it appears that SELECT * INTO new_table FROM old_table logs
> > each page as it's written to WAL. Is this actually needed? Couldn't the
> > database simply log that the SELECT ... INTO statement was executed
> > instead? Doing so would likely result in a large performance improvement
> > in most installs. Is there no provision for writing anything but data
> > page changes (or whole pages) to WAL?
>
> AFAIK it takes the same code path as CREATE TABLE AS SELECT, which
> already does exactly what you suggest (except when using PITR).
>

As I read, they did take the same code path, but did they "simply log that
the SELECT ... INTO statement was executed"? If so, how can we rely on the
unreliable content of the old_table to do recovery?

Regards,
Qingqing



---(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] WAL logging of SELECT ... INTO command

[Kris Jurka]

On Wed, 22 Mar 2006, Jim C. Nasby wrote:


Ok, I saw disk activity on the base directory and assumed it was pg_xlog
stuff. Turns out that both SELECT INTO and CREATE TABLE AS ignore
default_tablepsace and create the new tables in the base directory. I'm
guessing that's a bug... (this is on 8.1.2, btw).


This has been fixed in CVS HEAD as part of a patch to allow additional 
options to CREATE TABLE AS.


http://archives.postgresql.org/pgsql-patches/2006-02/msg00211.php

Kris Jurka


---(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] Intel C/C++ Compiler Tests

[Luke Lonergan]

Greg,


On 3/22/06 5:56 AM, "Spiegelberg, Greg" <[EMAIL PROTECTED]> wrote:

> Has anyone tested PostgreSQL 8.1.x compiled with Intel's Linux C/C++
> compiler?

We used to compile 8.0 with icc and 7.x before that.  We found very good
performance gains for Intel P4 architecture processors and some gains for
AMD Athlon.

Lately, the gcc compilers have caught up with icc on pipelining
optimizations and they generate better code for Opteron than icc, so we
found that icc was significantly slower than gcc on Opteron and no different
on P4/Xeon.

Maybe things have changed in newer versions of icc, the last tests I did
were about 1 year ago.

- Luke 



---(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] planner with index scan cost way off actual cost,

[Guillaume Cottenceau]

Hi Scott,

Scott Marlowe  writes:

> On Wed, 2006-03-22 at 02:04, Guillaume Cottenceau wrote:

[...]

> > Yes, we use 7.4.5 actually, because "it just works", so production
> > wants to first deal with all the things that don't work before
> > upgrading. I have recently discovered about the background writer
> > of 8.x which could be a supplementary reason to push for an
> > ugprade though.
> 
> Imagine you get a call from the manufacturer of your car.  There's a
> problem with the fuel pump, and, in a small percentage of accidents,
> your car could catch fire and kill everyone inside.
> 
> Do you go in for the recall, or ignore it because you just want your car
> to "just work?"

Ah, this holy computer/OS/whatever-to-cars comparison.. How many
million electrons would the world save if computer people would
abandon it? :)

> In the case of the third number in postgresql releases, that's what
> you're talking about.  the updates that have come after the 7.4.5
> version, just talking 7.4 series here, have included a few crash and
> data loss fixes.  Rare, but possible.

I guess we didn't know that. I for myself have (a bit more)
excuses because I'm on the development side :) But I've passed
the information to the operation team, thank you.

-- 
Guillaume Cottenceau

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Csaba Nagy]

On Wed, 2006-03-22 at 16:35, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Oh, so in other words, SELECT * INTO temp FROM table is inherently
> > non-deterministic at the physical level, so the only way to be able to
> > allow PITR to work is to duplicate all the physical changes. Darn.
> 
> Well, lemme put it this way: I'm not prepared to require that PG be
> deterministic at the physical level.  One obvious source of
> non-determinancy is the FSM, which is likely to hand out different free
> space to different transactions depending on what else is going on at
> the same time.  There are others, such as deliberately random
> tie-breaking during btree index insertion.

While you're at talking about WAL and PITR... I see from the aboce
discussion that PITR is already demanding special handling in the code
(I hope I got this one right, as the following are based on this).

What if the PITR logging would be disconnected from the WAL logging
completely ?

What I mean is to introduce a WAL subscription mechanism, which
basically means some incoming connections where we stream the log
records. We don't need to write them to disk at all in the normal case,
I guess usually PITR will store the records on some other machine so it
means network, not disk. And it doesn't need to be done synchronously,
it can lag behind the running transactions, and we can do it in batches
of WAL records.

It also would mean that the local WAL does not need to log the things
which are only needed for the PITR... that would likely mean some spared
WAL disk activity. Of course it also would mean that the local WAL and
PITR WAL are not the same, but that is not an issue I guess.

It would also permit immediate recycling of the WAL files if the current
archiving style is not used.

The drawbacks I can see (please add yours):
1) the need for the subscription management code with the added
complexity it implies;
2) problems if the WAL stream lags too much behind;
3) problems if the subscribed client's connection is interrupted;

Nr. 2 could be solved by saving the PITR WAL separately if the lag grows
over a threshold, and issue a warning. This could still be acceptable,
as the writing doesn't have to be synchronous and can be made in
relatively large blocks.
There could be a second bigger lag threshold which completely cancels
the subscription. All these thresholds should be configurable, as it
depends on the application what's more important, to have the standby
available all the time or have the primary faster if loaded...

Nr. 3. can be solved by either canceling the subscription on connection
drop, or by allowing a certain amount of time after which the
subscription is canceled. The client can reconnect before this timeout
expires. In the meantime the primary can store the PITR WAL on disk as
mentioned above...

Cheers,
Csaba.



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

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

Re: [PERFORM] planner with index scan cost way off actual cost,

[Scott Marlowe]

On Wed, 2006-03-22 at 02:04, Guillaume Cottenceau wrote:
> "Jim C. Nasby"  writes:
> 
> > On Tue, Mar 21, 2006 at 02:03:19PM +0100, Guillaume Cottenceau wrote:
> > > "Jim C. Nasby"  writes:
> > > 
> > > > On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote:
> > > > > I was going to recommend higher - but not knowing what else was 
> > > > > running, 
> > > > > kept it to quite conservative :-)... and given he's running java, the 
> > > > > JVM could easily eat 512M all by itself!
> > > > 
> > > > Oh, didn't pick up on java being in the mix. Yeah, it can be a real pig.
> > > > I think people often place too much emphasis on having a seperate
> > > > application server, but in the case of java you often have no choice.
> > > 
> > > Fortunately the servers use 2G or 4G of memory, only my test
> > > machine had 1G, as I believe I precised in a message; so I'm
> > > definitely going to use Mark's advices to enlarge a lot the
> > > shared buffers. Btw, what about sort_mem? I have seen it only
> > > little referenced in the documentation.
> > 
> > The biggest issue with setting work_mem (you're not doing current
> > development on 7.4 are you?) is ensuring that you don't push the server
> 
> Yes, we use 7.4.5 actually, because "it just works", so production
> wants to first deal with all the things that don't work before
> upgrading. I have recently discovered about the background writer
> of 8.x which could be a supplementary reason to push for an
> ugprade though.

Imagine you get a call from the manufacturer of your car.  There's a
problem with the fuel pump, and, in a small percentage of accidents,
your car could catch fire and kill everyone inside.

Do you go in for the recall, or ignore it because you just want your car
to "just work?"

In the case of the third number in postgresql releases, that's what
you're talking about.  the updates that have come after the 7.4.5
version, just talking 7.4 series here, have included a few crash and
data loss fixes.  Rare, but possible.

Don't worry about upgrading to 8.x until later, fine, but you should
really be upgrading to the latest patch level of 7.4.

I fight this same fight at work, by the way.  It's hard convincing
people that the updates are security / crash / data loss only...

---(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] Massive Inserts Strategies

[Jim C. Nasby]

Load the files into a temp table and go from there...

COPY ... FROM file;
UPDATE existing_table SET ... WHERE ...;
INSERT INTO existing_table SELECT * FROM temp_table WHERE NOT EXISTS(
SELECT * FROM existing_table WHERE ...)

On Wed, Mar 22, 2006 at 10:32:10AM -0500, ashah wrote:
> I have a database with foreign keys enabled on the schema. I receive 
> different 
> files, some of them are huge. And I need to load these files in the database 
> every night. There are several scenerios that I want to design an optimal 
> solution for -
> 
> 1. One of the file has around 80K records and I have to delete everything 
> from 
> the table and load this file. The provider never provides a "delta file" so I 
> dont have a way to identify which records are already present and which are 
> new. If I dont delete everything and insert fresh, I have to make around 80K 
> selects to decide if the records exist or not. Now there are lot of tables 
> that have foreign keys linked with this table so unless I disable the foreign 
> keys, I cannot really delete anything from this table. What would be a good  
> practise here?
> 
> 2. Another file that I receive has around 150K records that I need to load in 
> the database. Now one of the fields is logically a "foreign key" to another 
> table, and it is linked to the parent table via a database generated unique 
> ID instead of the actual value. But the file comes with the actual value. So 
> once again, I have to either drop the foreign key, or make 150K selects to 
> determine the serial ID so that the foreign key is satisfied. What would be a 
> good strategy in this scenerio ?
> 
> Please pardon my inexperience with database !
> 
> Thanks,
> Amit
> 
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 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] Massive Inserts Strategies

[PFC]

	For both cases, you could COPY your file into a temporary table and do a  
big JOIN with your existing table, one for inserting new rows, and one for  
updating existing rows.
	Doing a large bulk query is a lot more efficient than doing a lot of  
selects. Vacuum afterwards, and you'll be fine.


---(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] Massive Inserts Strategies

[ashah]

I have a database with foreign keys enabled on the schema. I receive different 
files, some of them are huge. And I need to load these files in the database 
every night. There are several scenerios that I want to design an optimal 
solution for -

1. One of the file has around 80K records and I have to delete everything from 
the table and load this file. The provider never provides a "delta file" so I 
dont have a way to identify which records are already present and which are 
new. If I dont delete everything and insert fresh, I have to make around 80K 
selects to decide if the records exist or not. Now there are lot of tables 
that have foreign keys linked with this table so unless I disable the foreign 
keys, I cannot really delete anything from this table. What would be a good  
practise here?

2. Another file that I receive has around 150K records that I need to load in 
the database. Now one of the fields is logically a "foreign key" to another 
table, and it is linked to the parent table via a database generated unique 
ID instead of the actual value. But the file comes with the actual value. So 
once again, I have to either drop the foreign key, or make 150K selects to 
determine the serial ID so that the foreign key is satisfied. What would be a 
good strategy in this scenerio ?

Please pardon my inexperience with database !

Thanks,
Amit

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

   http://archives.postgresql.org

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> Oh, so in other words, SELECT * INTO temp FROM table is inherently
> non-deterministic at the physical level, so the only way to be able to
> allow PITR to work is to duplicate all the physical changes. Darn.

Well, lemme put it this way: I'm not prepared to require that PG be
deterministic at the physical level.  One obvious source of
non-determinancy is the FSM, which is likely to hand out different free
space to different transactions depending on what else is going on at
the same time.  There are others, such as deliberately random
tie-breaking during btree index insertion.

regards, tom lane

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

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Jim C. Nasby]

On Wed, Mar 22, 2006 at 10:06:05AM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> >> PITR wants all changes. Without PITR we can optimize certain logging
> >> actions.
> 
> > The only change here is that we're creating a new table based on the
> > results of a SELECT. If that SELECT doesn't use anything that's
> > non-deterministic, then the machine doing the recovery should already
> > have all the data it needs, provided that we log the SELECT that was
> > used in the CTAS.
> 
> This is based on a fundamental misconception about the way PITR
> log-shipping works.  We log actions at the physical level (put this
> tuple here), not the logical here's-the-statement-we-executed level.
> The two approaches cannot mix, because as soon as there's any physical
> discrepancy at all, physical-level actions would be incorrectly applied
> to the slave database.

Oh, so in other words, SELECT * INTO temp FROM table is inherently
non-deterministic at the physical level, so the only way to be able to
allow PITR to work is to duplicate all the physical changes. Darn.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
>> PITR wants all changes. Without PITR we can optimise certain logging
>> actions.

> The only change here is that we're creating a new table based on the
> results of a SELECT. If that SELECT doesn't use anything that's
> non-deterministic, then the machine doing the recovery should already
> have all the data it needs, provided that we log the SELECT that was
> used in the CTAS.

This is based on a fundamental misconception about the way PITR
log-shipping works.  We log actions at the physical level (put this
tuple here), not the logical here's-the-statement-we-executed level.
The two approaches cannot mix, because as soon as there's any physical
discrepancy at all, physical-level actions would be incorrectly applied
to the slave database.

regards, tom lane

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

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

Re: [PERFORM] Migration study, step 1: bulk write performanceoptimization

[Tom Lane]

"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
>> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)")  
>> However, I tried increasing checkpoint_segments to 32 (512Mb) making it 
>> checkpoint every 15 second or so, but that gave a more uneven insert rate 
>> than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a 
>> better value? If I set checkpoint_segments to 64, what would a reasonable 
>> bgwriter setup be? I still need to improve my understanding of the relations 
>> between checkpoint_segments <-> shared_buffers <-> bgwriter...  :/

> Probably the easiest way is to set checkpoint_segments to something like
> 128 or 256 (or possibly higher), and then make bg_writer more aggressive
> by increasing bgwriter_*_maxpages dramatically (maybe start with 200).

Definitely.  You really don't want checkpoints happening oftener than
once per several minutes (five or ten if possible).  Push
checkpoint_segments as high as you need to make that happen, and then
experiment with making the bgwriter parameters more aggressive in order
to smooth out the disk write behavior.  Letting the physical writes
happen via bgwriter is WAY cheaper than checkpointing.

bgwriter parameter tuning is still a bit of a black art, so we'd be
interested to hear what works well for you.

regards, tom lane

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

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

[PERFORM] Intel C/C++ Compiler Tests

[Spiegelberg, Greg]

All,

Has anyone tested PostgreSQL 8.1.x compiled with Intel's Linux C/C++
compiler?

Greg

--
 Greg Spiegelberg
 [EMAIL PROTECTED]
 ISOdx Product Development Manager
 Cranel, Inc.
 

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

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Jim C. Nasby]

On Wed, Mar 22, 2006 at 01:08:34PM +, Simon Riggs wrote:
> On Wed, 2006-03-22 at 06:47 -0600, Jim C. Nasby wrote:
> 
> > Also, why do we log rows for CTAS/SELECT INTO when PITR is in use for
> > simple SELECTs (ones that don't call non-deterministic functions)? The
> > data should alread be available AFAICS...
> 
> Not sure what you're asking... SELECTs don't produce WAL.

Yes, there'd have to be some special kind of WAL entry that specifies
what select statement was used in CTAS.

> PITR wants all changes. Without PITR we can optimise certain logging
> actions.

The only change here is that we're creating a new table based on the
results of a SELECT. If that SELECT doesn't use anything that's
non-deterministic, then the machine doing the recovery should already
have all the data it needs, provided that we log the SELECT that was
used in the CTAS.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

   http://archives.postgresql.org

Re: [PERFORM] Migration study, step 1: bulk write performanceoptimization

[Mikael Carneholm]

Thanks, will try that. I'll report on the progress later, I have some unit 
tests to set up first but as soon as that is done I'll go back to optimizing 
insert performance.

Regards,
Mikael.

-Original Message-
From: Jim C. Nasby [mailto:[EMAIL PROTECTED]
Sent: den 22 mars 2006 13:55
To: Mikael Carneholm
Cc: Simon Riggs; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Migration study, step 1: bulk write
performanceoptimization


On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)")  
> However, I tried increasing checkpoint_segments to 32 (512Mb) making it 
> checkpoint every 15 second or so, but that gave a more uneven insert rate 
> than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better 
> value? If I set checkpoint_segments to 64, what would a reasonable bgwriter 
> setup be? I still need to improve my understanding of the relations between 
> checkpoint_segments <-> shared_buffers <-> bgwriter...  :/

Probably the easiest way is to set checkpoint_segments to something like
128 or 256 (or possibly higher), and then make bg_writer more aggressive
by increasing bgwriter_*_maxpages dramatically (maybe start with 200).
You might want to up lru_percent as well, otherwise it will take a
minimum of 20 seconds to fully scan.

Basically, slowly start increasing settings until performance smooths
out.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 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] WAL logging of SELECT ... INTO command

[Simon Riggs]

On Wed, 2006-03-22 at 06:47 -0600, Jim C. Nasby wrote:

> Also, why do we log rows for CTAS/SELECT INTO when PITR is in use for
> simple SELECTs (ones that don't call non-deterministic functions)? The
> data should alread be available AFAICS...

Not sure what you're asking... SELECTs don't produce WAL.

PITR wants all changes. Without PITR we can optimise certain logging
actions.

Best Regards, Simon Riggs


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

Re: [PERFORM] Migration study, step 1: bulk write performanceoptimization

[Jim C. Nasby]

On Wed, Mar 22, 2006 at 10:04:49AM +0100, Mikael Carneholm wrote:
> It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)")  
> However, I tried increasing checkpoint_segments to 32 (512Mb) making it 
> checkpoint every 15 second or so, but that gave a more uneven insert rate 
> than with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better 
> value? If I set checkpoint_segments to 64, what would a reasonable bgwriter 
> setup be? I still need to improve my understanding of the relations between 
> checkpoint_segments <-> shared_buffers <-> bgwriter...  :/

Probably the easiest way is to set checkpoint_segments to something like
128 or 256 (or possibly higher), and then make bg_writer more aggressive
by increasing bgwriter_*_maxpages dramatically (maybe start with 200).
You might want to up lru_percent as well, otherwise it will take a
minimum of 20 seconds to fully scan.

Basically, slowly start increasing settings until performance smooths
out.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [PERFORM] WAL logging of SELECT ... INTO command

[Jim C. Nasby]

On Tue, Mar 21, 2006 at 08:33:50PM +, Simon Riggs wrote:
> On Tue, 2006-03-21 at 06:22 -0600, Jim C. Nasby wrote:
> > Currently, it appears that SELECT * INTO new_table FROM old_table logs
> > each page as it's written to WAL. Is this actually needed? Couldn't the
> > database simply log that the SELECT ... INTO statement was executed
> > instead? Doing so would likely result in a large performance improvement
> > in most installs. Is there no provision for writing anything but data
> > page changes (or whole pages) to WAL?
> 
> AFAIK it takes the same code path as CREATE TABLE AS SELECT, which
> already does exactly what you suggest (except when using PITR).

Ok, I saw disk activity on the base directory and assumed it was pg_xlog
stuff. Turns out that both SELECT INTO and CREATE TABLE AS ignore
default_tablepsace and create the new tables in the base directory. I'm
guessing that's a bug... (this is on 8.1.2, btw).

Also, why do we log rows for CTAS/SELECT INTO when PITR is in use for
simple SELECTs (ones that don't call non-deterministic functions)? The
data should alread be available AFAICS...
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [PERFORM] Poor performance o

[Jim C. Nasby]

On Tue, Mar 21, 2006 at 05:04:16PM -0800, Craig A. James wrote:
> Tom Lane wrote:
> >"Craig A. James" <[EMAIL PROTECTED]> writes:
> >>It looks to me like the problem is the use of nested loops when a hash
> >>join should be used, but I'm no expert at query planning.
> >
> >Given the sizes of the tables involved, you'd likely have to boost up
> >work_mem before the planner would consider a hash join.  What nondefault
> >configuration settings do you have, anyway?
> 
> shared_buffers = 2
> work_mem = 32768
> effective_cache_size = 30
> 
> This is on a 4GB machine.  Is there a guideline for work_mem that's related 
> to table size?  Something like, "allow 2 MB per million rows"?

No. The general guide is "set it as large as possible without making the
machine start swapping." In some cases, you'll want to bump it up much
higher for certain queries, especially if you know those queries will
only run one at a time.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

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

Re: [PERFORM] Sequence Scan vs. Index scan

[Alejandro D. Burne]

2006/3/22, Steinar H. Gunderson <[EMAIL PROTECTED]>:
On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:> Explains:> > With SET ENABLE_SEQSCAN TO ON;> HashAggregate  (cost=251306.99..251627.36 rows=11650 width=78)
You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN./* Steinar */--Homepage: http://www.sesse.net/---(end of broadcast)---
TIP 6: explain analyze is your friend
Sorry, this is the result:

WITH SET ENABLE_SEQSCAN TO ON;

HashAggregate  (cost=251306.99..251627.36 rows=11650 width=78) (actual time=25089.024..25090.340 rows=1780 loops=1)
  ->  Hash Join  (cost=1894.30..250155.54 rows=153526
width=78) (actual time=3190.599..24944.418 rows=38009 loops=1)
    Hash Cond: ("outer".codigoliquidacionos = "inner".codigoliquidacionos)
    ->  Seq Scan on
detalleprestaciones dp  (cost=0.00..219621.32 rows=5420932
width=82) (actual time=0.058..23198.852 rows=5421786 loops=1)
    ->  Hash 
(cost=1891.01..1891.01 rows=1318 width=4) (actual time=60.777..60.777
rows=1530 loops=1)
 
->  Bitmap Heap Scan on liquidacionesos l 
(cost=43.89..1891.01 rows=1318 width=4) (actual time=1.843..59.574
rows=1530 loops=1)
   
Recheck Cond: (codigoobrasocial = 54)
   
Filter: ((per_a = '2005'::bpchar) AND facturada)
   
->  Bitmap Index Scan on ixliqos_os  (cost=0.00..43.89
rows=4541 width=0) (actual time=1.439..1.439 rows=4736 loops=1)
 
Index Cond: (codigoobrasocial = 54)
Total runtime: 25090.920 ms

WITH SET ENABLE_SEQSCAN TO OFF;
HashAggregate  (cost=2943834.84..2944155.21 rows=11650 width=78) (actual time=1479.361..1480.641 rows=1780 loops=1)
  ->  Nested Loop  (cost=0.00..2942683.39 rows=153526
width=78) (actual time=195.690..1345.494 rows=38009 loops=1)
    ->  Index Scan using
liqos_pkey on liquidacionesos l  (cost=0.00..3020.21 rows=1318
width=4) (actual time=174.546..666.761 rows=1530 loops=1)
 
Filter: ((per_a = '2005'::bpchar) AND facturada AND (codigoobrasocial =
54))
    ->  Index Scan using
dp_pkey on detalleprestaciones dp  (cost=0.00..2214.90 rows=1240
width=82) (actual time=0.333..0.422 rows=25 loops=1530)
 
Index Cond: (dp.codigoliquidacionos = "outer".codigoliquidacionos)
Total runtime: 1481.244 ms

Thanks again, Alejandro

Re: [PERFORM] Sequence Scan vs. Index scan

[Steinar H. Gunderson]

On Wed, Mar 22, 2006 at 08:50:20AM -0300, Alejandro D. Burne wrote:
> Explains:
> 
> With SET ENABLE_SEQSCAN TO ON;
> HashAggregate  (cost=251306.99..251627.36 rows=11650 width=78)

You'll need to post EXPLAIN ANALYZE results, not just EXPLAIN.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

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

Re: [PERFORM] Sequence Scan vs. Index scan

[Alejandro D. Burne]

2006/3/21, Reimer <[EMAIL PROTECTED]>:
Fernando,If you need to read all the table for example it would be better to readonly the data pages instead of read data and index pages.Reimer- Original Message -From: "Fernando Lujan" <
[EMAIL PROTECTED]>To: Sent: Tuesday, March 21, 2006 3:08 PM
Subject: [PERFORM] Sequence Scan vs. Index scan> Hi guys,>> I'm trying to figure out when Sequence Scan is better than Index Scan. I> just want to know this because I disabled the sequence scan in postgresql
> and receive a better result. :)>> Two tables.>> Table 1 (1 million rows )> ---> id> text> table2_id>> Table 2 (300 thousand rows)> --
> id> text 2>> When I join these two tables I have a sequence_scan. :(>> Thanks in advance.>> Fernando Lujan>> ---(end of broadcast)---
> TIP 6: explain analyze is your friend>---(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

Hi, I've got the same situation:

ENABLE_SEQSCAN ON  -> 5,031 ms

ENABLE_SEQSCAN OFF  -> 406 ms



Tables definition:

---



CREATE TABLE liquidacionesos

(

  codigoliquidacionos serial NOT NULL,

  codigoobrasocial int4 NOT NULL,

  quincena char(1) NOT NULL,

  per_m char(2) NOT NULL,

  per_a char(4) NOT NULL,

  nombreliquidacion varchar(60) NOT NULL,

  codigotipoliquidacionos int2 NOT NULL,

  importe numeric(12,2) NOT NULL,

  conformado bool NOT NULL,

  facturada bool NOT NULL,

  codigoremito int4 NOT NULL DEFAULT 0,

  codigoprofesion int2 NOT NULL DEFAULT 0,

  matriculaprofesional int4 NOT NULL DEFAULT 0,

  letrafactura char(1) NOT NULL DEFAULT ' '::bpchar,

  numerofactura varchar(13) NOT NULL DEFAULT '-'::character varying,

  importegravado numeric(12,2) NOT NULL DEFAULT 0,

  importenogravado numeric(12,2) NOT NULL DEFAULT 0,

  importeiva numeric(12,2) NOT NULL DEFAULT 0,

  importefactura numeric(12,2) NOT NULL DEFAULT 0,

  fechahora_cga timestamp NOT NULL DEFAULT now(),

  userid varchar(20) NOT NULL DEFAULT "current_user"(),

  numerosecuencia int4 NOT NULL DEFAULT 0,

  CONSTRAINT liqos_pkey PRIMARY KEY (codigoliquidacionos)

) 

WITHOUT OIDS TABLESPACE data;

ALTER TABLE liquidacionesos ALTER COLUMN codigoliquidacionos SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN per_a SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN per_m SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN quincena SET STATISTICS 100;

ALTER TABLE liquidacionesos ALTER COLUMN codigoobrasocial SET STATISTICS 100;

CREATE INDEX ixliqos_periodo

  ON liquidacionesos

  USING btree

  (per_a, per_m, quincena);





CREATE TABLE detalleprestaciones

(

  codigoliquidacionos int4 NOT NULL,

  numerosecuencia int4 NOT NULL,

  codigoprofesionclisanhosp int2 NOT NULL,

  matriculaprofesionalclisanhosp int4 NOT NULL,

  codigoctmclisanhosp int4 NOT NULL,

  codigoprofesionefector int2 NOT NULL,

  matriculaprofesionalefector int4 NOT NULL,

  codigoctmefector int4 NOT NULL,

  fechaprestacion date NOT NULL,

  codigonn char(6) NOT NULL,

  cantidad int2 NOT NULL,

  codigofacturacion int2 NOT NULL,

  porcentajehonorarios numeric(6,2) NOT NULL,

  porcentajederechos numeric(6,2) NOT NULL,

  importehonorarios numeric(12,2) NOT NULL,

  importederechos numeric(12,2) NOT NULL,

  importegastos numeric(12,2) NOT NULL,

  importegastosnogravados numeric(12,2) NOT NULL,

  importecompensacion numeric(12,2) NOT NULL,

  codigopadron int2 NOT NULL,

  codigoafiliado char(15) NOT NULL,

  numerobono varchar(15) NOT NULL,

  matriculaprofesionalprescriptor int4 NOT NULL,

  codigodevolucion int2 NOT NULL,

  importeforzado bool NOT NULL,

  codigotramo int2 NOT NULL DEFAULT 0,

  campocomodin int2 NOT NULL,

  fechahora_cga timestamp NOT NULL DEFAULT now(),

  userid varchar(20) NOT NULL DEFAULT "current_user"(),

  CONSTRAINT dp_pkey PRIMARY KEY (codigoliquidacionos, numerosecuencia)

) 

WITHOUT OIDS TABLESPACE data;

ALTER TABLE detalleprestaciones ALTER COLUMN codigoliquidacionos SET STATISTICS 100;



both vacummed and analyzed

table detalleprestaciones 5,408,590 rec

table liquidacionesos 16,752 rec



Query:





SELECT DP.CodigoProfesionEfector, DP.MatriculaProfesionalEfector, 

SUM((ImporteHonorarios+ImporteD
erechos+ImporteCompensacion)*Cantidad+ImporteGastos+ImporteGastosNoGravados) AS Importe
FROM DetallePrestaciones DP INNER JOIN LiquidacionesOS L ON DP.CodigoLiquidacionOS=L.CodigoLiquidacionOS
WHERE L.Per_a='2005' AND L.Facturada AND L.CodigoObraSocial IN(54)
GROUP BY DP.CodigoProfesionEfector, DP.MatriculaProfesionalEfector;

Explains:

With SET ENABLE_SEQSCAN TO ON;
HashAggregate  (cost=251306.99..251627.36 rows=11650 width=78)
  ->  Hash Join  (cost=1894.30..250155

Re: [PERFORM] Migration study, step 1: bulk write

[Simon Riggs]

On Wed, 2006-03-22 at 10:04 +0100, Mikael Carneholm wrote:
> but that gave a more uneven insert rate

Not sure what you mean, but happy to review test results.

You should be able to tweak other parameters from here as you had been
trying. Your bgwriter will be of some benefit now if you set it
aggressively enough to keep up.

Your thoughts on this process are welcome...

Best Regards, Simon Riggs


---(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] Query Feromance

[Marco Furetto]

I find another java program for monitory application query: 
http://www.p6spy.com/

with interface
http://www.jahia.net/jahia/page597.html

Thomas Pundt wrote:

Hi,

On Tuesday 21 March 2006 09:25, Marco Furetto wrote:
| I'm managing the db of a "Content Management environment" and I'm
| searching for a "Query analyzer" to improve performance because i don't
| know how many and what type of queries are executing on the system (for
| the "where and join" block).

as a first step, I'd enable query duration logging; in postgresql.conf
I have set

  log_min_duration_statement = 3000

this will log each query that needs more than 3 seconds to complete.

The next step would be to "explain analyze" the problematic queries.

Ciao,
Thomas



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

Re: [PERFORM] Migration study, step 1: bulk write performanceoptimization

[Mikael Carneholm]

>>On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote:

>> This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), compared to 
>> the raw dd result (~75.5Mb/s).
>> 
>> I assume this difference is due to: 
>> - simultaneous WAL write activity (assumed: for each byte written to the 
>> table, at least one byte is also written to WAL, in effect: 10Gb data 
>> inserted in the table equals 20Gb written to disk)
>> - lousy test method (it is done using a function => the transaction size is 
>> 10Gb, and 10Gb will *not* fit in wal_buffers :) )
>> - poor config

>> checkpoint_segments = 3 

>With those settings, you'll be checkpointing every 48 Mb, which will be
>every about once per second. Since the checkpoint will take a reasonable
>amount of time, even with fsync off, you'll be spending most of your
>time checkpointing. bgwriter will just be slowing you down too because
>you'll always have more clean buffers than you can use, since you have
>132MB of shared_buffers, yet flushing all of them every checkpoint.

>Please read you're logfile, which should have relevant WARNING messages.

It does ("LOG: checkpoints are occurring too frequently (2 seconds apart)")  
However, I tried increasing checkpoint_segments to 32 (512Mb) making it 
checkpoint every 15 second or so, but that gave a more uneven insert rate than 
with checkpoint_segments=3. Maybe 64 segments (1024Mb) would be a better value? 
If I set checkpoint_segments to 64, what would a reasonable bgwriter setup be? 
I still need to improve my understanding of the relations between 
checkpoint_segments <-> shared_buffers <-> bgwriter...  :/

- Mikael


---(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] planner with index scan cost way off actual cost,

[Guillaume Cottenceau]

"Jim C. Nasby"  writes:

> On Tue, Mar 21, 2006 at 02:03:19PM +0100, Guillaume Cottenceau wrote:
> > "Jim C. Nasby"  writes:
> > 
> > > On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote:
> > > > I was going to recommend higher - but not knowing what else was 
> > > > running, 
> > > > kept it to quite conservative :-)... and given he's running java, the 
> > > > JVM could easily eat 512M all by itself!
> > > 
> > > Oh, didn't pick up on java being in the mix. Yeah, it can be a real pig.
> > > I think people often place too much emphasis on having a seperate
> > > application server, but in the case of java you often have no choice.
> > 
> > Fortunately the servers use 2G or 4G of memory, only my test
> > machine had 1G, as I believe I precised in a message; so I'm
> > definitely going to use Mark's advices to enlarge a lot the
> > shared buffers. Btw, what about sort_mem? I have seen it only
> > little referenced in the documentation.
> 
> The biggest issue with setting work_mem (you're not doing current
> development on 7.4 are you?) is ensuring that you don't push the server

Yes, we use 7.4.5 actually, because "it just works", so production
wants to first deal with all the things that don't work before
upgrading. I have recently discovered about the background writer
of 8.x which could be a supplementary reason to push for an
ugprade though.

> into swapping. Remember that work_mem controls how much memory can be
> used for EACH sort or hash (maybe others) operation. Each query can
> consume multiples of work_mem (since it can do multiple sorts, for
> example), and of course each backend could be running a query at the
> same time. Because of all this it's pretty difficult to make work_mem
> recomendations without knowing a lot more about your environment.

Ok, I see. Thanks for the info!

-- 
Guillaume Cottenceau

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

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