public inbox for [email protected]
help / color / mirror / Atom feedOS cache management
5+ messages / 3 participants
[nested] [flat]
* OS cache management
@ 2017-09-06 08:12 Soni M <[email protected]>
2017-09-06 14:13 ` Re: OS cache management Gerardo Herzig <[email protected]>
2017-09-06 14:45 ` Re: [SPAM] OS cache management Moreno Andreo <[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 08:12 OS cache management Soni M <[email protected]>
@ 2017-09-06 14:13 ` Gerardo Herzig <[email protected]>
2017-09-10 05:48 ` Re: OS cache management 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: OS cache management
2017-09-06 08:12 OS cache management Soni M <[email protected]>
2017-09-06 14:13 ` Re: OS cache management Gerardo Herzig <[email protected]>
@ 2017-09-10 05:48 ` Soni M <[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-06 08:12 OS cache management Soni M <[email protected]>
@ 2017-09-06 14:45 ` Moreno Andreo <[email protected]>
2017-09-10 05:49 ` Re: [SPAM] OS cache management 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: [SPAM] OS cache management
2017-09-06 08:12 OS cache management Soni M <[email protected]>
2017-09-06 14:45 ` Re: [SPAM] OS cache management Moreno Andreo <[email protected]>
@ 2017-09-10 05:49 ` Soni M <[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