[PERFORM] PostgreSql under Linux

2003-08-07 Thread Wilson A. Galafassi Jr.
Hello people.   I'm installing Postgresql under linux for better performance and i want to know how is the best configuration.   My server is a dual pentium3 1ghz/1gb ram/36gb scsi. running only postgresql. My question is: 1. What is the best linux distribuition for better performance? 2. Doe

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Yaroslav Mazurak
Hi All! First, thanks for answers! Richard Huxton wrote: On Thursday 07 August 2003 09:24, Yaroslav Mazurak wrote: IIRC there is a limit on filesystem cache on freeBSD. 300MB by default. If that is the case, you might have to raise it to make effective_cache_size really effective..

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-07 Thread Sebastien Lemieux
> >> The idea here is to make sure that the planner's statistics reflect the > >> "full" state of the table, not the "empty" state. Otherwise it may pick > >> plans for the foreign key checks that are optimized for small tables. > > > I added the 'analyze' but without any noticable gain in speed

Re: [PERFORM] How Many Inserts Per Transactions

2003-08-07 Thread Shridhar Daithankar
On 5 Aug 2003 at 12:28, Christopher Browne wrote: > On Oracle, I have seen performance Suck Badly when using SQL*Load; if > I grouped too many updates together, it started blowing up the > "rollback segment," which was a Bad Thing. And in that kind of > context, there will typically be some "sweet

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-07 Thread Sebastien Lemieux
On Wed, 6 Aug 2003, Tom Lane wrote: > Sebastien Lemieux <[EMAIL PROTECTED]> writes: > > All the time is taken at the commit of both transaction. > > Sounds like the culprit is foreign-key checks. > > One obvious question is whether you have your foreign keys set up > efficiently in the first pla

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-07 Thread Tom Lane
Stephan Szabo <[EMAIL PROTECTED]> writes: > Another thing might be the management of the trigger queue. I don't think > 7.3.2 had the optimization for limiting the scans of the queue when you > have lots of deferred triggers. It looks like 7.3.4 may though. Good point. We put that in in 7.3.3,

Re: [PERFORM] EXTERNAL storage and substring on long strings

2003-08-07 Thread Scott Cain
> snipped much discussion about EXTERNAL storage and substring speed Joe and Tom, Thanks for all of your help; I went back to my (nearly) production database, and executed the `update feature set residues = residues ||'';` and then redid my benchmark. Here's a summary of the results: substr in

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Ron Johnson
On Thu, 2003-08-07 at 12:04, Yaroslav Mazurak wrote: > scott.marlowe wrote: > > > On Thu, 7 Aug 2003, Yaroslav Mazurak wrote: > > >>Shridhar Daithankar wrote: > [snip] > > My guess is that this is exactly what's happening to you, you're using so > > much memory that the machine is running out a

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Richard Huxton
On Thursday 07 August 2003 08:05, Yaroslav Mazurak wrote: > Hi All! > > Richard Huxton wrote: > >>>On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote: > sort_mem = 131072 > >>> > >>>This sort_mem value is *very* large - that's 131MB for *each sort* that > > It's not TOO large *for

Re: [PERFORM] Some vacuum & tuning help

2003-08-07 Thread Matthew T. O'Connor
From: "Tom Lane" <[EMAIL PROTECTED]> > "Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > So, now is precisely the time to be experimenting to find out what works well and what features are needed. Another quick question while I have your attention :-) Since pg_autovaccum is a contrib module doe

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Yaroslav Mazurak
Hi All! Tom Lane wrote: Yaroslav Mazurak <[EMAIL PROTECTED]> writes: fsync = false I'd turn fsync back on - unless you don't mind losing your data after a crash. This is temporary performance solution - I want get SELECT query result first, but current performance is too low. Disabl

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-07 Thread Tom Lane
"scott.marlowe" <[EMAIL PROTECTED]> writes: > On Wed, 6 Aug 2003, Tom Lane wrote: >> One obvious question is whether you have your foreign keys set up >> efficiently in the first place. As a rule, the referenced and >> referencing columns should have identical datatypes and both should >> be index

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Yaroslav Mazurak
Hi All! Richard Huxton wrote: On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote: sort_mem = 131072 This sort_mem value is *very* large - that's 131MB for *each sort* that It's not TOO large *for PostgreSQL*. When I'm inserting a large amount of data into tables, sort_mem helps. Valu

Re: [PERFORM] How to efficiently duplicate a whole schema?

2003-08-07 Thread Tom Lane
Sebastien Lemieux <[EMAIL PROTECTED]> writes: > On Wed, 6 Aug 2003, Tom Lane wrote: >> The idea here is to make sure that the planner's statistics reflect the >> "full" state of the table, not the "empty" state. Otherwise it may pick >> plans for the foreign key checks that are optimized for small

Re: [PERFORM] PostgreSQL performance problem -> tuning

2003-08-07 Thread Yaroslav Mazurak
Hi All! First, thanks for answers. Richard Huxton wrote: On Wednesday 06 August 2003 08:34, Yaroslav Mazurak wrote: Version 7.3.4 is just out - probably worth upgrading as soon as it's convenient. Has version 7.3.4 significant performance upgrade relative to 7.3.2? I've downloaded versi