public inbox for [email protected]help / color / mirror / Atom feed
OS cache management 5+ messages / 3 participants [nested] [flat]
* OS cache management @ 2017-09-06 08:12 Soni M <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Soni M @ 2017-09-06 08:12 UTC (permalink / raw) To: pgsql-performance 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. -- Regards, Soni Maula Harriz ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: OS cache management @ 2017-09-06 14:13 Gerardo Herzig <[email protected]> parent: Soni M <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Gerardo Herzig @ 2017-09-06 14:13 UTC (permalink / raw) To: Soni M <[email protected]>; +Cc: pgsql-performance ----- Mensaje original ----- > De: "Soni M" <[email protected]> > Para: [email protected] > 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 -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SPAM] OS cache management @ 2017-09-06 14:45 Moreno Andreo <[email protected]> parent: Soni M <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Moreno Andreo @ 2017-09-06 14:45 UTC (permalink / raw) To: pgsql-performance 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.- -- Sent via pgsql-performance mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: OS cache management @ 2017-09-10 05:48 Soni M <[email protected]> parent: Gerardo Herzig <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Soni M @ 2017-09-10 05:48 UTC (permalink / raw) To: Gerardo Herzig <[email protected]>; +Cc: pgsql-performance 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 <[email protected]> wrote: > > > ----- Mensaje original ----- > > De: "Soni M" <[email protected]> > > Para: [email protected] > > 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, Soni Maula Harriz ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SPAM] OS cache management @ 2017-09-10 05:49 Soni M <[email protected]> parent: Moreno Andreo <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Soni M @ 2017-09-10 05:49 UTC (permalink / raw) To: Moreno Andreo <[email protected]>; +Cc: pgsql-performance Yeah, thanks. We have it in count. On Wed, Sep 6, 2017 at 9:45 PM, Moreno Andreo <[email protected]> wrote: > 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.- > > > > -- > Sent via pgsql-performance mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Regards, Soni Maula Harriz ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-09-10 05:49 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-09-06 08:12 OS cache management Soni M <[email protected]> 2017-09-06 14:13 ` Gerardo Herzig <[email protected]> 2017-09-10 05:48 ` Soni M <[email protected]> 2017-09-06 14:45 ` Moreno Andreo <[email protected]> 2017-09-10 05:49 ` Soni M <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox