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
Dual PIII 800
RAID 5 SCSI disks
Platypus 8GB PCI QikDrive (the RAM drive).  http://www.platypus.net

The Platypus RAM drive is a PCI card with 8GB of ram onboard with an 
external power supply so if the main power to the server goes off, the RAM 
is still powered, so it's persistent between reboots.

Currently the disk size of our database is 3.2GB, so we put the whole 
pgsql directory on the RAM drive.  Current preformance is very 
snappy with the bottleneck being the CPUs.  

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

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.

I don't quite grasp the full inner workings of Postgresql, but 
for those of you who obviously do, is there a better way of setting things 
up where you could still use the RAM drive for portions of the pgsql 
directory structure while putting the rest on disk where it's safer?

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

Also, in the very near future we will be upgrading to another server, 
pg7.3.2 with dual P4 2.4 xenon's.  The RAM drive will go into this new 
server.

Any suggestions?

Thanks

Chris



Tue, 4 Mar 2003 07:03:59 -0800 (PST)
Chris Sutton <chris@smoothcorp.com>
Best setup for RAM drive
Chris Sutton kirjutas T, 04.03.2003 kell 17:03:
> 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
> Dual PIII 800
> RAID 5 SCSI disks
> Platypus 8GB PCI QikDrive (the RAM drive).  http://www.platypus.net
> 
> The Platypus RAM drive is a PCI card with 8GB of ram onboard with an 
> external power supply so if the main power to the server goes off, the RAM 
> is still powered, so it's persistent between reboots.
> 
> Currently the disk size of our database is 3.2GB, so we put the whole 
> pgsql directory on the RAM drive.  Current preformance is very 
> snappy with the bottleneck being the CPUs.  
> 
> 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).
> 
> 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.
> 
> I don't quite grasp the full inner workings of Postgresql, but 
> for those of you who obviously do, is there a better way of setting things 
> up where you could still use the RAM drive for portions of the pgsql 
> directory structure while putting the rest on disk where it's safer?
> 
> Should we just put pgsql/data/pg_xlog on the RAM drive?
> 
> Also, in the very near future we will be upgrading to another server, 
> pg7.3.2 with dual P4 2.4 xenon's.  The RAM drive will go into this new 
> server.
> 
> Any suggestions?

The most obvious suggestion is to put the WAL files on RAM disk - these
are the things that would most directly affect _write_ performance as
these are the only ones the *must* hit the disk befor the transaction
can be committed.

If you are after read performance, then you just ;) have to determine
which are the most frequently used files that are not reasonably cached
.

---------------
Hannu








04 Mar 2003 22:57:27 +0200
Hannu Krosing <hannu@tm.ee>
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


Wed, 5 Mar 2003 08:20:57 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Best setup for RAM drive
Why not just run PostgreSQL like everyone else does (from a hard drive) and
simply give it heaps of buffers - let PostgreSQL manage its RAM itself?

Unless you have your xlog on physical storage, you are asking for trouble.
Actually, unless you have all your database in physical storage, you are
asking for trouble!  (At least until we have point in time recovery, but
that still requires you to have your xlog on hard drive)

Chris

----- Original Message -----
From: "Chris Sutton" <chris@smoothcorp.com>
To: <pgsql-hackers@postgresql.org>
Sent: Tuesday, March 04, 2003 11:03 PM
Subject: [HACKERS] Best setup for RAM drive


> 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
> Dual PIII 800
> RAID 5 SCSI disks
> Platypus 8GB PCI QikDrive (the RAM drive).  http://www.platypus.net
>
> The Platypus RAM drive is a PCI card with 8GB of ram onboard with an
> external power supply so if the main power to the server goes off, the RAM
> is still powered, so it's persistent between reboots.
>
> Currently the disk size of our database is 3.2GB, so we put the whole
> pgsql directory on the RAM drive.  Current preformance is very
> snappy with the bottleneck being the CPUs.
>
> 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).
>
> 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.
>
> I don't quite grasp the full inner workings of Postgresql, but
> for those of you who obviously do, is there a better way of setting things
> up where you could still use the RAM drive for portions of the pgsql
> directory structure while putting the rest on disk where it's safer?
>
> Should we just put pgsql/data/pg_xlog on the RAM drive?
>
> Also, in the very near future we will be upgrading to another server,
> pg7.3.2 with dual P4 2.4 xenon's.  The RAM drive will go into this new
> server.
>
> Any suggestions?
>
> Thanks
>
> Chris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Wed, 5 Mar 2003 09:14:36 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Best setup for RAM drive
The idea of a RAM disk based database and reliable storage are in 
complete opposition. Forget it.

