Anyone for SSDs?

On Fri, Dec 10, 2010 at 1:39 AM, Vaibhav Kaushal
<vaibhavkaushal123@gmail.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.

This came suddenly in my mind so I am asking the experts here.

Considering the fact that SSDs will be common (at least for the
enterprise) in the coming years because of (of course you know the
reason) their less seek time and higher transfer rates per second, is
there someone trying for a ssd.c? In almost all cases even using md.c,
the kernel will handle it effectively but would it not be better that we
are well prepared to ask kernel for more?

Or has such an attempt already begun?

Questions about using SSDs with PostgreSQL would be more appropriate
on pgsql-performance, rather than here. If you search, you'll find
that the topic has been covered extensively in the archives.

But as far as the code goes, there doesn't seem to be any reason why
SSDs would require any changes to md.c, or an alternate
implementation. The interface the operating system presents is the
same.

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

On Fri, 2010-12-10 at 07:38 -0500, Robert Haas wrote:

On Fri, Dec 10, 2010 at 1:39 AM, Vaibhav Kaushal
<vaibhavkaushal123@gmail.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.

This came suddenly in my mind so I am asking the experts here.

Considering the fact that SSDs will be common (at least for the
enterprise) in the coming years because of (of course you know the
reason) their less seek time and higher transfer rates per second, is
there someone trying for a ssd.c? In almost all cases even using md.c,
the kernel will handle it effectively but would it not be better that we
are well prepared to ask kernel for more?

Or has such an attempt already begun?

Questions about using SSDs with PostgreSQL would be more appropriate
on pgsql-performance, rather than here. If you search, you'll find
that the topic has been covered extensively in the archives.

But as far as the code goes, there doesn't seem to be any reason why
SSDs would require any changes to md.c, or an alternate
implementation. The interface the operating system presents is the
same.

OK. Thanks a lot. I have not joined that list so I asked it here. :)
Will check that out.

- Vaibhav (*_*)

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Dec 10, 2010 at 1:39 AM, Vaibhav Kaushal
<vaibhavkaushal123@gmail.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.

But as far as the code goes, there doesn't seem to be any reason why
SSDs would require any changes to md.c, or an alternate
implementation. The interface the operating system presents is the
same.

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.

regards, tom lane

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@gmail.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.janes@gmail.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@agliodbs.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@commandprompt.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@gmail.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@gmail.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 (*_*)