Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2aIR-0007rd-1D for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 11:05:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1g2aIO-0002TO-UF for pgsql-performance@arkaria.postgresql.org; Wed, 19 Sep 2018 11:05:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2aIO-0002T3-J3 for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 11:05:24 +0000 Received: from sonic309-40.consmr.mail.bf2.yahoo.com ([74.6.129.214]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1g2aIJ-0006OR-7A for pgsql-performance@lists.postgresql.org; Wed, 19 Sep 2018 11:05:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1537355117; bh=Xft0Kpr1MmVuMcEveKkNmiulqa2nvX4LRtwkiAa41uI=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=rX3M9PFdluyC4cwb0ousTaN5BRRqjoMOJ5rPXTJ7OqhjUBW5glwQfv97PNOuoXIjuM1F7ciZ1gX25F8fe97vNpe1Al7ywcf7bMqnXwov04MzpTHXeyWFQarMyyaIWROH1nRFvEPwuencHp+m+B2HDRnLdWGJFRapHhY/2v3nFrKDnRHrBfn1h/WfYqmvC+rxKbebO9SAjhOqq0aN0pJueDxhMmqHzb7Vhvt6fuQcNIJrW3b02BMrwTFTX05D7Rfau6an7MDZ7KcPgL/xAll9vIzPfkNPetzWpwQREdsIEzFnyrghlC0ggd1zV2XH2EGiMocfmhB9shGkxqIcC+2ifQ== X-YMail-OSG: f3Y09ZMVM1kbxAJE9bTjc6_I9RvgXFBpbg2xdve_xoNQdffDyPW_kntaV3h3GAg n0jBCdPg2dhQA92PrpdMR8vrEHb06tZ0FtLrEbPJ3ml.YfxqjzFDTSwSJsnCpw6CBWu4.rMvFwiM pfXqUgghjP3fXsBCViMjgC5BLqFASCMO5UDncf0uyBOEust9fZlxAg5yvhK4SAS54p_NjoFeyDw3 h9hdINw80dzTnZb55DvgrjijtdlyKvgK8WwArhp5cddnM6vU4ivVTSMNDT4.1f0a6kbkkBznJFC7 XxUrLEQUba9h_8JpuewvVimqJIGAB7bbMPWiBf.f9FLrKMRspyYUWPPU1Tj7jTPRT.MLgf5MWQwm FEL6e4Fbml.0foS.m.2d0J3Gf0Fd71VI9dBZdUIvUVajVl7NPixNywVaZSG7rCKVlXVQLeW9kx5y 3itYOOEjk1mF3vsi5uchRyh0W21PozzSs3Qt.QycZVbZh8jduHhh39dkcqtwMCR2l01rgC.LXGIk PE3qGD1deA6b2L9I3Hn8V0VRWe7CDK6cWYBBdVtiEsetOSHksWOjQZG7aJvlAuhjV5k3LBsm7tw0 gWgLhFshshyG_13dAU.YYGN3d0fUxLmISV54909vg9FFbRnx.3uqS43HJ.7Zbhz0QSenQWOwFsA4 DgGLUHulYMZcPLHgThLYcDF5003ChPvhxeGxeEXEV0hN7Xv2iZ.6Uj9IXdvMdIa1LoSapnmNbYA9 PBU6yWGJfeRkb5MnvU9Eczoci1jHB_m.NfB9yE4t1gAa2l6eH7hZpkc2Ud4MdUxsRIekicN661_M 4FizByb5A1ubcxIh1ycYM28ugP2lTL0Y781e_RbajYTihbZHJD6YVVCgL1X1jtp6BTQiF98miSsb zzAuFlrYa0kx_dqy5VM9OczHtOQHm9dBO6AFzZ9ukpGzuwbWJz44vu36Jr65yIzUhR9FuUycGYQb C3_Sj4K5qQwTdezZFauhcq2k- Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.bf2.yahoo.com with HTTP; Wed, 19 Sep 2018 11:05:17 +0000 Date: Wed, 19 Sep 2018 11:01:16 +0000 (UTC) From: "Sam R." Reply-To: "Sam R." To: David Rowley Cc: Sergei Kornilov , "pgsql-performance@lists.postgresql.org" Message-ID: <926456011.7484910.1537354876717@mail.yahoo.com> In-Reply-To: References: <1321418371.5650982.1537346138600.ref@mail.yahoo.com> <1321418371.5650982.1537346138600@mail.yahoo.com> <5456011537348252@sas1-fed4e4c8a570.qloud-c.yandex.net> <1476619436.6435500.1537348704720@mail.yahoo.com> Subject: Re: To keep indexes in memory, is large enough effective_cache_size enough? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7484909_192081997.1537354876715" X-Mailer: WebService/1.1.12406 YahooMailNeo Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:60.0) Gecko/20100101 Firefox/60.0 Content-Length: 7067 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_7484909_192081997.1537354876715 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi! Thanks for all of the comments! David wrote:> if you mention=20 > how muchRAM the server has and how big the data is now Let's say for example: RAM: 64 GB Data: 500 GB - 1.5 TB, for example.=20 ( RAM: Less would of course be better, e.g. 32 GB, but we could maybe go fo= r an even little bit bigger value than 64 GB, if needed to. ) BR Sam On Wednesday, September 19, 2018 1:11 PM, David Rowley wrote: =20 On 19 September 2018 at 21:18, Sam R. wrote: > Ok. So, we should set also shared_buffers big. It might not be quite as beneficial as you might think. If your database is larger than RAM often having a smaller shared_buffers setting yields better performance.=C2=A0 The reason is that if you have a very large shared_buffers that the same buffers can end up cached in the kernel page cache and shared buffers. If you have a smaller shared buffers setting then the chances of that double buffering are reduced and the chances of finding a page cached somewhere increases. However, if your database is quite small and you can afford to fit all your data in shared buffers, with enough free RAM for everything else, then you might benefit from a large shared buffers, but it's important to also consider that some operations, such as DROP TABLE can become slow of shared buffers is very large. You might get more specific recommendations if you mention how much RAM the server has and how big the data is now and will be in the future. --=20 David Rowley=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services =20 ------=_Part_7484909_192081997.1537354876715 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi!

Thanks for all of the comme= nts!

David wrote:
> if you mention
> how muchRAM the server has and how big t= he data is now

Let's say for example:
RAM: 64 GB
Data: 500 G= B - 1.5 TB, for example.

( RAM: Less wou= ld of course=20 be better, e.g. 32 GB, but we could maybe go for an even little bit=20 bigger value than 64 GB, if needed to. )

BR Sam

On Wednesday, Se= ptember 19, 2018 1:11 PM, David Rowley <david.rowley@2ndquadrant.com>= wrote:


On 19 September 2018 at 21:18, Sam R. <samruohola@yahoo.com> wrote:

> Ok. So, we should set also share= d_buffers big.


It might not be q= uite as beneficial as you might think. If your
database i= s larger than RAM often having a smaller shared_buffers
s= etting yields better performance.  The reason is that if you have avery large shared_buffers that the same buffers can end up = cached in
the kernel page cache and shared buffers. If yo= u have a smaller shared
buffers setting then the chances = of that double buffering are reduced
and the chances of f= inding a page cached somewhere increases.

However, if your database is quite small and you can afford to fit all<= br clear=3D"none">your data in shared buffers, with enough free RAM for eve= rything else,
then you might benefit from a large shared = buffers, but it's important
to also consider that some op= erations, such as DROP TABLE can become
slow of shared bu= ffers is very large.

You might get mor= e specific recommendations if you mention how much
RAM th= e server has and how big the data is now and will be in the
future.

--
Davi= d Rowley                  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Servic= es

<= /div>

------=_Part_7484909_192081997.1537354876715--