pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWorking on huge RAM based datasets
16+ messages / 8 participants
[nested] [flat]
* Working on huge RAM based datasets
@ 2004-07-08 18:05 Andy Ballingall <andy_ballingall@bigfoot.com>
0 siblings, 1 reply; 16+ messages in thread
From: Andy Ballingall @ 2004-07-08 18:05 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm really stuck and I wonder if any of you could help.
I have an application which will be sitting on a quite large database
(roughly 8-16GB). The nature of the application is such that, on a second by
second basis, the working set of the database is likely to be a substantial
portion (e.g. between 50 and 70%) of the data - Just imagine an almost
stochastic sampling of the data in each table, and you'll get an idea.
Potentially quite smelly.
To start with, I thought. No problems. Just configure a DB server with an
obscene amount of RAM (e.g. 64GB), and configure PG with a shared buffer
cache that is big enough to hold every page of data in the database, plus
10% or whatever to allow for a bit of room, ensuring that there is enough
RAM in the box so that all the backend processes can do their thing, and
all the other services can do their thing, and the swap system on the host
remains idle.
Apparently not :(
I've read a number of places now saying that the PG cache has an optimal
size which isn't "as big as you can make it without affecting other stuff on
the machine".
The suggestion is to let linux take the strain for the lion's share of the
caching (using its buffer cache), and just make the PG cache big enough to
hold the data it needs for individual queries.
___
Ignoring for a moment the problem of answering the question 'so how big
shall I make the PG cache?', and ignoring the possibility that as the
database content changes over the months this answer will need updating from
time to time for optimal performance, does anyone have any actual experience
with trying to maintain a large, mainly RAM resident database?
What is it about the buffer cache that makes it so unhappy being able to
hold everything? I don't want to be seen as a cache hit fascist, but isn't
it just better if the data is just *there*, available in the postmaster's
address space ready for each backend process to access it, rather than
expecting the Linux cache mechanism, optimised as it may be, to have to do
the caching?
Is it that the PG cache entries are accessed through a 'not particularly
optimal for large numbers of tuples' type of strategy? (Optimal though it
might be for more modest numbers).
And on a more general note, with the advent of 64 bit addressing and rising
RAM sizes, won't there, with time, be more and more DB applications that
would want to capitalise on the potential speed improvements that come with
not having to work hard to get the right bits in the right bit of memory all
the time?
And finally, am I worrying too much, and actually this problem is common to
all databases?
Thanks for reading,
Andy
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-09 01:41 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Andy Ballingall <andy_ballingall@bigfoot.com>
0 siblings, 1 reply; 16+ messages in thread
From: Christopher Kings-Lynne @ 2004-07-09 01:41 UTC (permalink / raw)
To: Andy Ballingall <andy_ballingall@bigfoot.com>; +Cc: pgsql-performance
> What is it about the buffer cache that makes it so unhappy being able to
> hold everything? I don't want to be seen as a cache hit fascist, but isn't
> it just better if the data is just *there*, available in the postmaster's
> address space ready for each backend process to access it, rather than
> expecting the Linux cache mechanism, optimised as it may be, to have to do
> the caching?
Because the PostgreSQL buffer management algorithms are pitiful compared
to Linux's. In 7.5, it's improved with the new ARC algorithm, but still
- the Linux disk buffer cache will be very fast.
Chris
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-09 09:28 Andy Ballingall <andy_ballingall@bigfoot.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 2 replies; 16+ messages in thread
From: Andy Ballingall @ 2004-07-09 09:28 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-performance
Thanks, Chris.
> > What is it about the buffer cache that makes it so unhappy being able to
> > hold everything? I don't want to be seen as a cache hit fascist, but
isn't
> > it just better if the data is just *there*, available in the
postmaster's
> > address space ready for each backend process to access it, rather than
> > expecting the Linux cache mechanism, optimised as it may be, to have to
do
> > the caching?
>
> Because the PostgreSQL buffer management algorithms are pitiful compared
> to Linux's. In 7.5, it's improved with the new ARC algorithm, but still
> - the Linux disk buffer cache will be very fast.
>
I've had that reply elsewhere too. Initially, I was afraid that there was a
memory copy involved if the OS buffer cache supplied a block of data to PG,
but I've learned a lot more about the linux buffer cache, so it now makes
more sense to me why it's not a terrible thing to let the OS manage the
lions' share of the caching on a high RAM system.
On another thread, (not in this mailing list), someone mentioned that there
are a class of databases which, rather than caching bits of database file
(be it in the OS buffer cache or the postmaster workspace), construct a a
well indexed memory representation of the entire data in the postmaster
workspace (or its equivalent), and this, remaining persistent, allows the DB
to service backend queries far quicker than if the postmaster was working
with the assumption that most of the data was on disk (even if, in practice,
large amounts or perhaps even all of it resides in OS cache).
Though I'm no stranger to data management in general, I'm still in a steep
learning curve for databases in general and PG in particular, but I just
wondered how big a subject this is in the development group for PG at the
moment?
After all, we're now seeing the first wave of 'reasonably priced' 64 bit
servers supported by a proper 64 bit OS (e.g. linux). HP are selling a 4
Opteron server which can take 256GB of RAM, and that starts at $10000 (ok -
they don't give you that much RAM for that price - not yet, anyway!)
This is the future, isn't it? Each year, a higher percentage of DB
applications will be able to fit entirely in RAM, and that percentage is
going to be quite significant in just a few years. The disk system gets
relegated to a data preload on startup and servicing the writes as the
server does its stuff.
Regards,
Andy
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-09 14:16 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 2 replies; 16+ messages in thread
From: Merlin Moncure @ 2004-07-09 14:16 UTC (permalink / raw)
To: Andy Ballingall <andy_ballingall@bigfoot.com>; +Cc: pgsql-performance
> What is it about the buffer cache that makes it so unhappy being able
to
> hold everything? I don't want to be seen as a cache hit fascist, but
isn't
> it just better if the data is just *there*, available in the
postmaster's
> address space ready for each backend process to access it, rather than
> expecting the Linux cache mechanism, optimised as it may be, to have
to do
> the caching?
The disk cache on most operating systems is optimized. Plus, keeping
shared buffers low gives you more room to bump up the sort memory, which
will make your big queries run faster.
Merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-09 16:08 Andy Ballingall <andy_ballingall@bigfoot.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 0 replies; 16+ messages in thread
From: Andy Ballingall @ 2004-07-09 16:08 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-performance
>The disk cache on most operating systems is optimized. Plus, keeping
shared buffers low gives you more room to bump up the sort memory, which
will make your big queries run faster.
Thanks merlin,
Whether the OS caches the data or PG does, you still want it cached. If your
sorting backends gobble up the pages that otherwise would be filled with the
database buffers, then your postmaster will crawl, as it'll *really* have to
wait for stuff from disk. In my scenario, you'd spec the machine so that
there would be plenty of memory for *everything*.
On your OS optimisation point, OS caches are, of course, optimised. But
people have told me that PG's caching strategy is simply less well
optimised, and *that* is the reason for keeping the shared buffer cache down
in my scenario. That's a shame in a way, but I understand why it is the way
it is - other things have been addressed which speed up operations in
different ways. My 'all in RAM' scenario is very rare at the moment, so why
waste valuable development resources on developing optimised RAM based data
structures to hold the data for quicker query execution when hardly anyone
will see the benefit?
However - it won't be so rare for too much longer... If I gave you a half a
terabyte of RAM and a 4 processor 64 bit machine, I'm sure you could imagine
how much quicker databases could run if they were optimised for this sort of
platform.
Anyway, I'm looking forward to experimenting with stuff the way it works at
the moment.
Many thanks,
Andy
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-10 12:25 Andy Ballingall <andy_ballingall@bigfoot.com>
parent: Andy Ballingall <andy_ballingall@bigfoot.com>
1 sibling, 0 replies; 16+ messages in thread
From: Andy Ballingall @ 2004-07-10 12:25 UTC (permalink / raw)
To: J. Andrew Rogers <jrogers@neopolitan.com>; +Cc: pgsql-performance
Oops - sorry - I confused my numbers. The opteron machine in mind *only* has
up to 64GB of RAM (e.g. HP DL585) - here's the datapage:
http://h18004.www1.hp.com/products/servers/proliantdl585/index.html
Still - with *just* 64GB of RAM, that would comfortably provide for the type
of scenario I envisage. Is that still enough for your app?
The 256GB number came from something I read saying that the current crop of
64 bit chips will allow up to 256GB of RAM in principle, so it is just a
matter of time before the memory limit shoots up on these simple products.
If you are prepared to pay a bit more, already there are some big memory
options on linux:
E.g. you can have up to 192GB in an SGI Altix 350:
http://www.sgi.com/servers/altix/downloads/altix350_at_a_glance.pdf
Or up to 4 terabytes in it's bigger brother the Altix 3000 - but that's
getting a bit esoteric.
http://www.sgi.com/servers/altix/
(This won lots of awards recently)
The nice thing about the two things above is that they run linux in a single
address space NUMA setup, and in theory you can just bolt on more CPUs and
more RAM as your needs grow.
Thanks,
Andy
----- Original Message -----
From: "J. Andrew Rogers" <jrogers@neopolitan.com>
To: "Andy Ballingall" <andy_ballingall@bigfoot.com>
Sent: Friday, July 09, 2004 10:40 PM
Subject: Re: [PERFORM] Working on huge RAM based datasets
> On Fri, 2004-07-09 at 02:28, Andy Ballingall wrote:
> > After all, we're now seeing the first wave of 'reasonably priced' 64 bit
> > servers supported by a proper 64 bit OS (e.g. linux). HP are selling a 4
> > Opteron server which can take 256GB of RAM, and that starts at $10000
(ok -
> > they don't give you that much RAM for that price - not yet, anyway!)
>
>
> Which server is this?! They are selling an Opteron system that can hold
> 256 GB of RAM?
>
> I looked on their site, and couldn't find anything like that. I run
> some MASSIVE memory codes that don't need a lot of CPU, and if such a
> box existed, I'd be very interested.
>
> cheers,
>
> j. andrew rogers
>
>
>
----- Original Message -----
From: "J. Andrew Rogers" <jrogers@neopolitan.com>
To: "Andy Ballingall" <andy_ballingall@bigfoot.com>
Sent: Friday, July 09, 2004 10:40 PM
Subject: Re: [PERFORM] Working on huge RAM based datasets
> On Fri, 2004-07-09 at 02:28, Andy Ballingall wrote:
> > After all, we're now seeing the first wave of 'reasonably priced' 64 bit
> > servers supported by a proper 64 bit OS (e.g. linux). HP are selling a 4
> > Opteron server which can take 256GB of RAM, and that starts at $10000
(ok -
> > they don't give you that much RAM for that price - not yet, anyway!)
>
>
> Which server is this?! They are selling an Opteron system that can hold
> 256 GB of RAM?
>
> I looked on their site, and couldn't find anything like that. I run
> some MASSIVE memory codes that don't need a lot of CPU, and if such a
> box existed, I'd be very interested.
>
> cheers,
>
> j. andrew rogers
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-10 13:06 Christopher Browne <cbbrowne@acm.org>
parent: Andy Ballingall <andy_ballingall@bigfoot.com>
1 sibling, 0 replies; 16+ messages in thread
From: Christopher Browne @ 2004-07-10 13:06 UTC (permalink / raw)
To: pgsql-performance
Quoth andy_ballingall@bigfoot.com ("Andy Ballingall"):
> This is the future, isn't it? Each year, a higher percentage of DB
> applications will be able to fit entirely in RAM, and that percentage is
> going to be quite significant in just a few years. The disk system gets
> relegated to a data preload on startup and servicing the writes as the
> server does its stuff.
Regrettably, this may be something that fits better with MySQL, as it
already has an architecture oriented to having different "storage
engines" in behind.
There may be merit to the notion of implementing in-memory databases;
some assumptions change:
- You might use bitmap indices, although that probably "kills" MVCC;
- You might use T-trees rather than B-trees for indices, although
research seems to indicate that B-trees win out if there is a
great deal of concurrent access;
- It can become worthwhile to use compression schemes to fit more
records into memory that wouldn't be worthwhile if using demand
paging.
If you really want to try this, then look at Konstantin Knizhnik's
FastDB system:
http://www.ispras.ru/~knizhnik/fastdb.html
It assumes that your application will be a monolithic C++ process; if
that isn't the case, then performance will probably suffer due to
throwing in context switches.
The changes in assumptions are pretty vital ones, that imply you're
heading in a fairly different direction than that which PostgreSQL
seems to be taking.
That's not to say that there isn't merit to building a database system
using T-trees and bitmap indices attuned to applications where
main-memory storage is key; it's just that the proposal probably
should go somewhere else.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/languages.html
How does the guy who drives the snowplow get to work in the mornings?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-11 14:12 Jan Wieck <JanWieck@Yahoo.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 1 reply; 16+ messages in thread
From: Jan Wieck @ 2004-07-11 14:12 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Andy Ballingall <andy_ballingall@bigfoot.com>; pgsql-performance
On 7/9/2004 10:16 AM, Merlin Moncure wrote:
>> What is it about the buffer cache that makes it so unhappy being able
> to
>> hold everything? I don't want to be seen as a cache hit fascist, but
> isn't
>> it just better if the data is just *there*, available in the
> postmaster's
>> address space ready for each backend process to access it, rather than
>> expecting the Linux cache mechanism, optimised as it may be, to have
> to do
>> the caching?
>
> The disk cache on most operating systems is optimized. Plus, keeping
> shared buffers low gives you more room to bump up the sort memory, which
> will make your big queries run faster.
Plus, the situation will change dramatically with 7.5 where the disk
cache will have less information than the PG shared buffers, which will
become sequential scan resistant and will know that a block was pulled
in on behalf of vacuum and not because the regular database access
pattern required it.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-11 17:04 Christopher Browne <cbbrowne@acm.org>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 16+ messages in thread
From: Christopher Browne @ 2004-07-11 17:04 UTC (permalink / raw)
To: pgsql-performance
Martha Stewart called it a Good Thing when JanWieck@Yahoo.com (Jan Wieck) wrote:
> On 7/9/2004 10:16 AM, Merlin Moncure wrote:
>>> What is it about the buffer cache that makes it so unhappy being
>>> able to hold everything? I don't want to be seen as a cache hit
>>> fascist, but isn't it just better if the data is just *there*,
>>> available in the postmaster's address space ready for each backend
>>> process to access it, rather than expecting the Linux cache
>>> mechanism, optimised as it may be, to have to do the caching?
>> The disk cache on most operating systems is optimized. Plus,
>> keeping shared buffers low gives you more room to bump up the sort
>> memory, which will make your big queries run faster.
> Plus, the situation will change dramatically with 7.5 where the disk
> cache will have less information than the PG shared buffers, which
> will become sequential scan resistant and will know that a block was
> pulled in on behalf of vacuum and not because the regular database
> access pattern required it.
It'll be very curious how this changes things.
I _think_ it means that shared buffer usage becomes more efficient
both for small and large buffers, since vacuums and seq scans
shouldn't "eviscerate" the shared buffers the way they can in earlier
versions.
What would be most interesting to see is whether this makes it wise to
increase shared buffer size. It may be more effective to bump down
the cache a little, and bump up sort memory; hard to tell.
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/spreadsheets.html
"But life wasn't yes-no, on-off. Life was shades of gray, and
rainbows not in the order of the spectrum."
-- L. E. Modesitt, Jr., _Adiamante_
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-12 14:05 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 16+ messages in thread
From: Merlin Moncure @ 2004-07-12 14:05 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: pgsql-performance
Jan wrote:
> > The disk cache on most operating systems is optimized. Plus,
keeping
> > shared buffers low gives you more room to bump up the sort memory,
which
> > will make your big queries run faster.
>
> Plus, the situation will change dramatically with 7.5 where the disk
> cache will have less information than the PG shared buffers, which
will
> become sequential scan resistant and will know that a block was pulled
> in on behalf of vacuum and not because the regular database access
> pattern required it.
Hm. In my experience the different between data cached between shared
buffers and the O/S is not very much...both are fast. However, I almost
always see dramatic performance speedups for bumping up work mem. Are
you suggesting that it will be advantageous to bump up shared buffers?
Merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-12 14:23 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 2 replies; 16+ messages in thread
From: Merlin Moncure @ 2004-07-12 14:23 UTC (permalink / raw)
To: Andy Ballingall <andy_ballingall@bigfoot.com>; +Cc: pgsql-performance
Andy wrote:
> Whether the OS caches the data or PG does, you still want it cached.
If
> your
> sorting backends gobble up the pages that otherwise would be filled
with
> the
> database buffers, then your postmaster will crawl, as it'll *really*
have
> to
> wait for stuff from disk. In my scenario, you'd spec the machine so
that
> there would be plenty of memory for *everything*.
That's the whole point: memory is a limited resource. If pg is
crawling, then the problem is simple: you need more memory. The
question is: is it postgresql's responsibility to manage that resource?
Pg is a data management tool, not a memory management tool. The same
'let's manage everything' argument also frequently gets brought up wrt
file i/o, because people assume the o/s sucks at file management. In
reality, they are quite good, and through use of the generic interface
the administrator is free to choose a file system that best suits the
needs of the application.
At some point, hard disks will be replaced by solid state memory
technologies...do you really want to recode your memory manager when
this happens because all your old assumptions are no longer correct?
Merlin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-12 14:38 Rod Taylor <pg@rbt.ca>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 16+ messages in thread
From: Rod Taylor @ 2004-07-12 14:38 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
> What would be most interesting to see is whether this makes it wise to
> increase shared buffer size. It may be more effective to bump down
> the cache a little, and bump up sort memory; hard to tell.
How do we go about scheduling tests with the OSDL folks? If they could
do 10 runs with buffers between 1k and 500k it would help us get a broad
view of the situation.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-12 16:38 Josh Berkus <josh@agliodbs.com>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 1 reply; 16+ messages in thread
From: Josh Berkus @ 2004-07-12 16:38 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Rond, Chris,
> > What would be most interesting to see is whether this makes it wise to
> > increase shared buffer size. It may be more effective to bump down
> > the cache a little, and bump up sort memory; hard to tell.
>
> How do we go about scheduling tests with the OSDL folks? If they could
> do 10 runs with buffers between 1k and 500k it would help us get a broad
> view of the situation.
Yes. We'll need to. However, I'd like to wait until we're officially in
Beta. I'll be seeing the OSDL folks in person (PostgreSQL+OSDL BOF at Linux
World Expo!!) in a couple of weeks.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-12 18:01 Jan Wieck <JanWieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 16+ messages in thread
From: Jan Wieck @ 2004-07-12 18:01 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Rod Taylor <pg@rbt.ca>; Christopher Browne <cbbrowne@acm.org>; pgsql-performance
On 7/12/2004 12:38 PM, Josh Berkus wrote:
> Rond, Chris,
>
>> > What would be most interesting to see is whether this makes it wise to
>> > increase shared buffer size. It may be more effective to bump down
>> > the cache a little, and bump up sort memory; hard to tell.
>>
>> How do we go about scheduling tests with the OSDL folks? If they could
>> do 10 runs with buffers between 1k and 500k it would help us get a broad
>> view of the situation.
>
> Yes. We'll need to. However, I'd like to wait until we're officially in
> Beta. I'll be seeing the OSDL folks in person (PostgreSQL+OSDL BOF at Linux
> World Expo!!) in a couple of weeks.
>
Don't forget to add that ARC needs some time actually to let the
algorithm adjust the queue sizes and populate the cache according to the
access pattern. You can't start a virgin postmaster and then slam on the
accellerator of your test application by launching 500 concurrent
clients out of the blue and expect that it starts off airborne.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-19 09:12 Andy Ballingall <andy_ballingall@bigfoot.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 0 replies; 16+ messages in thread
From: Andy Ballingall @ 2004-07-19 09:12 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: pgsql-performance
Sorry for the late reply - I've been away.
Merlin, I'd like to come back with a few more points!
>That's the whole point: memory is a limited resource. If pg is
>crawling, then the problem is simple: you need more memory.
My posting only relates to the scenario where RAM is not a limiting factor,
a scenario which shall become increasingly common over the next few years,
as 64 bit processors and OSs allow the exploitation of ever larger, ever
cheaper RAM.
> The question is: is it postgresql's responsibility to manage that
resource?
I think you are confusing the issue of RAM and address space.
Any application can acquire a piece of address space for its own use. It is
the responsibility of the application to do what it needs with that address
space. I'm interested in how PG could do something better in its address
space when it knows that it can fit all the data it operates on within that
address space.
Though the OS is responsible for determining whether that address space is
RAM resident or not, in my scenario, this is irrelevant, because there
*will* be enough RAM for everything, and the OS will, in that scenario,
allow all the address space to become RAM resident.
I am not advocating undermining the OS in any way. It would be stupid to
make PGSQL take over the running of the hardware.
>Pg is a data management tool, not a memory management tool.
I'm not criticising PG. PG is actually a 'DISK/MEMORY' data management tool.
It manages data which lives on disks, but it can only operate on that data
in memory, and goes to some lengths to try to fit bits of disk data in a
defined piece of memory, and push them back out again.
At the moment, this model assumes that RAM is a scarce resource.
The model still 'sort of' works when RAM is actually not scarce, because the
OS effectively uses that extra RAM to make IO *appear* to be quicker, and
indeed, I've found that a hint has been added to PG to tell it how much the
OS is likely to be caching.
But the question is this:
"If you wrote a DB from scratch with the assumption that *all* the data
could fit in the address space allocated by the postmaster, and you were
confident that the OS had enough RAM so that you never suffered vmem page
misses, couldn't you make things go much faster?"
A more pertinent question is:
"Could PG be extended to have a flag, which when enabled, told it to operate
with the assumption that it could fit all the disk data in RAM, and
implement the data organisation optimisations that rely on the persistence
of data in address space?"
>The same
>'let's manage everything' argument also frequently gets brought up wrt
>file i/o, because people assume the o/s sucks at file management.
Well, I'm not saying this.
I have substantial experience with high performance file IO through a
filesystem.
But if you are interested in high speed IO, naive 'let the OS do everything'
approach isn't often good enough. You, the application, have to be aware
that the order and timing of IO requests, along with the size of IO block
you cause to trigger, have a dramatic impact on the speed with which the
data reaches your app, OS or no OS. Most high speed storage still relies on
spinning things containing data that can only be accessed in a certain way,
and data movement is page boundary sensitive. The OS may hide these details
from you, but you, the app writer, have to have an understanding of the
underlying reality if you want to optimise performance.
I want to stress that at no point am I advocating *not* using the OS. PG
should do ALL IO and memory allocation through the OS, otherwise you end up
with a platform specific product that is of little use.
That given, there is still the opportunity for PG to be able to operate far
more efficiently in my high memory scenario.
Wouldn't your backend processes like to have the entire database sitting
ready in address space (ram resident, of course!), indexes all fully built?
No tuple more than a few machine instructions away?
Imagine the postmaster isn't having to frantically decide which bits of data
to kick out of the workspace in order to keep the backends happy. Imagine
the postmaster isn't having to build structures to keep track of the newly
read in blocks of data from 'disk' (or OS cache).
Is this not a compelling scenario?
>At some point, hard disks will be replaced by solid state memory
>technologies...
This is irrelevant to my scenario, though solid state disks would allow
write speeds to improve, which would add to the gains which I am fishing for
here.
>do you really want to recode your memory manager when
>this happens because all your old assumptions are no longer correct?
My scenario assumes nothing about how the data is stored, but you are right
to flag the problems that arise when original assumptions about hardware
become incorrect.
For example, PG assumes that RAM is a rare resource, and it assumes the
postmaster cannot fit the entire database in a single address space.
*These* assumptions are now not correct, following the 64bit address space
breakthrough.
The availability of 64 bit addressing and huge banks of RAM is of enormous
significance to databases, and itIt is the whole reason for my post.
Over the next 5-10 years, an increasing proportion of databases will fit
comfortably in RAM resident address space on commodity equipment.
So, the question for the people involved in PG is: *how* can PG be improved
to make use of this, and reap the very substantial speed gains in this
scenario, without breaking the existing usage scenarios of PG in the
traditional 'DB > RAM' scenario?
The answer isn't "undermine the OS". The answer is "make the postmaster able
to build and operate with persistent, query optimised representations of the
disk data".
Yes, I guess that might be a lot of work.. But the DB that delivers this
performance will be very well placed in the next 5 years, don't you think?
Thanks for your comments,
Regards,
Andy
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Working on huge RAM based datasets
@ 2004-07-20 10:45 abhousehunt <abhousehunt@blueyonder.co.uk>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
1 sibling, 0 replies; 16+ messages in thread
From: abhousehunt @ 2004-07-20 10:45 UTC (permalink / raw)
To: pgsql-performance
Sorry for the late reply - I've been away, and I've had problems posting too
:(
Merlin, I'd like to come back with a few more points!
>That's the whole point: memory is a limited resource. If pg is
>crawling, then the problem is simple: you need more memory.
My posting only relates to the scenario where RAM is not a limiting factor,
a scenario which shall become increasingly common over the next few years,
as 64 bit processors and OSs allow the exploitation of ever larger, ever
cheaper RAM. Incidentally, If PG is crawling, memory might be the
problem...but not necessarily - could be disk bound on writes.
> The question is: is it postgresql's responsibility to manage that
resource?
I think you are confusing the issue of RAM and address space.
Any application can acquire a piece of address space for its own use. It is
the responsibility of the application to do what it needs with that address
space. I'm interested in how PG could do something better in its address
space when it knows that it can fit all the data it operates on within that
address space.
Though the OS is responsible for determining whether that address space is
RAM resident or not, in my scenario, this is irrelevant, because there
*will* be enough RAM for everything, and the OS will, in that scenario,
allow all the address space to become RAM resident.
I am not advocating undermining the OS in any way. It would be stupid to
make PGSQL take over the running of the hardware. I've learned the hard way
that bypassing the OS is just a big pain up the backside!
>Pg is a data management tool, not a memory management tool.
I'm not criticising PG. PG is actually a 'DISK/MEMORY' data management tool.
It manages data which lives on disks, but it can only operate on that data
in memory, and goes to some lengths to try to fit bits of disk data in a
defined piece of memory, and push them back out again.
At the moment, this model assumes that RAM is a scarce resource.
The model still 'sort of' works when RAM is actually not scarce, because the
OS effectively uses that extra RAM to make IO *appear* to be quicker, and
indeed, I've found that a hint has been added to PG to tell it how much the
OS is likely to be caching.
But the question is this:
"If you wrote a DB from scratch with the assumption that *all* the data
could fit in the address space allocated by the postmaster, and you were
confident that the OS had enough RAM so that you never suffered vmem page
misses, couldn't you make things go much faster?"
A more pertinent question is:
"Could PG be extended to have a flag, which when enabled, told it to operate
with the assumption that it could fit all the disk data in RAM, and
implement the data organisation optimisations that rely on the persistence
of data in address space?"
>The same
>'let's manage everything' argument also frequently gets brought up wrt
>file i/o, because people assume the o/s sucks at file management.
Well, I'm not saying this.
I have substantial experience with high performance file IO through a
filesystem.
But if you are interested in high speed IO, naive 'let the OS do everything'
approach isn't often good enough. You, the application, have to be aware
that the order and timing of IO requests, along with the size of IO block
you cause to trigger, have a dramatic impact on the speed with which the
data reaches your app, OS or no OS. Most high speed storage still relies on
spinning things containing data that can only be accessed in a certain way,
and data movement is page boundary sensitive. The OS may hide these details
from you, but you, the app writer, have to have an understanding of the
underlying reality if you want to optimise performance.
I want to stress that at no point am I advocating *not* using the OS. PG
should do ALL IO and memory allocation through the OS, otherwise you end up
with a platform specific product that is of little use.
That given, there is still the opportunity for PG to be able to operate far
more efficiently in my high memory scenario.
Wouldn't your backend processes like to have the entire database sitting
ready in address space (ram resident, of course!), indexes all fully built?
No tuple more than a few machine instructions away?
Imagine the postmaster isn't having to frantically decide which bits of data
to kick out of the workspace in order to keep the backends happy. Imagine
the postmaster isn't having to build structures to keep track of the newly
read in blocks of data from 'disk' (or OS cache). Imagine that everything
was just there...
Is this not a compelling scenario?
>At some point, hard disks will be replaced by solid state memory
>technologies...
This is irrelevant to my scenario. The optimisations I crave are to do with
getting the entire database in a query-optimised form near to the CPUS -
i.e. in fast RAM. (I'd expect solid state disk ram to be much slower than
the RAM that sits nearer the CPU).
The speed of the persistent storage system (whether spinning platters or
some sort of persistent solid state memory) isn't really of direct relevance
to my argument. Solid state disks would allow write speeds to improve, which
would add to the gains which I am fishing for. So when they come, I'll be
happy.
Still. Solid state disks aren't really an option right now. Big RAM is.
>do you really want to recode your memory manager when
>this happens because all your old assumptions are no longer correct?
My scenario assumes nothing about how the data is stored, but you are right
to flag the problems that arise when original assumptions about hardware
become incorrect.
For example, PG assumes that RAM is a rare resource, and it assumes the
postmaster cannot fit the entire database in a single address space.
*These* assumptions are now not correct, following the 64bit address space
breakthrough.
The availability of 64 bit addressing and huge banks of RAM is of enormous
significance to databases, and it is the whole reason for my post.
Over the next 5-10 years, an increasing proportion of databases will fit
comfortably in RAM resident address space on commodity equipment. My
particular nationwide application will fit into RAM now. Hence my interest!
So, the question for the people involved in PG is: *how* can PG be improved
to make use of this, and reap the very substantial speed gains in this
scenario, without breaking the existing usage scenarios of PG in the
traditional 'DB > RAM' scenario?
The answer isn't "undermine the OS". The answer might be"make the postmaster
able
to build and operate with persistent, query optimised representations of the
disk data".
Yes, I guess that might be a lot of work.. But the DB that delivers this
performance will be very well placed in the next 5 years, don't you think?
Anyway - I look forward to further feedback, and thanks for your comments so
far.
Regards,
Andy
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2004-07-20 10:45 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-08 18:05 Working on huge RAM based datasets Andy Ballingall <andy_ballingall@bigfoot.com>
2004-07-09 01:41 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-09 09:28 ` Andy Ballingall <andy_ballingall@bigfoot.com>
2004-07-10 12:25 ` Andy Ballingall <andy_ballingall@bigfoot.com>
2004-07-10 13:06 ` Christopher Browne <cbbrowne@acm.org>
2004-07-09 14:16 Re: Working on huge RAM based datasets Merlin Moncure <merlin.moncure@rcsonline.com>
2004-07-09 16:08 ` Andy Ballingall <andy_ballingall@bigfoot.com>
2004-07-11 14:12 ` Jan Wieck <JanWieck@Yahoo.com>
2004-07-11 17:04 ` Christopher Browne <cbbrowne@acm.org>
2004-07-12 14:38 ` Rod Taylor <pg@rbt.ca>
2004-07-12 16:38 ` Josh Berkus <josh@agliodbs.com>
2004-07-12 18:01 ` Jan Wieck <JanWieck@Yahoo.com>
2004-07-12 14:05 Re: Working on huge RAM based datasets Merlin Moncure <merlin.moncure@rcsonline.com>
2004-07-12 14:23 Re: Working on huge RAM based datasets Merlin Moncure <merlin.moncure@rcsonline.com>
2004-07-19 09:12 ` Andy Ballingall <andy_ballingall@bigfoot.com>
2004-07-20 10:45 ` abhousehunt <abhousehunt@blueyonder.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox