[PERFORM] join/from_collapse_limit and geqo_threshold default values

[Vlad Arkhipov]

I wonder why join_collapse_limit default values is set to 8 but 
geqo_threshold is 12. Optimizer doesn't change the order of JOIN's of 
queries that contains from 8 to 11 tables. Why it's 'wise' decision as 
documentation says?


from_collapse_limit (integer)

   The planner will merge sub-queries into upper queries if the
   resulting FROM list would have no more than this many items. Smaller
   values reduce planning time but might yield inferior query plans.
   The default is eight. It is usually wise to keep this less than
   geqo_threshold
   
.
   For more information see Section 14.3
   .

[PERFORM] Quad Xeon or Quad Opteron?

[Andrzej Zawadzki]

Hello,

 We're planning new production server for PostgreSQL and I'm wondering
which processor (or even platform) will be better: Quad Xeon or Quad
Opteron (for example SUN now has a new offer Sun Fire X4440 x64).

When I was buying my last database server, then SUN v40z was a really
very good choice (Intel's base server was slower). This v40z still works
pretty good but I need one more.

AFAIK Intel made some changes in chipset but... is this better then AMD
HyperTransport and Direct Connect Architecture from database point of
view? How about L3 cache - is this important for performance?

Do You have any opinions? Suggestions?

Thanks,

Best regards

-- 
Andrzej Zawadzki

-- 
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] Quad Xeon or Quad Opteron?

[Craig Ringer]

Andrzej Zawadzki wrote:
> Hello,
> 
>  We're planning new production server for PostgreSQL and I'm wondering
> which processor (or even platform) will be better: Quad Xeon or Quad
> Opteron (for example SUN now has a new offer Sun Fire X4440 x64).

[snip]

> Suggestions?

To get a more useful response here, you might want to include some
information about your workload and database size, and report on your
planned disk subsystem and RAM.

Also, based on what I've seen on this list rather than personal
experience, you might want to give more thought to your storage than to
CPU power. The usual thrust of advice seems to be: Get a fast, battery
backed RAID controller. "Fast" does not mean "fast sequential I/O in
ideal conditions so marketing can print a big number on the box"; you
need to consider random I/O too. Get lots of fast disks. Get enough RAM
to ensure that your indexes fit in RAM if possible.

Note, however, that I have no direct experience with big Pg databases;
I'm just trying to provide you with a guide of what information to
provide and what to think about so you can get better answers here from
people who actually have a clue.

--
Craig Ringer

-- 
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] Quad Xeon or Quad Opteron?

[Adam Tauno Williams]

> Also, based on what I've seen on this list rather than personal
> experience, you might want to give more thought to your storage than to
> CPU power. The usual thrust of advice seems to be: Get a fast, battery
> backed RAID controller. "Fast" does not mean "fast sequential I/O in
> ideal conditions so marketing can print a big number on the box"; you
> need to consider random I/O too. Get lots of fast disks. Get enough RAM
> to ensure that your indexes fit in RAM if possible.
> Note, however, that I have no direct experience with big Pg databases;
> I'm just trying to provide you with a guide of what information to
> provide and what to think about so you can get better answers here from
> people who actually have a clue.

Yep,  we've had PostreSQL databases for a long time.  The various
current generation processors, IMO, have no substantive difference in
practice;  at least not relative to the bang-for-the-buck or more RAM
and good I/O.


-- 
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] Quad Xeon or Quad Opteron?

[Knight, Doug]

Hi,
As a gauge, we recently purchased several servers as our systems get
close to going operational. We bought Dell 2900s, with the cheapest quad
core processors (dual) and put most of the expense into lots of drives
(8 15K 146GB SAS drives in a RAID 10 set), and the PERC 6 embedded
controller with 512MB battery backed cache. That gives us more spindles,
the RAID redundancy we want, plus the high, reliable throughput of the
BBC. The OS (and probably WAL) will run on a RAID 1 pair of 15K 76GB
drives. We also went with 8GB memory, which seemed to be the price cost
point in these systems (going above 8GB had a much higher cost).
Besides, in our prototyping, or systems had 2GB, which we rarely
exceeded, so 8GB should be plently (and we can always expand). 

So really, if you can save money on processors by going Opteron (and
your IT department doesn't have an Intel-based system requirement like
ours), put what you save into a good disk I/O subsystem. Hope that
helps.

