Thread: [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 = 1870772348, 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_date pushed to OS cache ? or it's just 1 file that contains index to this 1 year data.
How about index named order_by_customer, will ALL the index files pushed to OS cache ?
Can someone please explain about how OS cache works for this condition.
Thanks very much for the explanation.
--
Soni Maula Harriz
Regards,
----- Mensaje original ----- > De: "Soni M" <diptatapa@gmail.com> > Para: pgsql-performance@postgresql.org > Enviados: Miércoles, 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 = 1870772348, 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_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 thecache 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
Il 06/09/2017 10:12, Soni M ha scritto: > > > 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 > May not be relevant to what you asked, but if you want to keep last yeat data in a "small and fast" dataset separated (physically separated!) by old data (that's still available, but response times may vary), IMHO you should consider partitioning... https://www.postgresql.org/docs/current/static/ddl-partitioning.html HTH, Moreno.-
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@fmed.uba.ar> wrote:
----- Mensaje original -----
> De: "Soni M" <diptatapa@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Miércoles, 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 = 1870772348, 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_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
Regards,
Yeah, thanks. We have it in count.
On Wed, Sep 6, 2017 at 9:45 PM, Moreno Andreo <moreno.andreo@evolu-s.it> wrote:
Il 06/09/2017 10:12, Soni M ha scritto:May not be relevant to what you asked, but if you want to keep last yeat data in a "small and fast" dataset separated (physically separated!) by old data (that's still available, but response times may vary), IMHO you should consider partitioning...
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
https://www.postgresql.org/docs/current/static/ddl-partition ing.html
HTH,
Moreno.-
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Regards,