The question is: What do you need the *raw* speed of a RAM disk for, and 
what can you tollerate for overhead for reliability?

You have posed a question about how to implement a flawed solution, what 
is the original problem you are intending to solve?


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
>Dual PIII 800
>RAID 5 SCSI disks
>Platypus 8GB PCI QikDrive (the RAM drive).  http://www.platypus.net
>
>The Platypus RAM drive is a PCI card with 8GB of ram onboard with an 
>external power supply so if the main power to the server goes off, the RAM 
>is still powered, so it's persistent between reboots.
>
>Currently the disk size of our database is 3.2GB, so we put the whole 
>pgsql directory on the RAM drive.  Current preformance is very 
>snappy with the bottleneck being the CPUs.  
>
>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).
>
>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.
>
>I don't quite grasp the full inner workings of Postgresql, but 
>for those of you who obviously do, is there a better way of setting things 
>up where you could still use the RAM drive for portions of the pgsql 
>directory structure while putting the rest on disk where it's safer?
>
>Should we just put pgsql/data/pg_xlog on the RAM drive?
>
>Also, in the very near future we will be upgrading to another server, 
>pg7.3.2 with dual P4 2.4 xenon's.  The RAM drive will go into this new 
>server.
>
>Any suggestions?
>
>Thanks
>
>Chris
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>  
>



Wed, 05 Mar 2003 15:05:03 -0500
mlw <pgsql@mohawksoft.com>
Re: Best setup for RAM drive
Hannu Krosing kirjutas T, 04.03.2003 kell 22:57:
> Chris Sutton kirjutas T, 04.03.2003 kell 17:03:
> > 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
> > Dual PIII 800
> > RAID 5 SCSI disks
> > Platypus 8GB PCI QikDrive (the RAM drive).  http://www.platypus.net
> > 
> > The Platypus RAM drive is a PCI card with 8GB of ram onboard with an 
> > external power supply so if the main power to the server goes off, the RAM 
> > is still powered, so it's persistent between reboots.
> > 
> ...
> > 
> > Any suggestions?


One more suggestion - you should surely tweak configuration parameters -
for example ramdom read will no more be more expensive than sequential
read. 

I'm not sure if postgresql will automatically compensate for possible
reading of a data page several times when doing index scans so the above
suggestion may not be entirely true.

--------------------
Hannu


06 Mar 2003 23:14:22 +0200
Hannu Krosing <hannu@tm.ee>
Re: Best setup for RAM drive
mlw kirjutas K, 05.03.2003 kell 22:05:
> The idea of a RAM disk based database and reliable storage are in 
> complete opposition. Forget it.

I read from his post that the Platypus RAM disk _is_ his reliable
storage, just with some peculiar characteristics, like big transfer
speeds and uniform super-fast seeks.

> >
> >The Platypus RAM drive is a PCI card with 8GB of ram onboard with an 
> >external power supply so if the main power to the server goes off, the RAM 
> >is still powered, so it's persistent between reboots.

-------------
Hannu


06 Mar 2003 23:16:52 +0200
Hannu Krosing <hannu@tm.ee>
Re: Best setup for RAM drive
The platypus model he has has an external PSU.  The external PSU can be 
easily plugged into an UPS.  Thus if the main system is shut down as long 
as the UPS lives and/or there is power otherwise to the external PSU, the 
card will keep the data.

--On Thursday, March 06, 2003 11:16 PM +0200 Hannu Krosing <hannu@tm.ee> 
wrote:

> mlw kirjutas K, 05.03.2003 kell 22:05:
>> The idea of a RAM disk based database and reliable storage are in
>> complete opposition. Forget it.
>
> I read from his post that the Platypus RAM disk _is_ his reliable
> storage, just with some peculiar characteristics, like big transfer
> speeds and uniform super-fast seeks.
>
>> >
>> > The Platypus RAM drive is a PCI card with 8GB of ram onboard with an
>> > external power supply so if the main power to the server goes off, the
>> > RAM  is still powered, so it's persistent between reboots.
>
> -------------
> Hannu
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html



Thu, 06 Mar 2003 13:50:46 -0800
Michael Loftis <mloftis@wgops.com>
Re: Best setup for RAM drive