Thread: How to find if a SELECT is reading from buffer or disk ?

Hi,

---

The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. Get busy.

Balkrishna Sharma <b_ki@hotmail.com> wrote:

> I am increasing the shared_buffer size in postgresql.conf and want
> to measure its effect on READ. In essence I want to know if the
> SELECT queries I am firing repeatedly is reading from the buffer
> or going directly to the disk.

There's a third option -- PostgreSQL reads and writes will normally
go through the OS cache.

> Right now I am just looking at execution time of the SELECTs and
> trying to conclude. But there should be a direct way to see where
> the SELECT reads from.
> How can I accomplish this ?

You didn't mention your OS.  There's usually a way to monitor disk
I/O built in to the OS.  I usually start with:

vmstat 1

-Kevin

I am on Fedora 12 (x86_64). Will eventually be on RHE.

> Date: Tue, 25 May 2010 12:59:16 -0500
> From: Kevin.Grittner@wicourts.gov
> To: b_ki@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] How to find if a SELECT is reading from buffer or disk ?
>
> Balkrishna Sharma <b_ki@hotmail.com> wrote:
>
> > I am increasing the shared_buffer size in postgresql.conf and want
> > to measure its effect on READ. In essence I want to know if the
> > SELECT queries I am firing repeatedly is reading from the buffer
> > or going directly to the disk.
>
> There's a third option -- PostgreSQL reads and writes will normally
> go through the OS cache.
>
> > Right now I am just looking at execution time of the SELECTs and
> > trying to conclude. But there should be a direct way to see where
> > the SELECT reads from.
> > How can I accomplish this ?
>
> You didn't mention your OS. There's usually a way to monitor disk
> I/O built in to the OS. I usually start with:
>
> vmstat 1
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

---

The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. Get busy.

2010/5/25 Chirag Dave <cdave@ca.afilias.info>:
>
>
> On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma <b_ki@hotmail.com> wrote:
>>
>> Hi,
>> I am increasing the shared_buffer size in postgresql.conf and want to
>> measure its effect on READ. In essence I want to know if the SELECT queries
>> I am firing repeatedly is reading from the buffer or going directly to the
>> disk.
>> I am expecting the first SELECT to go to disk and the subsequent call of
>> the same SELECT to read from buffer .
>> Right now I am just looking at execution time of the SELECTs and trying to
>> conclude. But there should be a direct way to see where the SELECT reads
>> from.
>
> You can also use pg_stat_database view. you can compute cache reads
> percentage of the total number of reads (cache and physical) between the two
> snapshots using  pg_stat_database.blks_hit  and pg_stat_database.blks_read.

views does not reflect this exact behavior : hit and read are relative
to hit shared buffers and request a block (from OS page cache or from
disk).


>
> Chirag Dave  416-673-4102
> Database Administrator, Afilias Canada Corp.
> cdave@ca.afilias.info
>
>
>>
>> How can I accomplish this ?
>> Thanks
>> Bala
>> ________________________________
>> The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
>> Hotmail. Get busy.
>



--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support