Thread: RAMFS with Postgres
Hi, I am trying RAMFS solution with Postgres wherein I am pushing the most heavily used tables in RAM. I have 32GB RAM on a 64 bit opteron machine. My database size is 40GB. I think Linux allows max. of 16GB (half of available RAM) to be used directly to push tables to it. I am concerned about reliabilty here (what if there is a power failure). What are the things that need to be considered and what all can be done to ensure that there is no data loss in case something goes wrong. What steps must be taken to ensure data recovery. I am planning to use Slony replication to replicate my database to a diff node so that incase something goes wrong, I can restore it from replication node and start my runs on that data again. The only problem here is that I need to run engines from beginning. Is there any other way of doing the same thing or such a thing is good enough given the fact that a failure like this happens very rarely. The most imp. thing for me is the **data** which should not be lost under any circumstances. Has anyone used Slony replication before. How good is it. Is there anything else available which is better then Slony Replication? Regards, Vinita Bansal _________________________________________________________________ Meet interesting singles like you http://match.msn.co.in/match/mt.cfm?pg=channel&tcid=234764 Sign up with Match.com
On Tue, 2005-07-19 at 16:45 +0000, vinita bansal wrote:
> Hi,
>
> I am trying RAMFS solution with Postgres wherein I am pushing the most
> heavily used tables in RAM.
Why? I mean, what problem are you trying to solve?
> I have 32GB RAM on a 64 bit opteron machine. My database size is 40GB. I
> think Linux allows max. of 16GB (half of available RAM) to be used directly
> to push tables to it.
>
> I am concerned about reliabilty here (what if there is a power failure).
> What are the things that need to be considered and what all can be done to
> ensure that there is no data loss in case something goes wrong. What steps
> must be taken to ensure data recovery. I am planning to use Slony
> replication to replicate my database to a diff node so that incase something
> goes wrong, I can restore it from replication node and start my runs on that
> data again. The only problem here is that I need to run engines from
> beginning. Is there any other way of doing the same thing or such a thing is
> good enough given the fact that a failure like this happens very rarely. The
> most imp. thing for me is the **data** which should not be lost under any
> circumstances.
Then don't use RAMFS. Slony may be a good idea, but it's hard to tell if
you don't provide more info.
What is the database used for?
- heavy long running, CPU-based, read only queries?
- many simple queries but over the whole dataset (thus I/O based)?
- many INSERTs/UPDATEs?
Is the database accessed by many concurrent users? How many of them are
mostly read-only and how many perform writes?
Each problem in each scenario may have a different solution...
> Has anyone used Slony replication before. How good is it. Is there anything
> else available which is better then Slony Replication?
"better" is meaningless w/o a context. There are tasks in which Slony
may the best tool in the world, and others that require a totally
different approach. First you have to define what your problem is, and
why the obvious solution (a normal PostGreSQL server, with a standard
filesystem) does not work/fit. Then you choose a solution.
>
> Regards,
> Vinita Bansal
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
Hi, My application is database intensive. I am using 4 processes since I have 4 processeors on my box. There are times when all the 4 processes write to the database at the same time and times when all of them will read all at once. The database is definitely not read only. Out of the entire database, there are a few tables which are accessed most of the times and they are the ones which seem to be the bottleneck. I am trying to get as much performance improvement as possible by putting some of these tables in RAM so that they dont have to be read to/written from hard disk as they will be directly available in RAM. Here's where slony comes into picture, since we'll have to mainatin a copy of the database somewhere before running our application (everything in RAM will be lost if there's a power failure or anything else goes wrong). My concern is how good Slony is? How much time does it take to replicate database? If the time taken to replicate is much more then the perf. improvement we are getting by putting tables in memory, then there's no point in going in for such a solution. Do I have an alternative? Regards, Vinita Bansal >From: Marco Colombo <pgsql@esiway.net> >To: vinita bansal <sagivini@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: RAMFS with Postgres >Date: Wed, 20 Jul 2005 12:08:49 +0200 > >On Tue, 2005-07-19 at 16:45 +0000, vinita bansal wrote: > > Hi, > > > > I am trying RAMFS solution with Postgres wherein I am pushing the most > > heavily used tables in RAM. > >Why? I mean, what problem are you trying to solve? > > > I have 32GB RAM on a 64 bit opteron machine. My database size is 40GB. I > > think Linux allows max. of 16GB (half of available RAM) to be used >directly > > to push tables to it. > > > > I am concerned about reliabilty here (what if there is a power failure). > > What are the things that need to be considered and what all can be done >to > > ensure that there is no data loss in case something goes wrong. What >steps > > must be taken to ensure data recovery. I am planning to use Slony > > replication to replicate my database to a diff node so that incase >something > > goes wrong, I can restore it from replication node and start my runs on >that > > data again. The only problem here is that I need to run engines from > > beginning. Is there any other way of doing the same thing or such a >thing is > > good enough given the fact that a failure like this happens very rarely. >The > > most imp. thing for me is the **data** which should not be lost under >any > > circumstances. > >Then don't use RAMFS. Slony may be a good idea, but it's hard to tell if >you don't provide more info. > >What is the database used for? >- heavy long running, CPU-based, read only queries? >- many simple queries but over the whole dataset (thus I/O based)? >- many INSERTs/UPDATEs? > >Is the database accessed by many concurrent users? How many of them are >mostly read-only and how many perform writes? > >Each problem in each scenario may have a different solution... > > > Has anyone used Slony replication before. How good is it. Is there >anything > > else available which is better then Slony Replication? > >"better" is meaningless w/o a context. There are tasks in which Slony >may the best tool in the world, and others that require a totally >different approach. First you have to define what your problem is, and >why the obvious solution (a normal PostGreSQL server, with a standard >filesystem) does not work/fit. Then you choose a solution. > > > > > Regards, > > Vinita Bansal > >.TM. >-- > ____/ ____/ / > / / / Marco Colombo > ___/ ___ / / Technical Manager > / / / ESI s.r.l. > _____/ _____/ _/ Colombo@ESI.it > _________________________________________________________________ Logon to MSN Games http://www.msngamez.com/in/gamezone/ Enjoy unlimited action
On Thu, 2005-07-21 at 07:43 +0000, vinita bansal wrote:
> Hi,
>
> My application is database intensive. I am using 4 processes since I have 4
> processeors on my box. There are times when all the 4 processes write to the
> database at the same time and times when all of them will read all at once.
> The database is definitely not read only. Out of the entire database, there
> are a few tables which are accessed most of the times and they are the ones
> which seem to be the bottleneck. I am trying to get as much performance
> improvement as possible by putting some of these tables in RAM so that they
> dont have to be read to/written from hard disk as they will be directly
> available in RAM. Here's where slony comes into picture, since we'll have to
> mainatin a copy of the database somewhere before running our application
> (everything in RAM will be lost if there's a power failure or anything else
> goes wrong).
>
> My concern is how good Slony is?
> How much time does it take to replicate database? If the time taken to
> replicate is much more then the perf. improvement we are getting by putting
> tables in memory, then there's no point in going in for such a solution. Do
> I have an alternative?
>
> Regards,
> Vinita Bansal
You see, if those frequently accessed tables are read-only mostly,
there's no need at all to use RAMFS. They already are cached for sure in
either PostgreSQL buffers or the underlying OS page cache. If you don't
have enough RAM for that, increase it. Using RAMFS does only make things
worse. The OS page cache really knows about frequently accessed data,
usually much better than you do.
If there are frequent writes on those tables, still RAMFS is not the
answer. Have a look at the documentation and disable sync on writes, and
you'll get a similar effect (writes are in RAM and will be synced on
disk in blocks, much more efficiently). Of course you loose the safety
of data this way, in case of power failure, but it's still much better
than RAMFS, at least most of the data is on disk.
As for Slony, or other solutions, consider this: the _only_ way to have
data safety is to return 'OK' to the application only after you're
_sure_ about the fact the data is on some disk platter. So, even with a
replica server, data has to be transferred over the net, committed
remotely, the commit notification has to come back over the net, and
_then_ the database says 'OK' to the application. This is going to be
quite slow, possibly slower than synchronous writing on the local disks
(notice that the protocol is the same: send data to the disk, wait for a
write completed notification, say 'OK' to the application).
My advice is: tune you PostgreSQL, the best you can, _with_ sync write
enabled. Review and optimize your SQL. Do not use RAMFS. Upgrade your
hardware if that's not enough. Consider distributing the load on
different servers (you'll need a multi-master solution for that, search
the archives), that is, upgrade your hardware in number not in size.
I hope it helps,
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
On Thu, 2005-07-21 at 02:43, vinita bansal wrote: > Hi, > > My application is database intensive. I am using 4 processes since I have 4 > processeors on my box. There are times when all the 4 processes write to the > database at the same time and times when all of them will read all at once. > The database is definitely not read only. Out of the entire database, there > are a few tables which are accessed most of the times and they are the ones > which seem to be the bottleneck. I am trying to get as much performance > improvement as possible by putting some of these tables in RAM so that they > dont have to be read to/written from hard disk as they will be directly > available in RAM. Here's where slony comes into picture, since we'll have to > mainatin a copy of the database somewhere before running our application > (everything in RAM will be lost if there's a power failure or anything else > goes wrong). > > My concern is how good Slony is? > How much time does it take to replicate database? If the time taken to > replicate is much more then the perf. improvement we are getting by putting > tables in memory, then there's no point in going in for such a solution. Do > I have an alternative? My feeling is that you may be going about this the wrong way. Most likely the issue so far has been I/O contention. Have you tested your application using a fast, battery backed caching RAID controller on top of, say, a 10 disk RAID 1+0 array? Or even RAID 0 with another machine as the slony slave? Slony, by the way, is quite capable, but using a RAMFS master and a Disk drive based slave is kind of a recipe for disaster in ANY replication system under heavy load, since it is quite possible that the master could get very far ahead of the slave, since Slony is asynchronous replication. At some point you could have more data waiting to be replicated than your ramfs can hold and have some problems. If a built in RAID controller with battery backed caching isn't enough, you might want to look at a large, external storage array then. many hosting centers offer these as a standard part of their package, so rather than buying one, you might want to just rent one, so to speak.
On 21 Jul 2005, at 17:02, Scott Marlowe wrote: > On Thu, 2005-07-21 at 02:43, vinita bansal wrote: > >> Hi, >> >> My application is database intensive. I am using 4 processes since >> I have 4 >> processeors on my box. There are times when all the 4 processes >> write to the >> database at the same time and times when all of them will read all >> at once. >> The database is definitely not read only. Out of the entire >> database, there >> are a few tables which are accessed most of the times and they are >> the ones >> which seem to be the bottleneck. I am trying to get as much >> performance >> improvement as possible by putting some of these tables in RAM so >> that they >> dont have to be read to/written from hard disk as they will be >> directly >> available in RAM. Here's where slony comes into picture, since >> we'll have to >> mainatin a copy of the database somewhere before running our >> application >> (everything in RAM will be lost if there's a power failure or >> anything else >> goes wrong). >> >> My concern is how good Slony is? >> How much time does it take to replicate database? If the time >> taken to >> replicate is much more then the perf. improvement we are getting >> by putting >> tables in memory, then there's no point in going in for such a >> solution. Do >> I have an alternative? >> > > My feeling is that you may be going about this the wrong way. Most > likely the issue so far has been I/O contention. Have you tested your > application using a fast, battery backed caching RAID controller on > top > of, say, a 10 disk RAID 1+0 array? Or even RAID 0 with another > machine > as the slony slave? Isn't that slightly cost prohibitive? Even basic memory has enormously fast access/throughput these days, and for a fraction of the price. > Slony, by the way, is quite capable, but using a RAMFS master and a > Disk > drive based slave is kind of a recipe for disaster in ANY replication > system under heavy load, since it is quite possible that the master > could get very far ahead of the slave, since Slony is asynchronous > replication. At some point you could have more data waiting to be > replicated than your ramfs can hold and have some problems. > > If a built in RAID controller with battery backed caching isn't > enough, > you might want to look at a large, external storage array then. many > hosting centers offer these as a standard part of their package, so > rather than buying one, you might want to just rent one, so to speak. Again with the *money* RAM = Cheap. Disks = Expensive. At least when you look at speed/$. Your right about replicating to disk and to ram though, that is pretty likely to result in horrible problems if you don't keep load down. For some workloads though, I can see it working. As long as the total amount of data doesn't get larger than your RAMFS it could probably survive. > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
On Fri, 2005-07-22 at 09:56, Alex Stapleton wrote: > On 21 Jul 2005, at 17:02, Scott Marlowe wrote: > > > > My feeling is that you may be going about this the wrong way. Most > > likely the issue so far has been I/O contention. Have you tested your > > application using a fast, battery backed caching RAID controller on > > top > > of, say, a 10 disk RAID 1+0 array? Or even RAID 0 with another > > machine > > as the slony slave? > > Isn't that slightly cost prohibitive? Even basic memory has > enormously fast access/throughput these days, and for a fraction of > the price. I don't know. How expensive is your data? If you lose it all, is saving a few grand on hardware worth it? And I'm not being sarcastic. Some data isn't really worth getting too worked up over, some data is a big deal. Choose two: Fast, Cheap, Reliable. Plus, this really depends on your storage requirements. If you only need a couple of gigabytes, then you don't need that big of drives, because you'll be using a fair number of them. Let's say you pick up 10 9 gig Ultra SCSI drives off ebay, using 8 in a RAID1+0 with two spares. The going price for used 9 gig drives is about $20.00 or so. A good used RAID card with battery backed cache can be had on ebay for <$300, sometimes around $100 if you shop around. So, that's an 8 disk RAID 1+0, with two spares, and a controller, for about $500 or so. And, while it should have performance similar to what you're wanting to do with the ramfs setup, it should be able to survive someone tripping over the power cable with no loss of data. (I'd test it to be sure, not all RAID controllers / hard drive combos tell the truth about caching and fsync.) > > Slony, by the way, is quite capable, but using a RAMFS master and a > > Disk > > drive based slave is kind of a recipe for disaster in ANY replication > > system under heavy load, since it is quite possible that the master > > could get very far ahead of the slave, since Slony is asynchronous > > replication. At some point you could have more data waiting to be > > replicated than your ramfs can hold and have some problems. > > > > If a built in RAID controller with battery backed caching isn't > > enough, > > you might want to look at a large, external storage array then. many > > hosting centers offer these as a standard part of their package, so > > rather than buying one, you might want to just rent one, so to speak. > > Again with the *money* RAM = Cheap. Disks = Expensive. At least when > you look at speed/$. Your right about replicating to disk and to ram > though, that is pretty likely to result in horrible problems if you > don't keep load down. For some workloads though, I can see it > working. As long as the total amount of data doesn't get larger than > your RAMFS it could probably survive. Right, for certain bursty situations it would work just fine. For something that just kept tossing huge amounts of data at the system it could turn ugly fast.
Quoth alexs@advfn.com (Alex Stapleton):
> On 21 Jul 2005, at 17:02, Scott Marlowe wrote:
>
>> On Thu, 2005-07-21 at 02:43, vinita bansal wrote:
>>
>>> Hi,
>>>
>>> My application is database intensive. I am using 4 processes since
>>> I have 4
>>> processeors on my box. There are times when all the 4 processes
>>> write to the
>>> database at the same time and times when all of them will read all
>>> at once.
>>> The database is definitely not read only. Out of the entire
>>> database, there
>>> are a few tables which are accessed most of the times and they are
>>> the ones
>>> which seem to be the bottleneck. I am trying to get as much
>>> performance
>>> improvement as possible by putting some of these tables in RAM so
>>> that they
>>> dont have to be read to/written from hard disk as they will be
>>> directly
>>> available in RAM. Here's where slony comes into picture, since
>>> we'll have to
>>> mainatin a copy of the database somewhere before running our
>>> application
>>> (everything in RAM will be lost if there's a power failure or
>>> anything else
>>> goes wrong).
>>>
>>> My concern is how good Slony is?
>>> How much time does it take to replicate database? If the time
>>> taken to
>>> replicate is much more then the perf. improvement we are getting
>>> by putting
>>> tables in memory, then there's no point in going in for such a
>>> solution. Do
>>> I have an alternative?
>>>
>>
>> My feeling is that you may be going about this the wrong way. Most
>> likely the issue so far has been I/O contention. Have you tested
>> your application using a fast, battery backed caching RAID
>> controller on top of, say, a 10 disk RAID 1+0 array? Or even RAID
>> 0 with another machine as the slony slave?
>
> Isn't that slightly cost prohibitive? Even basic memory has
> enormously fast access/throughput these days, and for a fraction of
> the price.
Actually, the real question is whether or not *data loss* is "cost
prohibitive."
If you can accept significant risk of data loss, then there are plenty
of optimizations available.
If the cost of data loss is high enough, then building some form of
disk array is likely to be the answer.
No other answer than "beefing up disk" will speed things up without
introducing much greater risks of data loss.
>> Slony, by the way, is quite capable, but using a RAMFS master and a
>> Disk drive based slave is kind of a recipe for disaster in ANY
>> replication system under heavy load, since it is quite possible
>> that the master could get very far ahead of the slave, since Slony
>> is asynchronous replication. At some point you could have more
>> data waiting to be replicated than your ramfs can hold and have
>> some problems.
>>
>> If a built in RAID controller with battery backed caching isn't
>> enough, you might want to look at a large, external storage array
>> then. many hosting centers offer these as a standard part of their
>> package, so rather than buying one, you might want to just rent
>> one, so to speak.
>
> Again with the *money* RAM = Cheap. Disks = Expensive. At least when
> you look at speed/$. Your right about replicating to disk and to ram
> though, that is pretty likely to result in horrible problems if you
> don't keep load down. For some workloads though, I can see it
> working. As long as the total amount of data doesn't get larger than
> your RAMFS it could probably survive.
Memory does *zero* to improve the speed of committing transactions
onto disk, and therefore every dollar spent on memory is
counterproductive to that purpose.
More disks can (in some sense) help achieve the goal of committing
more transactions in less time, and is therefore a potentially useful
strategy for increasing transactions per second.
The fact that RAM might be pretty cheap doesn't mean it helps commit
transactions to disk faster, and is therefore an entire red herring.
--
output = ("cbbrowne" "@" "gmail.com")
http://cbbrowne.com/info/linuxdistributions.html
The Dalai Lama walks up to a hot dog vendor and says, "Make me one
with everything."
On Fri, 2005-07-22 at 15:56 +0100, Alex Stapleton wrote:
> On 21 Jul 2005, at 17:02, Scott Marlowe wrote:
>
> > On Thu, 2005-07-21 at 02:43, vinita bansal wrote:
> >
> >> Hi,
> >>
> >> My application is database intensive. I am using 4 processes since
> >> I have 4
> >> processeors on my box. There are times when all the 4 processes
> >> write to the
> >> database at the same time and times when all of them will read all
> >> at once.
> >> The database is definitely not read only. Out of the entire
> >> database, there
> >> are a few tables which are accessed most of the times and they are
> >> the ones
> >> which seem to be the bottleneck. I am trying to get as much
> >> performance
> >> improvement as possible by putting some of these tables in RAM so
> >> that they
> >> dont have to be read to/written from hard disk as they will be
> >> directly
> >> available in RAM. Here's where slony comes into picture, since
> >> we'll have to
> >> mainatin a copy of the database somewhere before running our
> >> application
> >> (everything in RAM will be lost if there's a power failure or
> >> anything else
> >> goes wrong).
> >>
> >> My concern is how good Slony is?
> >> How much time does it take to replicate database? If the time
> >> taken to
> >> replicate is much more then the perf. improvement we are getting
> >> by putting
> >> tables in memory, then there's no point in going in for such a
> >> solution. Do
> >> I have an alternative?
> >>
> >
> > My feeling is that you may be going about this the wrong way. Most
> > likely the issue so far has been I/O contention. Have you tested your
> > application using a fast, battery backed caching RAID controller on
> > top
> > of, say, a 10 disk RAID 1+0 array? Or even RAID 0 with another
> > machine
> > as the slony slave?
>
> Isn't that slightly cost prohibitive? Even basic memory has
> enormously fast access/throughput these days, and for a fraction of
> the price.
We are comparing a RAM + network solution vs. a RAM + disk solution. RAM
alone in not enough, since the OP wants 100% safety of data. Then you
need a network solution, and it has to be synchronous if you want 100%
safety. No network is going to beat a directly attached disk array on
the basis of performance/price.
> > Slony, by the way, is quite capable, but using a RAMFS master and a
> > Disk
> > drive based slave is kind of a recipe for disaster in ANY replication
> > system under heavy load, since it is quite possible that the master
> > could get very far ahead of the slave, since Slony is asynchronous
> > replication. At some point you could have more data waiting to be
> > replicated than your ramfs can hold and have some problems.
> >
> > If a built in RAID controller with battery backed caching isn't
> > enough,
> > you might want to look at a large, external storage array then. many
> > hosting centers offer these as a standard part of their package, so
> > rather than buying one, you might want to just rent one, so to speak.
>
> Again with the *money* RAM = Cheap. Disks = Expensive. At least when
> you look at speed/$. Your right about replicating to disk and to ram
> though, that is pretty likely to result in horrible problems if you
> don't keep load down. For some workloads though, I can see it
> working. As long as the total amount of data doesn't get larger than
> your RAMFS it could probably survive.
Ever heard of the page cache? If your data fits your RAMFS, it would fit
the OS cache just the same. For reads, the effect is exactly the same.
And just disable fsync if writes are a problem. It's anyway safer than
RAMFS, even if not 100% safe.
Face it, if you want 100% safety (loosing nothing in case of power
failure), you need to synchronously write to _some_ disk platter. Where
this disk is attached to, it's a matter of convenience. _If_ disk write
throughput _is_ the problem, you have to fix it. Be it on the local
host, or on a remote replica server, the disk system has to be fast
enough.
Consider:
1) PostgreSQL -> RAM -> disk
2) PostgreSQL -> RAM -> network ----------------> network -> RAM -> disk
no matter if you choose 1) or 2), the "disk" part has to be fast enough.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it