Subject: Re: PostgreSQL on ZFS: performance tuning
To: pgsql-performance@postgresql.org
References: <1cdf08f7-de8f-c55f-c1ec-29a5f15d5011@mail.ru>
 <c3e5970c-312f-2c98-c3a7-b3273f1d7c07@2ndquadrant.com>
 <5974493a-6785-1d7f-3b06-ba36468763ae@toco-domains.de>
 <2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com>
From: Karl Denninger <karl@denninger.net>
Message-ID: <d2ead42c-3428-a562-0d83-dec4ac6551f1@denninger.net>
Date: Tue, 27 Sep 2016 17:15:26 -0500
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.3.0
MIME-Version: 1.0
In-Reply-To: <2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha-512; boundary="------------ms030201040809080700010804"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a cryptographically signed message in MIME format.

--------------ms030201040809080700010804
Content-Type: multipart/alternative;
 boundary="------------86FCC5C88DBAF6B9261A642E"

This is a multi-part message in MIME format.
--------------86FCC5C88DBAF6B9261A642E
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 9/27/2016 16:38, Tomas Vondra wrote:
> On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote:
>>
>>
>> On 29.07.2016 08:30, Tomas Vondra wrote:
>>>
>>>
>>> On 07/29/2016 08:04 AM, trafdev wrote:
>>>> Hi.
>>>>
>>>> I have an OLAP-oriented DB (light occasional bulk writes and heavy
>>>> aggregated selects over large periods of data) based on Postgres
>>>> 9.5.3.
>>>>
>>>> Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD (root on
>>>> ZFS,
>>>> mirror).
>>>>
>>>> The largest table is 13GB (with a 4GB index on it), other tables
>>>> are 4,
>>>> 2 and less than 1GB.
>>>>
>>>> After reading a lot of articles and "howto-s" I've collected followi=
ng
>>>> set of tweaks and hints:
>>>>
>>>>
>>>> ZFS pools creation:
>>>> zfs create zroot/ara/sqldb
>>>> zfs create -o recordsize=3D8k -o primarycache=3Dall zroot/ara/sqldb/=
pgsql
>>>>
>>>>
>>>> zfs get primarycache,recordsize,logbias,compression
>>>> zroot/ara/sqldb/pgsql
>>>> NAME                   PROPERTY      VALUE         SOURCE
>>>> zroot/ara/sqldb/pgsql  primarycache  all           local
>>>> zroot/ara/sqldb/pgsql  recordsize    8K            local
>>>> zroot/ara/sqldb/pgsql  logbias       latency       local
>>>> zroot/ara/sqldb/pgsql  compression   lz4           inherited from
>>>> zroot
>>>>
>>>> L2ARC is disabled
>>>> VDEV cache is disabled
>>>>
>>>>
>>>> pgsql -c "mkdir /ara/sqldb/pgsql/data_ix"
>>>> pgsql -c "initdb --locale=3Den_US.UTF-8 -E UTF-8 -D
>>>> /ara/sqldb/pgsql/data"
>>>>
>>>>
>>>> /etc/sysctl.conf
>>>> vfs.zfs.metaslab.lba_weighting_enabled=3D0
>>>>
>>>>
>>>> postgresql.conf:
>>>> listen_addresses =3D '*'
>>>> max_connections =3D 100
>>>> shared_buffers =3D 16GB
>>>> effective_cache_size =3D 48GB
>>>
>>> It may not be a problem for your workload, but this
>>> effective_cache_size
>>> value is far too high.
>>
>> May i asked why? ZFS in default caches your size of RAM minus 1 GB.
>> Getting the shared buffer from the 64 GB RAM i would asume 47 GB
>> would be a better value. But this would not be far too high. So
>> please can you explain this?
>
> Because it's not a global value, but an estimate of how much RAM is
> available as a cache for a single query. So if you're running 10
> queries at the same time, they'll have to share the memory.
>
> It's a bit trickier as there's often a fair amount of cross-backend
> sharing (backends accessing the same data, so it's likely one backend
> loads data into cache, and then other backends access it too).
>
> It also ignores that memory may get allocated for other reasons - some
> queries may allocate quite a bit of memory for sorts/aggregations, so
> not only is
>
>    effective_cache_size =3D RAM - shared_buffers
>
> excessive as it ignores the per-query nature, but also because it
> neglects these other allocations.
>
> regards
>
You may well find that with lz4 compression a 128kb record size on that
filesystem is materially faster -- it is here for most workloads under
Postgres.


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

--------------86FCC5C88DBAF6B9261A642E
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3Dutf-8" http-equiv=3D"Content-Ty=
pe">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    On 9/27/2016 16:38, Tomas Vondra wrote:<br>
    <blockquote
      cite=3D"mid:2a522e5a-c79b-9bd5-46c8-441e8e762a87@2ndquadrant.com"
      type=3D"cite">On 09/27/2016 06:00 PM, Torsten Zuehlsdorff wrote:
      <br>
      <blockquote type=3D"cite">
        <br>
        <br>
        On 29.07.2016 08:30, Tomas Vondra wrote:
        <br>
        <blockquote type=3D"cite">
          <br>
          <br>
          On 07/29/2016 08:04 AM, trafdev wrote:
          <br>
          <blockquote type=3D"cite">Hi.
            <br>
            <br>
            I have an OLAP-oriented DB (light occasional bulk writes and
            heavy
            <br>
            aggregated selects over large periods of data) based on
            Postgres 9.5.3.
            <br>
            <br>
            Server is a FreeBSD 10.3 with 64GB of RAM and 2x500GB SSD
            (root on ZFS,
            <br>
            mirror).
            <br>
            <br>
            The largest table is 13GB (with a 4GB index on it), other
            tables are 4,
            <br>
            2 and less than 1GB.
            <br>
            <br>
            After reading a lot of articles and "howto-s" I've collected
            following
            <br>
            set of tweaks and hints:
            <br>
            <br>
            <br>
            ZFS pools creation:
            <br>
            zfs create zroot/ara/sqldb
            <br>
            zfs create -o recordsize=3D8k -o primarycache=3Dall
            zroot/ara/sqldb/pgsql
            <br>
            <br>
            <br>
            zfs get primarycache,recordsize,logbias,compression
            <br>
            zroot/ara/sqldb/pgsql
            <br>
            NAME=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PROPERTY=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 VALUE=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SOU=
RCE
            <br>
            zroot/ara/sqldb/pgsql=C2=A0 primarycache=C2=A0 all=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 local
            <br>
            zroot/ara/sqldb/pgsql=C2=A0 recordsize=C2=A0=C2=A0=C2=A0 8K=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 local
            <br>
            zroot/ara/sqldb/pgsql=C2=A0 logbias=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 latency=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 local
            <br>
            zroot/ara/sqldb/pgsql=C2=A0 compression=C2=A0=C2=A0 lz4=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 inherited
            from zroot
            <br>
            <br>
            L2ARC is disabled
            <br>
            VDEV cache is disabled
            <br>
            <br>
            <br>
            pgsql -c "mkdir /ara/sqldb/pgsql/data_ix"
            <br>
            pgsql -c "initdb --locale=3Den_US.UTF-8 -E UTF-8 -D
            /ara/sqldb/pgsql/data"
            <br>
            <br>
            <br>
            /etc/sysctl.conf
            <br>
            vfs.zfs.metaslab.lba_weighting_enabled=3D0
            <br>
            <br>
            <br>
            postgresql.conf:
            <br>
            listen_addresses =3D '*'
            <br>
            max_connections =3D 100
            <br>
            shared_buffers =3D 16GB
            <br>
            effective_cache_size =3D 48GB
            <br>
          </blockquote>
          <br>
          It may not be a problem for your workload, but this
          effective_cache_size
          <br>
          value is far too high.
          <br>
        </blockquote>
        <br>
        May i asked why? ZFS in default caches your size of RAM minus 1
        GB.
        <br>
        Getting the shared buffer from the 64 GB RAM i would asume 47 GB
        <br>
        would be a better value. But this would not be far too high. So
        <br>
        please can you explain this?
        <br>
      </blockquote>
      <br>
      Because it's not a global value, but an estimate of how much RAM
      is available as a cache for a single query. So if you're running
      10 queries at the same time, they'll have to share the memory.
      <br>
      <br>
      It's a bit trickier as there's often a fair amount of
      cross-backend sharing (backends accessing the same data, so it's
      likely one backend loads data into cache, and then other backends
      access it too).
      <br>
      <br>
      It also ignores that memory may get allocated for other reasons -
      some queries may allocate quite a bit of memory for
      sorts/aggregations, so not only is
      <br>
      <br>
      =C2=A0=C2=A0 effective_cache_size =3D RAM - shared_buffers
      <br>
      <br>
      excessive as it ignores the per-query nature, but also because it
      neglects these other allocations.
      <br>
      <br>
      regards
      <br>
      <br>
    </blockquote>
    You may well find that with lz4 compression a 128kb record size on
    that filesystem is materially faster -- it is here for most
    workloads under Postgres.<br>
    <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>

--------------86FCC5C88DBAF6B9261A642E--

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

