pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCompletely un-tuned Postgresql benchmark results: SSD vs desktop HDD
37+ messages / 12 participants
[nested] [flat]
* Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-07 23:47 Michael March <mmarch@gmail.com>
0 siblings, 4 replies; 37+ messages in thread
From: Michael March @ 2010-08-07 23:47 UTC (permalink / raw)
To: pgsql-performance
If anyone is interested I just completed a series of benchmarks of stock
Postgresql running on a normal HDD vs a SSD.
If you don't want to read the post, the summary is that SSDs are 5 to 7
times faster than a 7200RPM HDD drive under a pgbench load.
http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
Is this what everyone else is seeing?
Thanks!
--
mmarch@gmail.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 06:36 Scott Carey <scott@richrelevance.com>
parent: Michael March <mmarch@gmail.com>
3 siblings, 1 reply; 37+ messages in thread
From: Scott Carey @ 2010-08-08 06:36 UTC (permalink / raw)
To: Michael March <mmarch@gmail.com>; +Cc: pgsql-performance
SSD's actually vary quite a bit with typical postgres benchmark workloads. Many of them also do not guarantee data that has been sync'd will not be lost if power fails (most hard drives with a sane OS and file system do).
On Aug 7, 2010, at 4:47 PM, Michael March wrote:
If anyone is interested I just completed a series of benchmarks of stock Postgresql running on a normal HDD vs a SSD.
If you don't want to read the post, the summary is that SSDs are 5 to 7 times faster than a 7200RPM HDD drive under a pgbench load.
http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
Is this what everyone else is seeing?
Thanks!
--
mmarch@gmail.com<mailto:mmarch@gmail.com>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 06:49 Michael March <mmarch@gmail.com>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 2 replies; 37+ messages in thread
From: Michael March @ 2010-08-08 06:49 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: pgsql-performance
> SSD's actually vary quite a bit with typical postgres benchmark workloads.
>
You mean various SSDs from different vendors? Or are you saying the same SSD
model might vary in performance from drive to drive?
> Many of them also do not guarantee data that has been sync'd will not be
> lost if power fails (most hard drives with a sane OS and file system do).
>
What feature does an SSD need to have to insure that sync'd data is indeed
written to the SSD in the case of power loss?
>
>
> On Aug 7, 2010, at 4:47 PM, Michael March wrote:
>
> If anyone is interested I just completed a series of benchmarks of stock
> Postgresql running on a normal HDD vs a SSD.
>
> If you don't want to read the post, the summary is that SSDs are 5 to 7
> times faster than a 7200RPM HDD drive under a pgbench load.
>
>
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
>
> Is this what everyone else is seeing?
>
> Thanks!
>
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 06:55 Scott Marlowe <scott.marlowe@gmail.com>
parent: Michael March <mmarch@gmail.com>
3 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2010-08-08 06:55 UTC (permalink / raw)
To: Michael March <mmarch@gmail.com>; +Cc: pgsql-performance
On Sat, Aug 7, 2010 at 5:47 PM, Michael March <mmarch@gmail.com> wrote:
> If anyone is interested I just completed a series of benchmarks of stock
> Postgresql running on a normal HDD vs a SSD.
> If you don't want to read the post, the summary is that SSDs are 5 to 7
> times faster than a 7200RPM HDD drive under a pgbench load.
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
>
> Is this what everyone else is seeing?
> Thanks!
It's a good first swing, but I'd be interested in seeing how it runs
with various numbers of clients, and how it runs as the number of
clients goes past optimal. I.e. a nice smooth downward trend or a
horrible drop-off for whichever drives.
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 07:03 Michael March <mmarch@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Michael March @ 2010-08-08 07:03 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
>
> On Sat, Aug 7, 2010 at 5:47 PM, Michael March <mmarch@gmail.com> wrote:
> > If anyone is interested I just completed a series of benchmarks of stock
> > Postgresql running on a normal HDD vs a SSD.
> > If you don't want to read the post, the summary is that SSDs are 5 to 7
> > times faster than a 7200RPM HDD drive under a pgbench load.
> >
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
> >
> > Is this what everyone else is seeing?
> > Thanks!
>
> It's a good first swing, but I'd be interested in seeing how it runs
> with various numbers of clients, and how it runs as the number of
> clients goes past optimal. I.e. a nice smooth downward trend or a
> horrible drop-off for whichever drives.
>
>
Yeah. I was thinking the same thing..
I need to automate the tests so I can incrementally increase the scaling of
the seed tables and increase of number simultaneous clients over time. Put
another way,I need to do A LOT more tests that will gently increment all the
testable factors one small step at a time.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 07:30 Scott Marlowe <scott.marlowe@gmail.com>
parent: Michael March <mmarch@gmail.com>
1 sibling, 0 replies; 37+ messages in thread
From: Scott Marlowe @ 2010-08-08 07:30 UTC (permalink / raw)
To: Michael March <mmarch@gmail.com>; +Cc: Scott Carey <scott@richrelevance.com>; pgsql-performance
On Sun, Aug 8, 2010 at 12:49 AM, Michael March <mmarch@gmail.com> wrote:
>
>> SSD's actually vary quite a bit with typical postgres benchmark workloads.
>
> You mean various SSDs from different vendors? Or are you saying the same SSD
> model might vary in performance from drive to drive?
>
>>
>> Many of them also do not guarantee data that has been sync'd will not be
>> lost if power fails (most hard drives with a sane OS and file system do).
>
> What feature does an SSD need to have to insure that sync'd data is indeed
> written to the SSD in the case of power loss?
A big freaking capacitor and the ability to notice power's been cut
and start writing out the cache. There are a few that have it that
are coming out right about now. There was a post about one such drive
a few days ago, it was like 50G and $450 or so, so not cheap, but not
that bad compared to the $7000 drive bays with 16 15k6 drives I've
used in to the past to get good performance (3 to 4k tps)
--
To understand recursion, one must first understand recursion.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-08 10:12 Yeb Havinga <yebhavinga@gmail.com>
parent: Michael March <mmarch@gmail.com>
3 siblings, 0 replies; 37+ messages in thread
From: Yeb Havinga @ 2010-08-08 10:12 UTC (permalink / raw)
To: Michael March <mmarch@gmail.com>; +Cc: pgsql-performance
Michael March wrote:
> If anyone is interested I just completed a series of benchmarks of
> stock Postgresql running on a normal HDD vs a SSD.
>
> If you don't want to read the post, the summary is that SSDs are 5 to
> 7 times faster than a 7200RPM HDD drive under a pgbench load.
>
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
>
> Is this what everyone else is seeing?
I tested a SSD with a capacitor and posted conclusions here
http://archives.postgresql.org/pgsql-performance/2010-07/msg00449.php
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-09 16:49 Scott Carey <scott@richrelevance.com>
parent: Michael March <mmarch@gmail.com>
1 sibling, 2 replies; 37+ messages in thread
From: Scott Carey @ 2010-08-09 16:49 UTC (permalink / raw)
To: Michael March <mmarch@gmail.com>; +Cc: pgsql-performance
On Aug 7, 2010, at 11:49 PM, Michael March wrote:
SSD's actually vary quite a bit with typical postgres benchmark workloads.
You mean various SSDs from different vendors? Or are you saying the same SSD model might vary in performance from drive to drive?
Model to model (more specifically, controller chip to controller chip -- i.e. most 'Indilinx Barefoot' controller based SSD's perform similar).
Many of them also do not guarantee data that has been sync'd will not be lost if power fails (most hard drives with a sane OS and file system do).
What feature does an SSD need to have to insure that sync'd data is indeed written to the SSD in the case of power loss?
Either properly flush to storage when the OS / File sytem asks for it (most SSD's don't, most Hard Drives do), or have a supercapacitor to flush data on power loss.
The former can be achieved by turning off the write cache on some drives (such as Intel's X25-M and -E), but hurts performance.
Also, the amount of data at risk in a power loss varies between drives. For Intel's drives, its a small chunk of data ( < 256K). For some other drives, the cache can be over 30MB of outstanding writes.
For some workloads this is acceptable -- not every application is doing financial transactions. Not every part of the system needs to be on an SSD either -- the WAL, and various table spaces can all have different data integrity and performance requirements.
On Aug 7, 2010, at 4:47 PM, Michael March wrote:
If anyone is interested I just completed a series of benchmarks of stock Postgresql running on a normal HDD vs a SSD.
If you don't want to read the post, the summary is that SSDs are 5 to 7 times faster than a 7200RPM HDD drive under a pgbench load.
http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
Is this what everyone else is seeing?
Thanks!
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-09 22:41 Jeff Davis <pgsql@j-davis.com>
parent: Scott Carey <scott@richrelevance.com>
1 sibling, 1 reply; 37+ messages in thread
From: Jeff Davis @ 2010-08-09 22:41 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Michael March <mmarch@gmail.com>; pgsql-performance
On Mon, 2010-08-09 at 09:49 -0700, Scott Carey wrote:
> Also, the amount of data at risk in a power loss varies between
> drives. For Intel's drives, its a small chunk of data ( < 256K). For
> some other drives, the cache can be over 30MB of outstanding writes.
> For some workloads this is acceptable -- not every application is
> doing financial transactions. Not every part of the system needs to
> be on an SSD either -- the WAL, and various table spaces can all have
> different data integrity and performance requirements.
I don't think it makes sense to speak about the data integrity of a
drive in terms of the amount of data at risk, especially with a DBMS.
Depending on which 256K you lose, you might as well lose your entire
database.
That may be an exaggeration, but the point is that it's not as simple as
"this drive is only risking 256K data loss per outage".
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 16:21 Greg Smith <greg@2ndquadrant.com>
parent: Scott Carey <scott@richrelevance.com>
1 sibling, 2 replies; 37+ messages in thread
From: Greg Smith @ 2010-08-10 16:21 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Michael March <mmarch@gmail.com>; pgsql-performance
Scott Carey wrote:
> Also, the amount of data at risk in a power loss varies between
> drives. For Intel's drives, its a small chunk of data ( < 256K). For
> some other drives, the cache can be over 30MB of outstanding writes.
> For some workloads this is acceptable
No, it isn't ever acceptable. You can expect the type of data loss you
get when a cache fails to honor write flush calls results in
catastrophic database corruption. It's not "I lost the last few
seconds"; it's "the database is corrupted and won't start" after a
crash. This is why we pound on this topic on this list. A SSD that
fails to honor flush requests is completely worthless for anything other
than toy databases. You can expect significant work to recover any
portion of your data after the first unexpected power loss under heavy
write load in this environment, during which you're down. We do
database corruption recovery at 2ndQuadrant; while I can't talk about
the details of some recent incidents, I am not speaking theoretically
when I warn about this.
Michael, I would suggest you read
http://www.postgresql.org/docs/current/static/wal-reliability.html and
link to it at the end of your article. You are recommending that people
consider a configuration that will result in their data being lost.
That can be acceptable, if for example your data is possible to recreate
from backups or the like. But people should be extremely clear that
trade-off is happening, and your blog post is not doing that yet. Part
of the reason for the bang per buck you're seeing here is that cheap
SSDs are cheating.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 16:27 Greg Smith <greg@2ndquadrant.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 0 replies; 37+ messages in thread
From: Greg Smith @ 2010-08-10 16:27 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Jeff Davis wrote:
> Depending on which 256K you lose, you might as well lose your entire
> database.
>
Let's be nice and assume that you only lose one 8K block because of the
SSD write cache; that's not so bad, right? Guess what--you could easily
be the next lucky person who discovers the block corrupted is actually
in the middle of the pg_class system catalog, where the list of tables
in the database is at! Enjoy getting your data back again with that
piece missing. It's really a fun time, I'll tell you that.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 17:57 Brad Nicholson <bnichols@ca.afilias.info>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 2 replies; 37+ messages in thread
From: Brad Nicholson @ 2010-08-10 17:57 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On 8/10/2010 12:21 PM, Greg Smith wrote:
> Scott Carey wrote:
>> Also, the amount of data at risk in a power loss varies between
>> drives. For Intel's drives, its a small chunk of data ( < 256K).
>> For some other drives, the cache can be over 30MB of outstanding writes.
>> For some workloads this is acceptable
>
> No, it isn't ever acceptable. You can expect the type of data loss
> you get when a cache fails to honor write flush calls results in
> catastrophic database corruption. It's not "I lost the last few
> seconds"; it's "the database is corrupted and won't start" after a
> crash. This is why we pound on this topic on this list. A SSD that
> fails to honor flush requests is completely worthless for anything
> other than toy databases. You can expect significant work to recover
> any portion of your data after the first unexpected power loss under
> heavy write load in this environment, during which you're down. We do
> database corruption recovery at 2ndQuadrant; while I can't talk about
> the details of some recent incidents, I am not speaking theoretically
> when I warn about this.
>
What about putting indexes on them? If the drive fails and drops writes
on those, they could be rebuilt - assuming your system can function
without the index(es) temporarily.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:13 Karl Denninger <karl@denninger.net>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 1 reply; 37+ messages in thread
From: Karl Denninger @ 2010-08-10 18:13 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Brad Nicholson wrote:
> On 8/10/2010 12:21 PM, Greg Smith wrote:
>> Scott Carey wrote:
>>> Also, the amount of data at risk in a power loss varies between
>>> drives. For Intel's drives, its a small chunk of data ( < 256K).
>>> For some other drives, the cache can be over 30MB of outstanding
>>> writes.
>>> For some workloads this is acceptable
>>
>> No, it isn't ever acceptable. You can expect the type of data loss
>> you get when a cache fails to honor write flush calls results in
>> catastrophic database corruption. It's not "I lost the last few
>> seconds"; it's "the database is corrupted and won't start" after a
>> crash. This is why we pound on this topic on this list. A SSD that
>> fails to honor flush requests is completely worthless for anything
>> other than toy databases. You can expect significant work to recover
>> any portion of your data after the first unexpected power loss under
>> heavy write load in this environment, during which you're down. We
>> do database corruption recovery at 2ndQuadrant; while I can't talk
>> about the details of some recent incidents, I am not speaking
>> theoretically when I warn about this.
>
> What about putting indexes on them? If the drive fails and drops
> writes on those, they could be rebuilt - assuming your system can
> function without the index(es) temporarily.
You could put indices on them but as noted by Scott, he's SPOT ON.
ANY disk that says "write is complete" when it really is not is entirely
unsuitable for ANY real database use. It is simply a matter of time
before you have a failure of some sort that results in catastrophic data
loss. If you're LUCKY the database won't start and you know you're in
trouble. If you're UNLUCKY the database DOES start but there is
undetected and unrecoverable data corruption somewhere inside the data
tables, which you WILL discover at the most-inopportune moment (like
when you desperately NEED that business record for some reason.) You
cannot put either the tables or the logs on such a drive without running
the risk of a data corruption problem that WILL lose data and MAY be
catastrophic, depending on exactly what fails when.
While it is possible to recover that which is not damaged from a
database that has corruption like this it simply is not possible to
recover data that never made it to the disk - no matter what you do -
and the time and effort expended (not to mention money if you have to
bring in someone with specialized skills you do not possess) that result
from such "decisions" when things go wrong are extreme.
Don't do it.
-- Karl
Attachments:
karl.vcf (text/x-vcard, 122B) download
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:23 Scott Marlowe <scott.marlowe@gmail.com>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2010-08-10 18:23 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger <karl@denninger.net> wrote:
> ANY disk that says "write is complete" when it really is not is entirely
> unsuitable for ANY real database use. It is simply a matter of time
What about read only slaves where there's a master with 100+spinning
hard drives "getting it right" and you need a half dozen or so read
slaves? I can imagine that being ok, as long as you don't restart a
server after a crash without checking on it.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:28 Greg Smith <greg@2ndquadrant.com>
parent: Brad Nicholson <bnichols@ca.afilias.info>
1 sibling, 2 replies; 37+ messages in thread
From: Greg Smith @ 2010-08-10 18:28 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Brad Nicholson wrote:
> What about putting indexes on them? If the drive fails and drops
> writes on those, they could be rebuilt - assuming your system can
> function without the index(es) temporarily.
Dumping indexes on SSD is one of the better uses for them, presuming you
can survive what is likely to be an outage from a "can the site handle
full load?" perspective while they rebuild after a crash. As I'm sure
Brad is painfully aware of already, index rebuilding in PostgreSQL can
take a while. To spin my broken record here again, the main thing to
note when you consider that--relocate indexes onto SSD--is that the ones
you are most concerned about the performance of were likely to be
already sitting in RAM anyway, meaning the SSD speedup doesn't help
reads much. So the giant performance boost just isn't there in that case.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:38 Karl Denninger <karl@denninger.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 37+ messages in thread
From: Karl Denninger @ 2010-08-10 18:38 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Scott Marlowe wrote:
> On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger <karl@denninger.net> wrote:
>
>> ANY disk that says "write is complete" when it really is not is entirely
>> unsuitable for ANY real database use. It is simply a matter of time
>>
>
> What about read only slaves where there's a master with 100+spinning
> hard drives "getting it right" and you need a half dozen or so read
> slaves? I can imagine that being ok, as long as you don't restart a
> server after a crash without checking on it.
>
A read-only slave isn't read-only, is it?
I mean, c'mon - how does the data get there?
IF you mean "a server that only accepts SELECTs, does not accept UPDATEs
or INSERTs, and on a crash **reloads the entire database from the
master**", then ok.
Most people who will do this won't reload it after a crash. They'll
"inspect" the database and say "ok", and put it back online. Bad Karma
will ensue in the future.
Incidentally, that risk is not theoretical either (I know about this one
from hard experience. Fortunately the master was still ok and I was
able to force a full-table copy.... I didn't like it as the database was
a few hundred GB, but I had no choice.)
-- Karl
Attachments:
karl.vcf (text/x-vcard, 122B) download
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:47 Brad Nicholson <bnichols@ca.afilias.info>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 0 replies; 37+ messages in thread
From: Brad Nicholson @ 2010-08-10 18:47 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On 8/10/2010 2:28 PM, Greg Smith wrote:
> Brad Nicholson wrote:
>> What about putting indexes on them? If the drive fails and drops
>> writes on those, they could be rebuilt - assuming your system can
>> function without the index(es) temporarily.
>
> Dumping indexes on SSD is one of the better uses for them, presuming
> you can survive what is likely to be an outage from a "can the site
> handle full load?" perspective while they rebuild after a crash. As
> I'm sure Brad is painfully aware of already, index rebuilding in
> PostgreSQL can take a while. To spin my broken record here again, the
> main thing to note when you consider that--relocate indexes onto
> SSD--is that the ones you are most concerned about the performance of
> were likely to be already sitting in RAM anyway, meaning the SSD
> speedup doesn't help reads much. So the giant performance boost just
> isn't there in that case.
>
The case where I'm thinking they may be of use is for indexes you can
afford to lose. I'm thinking of ones that are needed by nightly batch
jobs, down stream systems or reporting - the sorts of things that you
can turn off during a rebuild, and where the data sets are not likely
to be in cache.
We have a few such cases, but we don't need the speed of SSD's for them.
Personally, I wouldn't entertain any SSD with a capacitor backing it for
anything, even indexes. Not worth the hassle to me.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 18:54 Brad Nicholson <bnichols@ca.afilias.info>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 1 reply; 37+ messages in thread
From: Brad Nicholson @ 2010-08-10 18:54 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On 8/10/2010 2:38 PM, Karl Denninger wrote:
> Scott Marlowe wrote:
>> On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger<karl@denninger.net> wrote:
>>
>>> ANY disk that says "write is complete" when it really is not is entirely
>>> unsuitable for ANY real database use. It is simply a matter of time
>>>
>>
>> What about read only slaves where there's a master with 100+spinning
>> hard drives "getting it right" and you need a half dozen or so read
>> slaves? I can imagine that being ok, as long as you don't restart a
>> server after a crash without checking on it.
>>
> A read-only slave isn't read-only, is it?
>
> I mean, c'mon - how does the data get there?
>
A valid case is a Slony replica if used for query offloading (not for
DR). It's considered a read-only subscriber from the perspective of
Slony as only Slony can modify the data (although you are technically
correct, it is not read only - controlled write may be more accurate).
In case of failure, a rebuild + resubscribe gets you back to the same
consistency. If you have high IO requirements, and don't have the
budget to rack up extra disk arrays to meet them, it could be an option.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 19:28 Karl Denninger <karl@denninger.net>
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 37+ messages in thread
From: Karl Denninger @ 2010-08-10 19:28 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Brad Nicholson wrote:
> On 8/10/2010 2:38 PM, Karl Denninger wrote:
>> Scott Marlowe wrote:
>>> On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger <karl@denninger.net> wrote:
>>>
>>>> ANY disk that says "write is complete" when it really is not is entirely
>>>> unsuitable for ANY real database use. It is simply a matter of time
>>>>
>>>
>>> What about read only slaves where there's a master with 100+spinning
>>> hard drives "getting it right" and you need a half dozen or so read
>>> slaves? I can imagine that being ok, as long as you don't restart a
>>> server after a crash without checking on it.
>>>
>> A read-only slave isn't read-only, is it?
>>
>> I mean, c'mon - how does the data get there?
>>
> A valid case is a Slony replica if used for query offloading (not for
> DR). It's considered a read-only subscriber from the perspective of
> Slony as only Slony can modify the data (although you are technically
> correct, it is not read only - controlled write may be more accurate).
>
> In case of failure, a rebuild + resubscribe gets you back to the same
> consistency. If you have high IO requirements, and don't have the
> budget to rack up extra disk arrays to meet them, it could be an option.
CAREFUL with that model and beliefs.
Specifically, the following will hose you without warning:
1. SLONY gets a change on the master.
2. SLONY commits it to the (read-only) slave.
3. Confirmation comes back to the master that the change was propagated.
4. Slave CRASHES without actually committing the changed data to stable
storage.
When the slave restarts it will not know that the transaction was lost.
Neither will the master, since it was told that it was committed. Slony
will happily go on its way and replicate forward, without any indication
of a problem - except that on the slave, there are one or more
transactions that are **missing**.
Some time later you issue an update that goes to the slave, but the
change previously lost causes the slave commit to violate referential
integrity. SLONY will fail to propagate that change and all behind it
- it effectively locks at that point in time.
You can recover from this by dropping the slave from replication and
re-inserting it, but that forces a full-table copy of everything in the
replication set. The bad news is that the queries to the slave in
question may have been returning erroneous data for some unknown period
of time prior to the lockup in replication (which hopefully you detect
reasonably quickly - you ARE watching SLONY queue depth with some
automated process, right?)
I can both cause this in the lab and have had it happen in the field.
It's a nasty little problem that bit me on a series of disks that
claimed to have write caching off, but in fact did not. I was very
happy that the data on the master was good at that point, as if I had
needed to failover to the slave (thinking it was a "good" copy) I would
have been in SERIOUS trouble.
-- Karl
Attachments:
karl.vcf (text/x-vcard, 122B) download
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 19:51 Brad Nicholson <bnichols@ca.afilias.info>
parent: Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 37+ messages in thread
From: Brad Nicholson @ 2010-08-10 19:51 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On 8/10/2010 3:28 PM, Karl Denninger wrote:
> Brad Nicholson wrote:
>> On 8/10/2010 2:38 PM, Karl Denninger wrote:
>>> Scott Marlowe wrote:
>>>> On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger<karl@denninger.net> wrote:
>>>>
>>>>> ANY disk that says "write is complete" when it really is not is entirely
>>>>> unsuitable for ANY real database use. It is simply a matter of time
>>>>>
>>>>
>>>> What about read only slaves where there's a master with 100+spinning
>>>> hard drives "getting it right" and you need a half dozen or so read
>>>> slaves? I can imagine that being ok, as long as you don't restart a
>>>> server after a crash without checking on it.
>>>>
>>> A read-only slave isn't read-only, is it?
>>>
>>> I mean, c'mon - how does the data get there?
>>>
>> A valid case is a Slony replica if used for query offloading (not for
>> DR). It's considered a read-only subscriber from the perspective of
>> Slony as only Slony can modify the data (although you are
>> technically correct, it is not read only - controlled write may be
>> more accurate).
>>
>> In case of failure, a rebuild + resubscribe gets you back to the
>> same consistency. If you have high IO requirements, and don't have
>> the budget to rack up extra disk arrays to meet them, it could be an
>> option.
> CAREFUL with that model and beliefs.
>
> Specifically, the following will hose you without warning:
>
> 1. SLONY gets a change on the master.
> 2. SLONY commits it to the (read-only) slave.
> 3. Confirmation comes back to the master that the change was propagated.
> 4. Slave CRASHES without actually committing the changed data to
> stable storage.
>
What will hose you is assuming that your data will be okay in the case
of a failure, which is a very bad assumption to make in the case on
unreliable SSD's. You are assuming I am implying that these should be
treated like reliable media - I am not.
In case of failure, you need to assume data loss until proven
otherwise. If there is a problem, rebuild.
> When the slave restarts it will not know that the transaction was
> lost. Neither will the master, since it was told that it was
> committed. Slony will happily go on its way and replicate forward,
> without any indication of a problem - except that on the slave, there
> are one or more transactions that are **missing**.
>
Correct.
> Some time later you issue an update that goes to the slave, but the
> change previously lost causes the slave commit to violate referential
> integrity. SLONY will fail to propagate that change and all behind
> it - it effectively locks at that point in time.
>
It will lock data flow to that subscriber, but not to others.
> You can recover from this by dropping the slave from replication and
> re-inserting it, but that forces a full-table copy of everything in
> the replication set. The bad news is that the queries to the slave in
> question may have been returning erroneous data for some unknown
> period of time prior to the lockup in replication (which hopefully you
> detect reasonably quickly - you ARE watching SLONY queue depth with
> some automated process, right?)
>
There are ways around that - run two subscribers and redirect your
queries on failure. Don't bring up the failed replica until it is
verified or rebuilt.
> I can both cause this in the lab and have had it happen in the field.
> It's a nasty little problem that bit me on a series of disks that
> claimed to have write caching off, but in fact did not. I was very
> happy that the data on the master was good at that point, as if I had
> needed to failover to the slave (thinking it was a "good" copy) I
> would have been in SERIOUS trouble.
It's very easy to cause those sorts of problems.
What I am saying is that the technology can have a use, if you are
aware of the sharp edges, and can both work around them and live with
them. Everything you are citing is correct, but is more implying that
they they are blindly thrown in without understanding the risks and
mitigating them.
I'm also not suggesting that this is a configuration I would endorse,
but it could potentially save a lot of money in certain use cases.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 19:52 Scott Marlowe <scott.marlowe@gmail.com>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 1 reply; 37+ messages in thread
From: Scott Marlowe @ 2010-08-10 19:52 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Greg Smith <greg@2ndquadrant.com>; Scott Carey <scott@richrelevance.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On Tue, Aug 10, 2010 at 12:38 PM, Karl Denninger <karl@denninger.net> wrote:
> Scott Marlowe wrote:
>
> On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger <karl@denninger.net> wrote:
>
>
> ANY disk that says "write is complete" when it really is not is entirely
> unsuitable for ANY real database use. It is simply a matter of time
>
>
> What about read only slaves where there's a master with 100+spinning
> hard drives "getting it right" and you need a half dozen or so read
> slaves? I can imagine that being ok, as long as you don't restart a
> server after a crash without checking on it.
>
>
> A read-only slave isn't read-only, is it?
>
> I mean, c'mon - how does the data get there?
Well, duh. However, what I'm looking at is having two big servers in
failover running on solid reliable hardware, and then a small army of
read only slony slaves that are used for things like sending user rss
feeds and creating weekly reports and such. These 1U machines with 12
to 24 cores and a single SSD drive are "disposable" in terms that if
they ever crash, there's a simple script to run that reinits the db
and then subscribes them to the set.
My point being, no matter how terrible an idea a certain storage media
is, there's always a use case for it. Even if it's very narrow.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 20:00 Christopher Browne <cbbrowne@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 37+ messages in thread
From: Christopher Browne @ 2010-08-10 20:00 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On Tue, Aug 10, 2010 at 3:52 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> My point being, no matter how terrible an idea a certain storage media
> is, there's always a use case for it. Even if it's very narrow.
The trouble is, if extra subscribers induce load on the "master,"
which they presumably will, then that sliver of "use case" may very
well get obscured by the cost, such that the sliver should be treated
as not existing :-(.
--
http://linuxfinances.info/info/linuxdistributions.html
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-10 20:06 Scott Marlowe <scott.marlowe@gmail.com>
parent: Christopher Browne <cbbrowne@gmail.com>
0 siblings, 0 replies; 37+ messages in thread
From: Scott Marlowe @ 2010-08-10 20:06 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@gmail.com>; +Cc: pgsql-performance
On Tue, Aug 10, 2010 at 2:00 PM, Christopher Browne <cbbrowne@gmail.com> wrote:
> On Tue, Aug 10, 2010 at 3:52 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> My point being, no matter how terrible an idea a certain storage media
>> is, there's always a use case for it. Even if it's very narrow.
>
> The trouble is, if extra subscribers induce load on the "master,"
> which they presumably will, then that sliver of "use case" may very
> well get obscured by the cost, such that the sliver should be treated
> as not existing :-(.
One master, one slave, master handles all writes, slave handles all of
the other subscribers. I've run a setup like this with as many as 8
or so slaves at the bottom of the pile with no problems at all.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-11 23:05 Scott Carey <scott@richrelevance.com>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 1 reply; 37+ messages in thread
From: Scott Carey @ 2010-08-11 23:05 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Michael March <mmarch@gmail.com>; pgsql-performance
On Aug 10, 2010, at 9:21 AM, Greg Smith wrote:
> Scott Carey wrote:
>> Also, the amount of data at risk in a power loss varies between
>> drives. For Intel's drives, its a small chunk of data ( < 256K). For
>> some other drives, the cache can be over 30MB of outstanding writes.
>> For some workloads this is acceptable
>
> No, it isn't ever acceptable. You can expect the type of data loss you
> get when a cache fails to honor write flush calls results in
> catastrophic database corruption. It's not "I lost the last few
> seconds";
I never said it was.
> it's "the database is corrupted and won't start" after a
> crash.
Which is sometimes acceptables. There is NO GUARANTEE that you won't lose data, ever. An increase in the likelihood is an acceptable tradeoff in some situations, especially when it is small. On ANY power loss event, with or without battery backed caches and such, you should do a consistency check on the system proactively. With less reliable hardware, that task becomes much more of a burden, and is much more likely to require restoring data from somewhere.
What is the likelihood that your RAID card fails, or that the battery that reported 'good health' only lasts 5 minutes and you lose data before power is restored? What is the likelihood of human error?
Not that far off from the likelihood of power failure in a datacenter with redundant power. One MUST have a DR plan. Never assume that your perfect hardware won't fail.
> This is why we pound on this topic on this list. A SSD that
> fails to honor flush requests is completely worthless for anything other
> than toy databases.
Overblown. Not every DB and use case is a financial application or business critical app. Many are not toys at all. Slave, read only DB's (or simply subset tablespaces) ...
Indexes. (per application, schema)
Tables. (per application, schema)
System tables / indexes.
WAL.
Each has different reliability requirement and consequences from losing recently written data. less than 8K can be fatal to the WAL, or table data. Corrupting some tablespaces is not a big deal. Corrupting others is catastrophic. The problem with the assertion that this hardware is worthless is that it implies that every user, every use case, is at the far end of the reliability requirement spectrum.
Yes, that can be a critical requirement for many, perhaps most, DB's. But there are many uses for slightly unsafe storage systems.
> You can expect significant work to recover any
> portion of your data after the first unexpected power loss under heavy
> write load in this environment, during which you're down. We do
> database corruption recovery at 2ndQuadrant; while I can't talk about
> the details of some recent incidents, I am not speaking theoretically
> when I warn about this.
I've done the single-user mode recover system tables by hand thing myself at 4AM, on a system with battery backed RAID 10, redundant power, etc. Raid cards die, and 10TB recovery times from backup are long.
Its a game of balancing your data loss tolerance with the likelihood of power failure. Both of these variables are highly variable, and not just with 'toy' dbs. If you know what you are doing, you can use 'fast but not completely safe' storage for many things safely. Chance of loss is NEVER zero, do not assume that 'good' hardware is flawless.
Imagine a common internet case where synchronous_commit=false is fine. Recovery from backups is a pain (but a daily snapshot is taken of the important tables, and weekly for easily recoverable other stuff). If you expect one power related failure every 2 years, it might be perfectly reasonable to use 'unsafe' SSD's in order to support high transaction load on the risk that that once every 2 year downtime is 12 hours long instead of 30 minutes, and includes losing up to a day's information. Applications like this exist all over the place.
> --
> Greg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com www.2ndQuadrant.us
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-11 23:10 Scott Carey <scott@richrelevance.com>
parent: Greg Smith <greg@2ndquadrant.com>
1 sibling, 0 replies; 37+ messages in thread
From: Scott Carey @ 2010-08-11 23:10 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; Michael March <mmarch@gmail.com>; pgsql-performance
On Aug 10, 2010, at 11:28 AM, Greg Smith wrote:
> Brad Nicholson wrote:
>> What about putting indexes on them? If the drive fails and drops
>> writes on those, they could be rebuilt - assuming your system can
>> function without the index(es) temporarily.
>
> Dumping indexes on SSD is one of the better uses for them, presuming you
> can survive what is likely to be an outage from a "can the site handle
> full load?" perspective while they rebuild after a crash. As I'm sure
> Brad is painfully aware of already, index rebuilding in PostgreSQL can
> take a while. To spin my broken record here again, the main thing to
> note when you consider that--relocate indexes onto SSD--is that the ones
> you are most concerned about the performance of were likely to be
> already sitting in RAM anyway, meaning the SSD speedup doesn't help
> reads much. So the giant performance boost just isn't there in that case.
>
For an OLTP type system, yeah. But for DW/OLAP and batch processing the gains are pretty big. Those indexes get kicked out of RAM and then pulled back in a lot. I'm talking about a server with 72GB of RAM that can't keep enough indexes in memory to avoid a lot of random access. Putting the indexes on an SSD has lowered the random I/O load on the other drives a lot, letting them get through sequential scans a lot faster.
Estimated power failure, once every 18 months (mostly due to human error). Rebuild indexes offline for 40 minutes every 18 months? No problem.
> --
> Greg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com www.2ndQuadrant.us
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-11 23:52 Scott Carey <scott@richrelevance.com>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 1 reply; 37+ messages in thread
From: Scott Carey @ 2010-08-11 23:52 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Brad Nicholson <bnichols@ca.afilias.info>; Greg Smith <greg@2ndquadrant.com>; Michael March <mmarch@gmail.com>; pgsql-performance
On Aug 10, 2010, at 11:38 AM, Karl Denninger wrote:
Scott Marlowe wrote:
On Tue, Aug 10, 2010 at 12:13 PM, Karl Denninger <karl@denninger.net><mailto:karl@denninger.net> wrote:
ANY disk that says "write is complete" when it really is not is entirely
unsuitable for ANY real database use. It is simply a matter of time
What about read only slaves where there's a master with 100+spinning
hard drives "getting it right" and you need a half dozen or so read
slaves? I can imagine that being ok, as long as you don't restart a
server after a crash without checking on it.
A read-only slave isn't read-only, is it?
I mean, c'mon - how does the data get there?
IF you mean "a server that only accepts SELECTs, does not accept UPDATEs or INSERTs, and on a crash **reloads the entire database from the master**", then ok.
"ENTIRE database"?
Depends on your tablespace setup and schema usage pattern.
If:
* 90% of your data tables are partitioned by date, and untouched a week after insert. Partitions are backed up incrementally.
* The remaining 10% of it is backed up daily, and of that 9% can be re-generated from data elsewhere if data is lost.
* System catalog and wal are on 'safest of safe' hardware.
Then your 'bulk' data on a slave can be on less than flawless hardware. Simply restore the tables from the last week from the master or backup when the (rare) power failure occurs. The remaining data is safe, since it is not written to.
Split up your 10% of non-date partitioned data into what needs to be on safe hardware and what does not (maybe some indexes, etc).
Most of the time, the incremental cost of getting a BBU is too small to not do it, so the above hardly applies. But if you have data that is known to be read-only, you can do many unconventional things with it safely.
Most people who will do this won't reload it after a crash. They'll "inspect" the database and say "ok", and put it back online. Bad Karma will ensue in the future.
Anyone going with something unconventional better know what they are doing and not just blindly plug it in and think everything will be OK. I'd never recommend unconventional setups for a user that wasn't an expert and understood the tradeoff.
Incidentally, that risk is not theoretical either (I know about this one from hard experience. Fortunately the master was still ok and I was able to force a full-table copy.... I didn't like it as the database was a few hundred GB, but I had no choice.)
Been there with 10TB with hardware that should have been perfectly safe. 5 days of copying, and wishing that pg_dump supported lzo compression so that the dump portion had a chance at keeping up with the much faster restore portion with some level of compression on to save the copy bandwidth.
-- Karl
<karl.vcf>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 00:45 Karl Denninger <karl@denninger.net>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 37+ messages in thread
From: Karl Denninger @ 2010-08-12 00:45 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Brad Nicholson <bnichols@ca.afilias.info>; Greg Smith <greg@2ndquadrant.com>; Michael March <mmarch@gmail.com>; pgsql-performance
Scott Carey wrote:
>
> On Aug 10, 2010, at 11:38 AM, Karl Denninger wrote:
>
> .....
>>
>> Most people who will do this won't reload it after a crash. They'll
>> "inspect" the database and say "ok", and put it back online. Bad
>> Karma will ensue in the future.
>
> Anyone going with something unconventional better know what they are
> doing and not just blindly plug it in and think everything will be OK.
> I'd never recommend unconventional setups for a user that wasn't an
> expert and understood the tradeoff.
True.
>>
>> Incidentally, that risk is not theoretical either (I know about this
>> one from hard experience. Fortunately the master was still ok and I
>> was able to force a full-table copy.... I didn't like it as the
>> database was a few hundred GB, but I had no choice.)
>
> Been there with 10TB with hardware that should have been perfectly
> safe. 5 days of copying, and wishing that pg_dump supported lzo
> compression so that the dump portion had a chance at keeping up with
> the much faster restore portion with some level of compression on to
> save the copy bandwidth.
Pipe it through ssh -C
PS: This works for SLONY and Bucardo too - set up a tunnel and then
change the port temporarily. This is especially useful when the DB
being COPY'd across has big fat honking BYTEA fields in it, which
otherwise expand about 400% - or more - on the wire.
-- Karl
Attachments:
karl.vcf (text/x-vcard, 122B) download
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:karl@denninger.net
x-mozilla-html:TRUE
version:2.1
end:vcard
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 00:53 gnuoytr@rcn.com
0 siblings, 2 replies; 37+ messages in thread
From: gnuoytr@rcn.com @ 2010-08-12 00:53 UTC (permalink / raw)
To: pgsql-performance
A number of amusing aspects to this discussion.
- I've carried out similar tests using the Intel X-25M with both PG and DB2 (both on linux). While it is a simple matter to build parallel databases on DB2, on HDD and SSD, with buffers and tablespaces and logging and on and on set to recreate as many scenarios as one wishes using a single engine instance, not so for PG. While PG is the "best" OS database, from a tuning and admin point of view there's rather a long way to go. No one should think that retail SSD should be used to support an enterprise database. People have gotten lulled into thinking otherwise as a result of the blurring of the two use cases in the HDD world where the difference is generally just QA.
- All flash SSD munge the byte stream, some (SandForce controlled in particular) more than others. Industrial strength flash SSD can have 64 internal channels, written in parallel; they don't run on commodity controllers. Treating SSD as just a faster HDD is a trip on the road to perdition. Industrial strength (DRAM) SSDs have been used by serious database folks for a couple of decades, but not the storefront semi-professionals who pervade the web start up world.
- The value of SSD in the database world is not as A Faster HDD(tm). Never was, despite the naive' who assert otherwise. The value of SSD is to enable BCNF datastores. Period. If you're not going to do that, don't bother. Silicon storage will never reach equivalent volumetric density, ever. SSD will never be useful in the byte bloat world of xml and other flat file datastores (resident in databases or not). Industrial strength SSD will always be more expensive/GB, and likely by a lot. (Re)factoring to high normalization strips out an order of magnitude of byte bloat, increases native data integrity by as much, reduces much of the redundant code, and puts the ACID where it belongs. All good things, but not effortless.
You're arguing about the wrong problem. Sufficiently bulletproof flash SSD exist and have for years, but their names are not well known (no one on this thread has named any), but neither the Intel parts nor any of their retail cousins have any place in the mix except development machines. Real SSD have MTBFs measured in decades; OEMs have qualified such parts, but you won't find them on the shelf at Best Buy. You need to concentrate on understanding what can be done with such drives that can't be done with vanilla HDD that cost 1/50 the dollars. Just being faster won't be the answer. Removing the difference between sequential file processing and true random access is what makes SSD worth the bother; makes true relational datastores second nature rather than rocket science.
Robert
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 04:30 Greg Smith <greg@2ndquadrant.com>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 1 reply; 37+ messages in thread
From: Greg Smith @ 2010-08-12 04:30 UTC (permalink / raw)
To: Scott Carey <scott@richrelevance.com>; +Cc: Michael March <mmarch@gmail.com>; pgsql-performance
Scott Carey wrote:
> What is the likelihood that your RAID card fails, or that the battery that reported 'good health' only lasts 5 minutes and you lose data before power is restored? What is the likelihood of human error?
>
These are all things that happen sometimes, sure. The problem with the
cheap SSDs is that they happen downright often if you actually test for
it. If someone is aware of the risk and makes an informed decision,
fine. But most of the time I see articles like the one that started
this thread that are oblivious to the issue, and that's really bad.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 04:49 Greg Smith <greg@2ndquadrant.com>
parent: gnuoytr@rcn.com
1 sibling, 0 replies; 37+ messages in thread
From: Greg Smith @ 2010-08-12 04:49 UTC (permalink / raw)
To: gnuoytr@rcn.com; +Cc: pgsql-performance
gnuoytr@rcn.com wrote:
> Sufficiently bulletproof flash SSD exist and have for years, but their names are not well known (no one on this thread has named any)
The models perceived as bulletproof are the really dangerous ones to
deploy. First, people let their guard down and stop being as paranoid
as they should be when they use them. Second, it becomes much more
difficult for them to justify buying more than one of the uber-SSD.
That combination makes it easier to go back to having a single copy of
their data, and there's a really bad road to wander down.
The whole idea that kicked off this thread was to enable building
systems cheap enough to allow making more inexpensive copies of the
data. My systems at home for example follow this model to some degree.
There's not a single drive more expensive than $100 to be found here,
but everything important to me is sitting on four of them in two systems
within seconds after I save it. However, even here I've found it worth
dropping enough money for a real battery-backed write cache, to reduce
the odds of write corruption on the more important of the servers. Not
doing so would be a dangerously cheap decision. That's similar to how I
feel about SSDs right now too. You need them to be expensive enough
that corruption is unusual rather than expected after a crash--it's
ridiculous to not spend enough to get something that's not completely
broken by design--while not spending so much that you can't afford to
deploy many of them.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 07:22 Arjen van der Meijden <acmmailing@tweakers.net>
parent: gnuoytr@rcn.com
1 sibling, 1 reply; 37+ messages in thread
From: Arjen van der Meijden @ 2010-08-12 07:22 UTC (permalink / raw)
To: pgsql-performance
On 12-8-2010 2:53 gnuoytr@rcn.com wrote:
> - The value of SSD in the database world is not as A Faster HDD(tm).
> Never was, despite the naive' who assert otherwise. The value of SSD
> is to enable BCNF datastores. Period. If you're not going to do
> that, don't bother. Silicon storage will never reach equivalent
> volumetric density, ever. SSD will never be useful in the byte bloat
> world of xml and other flat file datastores (resident in databases or
> not). Industrial strength SSD will always be more expensive/GB, and
> likely by a lot. (Re)factoring to high normalization strips out an
> order of magnitude of byte bloat, increases native data integrity by
> as much, reduces much of the redundant code, and puts the ACID where
> it belongs. All good things, but not effortless.
It is actually quite common to under-utilize (short stroke) hard drives
in the enterprise world. Simply because 'they' need more IOps per amount
of data than a completely utilized disk can offer.
As such the expense/GB can be much higher than simply dividing the
capacity by its price (and if you're looking at fiber channel disks,
that price is quite high already). And than it is relatively easy to
find enterprise SSD's with better pricing for the whole system as soon
as the IOps are more important than the capacity.
So in the current market, you may already be better off, price-wise,
with (expensive) SSD if you need IOps rather than huge amounts of
storage. And while you're in both cases not comparing separate disks to
SSD, you're replacing a 'disk based storage system' with a '(flash)
memory based storage system' and it basically becomes 'A Faster HDD' ;)
But you're right, that for data-heavy applications, completely replacing
HDD's with some form of SSD is not going to happen soon, maybe never.
Best regards,
Arjen
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 12:35 Brad Nicholson <bnichols@ca.afilias.info>
parent: Arjen van der Meijden <acmmailing@tweakers.net>
0 siblings, 1 reply; 37+ messages in thread
From: Brad Nicholson @ 2010-08-12 12:35 UTC (permalink / raw)
To: pgsql-performance
On 10-08-12 03:22 AM, Arjen van der Meijden wrote:
> On 12-8-2010 2:53 gnuoytr@rcn.com wrote:
>> - The value of SSD in the database world is not as A Faster HDD(tm).
>> Never was, despite the naive' who assert otherwise. The value of SSD
>> is to enable BCNF datastores. Period. If you're not going to do
>> that, don't bother. Silicon storage will never reach equivalent
>> volumetric density, ever. SSD will never be useful in the byte bloat
>> world of xml and other flat file datastores (resident in databases or
>> not). Industrial strength SSD will always be more expensive/GB, and
>> likely by a lot. (Re)factoring to high normalization strips out an
>> order of magnitude of byte bloat, increases native data integrity by
>> as much, reduces much of the redundant code, and puts the ACID where
>> it belongs. All good things, but not effortless.
>
> It is actually quite common to under-utilize (short stroke) hard
> drives in the enterprise world. Simply because 'they' need more IOps
> per amount of data than a completely utilized disk can offer.
> As such the expense/GB can be much higher than simply dividing the
> capacity by its price (and if you're looking at fiber channel disks,
> that price is quite high already). And than it is relatively easy to
> find enterprise SSD's with better pricing for the whole system as soon
> as the IOps are more important than the capacity.
And when you compare the ongoing operational costs of rack space,
powering and cooling for big arrays full of spinning disks to flash
based solutions the price comparison evens itself out even more.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-12 23:40 Scott Carey <scott@richrelevance.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 37+ messages in thread
From: Scott Carey @ 2010-08-12 23:40 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Michael March <mmarch@gmail.com>; pgsql-performance
On Aug 11, 2010, at 9:30 PM, Greg Smith wrote:
> Scott Carey wrote:
>> What is the likelihood that your RAID card fails, or that the battery that reported 'good health' only lasts 5 minutes and you lose data before power is restored? What is the likelihood of human error?
>>
>
> These are all things that happen sometimes, sure. The problem with the
> cheap SSDs is that they happen downright often if you actually test for
> it. If someone is aware of the risk and makes an informed decision,
> fine. But most of the time I see articles like the one that started
> this thread that are oblivious to the issue, and that's really bad.
>
Agreed. There is a HUGE gap between "ooh ssd's are fast, look!" and engineering a solution that uses them properly with all their strengths and faults. And as 'gnuoytr' points out, there is a big difference between an Intel SSD and say, this thing: http://www.nimbusdata.com/products/s-class_overview.html
> --
> Greg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> greg@2ndQuadrant.com www.2ndQuadrant.us
>
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-13 05:59 Arjen van der Meijden <acmmailing@tweakers.net>
parent: Scott Carey <scott@richrelevance.com>
0 siblings, 0 replies; 37+ messages in thread
From: Arjen van der Meijden @ 2010-08-13 05:59 UTC (permalink / raw)
To: pgsql-performance
On 13-8-2010 1:40 Scott Carey wrote:
> Agreed. There is a HUGE gap between "ooh ssd's are fast, look!" and
> engineering a solution that uses them properly with all their
> strengths and faults. And as 'gnuoytr' points out, there is a big
> difference between an Intel SSD and say, this thing:
> http://www.nimbusdata.com/products/s-class_overview.html
From the description it sounds as if its either FreeBSD or OpenSolaris
with ZFS with some webinterface-layer. That's not a bad thing per se,
but as the site suggests its 'only' $25k for the smallest (2.5TB?)
device. That makes it very likely that it are "off the shelf" MLC flash
drives. Given the design of the device and the pricing it probably are
your average 2.5"-drives with 100, 200 or 400GB capacity (maybe OCZ
vertex 2 pro, which do have such a capacitor?), similar to the Intel SSD
you compared it to.
And than we're basically back to square one, unless the devices have a
capacitor or ZFS works better with SSD-drives to begin with (it will at
least know silent data corruption did occur).
There are of course devices that are not built on top of normal disk
form factor SSD-drives like the Ramsan devices or Sun's F5100.
Best regards,
Arjen
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-13 06:06 Michael March <mmarch@gmail.com>
parent: Michael March <mmarch@gmail.com>
3 siblings, 0 replies; 37+ messages in thread
From: Michael March @ 2010-08-13 06:06 UTC (permalink / raw)
To: pgsql-performance
As a postscript to these tests.. I just tried the 500GB Monentus XT hybrid
SSD/HDD drive. I had this fantasy that it would at least do better than the
7200 rpm desktop drive.
Oh lord, my gut was wrong. The performance was inconsistent and never over
2/3rds the performance of the slowest desktop drive.
On Sat, Aug 7, 2010 at 4:47 PM, Michael March <mmarch@gmail.com> wrote:
> If anyone is interested I just completed a series of benchmarks of stock
> Postgresql running on a normal HDD vs a SSD.
>
> If you don't want to read the post, the summary is that SSDs are 5 to 7
> times faster than a 7200RPM HDD drive under a pgbench load.
>
>
> http://it-blog.5amsolutions.com/2010/08/performance-of-postgresql-ssd-vs.html
>
> Is this what everyone else is seeing?
>
> Thanks!
>
> --
> mmarch@gmail.com
>
--
<admiral>
Michael F. March ----- mmarch@gmail.com
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-18 06:37 david@lang.hm
parent: Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 37+ messages in thread
From: david@lang.hm @ 2010-08-18 06:37 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-performance
On Thu, 12 Aug 2010, Brad Nicholson wrote:
> On 10-08-12 03:22 AM, Arjen van der Meijden wrote:
>> On 12-8-2010 2:53 gnuoytr@rcn.com wrote:
>>> - The value of SSD in the database world is not as A Faster HDD(tm).
>>> Never was, despite the naive' who assert otherwise. The value of SSD
>>> is to enable BCNF datastores. Period. If you're not going to do
>>> that, don't bother. Silicon storage will never reach equivalent
>>> volumetric density, ever. SSD will never be useful in the byte bloat
>>> world of xml and other flat file datastores (resident in databases or
>>> not). Industrial strength SSD will always be more expensive/GB, and
>>> likely by a lot. (Re)factoring to high normalization strips out an
>>> order of magnitude of byte bloat, increases native data integrity by
>>> as much, reduces much of the redundant code, and puts the ACID where
>>> it belongs. All good things, but not effortless.
>>
>> It is actually quite common to under-utilize (short stroke) hard drives in
>> the enterprise world. Simply because 'they' need more IOps per amount of
>> data than a completely utilized disk can offer.
>> As such the expense/GB can be much higher than simply dividing the capacity
>> by its price (and if you're looking at fiber channel disks, that price is
>> quite high already). And than it is relatively easy to find enterprise
>> SSD's with better pricing for the whole system as soon as the IOps are more
>> important than the capacity.
>
> And when you compare the ongoing operational costs of rack space, powering
> and cooling for big arrays full of spinning disks to flash based solutions
> the price comparison evens itself out even more.
check your SSD specs, some of the high performance ones draw quite a bit
of power.
David Lang
^ permalink raw reply [nested|flat] 37+ messages in thread
* Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
@ 2010-08-18 11:49 gnuoytr@rcn.com
parent: david@lang.hm
0 siblings, 0 replies; 37+ messages in thread
From: gnuoytr@rcn.com @ 2010-08-18 11:49 UTC (permalink / raw)
To: pgsql-performance
If you can cite a specific device that draws more than 10% of the equivalently performing (e.g., short stroked) array, I would be very interested. There may be a DRAM SSD that draws more than a flash SSD, but I'd be really surprised to find a flash SSD that draws the same as any HDD, even at gross capacity.
Robert
---- Original message ----
>Date: Tue, 17 Aug 2010 23:37:26 -0700 (PDT)
>From: pgsql-performance-owner@postgresql.org (on behalf of david@lang.hm)
>Subject: Re: [PERFORM] Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD
>To: Brad Nicholson <bnichols@ca.afilias.info>
>Cc: pgsql-performance@postgresql.org
>
>On Thu, 12 Aug 2010, Brad Nicholson wrote:
>
>> On 10-08-12 03:22 AM, Arjen van der Meijden wrote:
>>> On 12-8-2010 2:53 gnuoytr@rcn.com wrote:
>>>> - The value of SSD in the database world is not as A Faster HDD(tm).
>>>> Never was, despite the naive' who assert otherwise. The value of SSD
>>>> is to enable BCNF datastores. Period. If you're not going to do
>>>> that, don't bother. Silicon storage will never reach equivalent
>>>> volumetric density, ever. SSD will never be useful in the byte bloat
>>>> world of xml and other flat file datastores (resident in databases or
>>>> not). Industrial strength SSD will always be more expensive/GB, and
>>>> likely by a lot. (Re)factoring to high normalization strips out an
>>>> order of magnitude of byte bloat, increases native data integrity by
>>>> as much, reduces much of the redundant code, and puts the ACID where
>>>> it belongs. All good things, but not effortless.
>>>
>>> It is actually quite common to under-utilize (short stroke) hard drives in
>>> the enterprise world. Simply because 'they' need more IOps per amount of
>>> data than a completely utilized disk can offer.
>>> As such the expense/GB can be much higher than simply dividing the capacity
>>> by its price (and if you're looking at fiber channel disks, that price is
>>> quite high already). And than it is relatively easy to find enterprise
>>> SSD's with better pricing for the whole system as soon as the IOps are more
>>> important than the capacity.
>>
>> And when you compare the ongoing operational costs of rack space, powering
>> and cooling for big arrays full of spinning disks to flash based solutions
>> the price comparison evens itself out even more.
>
>check your SSD specs, some of the high performance ones draw quite a bit
>of power.
>
>David Lang
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 37+ messages in thread
end of thread, other threads:[~2010-08-18 11:49 UTC | newest]
Thread overview: 37+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-07 23:47 Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD Michael March <mmarch@gmail.com>
2010-08-08 06:36 ` Scott Carey <scott@richrelevance.com>
2010-08-08 06:49 ` Michael March <mmarch@gmail.com>
2010-08-08 07:30 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-09 16:49 ` Scott Carey <scott@richrelevance.com>
2010-08-09 22:41 ` Jeff Davis <pgsql@j-davis.com>
2010-08-10 16:27 ` Greg Smith <greg@2ndquadrant.com>
2010-08-10 16:21 ` Greg Smith <greg@2ndquadrant.com>
2010-08-10 17:57 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-08-10 18:13 ` Karl Denninger <karl@denninger.net>
2010-08-10 18:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-10 18:38 ` Karl Denninger <karl@denninger.net>
2010-08-10 18:54 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-08-10 19:28 ` Karl Denninger <karl@denninger.net>
2010-08-10 19:51 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-08-10 19:52 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-10 20:00 ` Christopher Browne <cbbrowne@gmail.com>
2010-08-10 20:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-11 23:52 ` Scott Carey <scott@richrelevance.com>
2010-08-12 00:45 ` Karl Denninger <karl@denninger.net>
2010-08-10 18:28 ` Greg Smith <greg@2ndquadrant.com>
2010-08-10 18:47 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-08-11 23:10 ` Scott Carey <scott@richrelevance.com>
2010-08-11 23:05 ` Scott Carey <scott@richrelevance.com>
2010-08-12 04:30 ` Greg Smith <greg@2ndquadrant.com>
2010-08-12 23:40 ` Scott Carey <scott@richrelevance.com>
2010-08-13 05:59 ` Arjen van der Meijden <acmmailing@tweakers.net>
2010-08-08 06:55 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-08 07:03 ` Michael March <mmarch@gmail.com>
2010-08-08 10:12 ` Yeb Havinga <yebhavinga@gmail.com>
2010-08-13 06:06 ` Michael March <mmarch@gmail.com>
2010-08-12 00:53 Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD gnuoytr@rcn.com
2010-08-12 04:49 ` Greg Smith <greg@2ndquadrant.com>
2010-08-12 07:22 ` Arjen van der Meijden <acmmailing@tweakers.net>
2010-08-12 12:35 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-08-18 06:37 ` david@lang.hm
2010-08-18 11:49 ` Re: Completely un-tuned Postgresql benchmark results: SSD vs desktop HDD gnuoytr@rcn.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox