Re: Indexes on RAM disk = insanity?

If I could toss in my $0.02 ... We were provided a 2GB solid-state storage
device for testing purposes. Our initial idea was to put the WAL on it, which
we thought would greatly improve database performance. While it did help a bit,
it wasn't a gigantic performance gain.

What finally became the largest performance gain was to put the actual tables
and indexes on the device. We were using 7.4 at the time, so we symlinked the
files instread of using tablespaces. It was an incredible improvement.

Our only issue was that the database, in its entirety, is about 10 GB. You pay
roughtly $2K/gig for a solid state storage. Solid state storage is fantastic.
No moving parts, MTBF was way way longer than a mechanical drive, and it has
battery backup and a tiny mechanical drive to off-load the data onto in case of
a power failure.

It is a whole world of difference in stability compared to a ramdisk. Its
preformance is quite comparible. It is extremely expensive to impliment on a
large scale, so I'd spend my time trying to find out what's causing your
swap-outs. If indexes in RAM is your final solution, I'd consider the solid
state drive as your responsible solution... :)

CG

--- Steve Lane <slane(at)soliantconsulting(dot)com> wrote:

> We're going to build a new server with a bit more RAM -- 8 gigs.
>
> I know memory is somehow the bottleneck, but I don't think it's because I
> have too little. Performance monitors show a lot of swap activity during
> heavy query load testing, but available RAM is never maxed out, or even
> close. SOMETHING is starved for RAM, I just don't know what.
>
> We are running a web app here and have Apache and PG on the same box. I
> have PG buffers set very high, about 100K. Data set approaches 3 gigs.
>
> My gut feeling is I have RAM to spare, I'm just somehow not efficiently
> using what I have.
>
> I just wanted a gut-check reaction to this idea, and I don't hear anyone
> saying "NO! NEVER!". What I'm hearing is that it might, or might not, be
> practical, feasible, maintainable.
>
> Thanks for the replies. I'll look more at the memory usage and see what
> comes up.
>
> -- sgl
>
>
> > From: Chris Travers <chris(at)travelamericas(dot)com>
> > Date: Fri, 29 Jul 2005 09:50:07 -0700
> > To: Steve Lane <slane(at)soliantconsulting(dot)com>
> > Cc: <pgsql-admin(at)postgresql(dot)org>
> > Subject: Re: [ADMIN] Indexes on RAM disk = insanity?
> >
> > Steve Lane wrote:
> >
> >> All:
> >>
> >> We have a postgres 7.4 server where we're trying to achieve some speedups.
> >> Right now, at least superficially, RAM appears to be the bottleneck --
> lots
> >> of swaps in and out.
> >>
> >> There is another consultant beside myself in the mix and he asked this
> >> question: can we put the database indexes on a RAM disk? Won't that speed
> >> things up?
> >>
> >>
> > I am quite wary about putting the indexes on a RAM DISK for another reason.
> >
> > You say that RAM is your bottleneck, so putting anything unnecessary in
> > RAM seems like a good way to get less performance rather than more.
> > Additionally you have some overhead in tracking the files, etc. and they
> > will remain in RAM even when they are not used. This leaves the system
> > with less effective RAM for the memory intensive operations.
> >
> > Such a move might make a lot of sense if you had a LOT of RAM but disk
> > I/O was the bottleneck. However, if a lack of RAM is your problem,
> > putting more stuff in RAM doesn't seem very sound to me.
> >
> > Best Wishes,
> > Chris Travers
> > Metatron Technology Consulting
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com