pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMount database on RAM disk?
8+ messages / 8 participants
[nested] [flat]
* Mount database on RAM disk?
@ 2005-07-08 01:32 Stuart Bishop <stuart@stuartbishop.net>
2005-07-08 03:05 ` Re: Mount database on RAM disk? Joshua D. Drake <jd@commandprompt.com>
2005-07-08 03:30 ` Re: Mount database on RAM disk? Chris Browne <cbbrowne@acm.org>
2005-07-08 17:22 ` Re: Mount database on RAM disk? Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 8+ messages in thread
From: Stuart Bishop @ 2005-07-08 01:32 UTC (permalink / raw)
To: pgsql-performance
I'm putting together a road map on how our systems can scale as our load
increases. As part of this, I need to look into setting up some fast read
only mirrors of our database. We should have more than enough RAM to fit
everything into memory. I would like to find out if I could expect better
performance by mounting the database from a RAM disk, or if I would be
better off keeping that RAM free and increasing the effective_cache_size
appropriately.
I'd also be interested in knowing if this is dependant on whether I am
running 7.4, 8.0 or 8.1.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 01:32 Mount database on RAM disk? Stuart Bishop <stuart@stuartbishop.net>
@ 2005-07-08 03:05 ` Joshua D. Drake <jd@commandprompt.com>
2 siblings, 0 replies; 8+ messages in thread
From: Joshua D. Drake @ 2005-07-08 03:05 UTC (permalink / raw)
To: Stuart Bishop <stuart@stuartbishop.net>; +Cc: pgsql-performance
Stuart Bishop wrote:
> I'm putting together a road map on how our systems can scale as our load
> increases. As part of this, I need to look into setting up some fast read
> only mirrors of our database. We should have more than enough RAM to fit
> everything into memory. I would like to find out if I could expect better
> performance by mounting the database from a RAM disk, or if I would be
> better off keeping that RAM free and increasing the effective_cache_size
> appropriately.
In theory yes if you can fit the entire database onto a ram disk then
you would see a performance benefit.
Sincerely,
Joshua D. Drake
>
> I'd also be interested in knowing if this is dependant on whether I am
> running 7.4, 8.0 or 8.1.
>
>
--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 01:32 Mount database on RAM disk? Stuart Bishop <stuart@stuartbishop.net>
@ 2005-07-08 03:30 ` Chris Browne <cbbrowne@acm.org>
2005-07-08 14:03 ` Re: Mount database on RAM disk? Rod Taylor <pg@rbt.ca>
2 siblings, 1 reply; 8+ messages in thread
From: Chris Browne @ 2005-07-08 03:30 UTC (permalink / raw)
To: pgsql-performance
stuart@stuartbishop.net (Stuart Bishop) writes:
> I'm putting together a road map on how our systems can scale as our
> load increases. As part of this, I need to look into setting up some
> fast read only mirrors of our database. We should have more than
> enough RAM to fit everything into memory. I would like to find out
> if I could expect better performance by mounting the database from a
> RAM disk, or if I would be better off keeping that RAM free and
> increasing the effective_cache_size appropriately.
If you were willing to take on a not-inconsiderable risk, I'd think
that storing WAL files on a RAMDISK would be likely to be the fastest
improvement imaginable.
If I could get and deploy some SSD (Solid State Disk) devices that
would make this sort of thing *actually safe,* I'd expect that to be a
pretty fabulous improvement, at least for write-heavy database
activity.
> I'd also be interested in knowing if this is dependant on whether I
> am running 7.4, 8.0 or 8.1.
Behaviour of all three could be somewhat different, as management of
the shared cache has been in flux...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 01:32 Mount database on RAM disk? Stuart Bishop <stuart@stuartbishop.net>
2005-07-08 03:30 ` Re: Mount database on RAM disk? Chris Browne <cbbrowne@acm.org>
@ 2005-07-08 14:03 ` Rod Taylor <pg@rbt.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Rod Taylor @ 2005-07-08 14:03 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
> If I could get and deploy some SSD (Solid State Disk) devices that
> would make this sort of thing *actually safe,* I'd expect that to be a
> pretty fabulous improvement, at least for write-heavy database
> activity.
Not nearly as much as you would expect. For the price of the SSD and a
SCSI controller capable of keeping up to the SSD along with your regular
storage with enough throughput to keep up to structure IO, you can
purchase a pretty good mid-range SAN which will be just as capable and
much more versatile.
--
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 01:32 Mount database on RAM disk? Stuart Bishop <stuart@stuartbishop.net>
@ 2005-07-08 17:22 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2005-07-08 17:22 UTC (permalink / raw)
To: pgsql-performance; +Cc: Stuart Bishop <stuart@stuartbishop.net>
Stuart,
> I'm putting together a road map on how our systems can scale as our load
> increases. As part of this, I need to look into setting up some fast
> read only mirrors of our database. We should have more than enough RAM
> to fit everything into memory. I would like to find out if I could
> expect better performance by mounting the database from a RAM disk, or
> if I would be better off keeping that RAM free and increasing the
> effective_cache_size appropriately.
If you're accessing a dedicated, read-only system with a database small
enough to fit in RAM, it'll all be cached there anyway, at least on Linux
and BSD. You won't be gaining anything by creating a ramdisk.
BTW, effective_cache_size doesn't determine the amount of caching done. It
just informs the planner about how much db is likely to be cached. The
actual caching is up to the OS/filesystem.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
@ 2005-07-08 19:21 Merlin Moncure <merlin.moncure@rcsonline.com>
2005-07-09 21:48 ` Re: Mount database on RAM disk? Alex Stapleton <alexs@advfn.com>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2005-07-08 19:21 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Stuart Bishop <stuart@stuartbishop.net>; pgsql-performance
> Stuart,
>
> > I'm putting together a road map on how our systems can scale as our
load
> > increases. As part of this, I need to look into setting up some fast
> > read only mirrors of our database. We should have more than enough
RAM
> > to fit everything into memory. I would like to find out if I could
> > expect better performance by mounting the database from a RAM disk,
or
> > if I would be better off keeping that RAM free and increasing the
> > effective_cache_size appropriately.
>
> If you're accessing a dedicated, read-only system with a database
small
> enough to fit in RAM, it'll all be cached there anyway, at least on
Linux
> and BSD. You won't be gaining anything by creating a ramdisk.
ditto windows.
Files cached in memory are slower than reading straight from memory but
not nearly enough to justify reserving memory for your use. In other
words, your O/S is a machine with years and years of engineering
designed best how to dole memory out to caching and various processes.
Why second guess it?
Merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 19:21 Re: Mount database on RAM disk? Merlin Moncure <merlin.moncure@rcsonline.com>
@ 2005-07-09 21:48 ` Alex Stapleton <alexs@advfn.com>
2005-07-10 04:54 ` Re: Mount database on RAM disk? Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 8+ messages in thread
From: Alex Stapleton @ 2005-07-09 21:48 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: josh@agliodbs.com; Stuart Bishop <stuart@stuartbishop.net>; pgsql-performance
On 8 Jul 2005, at 20:21, Merlin Moncure wrote:
>> Stuart,
>>
>>
>>> I'm putting together a road map on how our systems can scale as our
>>>
> load
>
>>> increases. As part of this, I need to look into setting up some fast
>>> read only mirrors of our database. We should have more than enough
>>>
> RAM
>
>>> to fit everything into memory. I would like to find out if I could
>>> expect better performance by mounting the database from a RAM disk,
>>>
> or
>
>>> if I would be better off keeping that RAM free and increasing the
>>> effective_cache_size appropriately.
>>>
>>
>> If you're accessing a dedicated, read-only system with a database
>>
> small
>
>> enough to fit in RAM, it'll all be cached there anyway, at least on
>>
> Linux
>
>> and BSD. You won't be gaining anything by creating a ramdisk.
>>
>
>
>
> ditto windows.
>
> Files cached in memory are slower than reading straight from memory
> but
> not nearly enough to justify reserving memory for your use. In other
> words, your O/S is a machine with years and years of engineering
> designed best how to dole memory out to caching and various processes.
> Why second guess it?
Because sometimes it gets it wrong. The most brutal method is
occasionally the most desirable. Even if it not the "right" way to do
it.
> Merlin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Mount database on RAM disk?
2005-07-08 19:21 Re: Mount database on RAM disk? Merlin Moncure <merlin.moncure@rcsonline.com>
2005-07-09 21:48 ` Re: Mount database on RAM disk? Alex Stapleton <alexs@advfn.com>
@ 2005-07-10 04:54 ` Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 8+ messages in thread
From: Christopher Browne @ 2005-07-10 04:54 UTC (permalink / raw)
To: pgsql-performance
> On 8 Jul 2005, at 20:21, Merlin Moncure wrote:
>> ditto windows.
>>
>> Files cached in memory are slower than reading straight from memory
>> but not nearly enough to justify reserving memory for your use. In
>> other words, your O/S is a machine with years and years of
>> engineering designed best how to dole memory out to caching and
>> various processes. Why second guess it?
>
> Because sometimes it gets it wrong. The most brutal method is
> occasionally the most desirable. Even if it not the "right" way to do
> it.
The fact that cache allows reads to come from memory means that for
read-oriented activity, you're generally going to be better off
leaving RAM as "plain ordinary system memory" so that it can
automatically be drawn into service as cache.
Thus, the main reason to consider using a RAM-disk is the fact that
update times are negligible as there is not the latency of a
round-trip to the disk.
That would encourage its use for write-heavy tables, with the STRONG
caveat that a power outage could readily destroy the database :-(.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of the Evil Overlord #153. "My Legions of Terror will be an
equal-opportunity employer. Conversely, when it is prophesied that no
man can defeat me, I will keep in mind the increasing number of
non-traditional gender roles." <http://www.eviloverlord.com/;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-07-10 04:54 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-08 01:32 Mount database on RAM disk? Stuart Bishop <stuart@stuartbishop.net>
2005-07-08 03:05 ` Joshua D. Drake <jd@commandprompt.com>
2005-07-08 03:30 ` Chris Browne <cbbrowne@acm.org>
2005-07-08 14:03 ` Rod Taylor <pg@rbt.ca>
2005-07-08 17:22 ` Josh Berkus <josh@agliodbs.com>
2005-07-08 19:21 Re: Mount database on RAM disk? Merlin Moncure <merlin.moncure@rcsonline.com>
2005-07-09 21:48 ` Alex Stapleton <alexs@advfn.com>
2005-07-10 04:54 ` Christopher Browne <cbbrowne@acm.org>
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