Thread: 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
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
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
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) >
Chris, > 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). This is the drawback to RAM drives, period. If there was some way to use a RAM drive without risking your data, everyone would do it (or at least everyone who can afford it). I'd reccommend backing up your database every 15 or 30 minutes. Then when a power-out happens, you lose at most 25-45 minutes worth of data. PG_Dump is very fast -- if your database is on a RAM drive and being backed up to a second server over fast ethernet, I wouldn't be surprised if it takes no more than 2-3 minutes to back up. If this is a high-transaction database (where INSERTS and UPDATES are constant, like 600/minute), then putting the WAL files (the pg_xlog) directory on the RAM drive would speed up transaction processing considerably, as much as 2x. However, this still puts you at risk of database corruption in the event of an unxepected power-out. Mostly, you just need a really good UPS strategy ... ideally, this machine should be attached to a UPS giving it at least 30 minutes of live time in the event of a power-out, and software on the machine that will flush the RAM drive to a special partition before shutting down. -- Josh Berkus Aglio Database Solutions San Francisco
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) > > >
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
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
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