Re: There is a statistic table?

Helo everbody!

I need to know how much the postgres is going to disk to get blocks and how much it is going to cache? witch is the statistic table and what is the field that indicates blocks reads from the disk and the memory cache?

Another question is, what is the best memory configuration to keep more data in cache?

Thanks,

Waldomiro

On Thu, Oct 15, 2009 at 9:33 PM, waldomiro <waldomiro(at)shx(dot)com(dot)br> wrote:
> Helo everbody!
>
> I need to know how much the postgres is going to disk to get blocks and how much it is going to cache? witch is the statistic table and what is the field that indicates blocks reads from the disk and the memory cache?

Yep. Use psql to access postgres:

psql dbnamehere
\d pg_stat<tab><tab>

and you should get a list like:

pg_stat_activity pg_statio_all_indexes
pg_statio_sys_tables pg_statistic_relid_att_index
pg_stat_user_tables
pg_stat_all_indexes pg_statio_all_sequences
pg_statio_user_indexes pg_stats
pg_stat_all_tables pg_statio_all_tables
pg_statio_user_sequences pg_stat_sys_indexes
pg_stat_bgwriter pg_statio_sys_indexes
pg_statio_user_tables pg_stat_sys_tables
pg_stat_database pg_statio_sys_sequences
pg_statistic pg_stat_user_indexes

just select * from them and you can get an idea what is stored.
Interesting ones right off the bat are:

pg_stat_user_tables
pg_stat_user_indexes
pg_stat_all_tables
pg_stat_all_indexes

but feel free to look around.

> Another question is, what is the best memory configuration to keep more data in cache?

OS or pgsql cache? It's generally better to let the OS do the
majority of caching unless you are sure you can pin shared_buffers in
memory, since allocating too much to shared_buffers may result in
unused portions getting swapped out by some OSes which have aggressive
swapping behaviour. Set shared_buggers to 2G or 1/4 of memory
whichever is smaller to start with, then monitor and adjust from
there.

waldomiro wrote:
> I need to know how much the postgres is going to disk to get
> blocks and how much it is going to cache? witch is the
> statistic table and what is the field that indicates blocks
> reads from the disk and the memory cache?

The view pg_statio_all_tables will show you the number of
disk reads and buffer hits per table.

There are other statistics views, see
http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS

> Another question is, what is the best memory configuration to
> keep more data in cache?

That's easy - the greater shared_buffers is, the more cache you have.

Another option is to choose shared_buffers not too large and let
the filesystem cache buffer the database for you.

Yours,
Laurenz Albe

On 10/15/09 11:27 PM, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

> waldomiro wrote:
>> I need to know how much the postgres is going to disk to get
>> blocks and how much it is going to cache? witch is the
>> statistic table and what is the field that indicates blocks
>> reads from the disk and the memory cache?
>
> The view pg_statio_all_tables will show you the number of
> disk reads and buffer hits per table.

My understanding is that it will not show that. Since postgres can't
distinguish between a read that comes from OS cache and one that goes to
disk, you're out of luck on knowing anything exact.
The above shows what comes from shared_buffers versus the OS, however. And
if reads are all buffered, they are not coming from disk. Only those that
come from the OS _may_ have come from disk.

>
> There are other statistics views, see
> http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STA
> TS-VIEWS
>
>> Another question is, what is the best memory configuration to
>> keep more data in cache?
>
> That's easy - the greater shared_buffers is, the more cache you have.
>
> Another option is to choose shared_buffers not too large and let
> the filesystem cache buffer the database for you.
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

On Wed, Oct 21, 2009 at 11:17 AM, Scott Carey <scott(at)richrelevance(dot)com> wrote:
>
>
>
> On 10/15/09 11:27 PM, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>
>> waldomiro wrote:
>>> I need to know how much the postgres is going to disk to get
>>> blocks and how much it is going to cache? witch is the
>>> statistic table and what is the field that indicates blocks
>>> reads from the disk and the memory cache?
>>
>> The view pg_statio_all_tables will show you the number of
>> disk reads and buffer hits per table.
>
> My understanding is that it will not show that.  Since postgres can't
> distinguish between a read that comes from OS cache and one that goes to
> disk, you're out of luck on knowing anything exact.
> The above shows what comes from shared_buffers versus the OS, however.  And
> if reads are all buffered, they are not coming from disk.  Only those that
> come from the OS _may_ have come from disk.

I think he meant pg's shared_buffers not the OS kernel cache.

Le jeudi 22 octobre 2009 00:06:10, Scott Marlowe a écrit :
> On Wed, Oct 21, 2009 at 11:17 AM, Scott Carey <scott(at)richrelevance(dot)com>
wrote:
> > On 10/15/09 11:27 PM, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> >> waldomiro wrote:
> >>> I need to know how much the postgres is going to disk to get
> >>> blocks and how much it is going to cache? witch is the
> >>> statistic table and what is the field that indicates blocks
> >>> reads from the disk and the memory cache?
> >>
> >> The view pg_statio_all_tables will show you the number of
> >> disk reads and buffer hits per table.
> >
> > My understanding is that it will not show that. Since postgres can't
> > distinguish between a read that comes from OS cache and one that goes to
> > disk, you're out of luck on knowing anything exact.
> > The above shows what comes from shared_buffers versus the OS, however.
> > And if reads are all buffered, they are not coming from disk. Only
> > those that come from the OS _may_ have come from disk.
>
> I think he meant pg's shared_buffers not the OS kernel cache.
>

pgfincore let you know if block are in OS kernel cache or not.

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org