Message-ID: <559BC865.9080804@denninger.net>
Date: Tue, 07 Jul 2015 07:39:01 -0500
From: Karl Denninger <karl@denninger.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:31.0) Gecko/20100101 Thunderbird/31.7.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: New server: SSD/RAID recommendations?
References: 
 <CAFwQ8rfwyBMD7AiL659qtG+UhsOUZthLSBYF7ODTn6TKxJoTPg@mail.gmail.com>
 <VisenaEmail.44.70ed435cef1739b1.14e4c07dd3d@tc7-visena>
 <31701C89A44B714FB8D242F806E7286C5AED32FA@NTXBOIMBX01.micron.com>
 <559AB332.50304@pinpointresearch.com>
 <52A08B87-2A8A-43F2-92CE-2514F5AD141B@skogoglandskap.no>
 <1253229687.3798367.1436264898828.JavaMail.zimbra@desy.de>
 <985C5CEF-2E55-46AD-A29F-90D48D057A3F@skogoglandskap.no>
 <1052663055.3807152.1436266613895.JavaMail.zimbra@desy.de>
 <559BB7D8.6090701@denninger.net>
 <B9C4FEE4-1527-4CB0-B941-CF6F2CF38D4E@skogoglandskap.no>
In-Reply-To: <B9C4FEE4-1527-4CB0-B941-CF6F2CF38D4E@skogoglandskap.no>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha1; boundary="------------ms070606050602090908060300"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a cryptographically signed message in MIME format.

--------------ms070606050602090908060300
Content-Type: multipart/alternative;
 boundary="------------010304060709000405050406"

This is a multi-part message in MIME format.
--------------010304060709000405050406
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

On 7/7/2015 06:52, Graeme B. Bell wrote:
> Hi Karl,
>
> Great post, thanks.=20
>
> Though I don't think it's against conventional wisdom to aggregate writ=
es into larger blocks rather than rely on 4k performance on ssds :-)=20
>
> 128kb blocks + compression certainly makes sense. But it might make les=
s sense I suppose if you had some incredibly high rate of churn in your r=
ows.=20
> But for the work we do here, we could use 16MB blocks for all the diffe=
rence it would make. (Tip to others: don't do that. 128kb block performan=
ce is already enough out the IO bus to most ssds)
>
> Do you have your WAL log on a compressed zfs fs?=20
>
> Graeme Bell
Yes.

Data goes on one mirrored set of vdevs, pg_xlog goes on a second,
separate pool.  WAL goes on a third pool on RaidZ2.  WAL typically goes
on rotating storage since I use it (and a basebackup) as disaster
recovery (and in hot spare apps the source for the syncing hot standbys)
and that's nearly a big-block-write-only data stream.  Rotating media is
fine for that in most applications.  I take a new basebackup on
reasonable intervals and rotate the WAL logs to keep that from growing
without boundary.

I use LSI host adapters for the drives themselves (no hardware RAID);
I'm currently running on FreeBSD 10.1.  Be aware that ZFS on FreeBSD has
some fairly nasty issues that I developed (and publish) a patch for;
without it some workloads can result in very undesirable behavior where
working set gets paged out in favor of ZFS ARC; if that happens your
performance will go straight into the toilet.

Back before FreeBSD 9 when ZFS was simply not stable enough for me I
used ARECA hardware RAID adapters and rotating media with BBUs and large
cache memory installed on them with UFS filesystems.  Hardware adapters
are, however, a net lose in a ZFS environment even when they nominally
work well (and they frequently interact very badly with ZFS during
certain operations making them just flat-out unsuitable.)  All-in I far
prefer ZFS on a host adapter to UFS on a RAID adapter both from a data
integrity and performance standpoint.

My SSD drives of choice are all Intel; for lower-end requirements the
730s work very well; the S3500 is next and if your write volume is high
enough the S3700 has much greater endurance (but at a correspondingly
higher price.)  All three are properly power-fail protected.  All three
are much, much faster than rotating storage.  If you can saturate the
SATA channels and need still more I/O throughput NVMe drives are the
next quantum up in performance; I'm not there with our application at
the present time.

Incidentally while there are people who have questioned the 730 series
power loss protection I've tested it with plug-pulls and in addition it
watchdogs its internal power loss capacitors -- from the smartctl -a
display of one of them on an in-service machine here:

175 Power_Loss_Cap_Test     0x0033   100   100   010    Pre-fail=20
Always       -       643 (4 6868)


