Re: Best setup for RAM drive

On Tue, 4 Mar 2003, Chris Sutton wrote:

> Hello,
>
> I need some insight on the best way to use a RAM drive in a Postgresql
> installation. Here is our situation and current setup:
>
> Postgresql 7.2.1

First suggestion: upgrade to 7.2.4 to address several bugs.

> The concern of course is if something happends to the RAM drive we are
> S.O.L. and have to go to the last backup (pg_dump happens each night).

If you are concerned, I would definitely backup more often. Increased
performance of the disk system will speed up dumps.

> The other concern is if the disk size of the database grows past or near
> 8gb, we would either have to get a bigger RAM drive or somehow split
> things betten SCSI and RAM drive.

There has been a lot of talk over the last few years about introducing
user-defined storage locations for objects under Postgres. I'm not sure
that this will get into 7.4.

If it did, I would recommend storing hot tables/indexes (frequently
accessed) and all temporary backing files (used for large sorts, joins,
etc). The problem is, however, making sure the planner knows that the cost
of retrieving a page is different on a solid state disk when compared to a
RAID 5 on a PC.

You *could* use symlinks, but postgres wont know anything about
them: operations on relations/objects such as add, drop, rename, etc will
simply unlink the symlink and create a new file on the disk system.

> Should we just put pgsql/data/pg_xlog on the RAM drive?

You need to look at the nature of your database. If it is static, pg_xlog
isn't seeing much action. If there is a small amount of modification to
data but you can get it all with pg_dump on a frequent basis -- sure,
putting pg_xlog on a RAM disk will speed it up. If your database is
getting updated often and you cannot afford to lose data during a
powerfailure (RAM disk goes down too), then don't put pg_xlog on it. In
fact, put nothing important on it.

You can get a similar performance increase by turning off fsync() in
postgresql.conf -- but, you lose the guarantee of the persistence of your
data.

Gavin