Subject: Re: How to calculate how too much memory ram need a PostgreSQL
 Server?
To: pgsql-admin@lists.postgresql.org
References: <1617646911570-0.post@n3.nabble.com>
From: Karl Denninger <karl@denninger.net>
Message-ID: <a8ba4eba-8fdd-92c5-e6e3-f4c734050eb2@denninger.net>
Date: Mon, 5 Apr 2021 14:27:59 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.9.0
MIME-Version: 1.0
In-Reply-To: <1617646911570-0.post@n3.nabble.com>
Content-Type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha-512; boundary="------------ms020000050608080600020703"
Archived-At: 
 <https://www.postgresql.org/message-id/a8ba4eba-8fdd-92c5-e6e3-f4c734050eb2%40denninger.net>
Precedence: bulk

This is a cryptographically signed message in MIME format.

--------------ms020000050608080600020703
Content-Type: multipart/alternative;
 boundary="------------183A182837DD9CB39DDE9419"
Content-Language: en-US

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

On 4/5/2021 14:21, DrakoRod wrote:
>   Hi folks!!
>
> I support a PostgreSQL database it grew from 100 to 300 GB in couple mo=
nths,
> I we need tuning all OS parameters (is a virtual enviroment). Currently=20
the
> configuration is the follow:
>
> Virtual Machine
> Ubuntu 18.04
> 12 CPUS
> 8 GB in RAM
> 400 in Disk
> Cl=C3=BAster database size 320 GB
>
> Some queries have times too differents, the same query sometimes it exe=
cute
> in 8 10 seconds sometimes  in 12 15 minutes, many querys make joins bet=
ween
> 40, 50 millions rows. I know some factors like  relation between entiti=
es
> affect times, but in general I suspect the mainly problem si resources =
in
> OS.
>
> I suspect is a lack to memory ram but we need justify the change in OS =
RAM,
> so, How to calculate how too much memory ram need a PostgreSQL Server? =
Too
> need know how because the database size continue grows.
>
> Best Regards
> DrakoRod

IMHO you need to profile the workload (e.g. "Explain analyze" on the=20
queries in question), look at the indices in use and whether the planner =

can be helped with additional ones on the tables and then look at the=20
size of said indices and how all of this fits into the RAM you have and=20
what storage is behind it ( RAM -> SSD -> spinning rust.)

When the working set of the indices required for the queries in question =

(not so much the data itself) get outside of the available RAM space=20
performance gets hit fast since any sort of storage is a lot slower,=20
with spinning rust being excruciatingly slower since it has seek time=20
penalties that SSDs do not.

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

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

<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <div class=3D"moz-cite-prefix">On 4/5/2021 14:21, DrakoRod wrote:<br>=

    </div>
    <blockquote type=3D"cite"
      cite=3D"mid:1617646911570-0.post@n3.nabble.com">
      <pre class=3D"moz-quote-pre" wrap=3D""> Hi folks!!

I support a PostgreSQL database it grew from 100 to 300 GB in couple mont=
hs,
I we need tuning all OS parameters (is a virtual enviroment). Currently t=
he
configuration is the follow:

Virtual Machine
Ubuntu 18.04
12 CPUS
8 GB in RAM
400 in Disk
Cl=C3=BAster database size 320 GB

Some queries have times too differents, the same query sometimes it execu=
te
in 8 10 seconds sometimes  in 12 15 minutes, many querys make joins betwe=
en
40, 50 millions rows. I know some factors like  relation between entities=

affect times, but in general I suspect the mainly problem si resources in=

OS.

I suspect is a lack to memory ram but we need justify the change in OS RA=
M,
so, How to calculate how too much memory ram need a PostgreSQL Server? To=
o
need know how because the database size continue grows.

