Re: running postgres in a ramdisk

> My lab is looking into speeding up our databases beyond the tuning
> that can already be done through configuration. Has anyone had
> hands-on experience running postgres with PGDATA entirely in
> memory?

Yes, table growth is going to be your biggest problem if making your
system crash proof isn't.

> One possible configuration I can imagine is that we construct the
> database on disk, then on boot create an appropriate ramdisk and
> copy the whole postgres tree to the ramdisk. Then we start the
> postgres daemons pointing PGDATA to the ramdisk. The database in
> memory won't change (or, if it does, we don't care about saving the
> changes), so there's no concern about losing changes if there's a
> crash.

Are you running with default_transaction_read_only=true?

> Is this even a starter, or is postgres smart enough to use as much
> memory as it's told? We'd be getting a huge chunk of RAM (over
> 10GB!), so it would be possible to hold our entire database in RAM
> if needed.

Depends on the architecture. Any 32bit architecture, even with PAE
will be relatively worthless to you. PAE is massively expensive in
terms of memory access and isn't worth pursuing as an option, IMHO.
Finding yourself an Opteron and running it in 64bit mode is going to
be your best bet if you're trying to juggle more than 4GB of RAM,
regardless of how you're using that RAM (ie: ramdisk, /dev/shm, etc).

> Any pointers would be great--a first perusal of Google didn't turn
> up much (just stuff on loading a database from CD-ROM into a
> ramdisk).

Personally, I'd crank up the number of shared buffers.

http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node3.html

That way you're only caching what you really care about and not some
chunk of data that's inconsequential. By the same token, you may find
that the OS does a better job of doing the caching and cranking up the
FS cache will perform better as the amount of RAM in your system
increases.

-sc

--
Sean Chittenden