Re: Anyone for SSDs?

On Wed, Dec 29, 2010 at 3:34 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Bruce Momjian wrote:
> > Vaibhav Kaushal wrote:
> > > On Fri, 2010-12-10 at 18:07 -0800, Josh Berkus wrote:
> > > > On 12/10/10 5:06 PM, Daniel Loureiro wrote:
> > > > > An quicksort method in
> > > > > sequential disk its just awful to be thinking in a non SSD world,
> but
> > > > > its possible in an SSD.
> > > >
> > > > So, code it. Shouldn't be hard to write a demo comparison. I don't
> > > > believe that SSDs make quicksort-on-disk feasible, but would be happy
> to
> > > > be proven wrong.
> > >
> > > I too do not believe it in normal case. However, considering the
> 'types'
> > > of SSDs, it may be feasible! Asking for 'the next page and getting it'
> > > has a time delay in the process. While on a regular HDD with spindles,
> > > the question is "where is that page located", with SSDs, the question
> > > disappears, because the access time is uniform in case of SSDs. Also,
> > > the access time is about 100 times fasterm which would change quite a
> > > few things about the whole process.
> >
> > What _is_ interesting is that Postgres often has sequential and
> > random/disk ways of doing things, and by reducing random_page_cost when
> > using SSDs, you automatically use more random operations, so in a way
> > the Postgres code was already prepared for SSD usage. Surprisingly, we
> > had to change very little.
>
> To add to this very late reply, we basically had random methods to do
> things (in RAM), and sequential/random methods for disk. By changing
> random_page_cost, we favor doing random things on disk.
>
> The big question is whether there are random things we have never
> implemented on disk that now make sense --- off hand, I can't think of
> any.
>
>
The idea of us avoiding quicksort when we know we need to spill to disk is
the type of thing that I wonder if it should be investigated, if you figure
that "spill to disk" means ssd's so it's not so much of a performance
hit. This reminds me of some performance testing we did maybe a year, year
and a half ago, trying to see how best to get performance by adding some
SSD's into one of our servers. Basically speed increased as we changed
things like so:
put entire $pgdata on sata
put entire $pgdata on ssd
put xlogs on ssd, pgdata on sata
put pgdata and xlogs on sata, put arc on ssd, crank up postgres's memory
settings

arc being zfs's adaptive replacement cache, so basically giving the server a
second, very large level of memory to work with, and then configuring
postgres to make use of it. It wasn't terribly obvious to me why this ended
up outperforming the initial idea of putting everything on ssd, but my
impression was that the more you could force postgres into making decisions
as if it was dealing with fast storage rather than slow storage, the better
off you'd be (and that random_page_cost is not so wholly inclusive enough to
do this for you).

Robert Treat
http://www.xzilla.net