Best Regards
DrakoRod</pre>
    </blockquote>
    <p>IMHO you need to profile the workload (e.g. "Explain analyze" on
      the queries in question), look at the indices in use and whether
      the planner can be helped with additional ones on the tables and
      then look at the size of said indices and how all of this fits
      into the RAM you have and what storage is behind it ( RAM -&gt;
      SSD -&gt; spinning rust.)</p>
    <p>When the working set of the indices required for the queries in
      question (not so much the data itself) get outside of the
      available RAM space performance gets hit fast since any sort of
      storage is a lot slower, with spinning rust being excruciatingly
      slower since it has seek time penalties that SSDs do not.<br>
    </p>
    <blockquote type=3D"cite"
      cite=3D"mid:1617646911570-0.post@n3.nabble.com">
    </blockquote>
    <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>

--------------183A182837DD9CB39DDE9419--

--------------ms020000050608080600020703
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
DdgwggagMIIEiKADAgECAhMA5EiKghDOXrvfxYxjITXYDdhIMA0GCSqGSIb3DQEBCwUAMIGL
MQswCQYDVQQGEwJVUzEQMA4GA1UECAwHRmxvcmlkYTESMBAGA1UEBwwJTmljZXZpbGxlMRkw
FwYDVQQKDBBDdWRhIFN5c3RlbXMgTExDMRgwFgYDVQQLDA9DdWRhIFN5c3RlbXMgQ0ExITAf
BgNVBAMMGEN1ZGEgU3lzdGVtcyBMTEMgMjAxNyBDQTAeFw0xNzA4MTcxNjQyMTdaFw0yNzA4
MTUxNjQyMTdaMHsxCzAJBgNVBAYTAlVTMRAwDgYDVQQIDAdGbG9yaWRhMRkwFwYDVQQKDBBD
dWRhIFN5c3RlbXMgTExDMRgwFgYDVQQLDA9DdWRhIFN5c3RlbXMgQ0ExJTAjBgNVBAMMHEN1
ZGEgU3lzdGVtcyBMTEMgMjAxNyBJbnQgQ0EwggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIK
AoICAQC1aJotNUI+W4jP7xQDO8L/b4XiF4Rss9O0B+3vMH7Njk85fZ052QhZpMVlpaaO+sCI
KqG3oNEbuOHzJB/NDJFnqh7ijBwhdWutdsq23Ux6TvxgakyMPpT6TRNEJzcBVQA0kpby1DVD
0EKSK/FrWWBiFmSxg7qUfmIq/mMzgE6epHktyRM3OGq3dbRdOUgfumWrqHXOrdJz06xE9NzY
vc9toqZnd79FUtE/nSZVm1VS3Grq7RKV65onvX3QOW4W1ldEHwggaZxgWGNiR/D4eosAGFxn
uYeWlKEC70c99Mp1giWux+7ur6hc2E+AaTGh+fGeijO5q40OGd+dNMgK8Es0nDRw81lRcl24
SWUEky9y8DArgIFlRd6d3ZYwgc1DMTWkTavx3ZpASp5TWih6yI8ACwboTvlUYeooMsPtNa9E
6UQ1nt7VEi5syjxnDltbEFoLYcXBcqhRhFETJe9CdenItAHAtOya3w5+fmC2j/xJz29og1KH
YqWHlo3Kswi9G77an+zh6nWkMuHs+03DU8DaOEWzZEav3lVD4u76bKRDTbhh0bMAk4eXriGL
h4MUoX3Imfcr6JoyheVrAdHDL/BixbMH1UUspeRuqQMQ5b2T6pabXP0oOB4FqldWiDgJBGRd
zWLgCYG8wPGJGYgHibl5rFiI5Ix3FQncipc6SdUzOQIDAQABo4IBCjCCAQYwHQYDVR0OBBYE
FF3AXsKnjdPND5+bxVECGKtc047PMIHABgNVHSMEgbgwgbWAFBu1oRhUMNEzjODolDka5k4Q
EDBioYGRpIGOMIGLMQswCQYDVQQGEwJVUzEQMA4GA1UECAwHRmxvcmlkYTESMBAGA1UEBwwJ
TmljZXZpbGxlMRkwFwYDVQQKDBBDdWRhIFN5c3RlbXMgTExDMRgwFgYDVQQLDA9DdWRhIFN5
c3RlbXMgQ0ExITAfBgNVBAMMGEN1ZGEgU3lzdGVtcyBMTEMgMjAxNyBDQYIJAKxAy1WBo2kY
MBIGA1UdEwEB/wQIMAYBAf8CAQAwDgYDVR0PAQH/BAQDAgGGMA0GCSqGSIb3DQEBCwUAA4IC
AQCB5686UCBVIT52jO3sz9pKuhxuC2npi8ZvoBwt/IH9piPA15/CGF1XeXUdu2qmhOjHkVLN
gO7XB1G8CuluxofOIUce0aZGyB+vZ1ylHXlMeB0R82f5dz3/T7RQso55Y2Vog2Zb7PYTC5B9
oNy3ylsnNLzanYlcW3AAfzZcbxYuAdnuq0Im3EpGm8DoItUcf1pDezugKm/yKtNtY6sDyENj
tExZ377cYA3IdIwqn1Mh4OAT/Rmh8au2rZAo0+bMYBy9C11Ex0hQ8zWcvPZBDn4v4RtO8g+K
uQZQcJnO09LJNtw94W3d2mj4a7XrsKMnZKvm6W9BJIQ4Nmht4wXAtPQ1xA+QpxPTmsGAU0Cv
HmqVC7XC3qxFhaOrD2dsvOAK6Sn3MEpH/YrfYCX7a7cz5zW3DsJQ6o3pYfnnQz+hnwLlz4MK
17NIA0WOdAF9IbtQqarf44+PEyUbKtz1r0KGeGLs+VGdd2FLA0e7yuzxJDYcaBTVwqaHhU2/
Fna/jGU7BhrKHtJbb/XlLeFJ24yvuiYKpYWQSSyZu1R/gvZjHeGb344jGBsZdCDrdxtQQcVA
6OxsMAPSUPMrlg9LWELEEYnVulQJerWxpUecGH92O06wwmPgykkz//UmmgjVSh7ErNvL0lUY
UMfunYVO/O5hwhW+P4gviCXzBFeTtDZH259O7TCCBzAwggUYoAMCAQICEwCg0WvVwekjGFiO
62SckFwepz0wDQYJKoZIhvcNAQELBQAwezELMAkGA1UEBhMCVVMxEDAOBgNVBAgMB0Zsb3Jp
ZGExGTAXBgNVBAoMEEN1ZGEgU3lzdGVtcyBMTEMxGDAWBgNVBAsMD0N1ZGEgU3lzdGVtcyBD
QTElMCMGA1UEAwwcQ3VkYSBTeXN0ZW1zIExMQyAyMDE3IEludCBDQTAeFw0xNzA4MTcyMTIx
MjBaFw0yMjA4MTYyMTIxMjBaMFcxCzAJBgNVBAYTAlVTMRAwDgYDVQQIDAdGbG9yaWRhMRkw
FwYDVQQKDBBDdWRhIFN5c3RlbXMgTExDMRswGQYDVQQDDBJrYXJsQGRlbm5pbmdlci5uZXQw
ggIiMA0GCSqGSIb3DQEBAQUAA4ICDwAwggIKAoICAQC+HVSyxVtJhy3Ohs+PAGRuO//Dha9A
16l5FPATr6wude9zjX5f2lrkRyU8vhCXTZW7WbvWZKpcZ8r0dtZmiK9uF58Ec6hhvfkxJzbg
96WHBw5Fumd5ahZzuCJDtCAWW8R7/KN+zwzQf1+B3MVLmbaXAFBuKzySKhKMcHbK3/wjUYTg
y+3UK6v2SBrowvkUBC+jxNg3Wy12GsTXcUS/8FYIXgVVPgfZZrbJJb5HWOQpvvhILpPCD3xs
YJFNKEPltXKWHT7Qtc2HNqikgNwj8oqOb+PeZGMiWapsatKm8mxuOOGOEBhAoTVTwUHlMNTg
6QUCJtuWFCK38qOCyk9Haj+86lUU8RG6FkRXWgMbNQm1mWREQhw3axgGLSntjjnznJr5vsvX
SYR6c+XKLd5KQZcS6LL8FHYNjqVKHBYM+hDnrTZMqa20JLAF1YagutDiMRURU23iWS7bA9tM
cXcqkclTSDtFtxahRifXRI7Epq2GSKuEXe/1Tfb5CE8QsbCpGsfSwv2tZ/SpqVG08MdRiXxN
5tmZiQWo15IyWoeKOXl/hKxA9KPuDHngXX022b1ly+5ZOZbxBAZZMod4y4b4FiRUhRI97r9l
CxsP/EPHuuTIZ82BYhrhbtab8HuRo2ofne2TfAWY2BlA7ExM8XShMd9bRPZrNTokPQPUCWCg
CdIATQIDAQABo4IBzzCCAcswPAYIKwYBBQUHAQEEMDAuMCwGCCsGAQUFBzABhiBodHRwOi8v
b2NzcC5jdWRhc3lzdGVtcy5uZXQ6ODg4ODAJBgNVHRMEAjAAMBEGCWCGSAGG+EIBAQQEAwIF
oDAOBgNVHQ8BAf8EBAMCBeAwHQYDVR0lBBYwFAYIKwYBBQUHAwIGCCsGAQUFBwMEMDMGCWCG
SAGG+EIBDQQmFiRPcGVuU1NMIEdlbmVyYXRlZCBDbGllbnQgQ2VydGlmaWNhdGUwHQYDVR0O
BBYEFLElmNWeVgsBPe7O8NiBzjvjYnpRMIHKBgNVHSMEgcIwgb+AFF3AXsKnjdPND5+bxVEC
GKtc047PoYGRpIGOMIGLMQswCQYDVQQGEwJVUzEQMA4GA1UECAwHRmxvcmlkYTESMBAGA1UE
BwwJTmljZXZpbGxlMRkwFwYDVQQKDBBDdWRhIFN5c3RlbXMgTExDMRgwFgYDVQQLDA9DdWRh
IFN5c3RlbXMgQ0ExITAfBgNVBAMMGEN1ZGEgU3lzdGVtcyBMTEMgMjAxNyBDQYITAORIioIQ
zl6738WMYyE12A3YSDAdBgNVHREEFjAUgRJrYXJsQGRlbm5pbmdlci5uZXQwDQYJKoZIhvcN
AQELBQADggIBAJXboPFBMLMtaiUt4KEtJCXlHO/3ZzIUIw/eobWFMdhe7M4+0u3te0sr77QR
dcPKR0UeHffvpth2Mb3h28WfN0FmJmLwJk+pOx4u6uO3O0E1jNXoKh8fVcL4KU79oEQyYkbu
2HwbXBU9HbldPOOZDnPLi0whi/sbFHdyd4/w/NmnPgzAsQNZ2BYT9uBNr+jZw4SsluQzXG1X
lFL/qCBoi1N2mqKPIepfGYF6drbr1RnXEJJsuD+NILLooTNf7PMgHPZ4VSWQXLNeFfygoOOK
FiO0qfxPKpDMA+FHa8yNjAJZAgdJX5Mm1kbqipvb+r/H1UAmrzGMbhmf1gConsT5f8KU4n3Q
IM2sOpTQe7BoVKlQM/fpQi6aBzu67M1iF1WtODpa5QUPvj1etaK+R3eYBzi4DIbCIWst8MdA
1+fEeKJFvMEZQONpkCwrJ+tJEuGQmjoQZgK1HeloepF0WDcviiho5FlgtAij+iBPtwMuuLiL
shAXA5afMX1hYM4l11JXntle12EQFP1r6wOUkpOdxceCcMVDEJBBCHW2ZmdEaXgAm1VU+fnQ
qS/wNw/S0X3RJT1qjr5uVlp2Y0auG/eG0jy6TT0KzTJeR9tLSDXprYkN2l/Qf7/nT6Q03qyE
QnnKiBXWAZXveafyU/zYa7t3PTWFQGgWoC4w6XqgPo4KV44OMYIFBzCCBQMCAQEwgZIwezEL
MAkGA1UEBhMCVVMxEDAOBgNVBAgMB0Zsb3JpZGExGTAXBgNVBAoMEEN1ZGEgU3lzdGVtcyBM
TEMxGDAWBgNVBAsMD0N1ZGEgU3lzdGVtcyBDQTElMCMGA1UEAwwcQ3VkYSBTeXN0ZW1zIExM
QyAyMDE3IEludCBDQQITAKDRa9XB6SMYWI7rZJyQXB6nPTANBglghkgBZQMEAgMFAKCCAkUw
GAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMjEwNDA1MTgyODAw
WjBPBgkqhkiG9w0BCQQxQgRAV7ZbNxoT9F3aY34Lte3DBK0s+7AJXm2EqwBQhR9Zh8x5/N07
949LYNR1VGzWAFwYFFiUESv48+86FDXw3FEOHTBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFl
AwQBKjALBglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3
DQMCAgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGjBgkrBgEEAYI3EAQxgZUwgZIwezEL
MAkGA1UEBhMCVVMxEDAOBgNVBAgMB0Zsb3JpZGExGTAXBgNVBAoMEEN1ZGEgU3lzdGVtcyBM
TEMxGDAWBgNVBAsMD0N1ZGEgU3lzdGVtcyBDQTElMCMGA1UEAwwcQ3VkYSBTeXN0ZW1zIExM
QyAyMDE3IEludCBDQQITAKDRa9XB6SMYWI7rZJyQXB6nPTCBpQYLKoZIhvcNAQkQAgsxgZWg
gZIwezELMAkGA1UEBhMCVVMxEDAOBgNVBAgMB0Zsb3JpZGExGTAXBgNVBAoMEEN1ZGEgU3lz
dGVtcyBMTEMxGDAWBgNVBAsMD0N1ZGEgU3lzdGVtcyBDQTElMCMGA1UEAwwcQ3VkYSBTeXN0
ZW1zIExMQyAyMDE3IEludCBDQQITAKDRa9XB6SMYWI7rZJyQXB6nPTANBgkqhkiG9w0BAQEF
AASCAgBFIsmCyxBNoJajlb8BezO1ccNuq6E3AnZdG4Tcsmer6rqGV5QpqcFw5FTGseGP0Zhj
Gb+BjI0zrLOxUEju+X23ZxTC+TLUnaLHgewQ8yypnvHzqV1c3Aw4bR+8eqDAn+2NfdOIXcbn
dWTvEvMbsOAa+BX8HiVZJljQ2SIKoy8CRDiiyItk2fN6shZWR8e5gz7alv9SPtjf9Ydspgte
0YjYrgE1DvcGT1SvMlXrXgsMFdG0kzNnh2394ZSebqU/NI996LjD45tOA+65WYX2PErOlfZP
4CIwsMEXGAWqYQQVapWedZlSPrXgacEFDczIt/R2R05XK/OXBZT2ihCFqAusPi1vcsT0vXn6
KKNNhY1KJCPGobjQSu5XkCllYqwQufpYImtZJUMriZGuiuakd1bX7J8wy0t/mUJIQ63TnpSQ
c1jyGSTHJPjrnKHUsLafWM77zMDIrnOOkE4p0Z7j/T6P3JA8QAA7ZmBlSeQ7VGKJWuPJg/NW
xC11xFfy14UpuYb9XNhZpdT8KqB1Vyk/ERksCej9av1RTj5umNofMjwEzi125DBPuF1cHZ0k
Fpnfj5r1vfLl8aBG6uEheMlJFL7BQN/5ADPGAG1zrDhXJzjFC/o5GrnIoFuc1fBNPdPLO6SG
qJJ9iCxl3mvA2fk5yTv4RHIEnfFsrZHFp7cHb90mswAAAAAAAA==
--------------ms020000050608080600020703--