Re: running postgres in a ramdisk

Keith Keller wrote:

> Hi sfpuggers,
>
> 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?
>
> 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.
>
> 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.
>
> 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).

Assuming you are using Linux, the best way would be to use the tmpfs
device (/dev/shm) since the "standard" Linux ramdisk requires a kernel
recompile. Technically, tmpfs is RAM+swap but since your objective is to
run everything from RAM on a computer with gigabytes of memory, you
could easily eliminate the swapdisk. The process would be: (1) mount
/dev/shm to a directory, (2) copy your postgres dir
lock-stock-and-barrel to the ramdisk directory and (3) start up postgres
using that copy of the data. Tmpfs is dynamically resizable but the big
gotcha is that under 32-bit Linux, you're limited to 4GB max for a
ramdisk of any type.

If you can't live with a 4GB limit and can't upgrade to a 64-bit CPU+OS,
the 2nd best way would be to utilize the OS's caching. Upon bootup, copy
your postgres data directory to /dev/null. That should bring every
page into the OS cache and if you have fsync=false, you should be mostly
operating from RAM.