Re: Anyone for SSDs?

>> Most of you already know I am new to this list and newer to any OSS
>> development. However, while browsing the source code (of 9.0.1) I find
>> that there is only one way to store relations on disk - the magnetic
>> disk.

>The fact that it's called md.c is a hangover from the '80s. These days,
>the logic that the Berkeley guys envisioned being at that code level
>is generally in kernel device drivers. md.c can drive anything that
>behaves as a block device + filesystem, which is pretty much everything
>of interest.

I believe that PostgreSQL was been developed and optimized for
sequential access. To get full advantage of SSDs its necessary to
rewrite almost the whole project - there are so much code written with
the sequential mechanism in mind.

--
Daniel Loureiro

On Fri, Dec 10, 2010 at 12:21 PM, Daniel Loureiro <loureirorg(at)gmail(dot)com> wrote:
>>> Most of you already know I am new to this list and newer to any OSS
>>> development. However, while browsing the source code (of 9.0.1) I find
>>> that there is only one way to store relations on disk - the magnetic
>>> disk.
>
>>The fact that it's called md.c is a hangover from the '80s.  These days,
>>the logic that the Berkeley guys envisioned being at that code level
>>is generally in kernel device drivers.  md.c can drive anything that
>>behaves as a block device + filesystem, which is pretty much everything
>>of interest.
>
> I believe that PostgreSQL was been developed and optimized for
> sequential access. To get full advantage of SSDs its necessary to
> rewrite almost the whole project - there are so much code written with
> the sequential mechanism in mind.

I don't think that that is true at all. If you tell the planner that
a random page and a sequential page have the same cost, does it not
believe you?

Of course if you do a full table scan because their are no better
options, then it scans sequentially. But you have to scan the pages
in *some* order, and it is hard to see how something other than
sequential would be systematically better.

Cheers,

Jeff

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> Of course if you do a full table scan because their are no better
> options, then it scans sequentially. But you have to scan the pages
> in *some* order, and it is hard to see how something other than
> sequential would be systematically better.

In fact, if sequential *isn't* the best order for reading the whole
file, the filesystem has lost its marbles completely; because that is
the order in which most files are read, so files ought to be laid out
on disk (or whatever storage device) to be read most quickly that way.

regards, tom lane

> I believe that PostgreSQL was been developed and optimized for
> sequential access. To get full advantage of SSDs its necessary to
> rewrite almost the whole project - there are so much code written with
> the sequential mechanism in mind.

You can believe whatever you want, that doesn't make it true.

Unless you have some kind of hard data that SSD data access is somehow
*qualitatively* different from SAS data access, then you're just
engaging in idle water-cooler speculation.

Plenty of vendors launched products based on the supposed
"revolutionary" nature of SSDs when they first came out. All have
failed. SSDs are just faster disks, that's all. Their ratio of
random-access to sequential might be less than 4.0, but it's not 1.0.

Heck, even RAM isn't 1.0. I'm also involved with the Redis project,
which is an in-memory database. Even for a pure-RAM database, it turns
out that just using linked lists and 100% random access is slower than
accessing page images.

I use SSDs for many PostgreSQL instances. They work great. No changes
to PostgreSQL were required other than adjusting random_page_cost down
to 2.0 (this number could use exhaustive testing, but seems to work
pretty well right now).

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, 2010-12-10 at 15:08 -0800, Josh Berkus wrote:
> > I believe that PostgreSQL was been developed and optimized for
> > sequential access. To get full advantage of SSDs its necessary to
> > rewrite almost the whole project - there are so much code written with
> > the sequential mechanism in mind.
>
> You can believe whatever you want, that doesn't make it true.

Or more productively.

Actually, the only (that I know of) optimized for sequential access code
we have would be for the xlogs. All of the page writing within the
cluster would be random, as would all logging outside of the WAL itself.

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

On Fri, Dec 10, 2010 at 6:08 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Heck, even RAM isn't 1.0.  I'm also involved with the Redis project,
> which is an in-memory database.  Even for a pure-RAM database, it turns
> out that just using linked lists and 100% random access is slower than
> accessing page images.

