Обсуждение: Mount database on RAM disk?

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/

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

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/>

> 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.

--

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

> 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

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
>

> 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/>