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

--_c99ae5c5-baa8-46e4-9ba7-b457add1a220_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi=2C
I am increasing the shared_buffer size in postgresql.conf and want to measu=
re 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 th=
e 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 fr=
om.
How can I accomplish this ?
ThanksBala =20
____________________________________________________________ _____
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_c99ae5c5-baa8-46e4-9ba7-b457add1a220_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






Hi=2C

Re: How to find if a SELECT is reading from buffer or

Balkrishna Sharma 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

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

--_507e9662-eba1-450b-90a0-6714a9919032_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


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

> Date: Tue=2C 25 May 2010 12:59:16 -0500
> From: Kevin.Grittner@wicourts.gov
> To: b_ki@hotmail.com=3B pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] How to find if a SELECT is reading from buffer or d=
isk ?
>=20
> Balkrishna Sharma wrote:
> =20
> > 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.
> =20
> There's a third option -- PostgreSQL reads and writes will normally
> go through the OS cache.
> =20
> > 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 ?
> =20
> 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:
> =20
> vmstat 1
> =20
> -Kevin
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
=20
____________________________________________________________ _____
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with H=
otmail.=20
http://www.windowslive.com/campaign/thenewbusy?tile=3Dmultic alendar&ocid=3D=
PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5=

--_507e9662-eba1-450b-90a0-6714a9919032_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable






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

>=3B Date: =
Tue=2C 25 May 2010 12:59:16 -0500
>=3B From: Kevin.Grittner@wicourts.g=
ov
>=3B To: b_ki@hotmail.com=3B pgsql-admin@postgresql.org
>=3B S=
ubject: Re: [ADMIN] How to find if a SELECT is reading from buffer or disk=
?
>=3B
>=3B Balkrishna Sharma <=3Bb_ki@hotmail.com>=3B wrot=
e:
>=3B
>=3B >=3B I am increasing the shared_buffer size in p=
ostgresql.conf and want
>=3B >=3B to measure its effect on READ. In =
essence I want to know if the
>=3B >=3B SELECT queries I am firing r=
epeatedly is reading from the buffer
>=3B >=3B or going directly to =
the disk.
>=3B
>=3B There's a third option -- PostgreSQL reads =
and writes will normally
>=3B go through the OS cache.
>=3B
=
>=3B >=3B Right now I am just looking at execution time of the SELECTs =
and
>=3B >=3B trying to conclude. But there should be a direct way t=
o see where
>=3B >=3B the SELECT reads from.
>=3B >=3B How ca=
n I accomplish this ?
>=3B
>=3B You didn't mention your OS. Th=
ere's usually a way to monitor disk
>=3B I/O built in to the OS. I us=
ually start with:
>=3B
>=3B vmstat 1
>=3B
>=3B -Kev=
in
>=3B
>=3B --
>=3B Sent via pgsql-admin mailing list (pg=
sql-admin@postgresql.org)
>=3B To make changes to your subscription: r>>=3B http://www.postgresql.org/mailpref/pgsql-admin

/>

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

--00c09f8998bde315d304876f3d01
Content-Type: text/plain; charset=ISO-8859-1

On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma 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.

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.
>

--00c09f8998bde315d304876f3d01
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Chirag Dave  416-673-4102
D=

atabase Administrator, Afilias Canada Corp.

filias.info">cdave@ca.afilias.info

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

2010/5/25 Chirag Dave :
>
>
> On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma wro=
te:
>>
>> 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 quer=
ies
>> I am firing repeatedly is reading from the buffer or going directly to t=
he
>> 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=A0 pg_stat_database.blks_hit=A0 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.
>



--=20
C=E9dric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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

--00c09f8a4ce49fb7da04877fecd3
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, May 26, 2010 at 5:25 AM, C=E9dric Villemain <
cedric.villemain.debian@gmail.com> wrote:

> 2010/5/25 Chirag Dave :
> >
> >
> > On Tue, May 25, 2010 at 1:48 PM, Balkrishna Sharma
> 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 tryin=
g
> to
> >> conclude. But there should be a direct way to see where the SELECT rea=
ds
> >> 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 th=
e
> 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).
>
>
Correct, thats where pgFincore will be usefull.

>
> >
> > 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=E9dric Villemain 2ndQuadrant
> http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
>

--00c09f8a4ce49fb7da04877fecd3
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable