Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f5zPH-0003Kw-DX for pgsql-performance@arkaria.postgresql.org; Tue, 10 Apr 2018 19:58:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1f5zPG-0005eD-Cp for pgsql-performance@arkaria.postgresql.org; Tue, 10 Apr 2018 19:58:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f5zPF-0005e3-Os for pgsql-performance@lists.postgresql.org; Tue, 10 Apr 2018 19:58:18 +0000 Received: from mail.morefoo.com ([207.99.53.222]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1f5zPA-0007u6-RX for pgsql-performance@postgresql.org; Tue, 10 Apr 2018 19:58:17 +0000 Received: from nac.morefoo.com (mail.morefoo.com [207.99.53.222]) by mail.morefoo.com (Postfix) with ESMTP id E61282D4844; Tue, 10 Apr 2018 15:58:09 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=morefoo.com; s=mail; t=1523390290; bh=8bT6zSuKknHU6BzuzaelJn6xGVmTsVC2TgMM3QaWRho=; h=From:Subject:Date:In-Reply-To:Cc:To:References; b=Uat8czvStJ76YrZ6cYphH5hzZwyNeXGDMvd3scFxRlZDEwzCqmCZxn0zc+Q+3S1iU AIlLP9NibCroRTTbc+4vraHasbu1X2PUysPxHJ/gkFvFbuZsH1mjgDeihVRTusJsOn ZR2i1fWpWzO8X1GvhKFPZNeIWm7IFuqbC/mTpuQU= X-Virus-Scanned: amavisd-new at morefoo.com Received: from mail.morefoo.com ([207.99.53.222]) by nac.morefoo.com (nac.morefoo.com [207.99.53.222]) (amavisd-new, port 10024) with ESMTP id Bv-g2LKOd792; Tue, 10 Apr 2018 15:58:07 -0400 (EDT) Received: from frankentosh.sporklab.com (pool-71-187-162-242.nwrknj.fios.verizon.net [71.187.162.242]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) (Authenticated sender: css@morefoo.com) by mail.morefoo.com (Postfix) with ESMTPSA id F3A1E2D481A; Tue, 10 Apr 2018 15:58:06 -0400 (EDT) From: Charles Sprickman Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_F3904BF0-BCCB-42C5-9E04-A81F13643F5A" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: Latest advice on SSD? Date: Tue, 10 Apr 2018 15:58:06 -0400 In-Reply-To: Cc: Aaron , Benjamin Scherrey , Craig James , "pgsql-performa." To: Dmitry Shalashov References: <5EA40A5C-B056-44DD-8E43-13182BE9189F@gmail.com> X-Mailer: Apple Mail (2.3273) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_F3904BF0-BCCB-42C5-9E04-A81F13643F5A Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Apr 10, 2018, at 3:11 PM, Dmitry Shalashov = wrote: >=20 > > SSDs are generally slower than spinning at sequential IO and way = faster at random.=20 >=20 > 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+. >=20 > I'm curious to know where are you drawing these conclusions from? Yeah, that sequential info sounds weird. I=E2=80=99m 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=E2=80=99s 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 >=20 > 1. = https://blog.seagate.com/enterprises/mach2-and-hamr-breakthrough-ocp/ = >=20 >=20 > 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.=20 >=20 > SSDs are generally slower than spinning at sequential IO and way = faster at random. >=20 > 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.) >=20 > A REASONABLE PERSON SHOULD ASSUME THAT UNBENCHMARKED AND UNRESEARCHED = MIGRATION FROM TUNED SPINNING TO SSD WILL SLOW YOU DOWN >=20 > /Aaron=20 >=20 >=20 > On Apr 10, 2018, at 12:54 PM, Benjamin Scherrey = > wrote: >=20 >> 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. >>=20 >> 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.=20 >>=20 >> good luck (and pardon the crass commercial comments!), >>=20 >> -- Ben Scherrey >>=20 >> 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. >>=20 >> 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. >>=20 >> 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 >>=20 >> The current system peaks at about 7000 TPS from pgbench. >>=20 >> Our system is a mix of non-transactional searching (customers) and = transactional data loading (us). >>=20 >> Thanks! >> Craig >>=20 >> --=20 >> --------------------------------- >> Craig A. James >> Chief Technology Officer >> eMolecules, Inc. >> --------------------------------- >>=20 >=20 --Apple-Mail=_F3904BF0-BCCB-42C5-9E04-A81F13643F5A Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=E2=80=99m 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=E2=80=99s 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-breakthroug= h-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.
---------------------------------



= --Apple-Mail=_F3904BF0-BCCB-42C5-9E04-A81F13643F5A--