Any advice on any of this (other than drop the project while
>you're still a little bit sane)?
Since you are doing more writing than reading, and those writes are going to be
relatively small, you may not get as much out of block sizes larger than the
average DB write.
My initial
membered from my CS classes back in college :)
-- Mark
On Fri, 2005-09-23 at 13:17 -0400, Tom Lane wrote:
> Ron Peacetree <[EMAIL PROTECTED]> writes:
> > 2= No optimal external sorting algorithm should use more than 2 passes.
> > 3= Optimal external sorting algorithms should
From: Tom Lane <[EMAIL PROTECTED]>
Sent: Sep 23, 2005 2:15 PM
Subject: Re: [PERFORM] Releasing memory during External sorting?
>Mark Lewis <[EMAIL PROTECTED]> writes:
>> operations != passes. If you were clever, you could probably write a
>> modified bubble-sort algorithm that only made 2 passes
From: Dann Corbit <[EMAIL PROTECTED]>
Sent: Sep 23, 2005 5:38 PM
Subject: RE: [HACKERS] [PERFORM] Releasing memory during External sorting?
>_C Unleashed_ also explains how to use a callback function to perform
>arbitrary radix sorts (you simply need a method that returns the
>[bucketsize] most si
It looks like a rebranded low end Adaptec 64MB PCI-X <-> SATA RAID card.
Looks like the 64MB buffer is not upgradable.
Looks like it's SATA, not SATA II
There are much better ways to spend your money.
These are the products with the current best price/performance ratio:
http://www.areca.us/produc
From: Simon Riggs <[EMAIL PROTECTED]>
Sent: Sep 23, 2005 5:37 AM
Subject: [PERFORM] Releasing memory during External sorting?
>I have concerns about whether we are overallocating memory for use in
>external sorts. (All code relating to this is in tuplesort.c)
>
A decent external sorting algorithm,
While I understand being economical, at some point one crosses the line
to being penny wise and pound foolish.
How much is the data on this server going to be worth?
How much much will it cost you to recover or restore it (assuming that
is even possible if you lose it)?
If your data is worth noth
>From: Ron Peacetree <[EMAIL PROTECTED]>
>Sent: Sep 24, 2005 6:30 AM
>Subject: Re: [HACKERS] [PERFORM] Releasing memory during External sorting?
>
>... the amount of IO done is the most
>important of the things that you should be optimizing for in
>choosing an
>From: Dann Corbit <[EMAIL PROTECTED]>
>Sent: Sep 26, 2005 5:13 PM
>To: Ron Peacetree <[EMAIL PROTECTED]>, pgsql-hackers@postgresql.org,
> pgsql-performance@postgresql.org
>Subject: RE: [HACKERS] [PERFORM] A Better External Sort?
>
>I think that the btre
SECOND ATTEMPT AT POST. Web mailer appears to have
eaten first one. I apologize in advance if anyone gets two
versions of this post.
=r
>From: Tom Lane <[EMAIL PROTECTED]>
>Sent: Sep 26, 2005 9:42 PM
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>So far, you've blithely assumed th
>From: Josh Berkus
>ent: Sep 27, 2005 12:15 PM
>To: Ron Peacetree <[EMAIL PROTECTED]>
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>I've somehow missed part of this thread, which is a shame since this is
>an area of primary concern for me.
>
&g
>From: "Jeffrey W. Baker" <[EMAIL PROTECTED]>
>Sent: Sep 27, 2005 1:26 PM
>To: Ron Peacetree <[EMAIL PROTECTED]>
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>On Tue, 2005-09-27 at 13:15 -0400, Ron Peacetree wrote:
>
>>That Btree can
>From: Matthew Nuzum <[EMAIL PROTECTED]>
>Sent: Sep 28, 2005 4:02 PM
>Subject: [PERFORM] Logarithmic change (decrease) in performance
>
Small nit-pick: A "logarithmic decrease" in performance would be
a relatively good thing, being better than either a linear or
exponential decrease in performance
In the interest of efficiency and "not reinventing the wheel", does anyone know
where I can find C or C++ source code for a Btree variant with the following
properties:
A= Data elements (RIDs) are only stored in the leaves, Keys (actually
KeyPrefixes; see "D" below) and Node pointers are only stor
If I've done this correctly, there should not be anywhere near
the number of context switches we currently see while sorting.
Each unscheduled context switch represents something unexpected
occuring or things not being where they are needed when they are
needed. Reducing such circumstances to the
>From: "Jeffrey W. Baker" <[EMAIL PROTECTED]>
>Sent: Sep 29, 2005 12:27 AM
>To: Ron Peacetree <[EMAIL PROTECTED]>
>Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>You are e
>From: "Jeffrey W. Baker" <[EMAIL PROTECTED]>
>Sent: Sep 29, 2005 12:33 AM
>Subject: Sequential I/O Cost (was Re: [PERFORM] A Better External Sort?)
>
>On Wed, 2005-09-28 at 12:03 -0400, Ron Peacetree wrote:
>>>From: "Jeffrey W. Baker" <[EMAIL
ntial passes, returning the first value
>>>on the first pass and the second value on the second pass.
>>> This will be faster than the method you propose.
>>
>>Ron Peacetree:
>>1= No that was not my main example. It was the simplest example
>>used to frame
>From: Zeugswetter Andreas DAZ SD <[EMAIL PROTECTED]>
>Sent: Sep 29, 2005 9:28 AM
>Subject: RE: [HACKERS] [PERFORM] A Better External Sort?
>
>>In my original example, a sequential scan of the 1TB of 2KB
>>or 4KB records, => 250M or 500M records of data, being sorted
>>on a binary value key will
>From: Josh Berkus
>Sent: Sep 29, 2005 12:54 PM
>Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
>
>The biggest single area where I see PostgreSQL external
>sort sucking is on index creation on large tables. For
>example, for free version of TPCH, it takes only 1.5 hours to
>load a 60GB
could replace the
present sorting code with infinitely fast sorting code and we'd
still be scrod performance wise.
So why does basic IO suck so badly?
Ron
-Original Message-
From: Josh Berkus
Sent: Sep 30, 2005 1:23 PM
To: Ron Peacetree <[EMAIL PROTECTED]>
Cc: pg
less ROI.
Ron
-Original Message-----
From: Josh Berkus
Sent: Sep 30, 2005 4:41 PM
To: Ron Peacetree <[EMAIL PROTECTED]>
Cc: pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
Ron,
> That 11MBps was your =bulk load
*blink* Tapes?! I thought that was a typo...
If our sort is code based on sorting tapes, we've made a mistake. HDs
are not tapes, and Polyphase Merge Sort and it's brethren are not the
best choices for HD based sorts.
Useful references to this point:
Knuth, Vol 3 section 5.4.9, (starts p356 of 2
so
that I can.)
Ron
-Original Message-
From: Andrew Dunstan <[EMAIL PROTECTED]>
Sent: Oct 1, 2005 11:19 AM
To: Ron Peacetree <[EMAIL PROTECTED]>
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
Ron Peacetree wrote:
>The good news is all this means it's easy
ut
Sent: Oct 1, 2005 12:19 PM
Subject: Re: [HACKERS] [PERFORM] A Better External Sort?
On Sat, Oct 01, 2005 at 10:22:40AM -0400, Ron Peacetree wrote:
> Assuming we get the abyssmal physical IO performance fixed...
> (because until we do, _nothing_ is going to help us as much)
I'm still
Nah. It's still not right. It needs:
1= full PCI, preferably at least 64b 133MHz PCI-X, bandwidth.
A RAM card should blow the doors off the fastest commodity
RAID setup you can build.
2= 8-16 DIMM slots
3= a standard battery type that I can pick up spares for easily
4= ECC support
If it had all
Jeff, are those _burst_ rates from HD buffer or _sustained_ rates from
actual HD media? Rates from IO subsystem buffer or cache are
usually considerably higher than Average Sustained Transfer Rate.
Also, are you measuring _raw_ HD IO (bits straight off the platters, no
FS or other overhead) or _c
Let's pretend we get a 24HD HW RAID solution like that J Baker
says he has access to and set it up as a RAID 10. Assuming
it uses two 64b 133MHz PCI-X busses and has the fastest HDs
available on it, Jeff says he can hit ~1GBps of XFS FS IO rate
with that set up (12*83.3MBps= 1GBps).
Josh says th
OK, change "performance" to "single thread performance" and we
still have a valid starting point for a discussion.
Ron
-Original Message-
From: Gregory Maxwell <[EMAIL PROTECTED]>
Sent: Oct 3, 2005 8:19 PM
To: Ron Peacetree <[EMAIL PROTECTED]>
Subject:
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 pap
"Joshua D. Drake" <[EMAIL PROTECTED]>
Sent: Oct 4, 2005 9:32 PM
To: "Douglas J. Trainor" <[EMAIL PROTECTED]>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is There Any Way
Douglas J. Trainor wrote:
>
> Ron Peacetree sounds like someone talkin
I've now gotten verification from multiple working DBA's that DB2, Oracle, and
SQL Server can achieve ~250MBps ASTR (with as much as ~500MBps ASTR in
setups akin to Oracle RAC) when attached to a decent (not outrageous, but
decent) HD subsystem...
I've not yet had any RW DBA verify Jeff Baker's su
From: Kevin Grittner <[EMAIL PROTECTED]>
Sent: Oct 5, 2005 2:16 AM
Subject: Re: [PERFORM] Is There Any Way
>First off, Mr. Trainor's response proves nothing about anyone or
>anything except Mr. Trainor.
>
Fair Enough. I apologize for the inappropriately general statement.
>I'm going to of
First I wanted to verify that pg's IO rates were inferior to The Competition.
Now there's at least an indication that someone else has solved similar
problems. Existence proofs make some things easier ;-)
Is there any detailed programmer level architectual doc set for pg? I know
"the best doc is
I'm putting in as much time as I can afford thinking about pg related
performance issues. I'm doing it because of a sincere desire to help
understand and solve them, not to annoy people.
If I didn't believe in pg, I would't be posting thoughts about how to
make it better.
It's probably worth s
Databases basically come in 4 sizes:
1= The entire DB fits into memory.
2= The performance critical table(s) fit(s) into memory
3= The indexes of the performance critical table(s) fit into memory.
4= Neither the performance critical tables nor their indexes fit into memory.
Performance decreases
I'm surprised that no one seems to have yet suggested the following
simple experiment:
Increase the RAM 4GB -> 8GB, tune for best performance, and
repeat your 100M row insert experiment.
Does overall insert performance change? Does the performance
drop rows in still occur? Does it occur in ~ t
0= Optimize your schema to be a tight as possible. Your goal is to give
yourself the maximum chance that everything you want to work on is in RAM when
you need it.
1= Upgrade your RAM to as much as you can possibly strain to afford. 4GB at
least. It's that important.
2= If the _entire_ DB doe
...and on those notes, let me repeat my often stated advice that a DB server
should be configured with as much RAM as is feasible. 4GB or more strongly
recommended.
I'll add that the HW you are using for a DB server should be able to hold _at
least_ 4GB of RAM (note that modern _laptops_ can h
The point Gentlemen, was that Good Architecture is King. That's what I was
trying to emphasize by calling proper DB architecture step 0. All other things
being equal (and they usually aren't, this sort of stuff is _very_ context
dependent), the more of your critical schema that you can fit int
---Original Message-
From: Kurt De Grave <[EMAIL PROTECTED]>
Sent: Nov 10, 2005 5:40 AM
To: Ron Peacetree <[EMAIL PROTECTED]>
Cc: Charlie Savage <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Sort performance on large tables
On Wed, 9 Nov 2005,
Where "*" ==
{print | save to PDF | save to format | display on screen}
Anyone know of one?
TiA
Ron
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
The US Dept of Homeland Security has at least two =10=TB SSDs.
Rumor is they are being used for Carnivore or an offshoot/descendent of
Carnivore.
Good luck getting them to give you benchmark data.
You need >deep< pockets to afford >= 1TB of SSD.
(...and as the example shows, perhaps more money
As others have noted, the current price/performance "sweet spot" for DB servers
is 2S 2C AMD CPUs. These CPUs are also the highest performing x86 compatible
solution for pg.
If you must go Intel for some reason, then wait until the new NGMA CPU's
(Conroe, Merom, Woodcrest) come out and see how
I've had intermittent "freeze and reboot" and, worse, just plain freeze
problems with the Core Duo's I've been testing. I have not been able to narrow
it down so I do not know if it is a platform issue or a CPU issue. It appears
to be HW, not SW, related since I have experienced the problem bo
>Another benefit of Pentium D over AMD X2, at least until AMD chooses
>to switch, is that Pentium D supports DDR2, whereas AMD only supports
>DDR. There are a lot of technical pros and cons to each - with claims
>from AMD that DDR2 can be slower than DDR - but one claim that isn't
>often made, but
I'm posting this to the entire performance list in the hopes that it will be
generally useful.
=r
-Original Message-
>From: [EMAIL PROTECTED]
>Sent: Apr 26, 2006 3:25 AM
>To: Ron Peacetree <[EMAIL PROTECTED]>
>Subject: Re: [PERFORM] Large (8M) cache vs. dual-core
actually are.
Again, my apologies.
Ron
-Original Message-
>From: Ron Peacetree <[EMAIL PROTECTED]>
>Sent: Apr 26, 2006 8:40 AM
>To: [EMAIL PROTECTED], pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] Large (8M) cache vs. dual-core CPUs
>
>I'm posting t
The best of all worlds is to use a HW RAID card with battery backed cache.
Then you can have both high performance and high reliability.
Benches suggest that the best such cards currently are the Areca cards which
support up to 2GB of battery backed cache.
Ron
-Original Message-
>From:
f BBC.
ARC-11xx are the PCI-X based products.
ARC-12xx are the PCI-E based products.
Reviews at places like tweakers.net
Areca is based in Taiwan, but has European and US offices as well
Ron Peacetree
-Original Message-
>From: Kenji Morishige <[EMAIL PROTECTED]>
>Sent: Jul 5,
>From: Mikael Carneholm <[EMAIL PROTECTED]>
>Sent: Jul 16, 2006 6:52 PM
>To: pgsql-performance@postgresql.org
>Subject: [PERFORM] RAID stripe size question
>
>I have finally gotten my hands on the MSA1500 that we ordered some time
>ago. It has 28 x 10K 146Gb drives,
>
Unless I'm missing something,
-Original Message-
>From: Mikael Carneholm <[EMAIL PROTECTED]>
>Sent: Jul 17, 2006 5:16 PM
>To: Ron Peacetree <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org
>Subject: RE: [PERFORM] RAID stripe size question
>
>>15Krpm HDs will have average access
>From: Alex Turner <[EMAIL PROTECTED]>
>Sent: Jul 18, 2006 12:21 AM
>To: Ron Peacetree <[EMAIL PROTECTED]>
>Cc: Mikael Carneholm <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] RAID stripe size question
>
>On 7/17/06,
c: Luke Lonergan <[EMAIL PROTECTED]>, Mikael Carneholm <[EMAIL PROTECTED]>,
>Ron Peacetree <[EMAIL PROTECTED]>, pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] RAID stripe size question
>
>On Tue, 2006-07-18 at 14:27, Alex Turner wrote:
>> This is a great
54 matches
Mail list logo
