Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dqv6j-0008Cf-0r for pgsql-performance@arkaria.postgresql.org; Sun, 10 Sep 2017 05:48:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dqv6g-0000Eg-Qs for pgsql-performance@arkaria.postgresql.org; Sun, 10 Sep 2017 05:48:34 +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.84_2) (envelope-from ) id 1dqv6f-0000E5-Vz for pgsql-performance@postgresql.org; Sun, 10 Sep 2017 05:48:34 +0000 Received: from mail-it0-x233.google.com ([2607:f8b0:4001:c0b::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dqv6X-0006wj-9Z for pgsql-performance@postgresql.org; Sun, 10 Sep 2017 05:48:32 +0000 Received: by mail-it0-x233.google.com with SMTP id 6so8290612itl.1 for ; Sat, 09 Sep 2017 22:48:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=gcjFXReuUab32RCa/BRYFvV54/CboePss9m0z/0vOBk=; b=GBu0FlpFe8sSVJWkIulL4AoiqePsRtbtyO3YYi3CPJM0rU2/3dAEHq5xcRCZGjMZeY p92H7CT1aP5dPGmuV4KsjbI1LG1YAhbjsFnCVEzV/a+DWIiMHW1xiRdTh1MLdBEKYOPy tYODvzP2yUNRLITYQCtQEluPiADLUa/cXOudz4fw8KOVNtYz8s0B8ZFTVu4HQnQENFAE lg2rmEeAOoG3fqxr3wrVxZ9bsZEgGrDIevn5qy1F0DY4kdJzTEB9ppInZSxfe/BE8wVy pqhqOxwvI+C42D0GZZphRwHh0lI0pscXz58F2yhIu/KHH/u2TAUYXiD6kmLpZImWdAa5 D17Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=gcjFXReuUab32RCa/BRYFvV54/CboePss9m0z/0vOBk=; b=j75fEH7jMsiWMUtSReIhH6QCCgEckxgwqHquKzpgfyqQcSfKl+qJhiMVOaENHskrkT nB8qJu4711gpw4uIQ2luvcGWG/aRahFNuHTUJ8xHrlI6deC+d92/bd5/bwFm0ibWk17R 5Vh+rw3H2SJ4wZmLuy0u4qqKeVMabNOr7M9ZwIYZ3VHqn8u6ImUl1dcC8EUAhdcpqVs4 9Xrl4s8ThbAvsMFlhgFYrAm8CzTEv6DjkXMOtZ+UqcRHGEDtU2eUNGxXN2BXZYe0jbPC vtYgMnvpisDlUhJXbwN/PmIp/mIZWHltDJwB35bwQIODAnYoJ6mNSYhUbSCF0Nn5MbvK Wu1w== X-Gm-Message-State: AHPjjUiYe3hhy5VKFcHDHZM+dFkBCOv46tVETVbKu3FGURLmoes4pwBZ pMrsFOooluqpgQ2RFRjXSkoYVDr+g0ZYxsOyFp4= X-Google-Smtp-Source: AOwi7QAF539ryz+xYyXqB/7iC5YiJVBpUGn7s8LdBVQ4I02mDdhZ3E2LuL5ELllU144qdLWUmfzonmdRoe+KAMjz0RI= X-Received: by 10.36.50.69 with SMTP id j66mr7365658ita.21.1505022503549; Sat, 09 Sep 2017 22:48:23 -0700 (PDT) MIME-Version: 1.0 Received: by 10.79.140.148 with HTTP; Sat, 9 Sep 2017 22:48:23 -0700 (PDT) In-Reply-To: <1752296765.269917.1504707214219.JavaMail.zimbra@fmed.uba.ar> References: <1752296765.269917.1504707214219.JavaMail.zimbra@fmed.uba.ar> From: Soni M Date: Sun, 10 Sep 2017 12:48:23 +0700 Message-ID: Subject: Re: OS cache management To: Gerardo Herzig Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114a8fcab400910558cf5bb9" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a114a8fcab400910558cf5bb9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable In our environment, OS cache is much bigger than postgres buffers. Postgres buffers around 8 GB, OS cache more than 100 GB. Maybe we should inspect pgfincore On Wed, Sep 6, 2017 at 9:13 PM, Gerardo Herzig wrote: > > > ----- Mensaje original ----- > > De: "Soni M" > > Para: pgsql-performance@postgresql.org > > Enviados: Mi=C3=A9rcoles, 6 de Septiembre 2017 5:12:26 > > Asunto: [PERFORM] OS cache management > > > > Hello All, I would like to know about how OS cache works for postgres > table > > and index file. > > > > Let's say I have 10 year data, and commonly used data only the last 1 > year. > > This data is quite big, so each table and index file is divided into > > several file in PGDATA/base > > > > Let's say 1 index named order_by_date has relfilenode =3D 1870772348, a= nd > > it's file consist of 1870772348, 1870772348.1, and 1870772348.2 > > > > And for oftenly queried 1 year data, do ALL files for the order_by_date > > pushed to OS cache ? or it's just 1 file that contains index to this 1 > year > > data. > > > > Postgres has its own cache (defined by the "shared_buffers" variable). > Usually, the unit of movement in and out from the cache is a 8k page > (defined at compilation time), so you cant put it directly in terms of > files. > > There is an extension that can inspect the cache contents: > https://www.postgresql.org/docs/current/static/pgbuffercache.html > > HTH > Gerardo > --=20 Regards, Soni Maula Harriz --001a114a8fcab400910558cf5bb9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
In our environment, OS cache is much bigger than postgres = buffers. Postgres buffers around 8 GB, OS cache more than 100 GB. Maybe we = should inspect pgfincore

On Wed, Sep 6, 2017 at 9:13 PM, Gerardo Herzig <gherzig@fme= d.uba.ar> wrote:


----- Mensaje original -----
> De: "Soni M" <dipt= atapa@gmail.com>
> Para: pgsql-perfor= mance@postgresql.org
> Enviados: Mi=C3=A9rcoles, 6 de Septiembre 2017 5:12:26
> Asunto: [PERFORM] OS cache management
>
> Hello All, I would like to know about how OS cache works for postgres = table
> and index file.
>
> Let's say I have 10 year data, and commonly used data only the las= t 1 year.
> This data is quite big, so each table and index file is divided into > several file in PGDATA/base
>
> Let's say 1 index named order_by_date has relfilenode =3D 18707723= 48, and
> it's file consist of 1870772348, 1870772348.1, and 1870772348.2
>
> And for oftenly queried 1 year data, do ALL files for the order_by_dat= e
> pushed to OS cache ? or it's just 1 file that contains index to th= is 1 year
> data.
>

Postgres has its own cache (defined by the "shared_buffers"= ; variable). Usually, the unit of movement in and out from the cache is a 8= k page (defined at compilation time), so you cant put it directly in terms = of files.

There is an extension that can inspect the cache contents:
https://www.postgresql.org/doc= s/current/static/pgbuffercache.html

HTH
Gerardo



--
Regards,

Soni Maula Harriz
--001a114a8fcab400910558cf5bb9--