MIAGCSqGSIb3DQEHAqCAMIACAQExDzANBglghkgBZQMEAgMFADCABgkqhkiG9w0BBwEAAKCC
Bl8wggZbMIIEQ6ADAgECAgEpMA0GCSqGSIb3DQEBCwUAMIGQMQswCQYDVQQGEwJVUzEQMA4G
A1UECBMHRmxvcmlkYTESMBAGA1UEBxMJTmljZXZpbGxlMRkwFwYDVQQKExBDdWRhIFN5c3Rl
bXMgTExDMRwwGgYDVQQDExNDdWRhIFN5c3RlbXMgTExDIENBMSIwIAYJKoZIhvcNAQkBFhND
dWRhIFN5c3RlbXMgTExDIENBMB4XDTE1MDQyMTAyMjE1OVoXDTIwMDQxOTAyMjE1OVowWjEL
MAkGA1UEBhMCVVMxEDAOBgNVBAgTB0Zsb3JpZGExGTAXBgNVBAoTEEN1ZGEgU3lzdGVtcyBM
TEMxHjAcBgNVBAMTFUthcmwgRGVubmluZ2VyIChPQ1NQKTCCAiIwDQYJKoZIhvcNAQEBBQAD
ggIPADCCAgoCggIBALmEWPhAdphrWd4K5VTvE5pxL3blRQPyGF3ApjUjgtavqU1Y8pbI3Byg
XDj2/Uz9Si8XVj/kNbKEjkRh5SsNvx3Fc0oQ1uVjyCq7zC/kctF7yLzQbvWnU4grAPZ3IuAp
3/fFxIVaXpxEdKmyZAVDhk9az+IgHH43rdJRIMzxJ5vqQMb+n2EjadVqiGPbtG9aZEImlq7f
IYDTnKyToi23PAnkPwwT+q1IkI2DTvf2jzWrhLR5DTX0fUYC0nxlHWbjgpiapyJWtR7K2YQO
aevQb/3vN9gSojT2h+cBem7QIj6U69rEYcEDvPyCMXEV9VcXdcmW42LSRsPvZcBHFkWAJqMZ
Myiz4kumaP+s+cIDaXitR/szoqDKGSHM4CPAZV9Yh8asvxQL5uDxz5wvLPgS5yS8K/o7zDR5
vNkMCyfYQuR6PAJxVOk5Arqvj9lfP3JSVapwbr01CoWDBkpuJlKfpQIEeC/pcCBKknllbMYq
yHBO2TipLyO5Ocd1nhN/nOsO+C+j31lQHfOMRZaPQykXVPWG5BbhWT7ttX4vy5hOW6yJgeT/
o3apynlp1cEavkQRS8uJHoQszF6KIrQMID/JfySWvVQ4ksnfzwB2lRomrdrwnQ4eG/HBS+0l
eozwOJNDIBlAP+hLe8A5oWZgooIIK/SulUAsfI6Sgd8dTZTTYmlhAgMBAAGjgfQwgfEwNwYI
KwYBBQUHAQEEKzApMCcGCCsGAQUFBzABhhtodHRwOi8vY3VkYXN5c3RlbXMubmV0Ojg4ODgw
CQYDVR0TBAIwADARBglghkgBhvhCAQEEBAMCBaAwCwYDVR0PBAQDAgXgMCwGCWCGSAGG+EIB
DQQfFh1PcGVuU1NMIEdlbmVyYXRlZCBDZXJ0aWZpY2F0ZTAdBgNVHQ4EFgQUxRyULenJaFwX
RtT79aNmIB/u5VkwHwYDVR0jBBgwFoAUJHGbnYV9/N3dvbDKkpQDofrTbTUwHQYDVR0RBBYw
FIESa2FybEBkZW5uaW5nZXIubmV0MA0GCSqGSIb3DQEBCwUAA4ICAQBPf3cYtmKowmGIYsm6
eBinJu7QVWvxi1vqnBz3KE+HapqoIZS8/PolB/hwiY0UAE1RsjBJ7yEjihVRwummSBvkoOyf
G30uPn4yg4vbJkR9lTz8d21fPshWETa6DBh2jx2Qf13LZpr3Pj2fTtlu6xMYKzg7cSDgd2bO
sJGH/rcvva9Spkx5Vfq0RyOrYph9boshRN3D4tbWgBAcX9POdXCVfJONDxhfBuPHsJ6vEmPb
An+XL5Yl26XYFPiODQ+Qbk44Ot1kt9s7oS3dVUrh92Qv0G3J3DF+Vt6C15nED+f+bk4gScu+
JHT7RjEmfa18GT8DcT//D1zEke1Ymhb41JH+GyZchDRWtjxsS5OBFMzrju7d264zJUFtX7iJ
3xvpKN7VcZKNtB6dLShj3v/XDsQVQWXmR/1YKWZ93C3LpRs2Y5nYdn6gEOpL/WfQFThtfnat
HNc7fNs5vjotaYpBl5H8+VCautKbGOs219uQbhGZLYTv6okuKcY8W+4EJEtK0xB08vqr9Jd0
FS9MGjQE++GWo+5eQxFt6nUENHbVYnsr6bYPQsZH0CRNycgTG9MwY/UIXOf4W034UpR82TBG
1LiMsYfb8ahQJhs3wdf1nzipIjRwoZKT1vGXh/cj3gwSr64GfenURBxaFZA5O1acOZUjPrRT
n3ci4McYW/0WVVA3lDGCBRMwggUPAgEBMIGWMIGQMQswCQYDVQQGEwJVUzEQMA4GA1UECBMH
RmxvcmlkYTESMBAGA1UEBxMJTmljZXZpbGxlMRkwFwYDVQQKExBDdWRhIFN5c3RlbXMgTExD
MRwwGgYDVQQDExNDdWRhIFN5c3RlbXMgTExDIENBMSIwIAYJKoZIhvcNAQkBFhNDdWRhIFN5
c3RlbXMgTExDIENBAgEpMA0GCWCGSAFlAwQCAwUAoIICTTAYBgkqhkiG9w0BCQMxCwYJKoZI
hvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNjA5MjcyMjE1MjZaME8GCSqGSIb3DQEJBDFCBEDD
Bb13/ezfNPDLPJwSuCRGdAV4n0xW1LwpR65mbAu5RWckI/7pKuEl1HWXpCqpMFrA0Uy6coO9
aVK0gNyZkALJMGwGCSqGSIb3DQEJDzFfMF0wCwYJYIZIAWUDBAEqMAsGCWCGSAFlAwQBAjAK
BggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYI
KoZIhvcNAwICASgwgacGCSsGAQQBgjcQBDGBmTCBljCBkDELMAkGA1UEBhMCVVMxEDAOBgNV
BAgTB0Zsb3JpZGExEjAQBgNVBAcTCU5pY2V2aWxsZTEZMBcGA1UEChMQQ3VkYSBTeXN0ZW1z
IExMQzEcMBoGA1UEAxMTQ3VkYSBTeXN0ZW1zIExMQyBDQTEiMCAGCSqGSIb3DQEJARYTQ3Vk
YSBTeXN0ZW1zIExMQyBDQQIBKTCBqQYLKoZIhvcNAQkQAgsxgZmggZYwgZAxCzAJBgNVBAYT
AlVTMRAwDgYDVQQIEwdGbG9yaWRhMRIwEAYDVQQHEwlOaWNldmlsbGUxGTAXBgNVBAoTEEN1
ZGEgU3lzdGVtcyBMTEMxHDAaBgNVBAMTE0N1ZGEgU3lzdGVtcyBMTEMgQ0ExIjAgBgkqhkiG
9w0BCQEWE0N1ZGEgU3lzdGVtcyBMTEMgQ0ECASkwDQYJKoZIhvcNAQEBBQAEggIAgE5RFYCl
oEvvwTci7h9tTc6aay+CmLcmnWLWN3Xv4x/zr7XbpT1mgC38fQTeizpObqUHWz9QY3iq7LpH
G4Jeulj7Ah9LXA9h1+44JoJvhAUfBE3HhZb7R6vwPzKJECXZefjNsU2UCHVFi0AfB6DH5Ick
F1JP4aIdj0dHtEPvcp/H3vD6sAv68mherctO+mahANIqCXBFQprc9kWge5jtTCdxxwO7fzfX
vXDZXiSUP3LlGqq8df14h15jgoxrXf8TR9eQICGZQAD9uGyEK946BJKZbkoyk1P4t1oCHvl2
Oc5ImTC/n2OL7UP8C/QCu09Hyf2fLjHURv5TM7WD9xxtOyLa+l5fGogjAribQ+ayvc/Sxt1M
P1SGASGX7OTxyShfh43g9jYZAr8Skf8VKoAD00q1DABt2lUg+rxXF27xpgqVeRjgOgQkBQTP
YP1OMNPeMZndGqqq1pfro6EEsxX2z8gQQU5qAL8xuQiuaYnWqB19IBpGpVy9UEOevf6cYHft
lKUQUvgNVC539X7qkT1kcsbTcM2dqLTj675Ar/VYTeB4XJKiFynFgSKEnt/BIZshmJk1kc4x
B+1wBh8Z26kjGW4dFnKoYRZIJ0J0X8EOh49m8H8va04wopBifvqLUzDtfepFWIRBm3o931pM
TtLW200CrGUnpiMjW/OzuDSYVnQAAAAAAAA=
--------------ms030201040809080700010804--