--=20
Karl Denninger
karl@denninger.net <mailto:karl@denninger.net>
/The Market Ticker/
/[S/MIME encrypted email preferred]/

--------------010304060709000405050406
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3Dwindows-1252"
      http-equiv=3D"Content-Type">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    On 7/7/2015 06:52, Graeme B. Bell wrote:<br>
    <blockquote
      cite=3D"mid:B9C4FEE4-1527-4CB0-B941-CF6F2CF38D4E@skogoglandskap.no"=

      type=3D"cite">
      <pre wrap=3D"">Hi Karl,

Great post, thanks.=20

Though I don't think it's against conventional wisdom to aggregate writes=
 into larger blocks rather than rely on 4k performance on ssds :-)=20

128kb blocks + compression certainly makes sense. But it might make less =
sense I suppose if you had some incredibly high rate of churn in your row=
s.=20
But for the work we do here, we could use 16MB blocks for all the differe=
nce it would make. (Tip to others: don't do that. 128kb block performance=
 is already enough out the IO bus to most ssds)

Do you have your WAL log on a compressed zfs fs?=20

Graeme Bell
</pre>
    </blockquote>
    Yes.<br>
    <br>
    Data goes on one mirrored set of vdevs, pg_xlog goes on a second,
    separate pool.=A0 WAL goes on a third pool on RaidZ2.=A0 WAL typicall=
y
    goes on rotating storage since I use it (and a basebackup) as
    disaster recovery (and in hot spare apps the source for the syncing
    hot standbys) and that's nearly a big-block-write-only data stream.=A0=

    Rotating media is fine for that in most applications.=A0 I take a new=

    basebackup on reasonable intervals and rotate the WAL logs to keep
    that from growing without boundary.<br>
    <br>
    I use LSI host adapters for the drives themselves (no hardware
    RAID); I'm currently running on FreeBSD 10.1.=A0 Be aware that ZFS on=

    FreeBSD has some fairly nasty issues that I developed (and publish)
    a patch for; without it some workloads can result in very
    undesirable behavior where working set gets paged out in favor of
    ZFS ARC; if that happens your performance will go straight into the
    toilet.<br>
    <br>
    Back before FreeBSD 9 when ZFS was simply not stable enough for me I
    used ARECA hardware RAID adapters and rotating media with BBUs and
    large cache memory installed on them with UFS filesystems.=A0 Hardwar=
e
    adapters are, however, a net lose in a ZFS environment even when
    they nominally work well (and they frequently interact very badly
    with ZFS during certain operations making them just flat-out
    unsuitable.)=A0 All-in I far prefer ZFS on a host adapter to UFS on a=

    RAID adapter both from a data integrity and performance standpoint.<b=
r>
    <br>
    My SSD drives of choice are all Intel; for lower-end requirements
    the 730s work very well; the S3500 is next and if your write volume
    is high enough the S3700 has much greater endurance (but at a
    correspondingly higher price.)=A0 All three are properly power-fail
    protected.=A0 All three are much, much faster than rotating storage.=A0=

    If you can saturate the SATA channels and need still more I/O
    throughput NVMe drives are the next quantum up in performance; I'm
    not there with our application at the present time.<br>
    <br>
    Incidentally while there are people who have questioned the 730
    series power loss protection I've tested it with plug-pulls and in
    addition it watchdogs its internal power loss capacitors -- from the
    smartctl -a display of one of them on an in-service machine here:<br>=

    <br>
    175 Power_Loss_Cap_Test=A0=A0=A0=A0 0x0033=A0=A0 100=A0=A0 100=A0=A0 =
010=A0=A0=A0 Pre-fail=A0
    Always=A0=A0=A0=A0=A0=A0 -=A0=A0=A0=A0=A0=A0 643 (4 6868)<br>
    <br>
    <br>
    <div class=3D"moz-signature">-- <br>
      Karl Denninger<br>
      <a href=3D"mailto:karl@denninger.net">karl@denninger.net</a><br>
      <i>The Market Ticker</i><br>
      <font size=3D"-2"><i>[S/MIME encrypted email preferred]</i></font>
    </div>
  </body>
</html>

--------------010304060709000405050406--

--------------ms070606050602090908060300
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: S/MIME Cryptographic Signature

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGXzCC
BlswggRDoAMCAQICASkwDQYJKoZIhvcNAQELBQAwgZAxCzAJBgNVBAYTAlVTMRAwDgYDVQQI
EwdGbG9yaWRhMRIwEAYDVQQHEwlOaWNldmlsbGUxGTAXBgNVBAoTEEN1ZGEgU3lzdGVtcyBM
TEMxHDAaBgNVBAMTE0N1ZGEgU3lzdGVtcyBMTEMgQ0ExIjAgBgkqhkiG9w0BCQEWE0N1ZGEg
U3lzdGVtcyBMTEMgQ0EwHhcNMTUwNDIxMDIyMTU5WhcNMjAwNDE5MDIyMTU5WjBaMQswCQYD
VQQGEwJVUzEQMA4GA1UECBMHRmxvcmlkYTEZMBcGA1UEChMQQ3VkYSBTeXN0ZW1zIExMQzEe
MBwGA1UEAxMVS2FybCBEZW5uaW5nZXIgKE9DU1ApMIICIjANBgkqhkiG9w0BAQEFAAOCAg8A
MIICCgKCAgEAuYRY+EB2mGtZ3grlVO8TmnEvduVFA/IYXcCmNSOC1q+pTVjylsjcHKBcOPb9
TP1KLxdWP+Q1soSORGHlKw2/HcVzShDW5WPIKrvML+Ry0XvIvNBu9adTiCsA9nci4Cnf98XE
hVpenER0qbJkBUOGT1rP4iAcfjet0lEgzPEnm+pAxv6fYSNp1WqIY9u0b1pkQiaWrt8hgNOc
rJOiLbc8CeQ/DBP6rUiQjYNO9/aPNauEtHkNNfR9RgLSfGUdZuOCmJqnIla1HsrZhA5p69Bv
/e832BKiNPaH5wF6btAiPpTr2sRhwQO8/IIxcRX1Vxd1yZbjYtJGw+9lwEcWRYAmoxkzKLPi
S6Zo/6z5wgNpeK1H+zOioMoZIczgI8BlX1iHxqy/FAvm4PHPnC8s+BLnJLwr+jvMNHm82QwL
J9hC5Ho8AnFU6TkCuq+P2V8/clJVqnBuvTUKhYMGSm4mUp+lAgR4L+lwIEqSeWVsxirIcE7Z
OKkvI7k5x3WeE3+c6w74L6PfWVAd84xFlo9DKRdU9YbkFuFZPu21fi/LmE5brImB5P+jdqnK
eWnVwRq+RBFLy4kehCzMXooitAwgP8l/JJa9VDiSyd/PAHaVGiat2vCdDh4b8cFL7SV6jPA4
k0MgGUA/6Et7wDmhZmCigggr9K6VQCx8jpKB3x1NlNNiaWECAwEAAaOB9DCB8TA3BggrBgEF
BQcBAQQrMCkwJwYIKwYBBQUHMAGGG2h0dHA6Ly9jdWRhc3lzdGVtcy5uZXQ6ODg4ODAJBgNV
HRMEAjAAMBEGCWCGSAGG+EIBAQQEAwIFoDALBgNVHQ8EBAMCBeAwLAYJYIZIAYb4QgENBB8W
HU9wZW5TU0wgR2VuZXJhdGVkIENlcnRpZmljYXRlMB0GA1UdDgQWBBTFHJQt6cloXBdG1Pv1
o2YgH+7lWTAfBgNVHSMEGDAWgBQkcZudhX383d29sMqSlAOh+tNtNTAdBgNVHREEFjAUgRJr
YXJsQGRlbm5pbmdlci5uZXQwDQYJKoZIhvcNAQELBQADggIBAE9/dxi2YqjCYYhiybp4GKcm
7tBVa/GLW+qcHPcoT4dqmqghlLz8+iUH+HCJjRQATVGyMEnvISOKFVHC6aZIG+Sg7J8bfS4+
fjKDi9smRH2VPPx3bV8+yFYRNroMGHaPHZB/Xctmmvc+PZ9O2W7rExgrODtxIOB3Zs6wkYf+
ty+9r1KmTHlV+rRHI6timH1uiyFE3cPi1taAEBxf0851cJV8k40PGF8G48ewnq8SY9sCf5cv
liXbpdgU+I4ND5BuTjg63WS32zuhLd1VSuH3ZC/QbcncMX5W3oLXmcQP5/5uTiBJy74kdPtG
MSZ9rXwZPwNxP/8PXMSR7ViaFvjUkf4bJlyENFa2PGxLk4EUzOuO7t3brjMlQW1fuInfG+ko
3tVxko20Hp0tKGPe/9cOxBVBZeZH/VgpZn3cLculGzZjmdh2fqAQ6kv9Z9AVOG1+dq0c1zt8
2zm+Oi1pikGXkfz5UJq60psY6zbX25BuEZkthO/qiS4pxjxb7gQkS0rTEHTy+qv0l3QVL0wa
NAT74Zaj7l5DEW3qdQQ0dtVieyvptg9CxkfQJE3JyBMb0zBj9Qhc5/hbTfhSlHzZMEbUuIyx
h9vxqFAmGzfB1/WfOKkiNHChkpPW8ZeH9yPeDBKvrgZ96dREHFoVkDk7Vpw5lSM+tFOfdyLg
xxhb/RZVUDeUMYIE4zCCBN8CAQEwgZYwgZAxCzAJBgNVBAYTAlVTMRAwDgYDVQQIEwdGbG9y
aWRhMRIwEAYDVQQHEwlOaWNldmlsbGUxGTAXBgNVBAoTEEN1ZGEgU3lzdGVtcyBMTEMxHDAa
BgNVBAMTE0N1ZGEgU3lzdGVtcyBMTEMgQ0ExIjAgBgkqhkiG9w0BCQEWE0N1ZGEgU3lzdGVt
cyBMTEMgQ0ECASkwCQYFKw4DAhoFAKCCAiEwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAc
BgkqhkiG9w0BCQUxDxcNMTUwNzA3MTIzOTAxWjAjBgkqhkiG9w0BCQQxFgQUgCNXjRJD6O68
8ZIjrzldMk2VnkswbAYJKoZIhvcNAQkPMV8wXTALBglghkgBZQMEASowCwYJYIZIAWUDBAEC
MAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzAN
BggqhkiG9w0DAgIBKDCBpwYJKwYBBAGCNxAEMYGZMIGWMIGQMQswCQYDVQQGEwJVUzEQMA4G
A1UECBMHRmxvcmlkYTESMBAGA1UEBxMJTmljZXZpbGxlMRkwFwYDVQQKExBDdWRhIFN5c3Rl
bXMgTExDMRwwGgYDVQQDExNDdWRhIFN5c3RlbXMgTExDIENBMSIwIAYJKoZIhvcNAQkBFhND
dWRhIFN5c3RlbXMgTExDIENBAgEpMIGpBgsqhkiG9w0BCRACCzGBmaCBljCBkDELMAkGA1UE
BhMCVVMxEDAOBgNVBAgTB0Zsb3JpZGExEjAQBgNVBAcTCU5pY2V2aWxsZTEZMBcGA1UEChMQ
Q3VkYSBTeXN0ZW1zIExMQzEcMBoGA1UEAxMTQ3VkYSBTeXN0ZW1zIExMQyBDQTEiMCAGCSqG
SIb3DQEJARYTQ3VkYSBTeXN0ZW1zIExMQyBDQQIBKTANBgkqhkiG9w0BAQEFAASCAgAhAV7b
wMNs1ugNil9TMAan+Fj1dWB0dSBn3AmYzXnetLPw6AHb1n9DOGlNhL3sQV7eEW4BH9rL5HH9
GixojsrWltDq2uoISwNf9DWVoJ+QueN49OlKRSahEWbL1L6SJY8lhc+CXCyFoikc4e6h0PEh
xu0q84Uz9KaloYg24z3/q9oK1Xkj8W7Gh47wpslGgn/Zyz13DPhCDuJx4P4COfG0NRO/vOR5
2H8LtO1nVIucbs+TBrQcMnui3uuiZ7qnvhrtjIAQztXlW8eOo9cgTqt62STBDwf0VCsHEnu+
Wk6qgtnKE4qEdWgNCuNY6K8Wm/unruSWRDgf6F32PWFhs1+uMUVv9ncAMY7ZH7aJkXju5lZK
LLdn+QkFIBrxjx9PbQK8OXvnvz3H8pO+BXhQcqyJhpjMg7Rb18PreUv8b9Kj0f3NXYqKokSA
d/z0XIatRsfbzbTiUZ/4huyHgm2fjUkgxeslnNmnRqcNlf6mx3tLbT0XC+7yKxoPHAbfYZ9t
Dm9roHRez1l0mJM7288lJbQAPeIkp2rf1yXk8e51NaN4RS3bCxpanNjvb7mDUkYelJzRu7OQ
v1H9rmaxTLjgeX9uGzluze9+PR1US7fv6hqD8g1EMDQplaOGlwqZzJ+Vus7XPrtwrjaAWNvW
LaUObuICxgZMEUnef2T9hWFuA6wFFAAAAAAAAA==
--------------ms070606050602090908060300--