pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLatest advice on SSD?
13+ messages / 10 participants
[nested] [flat]
* Latest advice on SSD?
@ 2018-04-10 02:36 Craig James <cjames@emolecules.com>
2018-04-10 17:41 ` Re: Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Re: Latest advice on SSD? Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 22:56 ` Re: Latest advice on SSD? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-11 01:39 ` Re: Latest advice on SSD? Matthew Hall <mhall@mhcomputing.net>
0 siblings, 4 replies; 13+ messages in thread
From: Craig James @ 2018-04-10 02:36 UTC (permalink / raw)
To: pgsql-performance
One of our four "big iron" (spinning disks) servers went belly up today.
(Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to
a cloud service at the end of the year, so bad timing on this. We didn't
want to buy any more hardware, but now it looks like we have to.
I followed the discussions about SSD drives when they were first becoming
mainstream; at that time, the Intel devices were king. Can anyone recommend
what's a good SSD configuration these days? I don't think we want to buy a
new server with spinning disks.
We're replacing:
8 core (Intel)
48GB memory
12-drive 7200 RPM 500GB
RAID1 (2 disks, OS and WAL log)
RAID10 (8 disks, postgres data dir)
2 spares
Ubuntu 16.04
Postgres 9.6
The current system peaks at about 7000 TPS from pgbench.
Our system is a mix of non-transactional searching (customers) and
transactional data loading (us).
Thanks!
Craig
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
@ 2018-04-10 17:41 ` Craig James <cjames@emolecules.com>
3 siblings, 0 replies; 13+ messages in thread
From: Craig James @ 2018-04-10 17:41 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Apr 10, 2018 at 12:21 AM, Andreas Joseph Krogh <andreas@visena.com>
wrote:
> På tirsdag 10. april 2018 kl. 04:36:27, skrev Craig James <
> cjames@emolecules.com>:
>
> One of our four "big iron" (spinning disks) servers went belly up today.
> (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to
> a cloud service at the end of the year, so bad timing on this. We didn't
> want to buy any more hardware, but now it looks like we have to.
>
> I followed the discussions about SSD drives when they were first becoming
> mainstream; at that time, the Intel devices were king. Can anyone recommend
> what's a good SSD configuration these days? I don't think we want to buy a
> new server with spinning disks.
>
> We're replacing:
> 8 core (Intel)
> 48GB memory
> 12-drive 7200 RPM 500GB
> RAID1 (2 disks, OS and WAL log)
> RAID10 (8 disks, postgres data dir)
> 2 spares
> Ubuntu 16.04
> Postgres 9.6
>
> The current system peaks at about 7000 TPS from pgbench.
>
>
> With what arguments (also initialization)?
>
pgbench -i -s 100 -U test
pgbench -U test -c ... -t ...
-c -t TPS
5 20000 5202
10 10000 7916
20 5000 7924
30 3333 7270
40 2500 5020
50 2000 6417
>
> --
> Andreas Joseph Krogh
>
>
--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
@ 2018-04-10 17:54 ` Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 19:00 ` Re: Latest advice on SSD? Aaron <aaron.werman@gmail.com>
3 siblings, 1 reply; 13+ messages in thread
From: Benjamin Scherrey @ 2018-04-10 17:54 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
You don't mention the size of your database. Does it fit in memory? If so
your disks aren't going to matter a whole lot outside of potentially being
i/o bound on the writes. Otherwise getting your data into SSDs absolutely
can have a few multiples of performance impact. The NVME M.2 drives can
really pump out the data. Maybe push your WAL onto those (as few
motherboards have more than two connectors) and use regular SSDs for your
data if you have high write rates.
Meanwhile, if you're looking for strong cloud hosting for Postgres but the
speed of physical hardware, feel free to contact me as my company does this
for some companies who found i/o limits on regular cloud providers to be
way too slow for their needs.
good luck (and pardon the crass commercial comments!),
-- Ben Scherrey
On Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@emolecules.com> wrote:
> One of our four "big iron" (spinning disks) servers went belly up today.
> (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to
> a cloud service at the end of the year, so bad timing on this. We didn't
> want to buy any more hardware, but now it looks like we have to.
>
> I followed the discussions about SSD drives when they were first becoming
> mainstream; at that time, the Intel devices were king. Can anyone recommend
> what's a good SSD configuration these days? I don't think we want to buy a
> new server with spinning disks.
>
> We're replacing:
> 8 core (Intel)
> 48GB memory
> 12-drive 7200 RPM 500GB
> RAID1 (2 disks, OS and WAL log)
> RAID10 (8 disks, postgres data dir)
> 2 spares
> Ubuntu 16.04
> Postgres 9.6
>
> The current system peaks at about 7000 TPS from pgbench.
>
> Our system is a mix of non-transactional searching (customers) and
> transactional data loading (us).
>
> Thanks!
> Craig
>
> --
> ---------------------------------
> Craig A. James
> Chief Technology Officer
> eMolecules, Inc.
> ---------------------------------
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Re: Latest advice on SSD? Benjamin Scherrey <scherrey@proteus-tech.com>
@ 2018-04-10 19:00 ` Aaron <aaron.werman@gmail.com>
2018-04-10 19:11 ` Re: Latest advice on SSD? Dmitry Shalashov <skaurus@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron @ 2018-04-10 19:00 UTC (permalink / raw)
To: Benjamin Scherrey <scherrey@proteus-tech.com>; +Cc: Craig James <cjames@emolecules.com>; pgsql-performance
RDBMS such as pg are beasts that turn random IO requests, traditionally slow in spinning drives, into sequential. WAL is a good example of this.
SSDs are generally slower than spinning at sequential IO and way faster at random.
Expect therefore for SSD to help if you are random IO bound. (Some cloud vendors offer SSD as a way to get dedicated local io and bandwidth - so sometimes it helps stablize performance vs. virtualized shared io.)
A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN
/Aaron
> On Apr 10, 2018, at 12:54 PM, Benjamin Scherrey <scherrey@proteus-tech.com> wrote:
>
> You don't mention the size of your database. Does it fit in memory? If so your disks aren't going to matter a whole lot outside of potentially being i/o bound on the writes. Otherwise getting your data into SSDs absolutely can have a few multiples of performance impact. The NVME M.2 drives can really pump out the data. Maybe push your WAL onto those (as few motherboards have more than two connectors) and use regular SSDs for your data if you have high write rates.
>
> Meanwhile, if you're looking for strong cloud hosting for Postgres but the speed of physical hardware, feel free to contact me as my company does this for some companies who found i/o limits on regular cloud providers to be way too slow for their needs.
>
> good luck (and pardon the crass commercial comments!),
>
> -- Ben Scherrey
>
>> On Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@emolecules.com> wrote:
>> One of our four "big iron" (spinning disks) servers went belly up today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to a cloud service at the end of the year, so bad timing on this. We didn't want to buy any more hardware, but now it looks like we have to.
>>
>> I followed the discussions about SSD drives when they were first becoming mainstream; at that time, the Intel devices were king. Can anyone recommend what's a good SSD configuration these days? I don't think we want to buy a new server with spinning disks.
>>
>> We're replacing:
>> 8 core (Intel)
>> 48GB memory
>> 12-drive 7200 RPM 500GB
>> RAID1 (2 disks, OS and WAL log)
>> RAID10 (8 disks, postgres data dir)
>> 2 spares
>> Ubuntu 16.04
>> Postgres 9.6
>>
>> The current system peaks at about 7000 TPS from pgbench.
>>
>> Our system is a mix of non-transactional searching (customers) and transactional data loading (us).
>>
>> Thanks!
>> Craig
>>
>> --
>> ---------------------------------
>> Craig A. James
>> Chief Technology Officer
>> eMolecules, Inc.
>> ---------------------------------
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Re: Latest advice on SSD? Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 19:00 ` Re: Latest advice on SSD? Aaron <aaron.werman@gmail.com>
@ 2018-04-10 19:11 ` Dmitry Shalashov <skaurus@gmail.com>
2018-04-10 19:58 ` Re: Latest advice on SSD? Charles Sprickman <css@morefoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Dmitry Shalashov @ 2018-04-10 19:11 UTC (permalink / raw)
To: Aaron <aaron.werman@gmail.com>; +Cc: Benjamin Scherrey <scherrey@proteus-tech.com>; Craig James <cjames@emolecules.com>; pgsql-performance
> SSDs are generally slower than spinning at sequential IO and way faster
at random.
Unreleased yet Seagate HDD boasts 480MB/s sequential read speed [1], and no
HDD now can achieve that.
Even SATA-3 SSD's could be faster than that for years now (550MB/s are
quite typical), and NVME ones could be easily faster than 1GB/s and up to
3GB/s+.
I'm curious to know where are you drawing these conclusions from?
1. https://blog.seagate.com/enterprises/mach2-and-hamr-breakthrough-ocp/
Dmitry Shalashov, relap.io & surfingbird.ru
2018-04-10 22:00 GMT+03:00 Aaron <aaron.werman@gmail.com>:
> RDBMS such as pg are beasts that turn random IO requests, traditionally
> slow in spinning drives, into sequential. WAL is a good example of this.
>
> SSDs are generally slower than spinning at sequential IO and way faster at
> random.
>
> Expect therefore for SSD to help if you are random IO bound. (Some cloud
> vendors offer SSD as a way to get dedicated local io and bandwidth - so
> sometimes it helps stablize performance vs. virtualized shared io.)
>
> A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED
> MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN
>
> /Aaron
>
>
> On Apr 10, 2018, at 12:54 PM, Benjamin Scherrey <scherrey@proteus-tech.com>
> wrote:
>
> You don't mention the size of your database. Does it fit in memory? If so
> your disks aren't going to matter a whole lot outside of potentially being
> i/o bound on the writes. Otherwise getting your data into SSDs absolutely
> can have a few multiples of performance impact. The NVME M.2 drives can
> really pump out the data. Maybe push your WAL onto those (as few
> motherboards have more than two connectors) and use regular SSDs for your
> data if you have high write rates.
>
> Meanwhile, if you're looking for strong cloud hosting for Postgres but the
> speed of physical hardware, feel free to contact me as my company does this
> for some companies who found i/o limits on regular cloud providers to be
> way too slow for their needs.
>
> good luck (and pardon the crass commercial comments!),
>
> -- Ben Scherrey
>
> On Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@emolecules.com>
> wrote:
>
>> One of our four "big iron" (spinning disks) servers went belly up today.
>> (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to
>> a cloud service at the end of the year, so bad timing on this. We didn't
>> want to buy any more hardware, but now it looks like we have to.
>>
>> I followed the discussions about SSD drives when they were first becoming
>> mainstream; at that time, the Intel devices were king. Can anyone recommend
>> what's a good SSD configuration these days? I don't think we want to buy a
>> new server with spinning disks.
>>
>> We're replacing:
>> 8 core (Intel)
>> 48GB memory
>> 12-drive 7200 RPM 500GB
>> RAID1 (2 disks, OS and WAL log)
>> RAID10 (8 disks, postgres data dir)
>> 2 spares
>> Ubuntu 16.04
>> Postgres 9.6
>>
>> The current system peaks at about 7000 TPS from pgbench.
>>
>> Our system is a mix of non-transactional searching (customers) and
>> transactional data loading (us).
>>
>> Thanks!
>> Craig
>>
>> --
>> ---------------------------------
>> Craig A. James
>> Chief Technology Officer
>> eMolecules, Inc.
>> ---------------------------------
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Re: Latest advice on SSD? Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 19:00 ` Re: Latest advice on SSD? Aaron <aaron.werman@gmail.com>
2018-04-10 19:11 ` Re: Latest advice on SSD? Dmitry Shalashov <skaurus@gmail.com>
@ 2018-04-10 19:58 ` Charles Sprickman <css@morefoo.com>
2018-04-10 20:15 ` Re: Latest advice on SSD? Frits Jalvingh <jal@etc.to>
0 siblings, 1 reply; 13+ messages in thread
From: Charles Sprickman @ 2018-04-10 19:58 UTC (permalink / raw)
To: Dmitry Shalashov <skaurus@gmail.com>; +Cc: Aaron <aaron.werman@gmail.com>; Benjamin Scherrey <scherrey@proteus-tech.com>; Craig James <cjames@emolecules.com>; pgsql-performance
> On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:
>
> > SSDs are generally slower than spinning at sequential IO and way faster at random.
>
> Unreleased yet Seagate HDD boasts 480MB/s sequential read speed [1], and no HDD now can achieve that.
> Even SATA-3 SSD's could be faster than that for years now (550MB/s are quite typical), and NVME ones could be easily faster than 1GB/s and up to 3GB/s+.
>
> I'm curious to know where are you drawing these conclusions from?
Yeah, that sequential info sounds weird.
I’m only chiming in because I just setup one of those SoHo NAS boxes (Qnap) and it had both SSDs and HDDs installed. This was to be used for video editing, so it’s almost all sequential reads/writes. On 10Gb/s ethernet sequential reads off the cached content on the SSDs was somewhere around 800MB/s. These were non-enterprise SSDs.
Charles
>
> 1. https://blog.seagate.com/enterprises/mach2-and-hamr-breakthrough-ocp/ <https://blog.seagate.com/enterprises/mach2-and-hamr-breakthrough-ocp/;
>
>
> Dmitry Shalashov, relap.io <http://relap.io/; & surfingbird.ru <http://surfingbird.ru/;
> 2018-04-10 22:00 GMT+03:00 Aaron <aaron.werman@gmail.com <mailto:aaron.werman@gmail.com>>:
> RDBMS such as pg are beasts that turn random IO requests, traditionally slow in spinning drives, into sequential. WAL is a good example of this.
>
> SSDs are generally slower than spinning at sequential IO and way faster at random.
>
> Expect therefore for SSD to help if you are random IO bound. (Some cloud vendors offer SSD as a way to get dedicated local io and bandwidth - so sometimes it helps stablize performance vs. virtualized shared io.)
>
> A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN
>
> /Aaron
>
>
> On Apr 10, 2018, at 12:54 PM, Benjamin Scherrey <scherrey@proteus-tech.com <mailto:scherrey@proteus-tech.com>> wrote:
>
>> You don't mention the size of your database. Does it fit in memory? If so your disks aren't going to matter a whole lot outside of potentially being i/o bound on the writes. Otherwise getting your data into SSDs absolutely can have a few multiples of performance impact. The NVME M.2 drives can really pump out the data. Maybe push your WAL onto those (as few motherboards have more than two connectors) and use regular SSDs for your data if you have high write rates.
>>
>> Meanwhile, if you're looking for strong cloud hosting for Postgres but the speed of physical hardware, feel free to contact me as my company does this for some companies who found i/o limits on regular cloud providers to be way too slow for their needs.
>>
>> good luck (and pardon the crass commercial comments!),
>>
>> -- Ben Scherrey
>>
>> On Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@emolecules.com <mailto:cjames@emolecules.com>> wrote:
>> One of our four "big iron" (spinning disks) servers went belly up today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to a cloud service at the end of the year, so bad timing on this. We didn't want to buy any more hardware, but now it looks like we have to.
>>
>> I followed the discussions about SSD drives when they were first becoming mainstream; at that time, the Intel devices were king. Can anyone recommend what's a good SSD configuration these days? I don't think we want to buy a new server with spinning disks.
>>
>> We're replacing:
>> 8 core (Intel)
>> 48GB memory
>> 12-drive 7200 RPM 500GB
>> RAID1 (2 disks, OS and WAL log)
>> RAID10 (8 disks, postgres data dir)
>> 2 spares
>> Ubuntu 16.04
>> Postgres 9.6
>>
>> The current system peaks at about 7000 TPS from pgbench.
>>
>> Our system is a mix of non-transactional searching (customers) and transactional data loading (us).
>>
>> Thanks!
>> Craig
>>
>> --
>> ---------------------------------
>> Craig A. James
>> Chief Technology Officer
>> eMolecules, Inc.
>> ---------------------------------
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Re: Latest advice on SSD? Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 19:00 ` Re: Latest advice on SSD? Aaron <aaron.werman@gmail.com>
2018-04-10 19:11 ` Re: Latest advice on SSD? Dmitry Shalashov <skaurus@gmail.com>
2018-04-10 19:58 ` Re: Latest advice on SSD? Charles Sprickman <css@morefoo.com>
@ 2018-04-10 20:15 ` Frits Jalvingh <jal@etc.to>
0 siblings, 0 replies; 13+ messages in thread
From: Frits Jalvingh @ 2018-04-10 20:15 UTC (permalink / raw)
To: Charles Sprickman <css@morefoo.com>; +Cc: Dmitry Shalashov <skaurus@gmail.com>; Aaron <aaron.werman@gmail.com>; Benjamin Scherrey <scherrey@proteus-tech.com>; Craig James <cjames@emolecules.com>; pgsql-performance
Well, I can give a measurement on my home PC, a Linux box running Ubuntu
17.10 with a Samsung 960 EVO 512GB NVME disk containing Postgres 10. Using
your pgbench init I got for example:
pgbench -c 10 -t 10000 test
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
latency average = 0.679 ms
tps = 14730.402329 (including connections establishing)
tps = 14733.000950 (excluding connections establishing)
I will try to run a test on our production system which has a pair of Intel
DC P4600 2TB in RAID0 tomorrow.
On Tue, Apr 10, 2018 at 9:58 PM Charles Sprickman <css@morefoo.com> wrote:
> On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:
>
> > SSDs are generally slower than spinning at sequential IO and way faster
> at random.
>
> Unreleased yet Seagate HDD boasts 480MB/s sequential read speed [1], and
> no HDD now can achieve that.
> Even SATA-3 SSD's could be faster than that for years now (550MB/s are
> quite typical), and NVME ones could be easily faster than 1GB/s and up to
> 3GB/s+.
>
> I'm curious to know where are you drawing these conclusions from?
>
>
> Yeah, that sequential info sounds weird.
>
> I’m only chiming in because I just setup one of those SoHo NAS boxes
> (Qnap) and it had both SSDs and HDDs installed. This was to be used for
> video editing, so it’s almost all sequential reads/writes. On 10Gb/s
> ethernet sequential reads off the cached content on the SSDs was somewhere
> around 800MB/s. These were non-enterprise SSDs.
>
> Charles
>
>
> 1. https://blog.seagate.com/enterprises/mach2-and-hamr-breakthrough-ocp/
>
>
> Dmitry Shalashov, relap.io & surfingbird.ru
>
> 2018-04-10 22:00 GMT+03:00 Aaron <aaron.werman@gmail.com>:
>
>> RDBMS such as pg are beasts that turn random IO requests, traditionally
>> slow in spinning drives, into sequential. WAL is a good example of this.
>>
>> SSDs are generally slower than spinning at sequential IO and way faster
>> at random.
>>
>> Expect therefore for SSD to help if you are random IO bound. (Some cloud
>> vendors offer SSD as a way to get dedicated local io and bandwidth - so
>> sometimes it helps stablize performance vs. virtualized shared io.)
>>
>> A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED
>> MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN
>>
>> /Aaron
>>
>>
>> On Apr 10, 2018, at 12:54 PM, Benjamin Scherrey <
>> scherrey@proteus-tech.com> wrote:
>>
>> You don't mention the size of your database. Does it fit in memory? If so
>> your disks aren't going to matter a whole lot outside of potentially being
>> i/o bound on the writes. Otherwise getting your data into SSDs absolutely
>> can have a few multiples of performance impact. The NVME M.2 drives can
>> really pump out the data. Maybe push your WAL onto those (as few
>> motherboards have more than two connectors) and use regular SSDs for your
>> data if you have high write rates.
>>
>> Meanwhile, if you're looking for strong cloud hosting for Postgres but
>> the speed of physical hardware, feel free to contact me as my company does
>> this for some companies who found i/o limits on regular cloud providers to
>> be way too slow for their needs.
>>
>> good luck (and pardon the crass commercial comments!),
>>
>> -- Ben Scherrey
>>
>> On Tue, Apr 10, 2018 at 9:36 AM, Craig James <cjames@emolecules.com>
>> wrote:
>>
>>> One of our four "big iron" (spinning disks) servers went belly up today.
>>> (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to
>>> a cloud service at the end of the year, so bad timing on this. We didn't
>>> want to buy any more hardware, but now it looks like we have to.
>>>
>>> I followed the discussions about SSD drives when they were first
>>> becoming mainstream; at that time, the Intel devices were king. Can anyone
>>> recommend what's a good SSD configuration these days? I don't think we want
>>> to buy a new server with spinning disks.
>>>
>>> We're replacing:
>>> 8 core (Intel)
>>> 48GB memory
>>> 12-drive 7200 RPM 500GB
>>> RAID1 (2 disks, OS and WAL log)
>>> RAID10 (8 disks, postgres data dir)
>>> 2 spares
>>> Ubuntu 16.04
>>> Postgres 9.6
>>>
>>> The current system peaks at about 7000 TPS from pgbench.
>>>
>>> Our system is a mix of non-transactional searching (customers) and
>>> transactional data loading (us).
>>>
>>> Thanks!
>>> Craig
>>>
>>> --
>>> ---------------------------------
>>> Craig A. James
>>> Chief Technology Officer
>>> eMolecules, Inc.
>>> ---------------------------------
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
@ 2018-04-10 22:56 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
3 siblings, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2018-04-10 22:56 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
We have been using the Intel S3710 (or minor model variations thereof).
They have been great (consistent performance, power off safe and good
expected lifetime). Also 2 of them in RAID1 easily outperform a
reasonably large number of 10K spinners in RAID10.
Now you *can* still buy the S37xx series, but eventually I guess we'll
have to look at something more modern like the S45xx series. But I'm not
so keen on them (they use TLC NAND which may give less consistent
performance, plus they appear to have slightly lower expected lifetime).
I think there was a thread a year or more ago on this list specifically
about this very issue that might be worth searching for.
The TLC NAND seems like a big deal - most modern SSD are built using
it...they solve the high latency problem with SLC caches. So you get
brilliant performance until the cache is full, then it drops off a
cliff. Bigger/more expensive drives have bigger caches, so it is well
worth finding in depth reviews of the exact models you might wish to
evaluate!
regards
Mark
On 10/04/18 14:36, Craig James wrote:
> One of our four "big iron" (spinning disks) servers went belly up
> today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're
> planning to move to a cloud service at the end of the year, so bad
> timing on this. We didn't want to buy any more hardware, but now it
> looks like we have to.
>
> I followed the discussions about SSD drives when they were first
> becoming mainstream; at that time, the Intel devices were king. Can
> anyone recommend what's a good SSD configuration these days? I don't
> think we want to buy a new server with spinning disks.
>
> We're replacing:
> 8 core (Intel)
> 48GB memory
> 12-drive 7200 RPM 500GB
> RAID1 (2 disks, OS and WAL log)
> RAID10 (8 disks, postgres data dir)
> 2 spares
> Ubuntu 16.04
> Postgres 9.6
>
> The current system peaks at about 7000 TPS from pgbench.
>
> Our system is a mix of non-transactional searching (customers) and
> transactional data loading (us).
>
> Thanks!
> Craig
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
@ 2018-04-11 01:39 ` Matthew Hall <mhall@mhcomputing.net>
2018-04-12 05:11 ` Re: Latest advice on SSD? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
3 siblings, 1 reply; 13+ messages in thread
From: Matthew Hall @ 2018-04-11 01:39 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
The most critical bit of advice I've found is setting this preference:
https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-...
I'm using 4 512GB Samsung 850 EVOs in a hardware RAID 10 on a 1U server with about 144 GB RAM and 8 Xeon cores. I usually burn up CPU more than I burn up disks or RAM as compared to using magnetic where I had horrible IO wait percentages, so it seems to be performing quite well so far.
Matthew Hall
> On Apr 9, 2018, at 7:36 PM, Craig James <cjames@emolecules.com> wrote:
>
> One of our four "big iron" (spinning disks) servers went belly up today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're planning to move to a cloud service at the end of the year, so bad timing on this. We didn't want to buy any more hardware, but now it looks like we have to.
>
> I followed the discussions about SSD drives when they were first becoming mainstream; at that time, the Intel devices were king. Can anyone recommend what's a good SSD configuration these days? I don't think we want to buy a new server with spinning disks.
>
> We're replacing:
> 8 core (Intel)
> 48GB memory
> 12-drive 7200 RPM 500GB
> RAID1 (2 disks, OS and WAL log)
> RAID10 (8 disks, postgres data dir)
> 2 spares
> Ubuntu 16.04
> Postgres 9.6
>
> The current system peaks at about 7000 TPS from pgbench.
>
> Our system is a mix of non-transactional searching (customers) and transactional data loading (us).
>
> Thanks!
> Craig
>
> --
> ---------------------------------
> Craig A. James
> Chief Technology Officer
> eMolecules, Inc.
> ---------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-11 01:39 ` Re: Latest advice on SSD? Matthew Hall <mhall@mhcomputing.net>
@ 2018-04-12 05:11 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-13 09:55 ` Re: Latest advice on SSD? Ants Aasma <ants.aasma@eesti.ee>
0 siblings, 1 reply; 13+ messages in thread
From: Mark Kirkwood @ 2018-04-12 05:11 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
The 512 Gb model is big enough that the SLC cache and performance is
gonna be ok. What would worry me is the lifetime: individual 512 Gb 850
EVOs are rated at 150 Tb over 5 years. Compare that to the Intel S3710 -
400 Gb is rated at 8 Pb over 5 years. These drives are fast enough so
that you *might* write more than 4x 150 = 600 Tb over 5 years...
In addition - Samsung are real cagey about the power loss reliability of
these drives - I suspect that if you do lose power unexpectedly then
data corruption will result (no capacitors to keep RAM cache in sync).
regards
Mark
On 11/04/18 13:39, Matthew Hall wrote:
> The most critical bit of advice I've found is setting this preference:
>
> https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-...
>
> I'm using 4 512GB Samsung 850 EVOs in a hardware RAID 10 on a 1U
> server with about 144 GB RAM and 8 Xeon cores. I usually burn up CPU
> more than I burn up disks or RAM as compared to using magnetic where I
> had horrible IO wait percentages, so it seems to be performing quite
> well so far.
>
> Matthew Hall
>
> On Apr 9, 2018, at 7:36 PM, Craig James <cjames@emolecules.com
> <mailto:cjames@emolecules.com>> wrote:
>
>> One of our four "big iron" (spinning disks) servers went belly up
>> today. (Thanks, Postgres and pgbackrest! Easy recovery.) We're
>> planning to move to a cloud service at the end of the year, so bad
>> timing on this. We didn't want to buy any more hardware, but now it
>> looks like we have to.
>>
>> I followed the discussions about SSD drives when they were first
>> becoming mainstream; at that time, the Intel devices were king. Can
>> anyone recommend what's a good SSD configuration these days? I don't
>> think we want to buy a new server with spinning disks.
>>
>> We're replacing:
>> 8 core (Intel)
>> 48GB memory
>> 12-drive 7200 RPM 500GB
>> RAID1 (2 disks, OS and WAL log)
>> RAID10 (8 disks, postgres data dir)
>> 2 spares
>> Ubuntu 16.04
>> Postgres 9.6
>>
>> The current system peaks at about 7000 TPS from pgbench.
>>
>> Our system is a mix of non-transactional searching (customers) and
>> transactional data loading (us).
>>
>> Thanks!
>> Craig
>>
>> --
>> ---------------------------------
>> Craig A. James
>> Chief Technology Officer
>> eMolecules, Inc.
>> ---------------------------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-11 01:39 ` Re: Latest advice on SSD? Matthew Hall <mhall@mhcomputing.net>
2018-04-12 05:11 ` Re: Latest advice on SSD? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2018-04-13 09:55 ` Ants Aasma <ants.aasma@eesti.ee>
0 siblings, 0 replies; 13+ messages in thread
From: Ants Aasma @ 2018-04-13 09:55 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Apr 12, 2018 at 8:11 AM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:
> The 512 Gb model is big enough that the SLC cache and performance is gonna
> be ok. What would worry me is the lifetime: individual 512 Gb 850 EVOs are
> rated at 150 Tb over 5 years. Compare that to the Intel S3710 - 400 Gb is
> rated at 8 Pb over 5 years. These drives are fast enough so that you *might*
> write more than 4x 150 = 600 Tb over 5 years...
>
>
> In addition - Samsung are real cagey about the power loss reliability of
> these drives - I suspect that if you do lose power unexpectedly then data
> corruption will result (no capacitors to keep RAM cache in sync).
I have done a lot of pull-the-plug testing on Samsung 850 M2 drives as
a side effect of a HA demo setup. I haven't kept any numbers, but on a
tiny database with a smallish 100tps workload I am seeing data
corruption in about 1% of cases. Things like empty pg_control files,
sections of WAL replaced with zeroes and/or old data. OS level write
cache tuning is not enough to get rid of it.
Based on that and the fact that interrupting SSD garbage collection
might also cause data loss, my recommendation is to either avoid
consumer drives for important databases. Or if you are adventurous
have multiple replicas in different power domains and have operational
procedures in place to reimage hosts on power loss.
--
Ants Aasma
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
@ 2018-05-11 14:48 Evgeniy Shishkin <itparanoia@gmail.com>
2018-05-12 05:19 ` Re: Latest advice on SSD? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 13+ messages in thread
From: Evgeniy Shishkin @ 2018-05-11 14:48 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; +Cc: pgsql-performance@lists.postgresql.org
> On May 11, 2018, at 15:11, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:
>
> On 11/05/18 23:23, Andreas Joseph Krogh wrote:
>
>> På onsdag 09. mai 2018 kl. 22:00:16, skrev Andreas Joseph Krogh <andreas@visena.com <mailto:andreas@visena.com>>:
>>
>> FWIW; We're testing
>> this: https://www.supermicro.nl/products/system/1U/1029/SYS-1029U-TN10RT.cfm
>> with 4 x Micron NVMe 9200 PRO NVMe 3.84TB U.2 in RAID-10:
>>
> These Micron disks look interesting (pretty good IOPS and lifetime numbers). However (as usual with Micron, sadly) no data about power off safety. Do you know if the the circuit board has capacitors?
According to https://www.micron.com/~/media/documents/products/data-sheet/ssd/9200_u_2_pcie_ssd.pdf <https://www.micron.com/~/media/documents/products/data-sheet/ssd/9200_u_2_pcie_ssd.pdf;
The SSD supports an unexpected power loss with a power-backed write cache. No userdata is lost during an unexpected power loss. When power is subsequently restored, theSSD returns to a ready state within a maximum of 60 seconds.=
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Latest advice on SSD?
2018-05-11 14:48 Re: Latest advice on SSD? Evgeniy Shishkin <itparanoia@gmail.com>
@ 2018-05-12 05:19 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2018-05-12 05:19 UTC (permalink / raw)
To: Evgeniy Shishkin <itparanoia@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On 12/05/18 02:48, Evgeniy Shishkin wrote:
>
>
>> On May 11, 2018, at 15:11, Mark Kirkwood
>> <mark.kirkwood@catalyst.net.nz
>> <mailto:mark.kirkwood@catalyst.net.nz>> wrote:
>>
>> On 11/05/18 23:23, Andreas Joseph Krogh wrote:
>>
>>> På onsdag 09. mai 2018 kl. 22:00:16, skrev Andreas Joseph Krogh
>>> <andreas@visena.com <mailto:andreas@visena.com>
>>> <mailto:andreas@visena.com>>:
>>>
>>> FWIW; We're testing
>>> this:
>>> https://www.supermicro.nl/products/system/1U/1029/SYS-1029U-TN10RT.cfm
>>> with 4 x Micron NVMe 9200 PRO NVMe 3.84TB U.2 in RAID-10:
>> These Micron disks look interesting (pretty good IOPS and lifetime
>> numbers). However (as usual with Micron, sadly) no data about power
>> off safety. Do you know if the the circuit board has capacitors?
>
> According to
> https://www.micron.com/~/media/documents/products/data-sheet/ssd/9200_u_2_pcie_ssd.pdf
> <https://www.micron.com/%7E/media/documents/products/data-sheet/ssd/9200_u_2_pcie_ssd.pdf;
>
> The SSD supports an unexpected power loss with a power-backed write
> cache. No userdata is lost during an unexpected power loss. When power
> is subsequently restored, theSSD returns to a ready state within a
> maximum of 60 seconds.
Excellent, and thanks for finding the details - note the document
explicitly states that they have capacitor backed power loss protection.
So looking good as a viable alternative to Intel's S4500, S4600, P4500,
P4600 range. One point to note - we've been here before with Micron
claiming power loss protection and having to retract it later (Crucial
M550 range...I have 2 of these BTW) - but to be fair to Micron the
Crucial range is purely consumer and this Micron 9200 is obviously an
enterprise targeted product. But some power loss testing might be advised!
Cheers
Mark
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2018-05-12 05:19 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-10 02:36 Latest advice on SSD? Craig James <cjames@emolecules.com>
2018-04-10 17:41 ` Craig James <cjames@emolecules.com>
2018-04-10 17:54 ` Benjamin Scherrey <scherrey@proteus-tech.com>
2018-04-10 19:00 ` Aaron <aaron.werman@gmail.com>
2018-04-10 19:11 ` Dmitry Shalashov <skaurus@gmail.com>
2018-04-10 19:58 ` Charles Sprickman <css@morefoo.com>
2018-04-10 20:15 ` Frits Jalvingh <jal@etc.to>
2018-04-10 22:56 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-11 01:39 ` Matthew Hall <mhall@mhcomputing.net>
2018-04-12 05:11 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-13 09:55 ` Ants Aasma <ants.aasma@eesti.ee>
2018-05-11 14:48 Re: Latest advice on SSD? Evgeniy Shishkin <itparanoia@gmail.com>
2018-05-12 05:19 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
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