That's a slightly different problem, though. Sequential vs. random
access is about whether fetching pages n, n+1, n+2, ... is faster than
skipping around, not whether accessing fewer pages is faster than
more.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>> Heck, even RAM isn't 1.0. I'm also involved with the Redis project,
>> which is an in-memory database. Even for a pure-RAM database, it turns
>> out that just using linked lists and 100% random access is slower than
>> accessing page images.
>
> That's a slightly different problem, though. Sequential vs. random
> access is about whether fetching pages n, n+1, n+2, ... is faster than
> skipping around, not whether accessing fewer pages is faster than
> more.

It's not though. Redis stores stuff as lists and sets, so it actually
does a lot of sequential access of data. Like if people are accessing
an ordered set, they're usually pulling the whole thing. It turns out
that *even in RAM* storing stuff in an ordered fashion on data "pages"
is more efficient than just using pointers.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Fri, Dec 10, 2010 at 3:13 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> Actually, the only (that I know of) optimized for sequential access code
> we have would be for the xlogs.

And even that is more of a book-keeping simplification, rather than an
optimization.

You have to know where to find the logically next (in a PG sense)
record. If the logically next record is
not right after (in a file system sense) the previous record, then
where is it and how do you find it?

If you really wanted to make it non-sequential, you could, with a
substantial amount of work. But why
would you want to? On spinning rust, you might want to try
leap-frogging the platter, but that is
never going to be generalizable to different work-loads, much less
different hardware.

Cheers,

Jeff

On 10.12.2010 21:21, Daniel Loureiro wrote:
>
>> The fact that it's called md.c is a hangover from the '80s. These days,
>> the logic that the Berkeley guys envisioned being at that code level
>> is generally in kernel device drivers. md.c can drive anything that
>> behaves as a block device + filesystem, which is pretty much everything
>> of interest.
> I believe that PostgreSQL was been developed and optimized for
> sequential access. To get full advantage of SSDs its necessary to
> rewrite almost the whole project - there are so much code written with
> the sequential mechanism in mind.
Nope, as a matter of fact postgreSQL was developed as a university
project with flexibility and extensibility among top goals.
Yes, "magnetic disk" is the only storage manager left in current code
base, but the original design had more, most notably the WORM (Write
Once Read Many) disks, one of the uses being for the old design of
VACUUM which did not throw away deleted rows but moved them to WORM
disks for historical queries. The WORM disks were the "next big thing in
storage" a few tens of years ago.

And as Josh Berkus notes in another replay, nowadays even RAM is not
neutral to access patterns - pipeline stalls and cache flushes can have
impact of several orders of magnitude on execution speeds.

----------------------
Hannu Krosing
PostgreSQL Infinite Scalability and High Availability
http://www.2ndquadrant.com/books/

> You can believe whatever you want, that doesn't make it true.
completely agree. Like yours, Its just my point of view, not the reality.

I agree with most points here, but I wondering how many good ideas are
killed with the thought: "this will be a performance killer with so
many random access, lets discarded it". If in 80's the sequential
access has more cost compared with random access (ok, there's not the
SSD case), will be the PostgreSQL in the same design that it have
nowadays ?

--
Daniel Loureiro.

> You can believe whatever you want, that doesn't make it true.
completely agree. Like yours, Its just my point of view, not the reality.

I agree with some points here, but I wondering how many good ideas are
killed with the thought: "this will be a performance killer with so
many random access, lets discarded it". An quicksort method in
sequential disk its just awful to be thinking in a non SSD world, but
its possible in an SSD.

If in 80's the sequential access has more cost compared with random
access will be the PostgreSQL in the same design that it have nowadays
?

--
Daniel Loureiro

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.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

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.

I would like to do that (coding), but I do not have a SSD on my
machine! :( Would it be impractical to try it for me? Again I do not
know how to test PG :(

May be some of those I meet on the chat, and are into the enterprise may
do it, but I would like to be a part of it.

-Vaibhav (*_*)

* Vaibhav Kaushal (vaibhavkaushal123(at)gmail(dot)com) wrote:
> I would like to do that (coding), but I do not have a SSD on my
> machine! :( Would it be impractical to try it for me? Again I do not
> know how to test PG :(

No, it's not a trivial amount of work. Perhaps someone will be curious
enough to try it, but I wouldn't count on it.

Stephen

On Fri, 2010-12-10 at 23:19 -0500, Stephen Frost wrote:
> * Vaibhav Kaushal (vaibhavkaushal123(at)gmail(dot)com) wrote:
> > I would like to do that (coding), but I do not have a SSD on my
> > machine! :( Would it be impractical to try it for me? Again I do not
> > know how to test PG :(
>
> No, it's not a trivial amount of work. Perhaps someone will be curious
> enough to try it, but I wouldn't count on it.
>
> Stephen
Well, thanks for the word.

-Vaibhav (^_^)

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.

I would like to do that, but I do not have a SSD on my machine! :( Would
it be impractical to try it for me?

May be some of those I meet on the chat, and are into the enterprise may
do it, but I would like to be a part of it.

-Vaibhav (*_*)

On Fri, Dec 10, 2010 at 8:09 PM, Vaibhav Kaushal
<vaibhavkaushal123(at)gmail(dot)com> 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.

I don't understand what it is you are proposing. Quicksort is usually
swap based, and so the records would need to be the same size. Are
you proposing to do an in-memory sort of pointers, which reference
on-disk records? Or an on-disk sort of pointers which reference
on-disk (but somewhere else) records? If you are swapping pointers on
disk, you have to consider the write performance, not just the read
performance.

> I would like to do that (coding), but I do not have a SSD on my
> machine!

That doesn't mean you can't do the coding, it just means you can't
test the performance.
The barrier to get someone else to performance test it for you is a
lot lower than the
barrier to get someone else to write it for you and then performance
test it for you.
(But I can't test-drive it, as I don't have any computer which has
both an SSD and
a hard drive, just one or the other. And the one with SSD would be
hard to compile
PG on.)

> :( Would it be impractical to try it for me? Again I do not
> know how to test PG :(

Yeah, I think it would be impractical. If I thought it would likely
work, it would be different (at least, it might be if I had the right
hardware).
But I think it would likely not work.

Cheers,

Jeff

loureirorg(at)gmail(dot)com (Daniel Loureiro) writes:
>> You can believe whatever you want, that doesn't make it true.
> completely agree. Like yours, Its just my point of view, not the reality.
>
> I agree with some points here, but I wondering how many good ideas are
> killed with the thought: "this will be a performance killer with so
> many random access, lets discarded it". An quicksort method in
> sequential disk its just awful to be thinking in a non SSD world, but
> its possible in an SSD.
>
> If in 80's the sequential access has more cost compared with random
> access will be the PostgreSQL in the same design that it have nowadays
> ?

What turns out to be surprising is that the behaviours of new kinds of
media not infrequently retrieve the usefulness of algorithms designed
for elderly sorts of media.

The entertaining one, on Postgres, has been that the behaviour of fairly
large amounts of memory, as compared to the slower access rates for disk
has led to retrieving tape-oriented algorithms from
thought-to-be-obsolete literature.

I don't think it's too likely that SSD changes this.

But what is rather interesting is that the issue *isn't* one of how fast
it is to sort things on disk - it is of how to sort in memory.

It's quite feasible to load blocks of data into memory, sort in memory,
via [some means], and then do tape merges to get the fully ordered
result that is then sequentially written out to disk.

Replacing [some means] with Quicksort is a plausible idea. I doubt it'd
be an improvement on what is already there, but there's already room for
it to work.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
Outside of a dog, a book is man's best friend. Inside of a dog, it's
too dark to read. -Groucho Marx

Tom Lane wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > Of course if you do a full table scan because their are no better
> > options, then it scans sequentially. But you have to scan the pages
> > in *some* order, and it is hard to see how something other than
> > sequential would be systematically better.
>
> In fact, if sequential *isn't* the best order for reading the whole
> file, the filesystem has lost its marbles completely; because that is
> the order in which most files are read, so files ought to be laid out
> on disk (or whatever storage device) to be read most quickly that way.

Plus kernel read-ahead helps with sequential access too because the
kernel can guess the next blocks to be requested --- hard to do that
with random I/O. SSD have fast access but still benefit from
read-ahead.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

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

Robert Treat wrote:
> > > 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

You mean using quicksort from an (SSD) disk vs. tape sorts --- good
point.

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

Yes, I wonder if this requires futher investigation.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +