Thread: 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/
Attachment
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/>