Sv: Latest advice on SSD?

[Andreas Joseph Krogh]

På tirsdag 10. april 2018 kl. 04:36:27, skrev Craig James mailto:cja...@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)?
 
--
Andreas Joseph Krogh

 

Re: Latest advice on SSD?

[Craig James]

On Tue, Apr 10, 2018 at 12:21 AM, Andreas Joseph Krogh 
wrote:

> På tirsdag 10. april 2018 kl. 04:36:27, skrev Craig James <
> cja...@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   2  5202
10  1  7916
20  5000   7924
30     7270
40  2500   5020
50  2000   6417


>
> --
> Andreas Joseph Krogh
>
>



-- 
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-

Re: Latest advice on SSD?

[Benjamin Scherrey]

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

Re: Latest advice on SSD?

[Aaron]

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

Re: Latest advice on SSD?

[Dmitry Shalashov]

> 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 :

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

Re: Latest advice on SSD?

[Charles Sprickman]

> On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov  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  >:
> 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  > 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 > > 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.
>> -
>> 
> 

Re: Latest advice on SSD?

[Frits Jalvingh]

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 1 test
starting vacuum...end.
transaction type: 
scaling factor: 100
query mode: simple
number of clients: 10
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 10/10
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  wrote:

> On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov  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 :
>
>> 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 <
>> scher...@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 
>> 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.
>>> -
>>>
>>
>>
>
>

Re: Latest advice on SSD?

[Mark Kirkwood]

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
>

Re: Latest advice on SSD?

[Matthew Hall]

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-by-50x-85593b8991b0

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

Re: [PERFORM] Dissuade the use of exclusion constraint index

[Adam Brusselback]

Just wondering if anyone has any thoughts on what I can do to alleviate
this issue?

I'll kinda at a loss as to what to try to tweak for this.