Doug

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Adam Tauno
Williams
Sent: Friday, May 23, 2008 8:22 AM
To: pgsql-performance
Subject: Re: [PERFORM] Quad Xeon or Quad Opteron?

> Also, based on what I've seen on this list rather than personal
> experience, you might want to give more thought to your storage than
to
> CPU power. The usual thrust of advice seems to be: Get a fast, battery
> backed RAID controller. "Fast" does not mean "fast sequential I/O in
> ideal conditions so marketing can print a big number on the box"; you
> need to consider random I/O too. Get lots of fast disks. Get enough
RAM
> to ensure that your indexes fit in RAM if possible.
> Note, however, that I have no direct experience with big Pg databases;
> I'm just trying to provide you with a guide of what information to
> provide and what to think about so you can get better answers here
from
> people who actually have a clue.

Yep,  we've had PostreSQL databases for a long time.  The various
current generation processors, IMO, have no substantive difference in
practice;  at least not relative to the bang-for-the-buck or more RAM
and good I/O.


-- 
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] Quad Xeon or Quad Opteron?

[Reid Thompson]

This may be of interest...


http://weblog.infoworld.com/yager/archives/2008/05/ahead_of_the_cu_4.html

-Original Message-
From: [EMAIL PROTECTED] on behalf of Andrzej Zawadzki
Sent: Fri 5/23/2008 6:41 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Quad Xeon or Quad Opteron?
 
Hello,

 We're planning new production server for PostgreSQL and I'm wondering
which processor (or even platform) will be better: Quad Xeon or Quad
Opteron (for example SUN now has a new offer Sun Fire X4440 x64).

When I was buying my last database server, then SUN v40z was a really
very good choice (Intel's base server was slower). This v40z still works
pretty good but I need one more.

AFAIK Intel made some changes in chipset but... is this better then AMD
HyperTransport and Direct Connect Architecture from database point of
view? How about L3 cache - is this important for performance?

Do You have any opinions? Suggestions?

Thanks,

Best regards

-- 
Andrzej Zawadzki

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

[PERFORM] index performance on large tables with update and insert

[Jessica Richard]

I have a large table with about 2 million rows and it will keep growing...

I need to do update/inserts, and select as well.

An index will speed up the select, but it will slow down the updates.

Are all Postgres indexes ordered? i.e., with every update, the index pages will 
have to be physically reordered?

Does Postgres have any kind of non-ordered indexes (like Syabse's non-clustered 
index)?

What is the common way to take care of the performance issue when you have to 
do both update and select on the same large table?

Thanks,
Jessica



  

[PERFORM] IBM ServRAID-MR10M / LSI1078ROC advice

[Kevin Grittner]

We're about to try out a new BBU controller card, and would welcome
any advice from anyone with experience with this hardware.
 
It is an IBM ServeRAID-MR10M SAS/SATA Controller with the optional
BBU.  The docs say it is "a LSI1078ROC-based PCI Express RAID
adapter."  We're hooking it up to four drawers of 12 drives each; all
drives are 146 GB 3.5 inch 15 kRPM HS SAS.  Our hope is to set up
three drawers in RAID 10.  I don't know if this will work -- our
previous adapter refused to allow more than 14 drives in RAID 10, so
we had to use RAID 5 for the "big" partition.  The other drawer will
be RAID 5.
 
Tips?
 
-Kevin


-- 
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] index performance on large tables with update and insert

[Magnus Hagander]

Jessica Richard wrote:
> I have a large table with about 2 million rows and it will keep
> growing...
> 
> I need to do update/inserts, and select as well.
> 
> An index will speed up the select, but it will slow down the updates.
> 
> Are all Postgres indexes ordered? i.e., with every update, the index
> pages will have to be physically reordered?
> 
> Does Postgres have any kind of non-ordered indexes (like Syabse's
> non-clustered index)?

All PostgreSQL indexes are like the non-clustered ones in Sybase or SQL
Server.


> What is the common way to take care of the performance issue when you
> have to do both update and select on the same large table?

Create the indexes you actually need to make the selects and updates
fast, just make sure you don't create any unnecessary ones. Usually,
your UPDATEs will also require indexes - only the INSERTs actually are
losing.

//Magnus

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