| Lists: | pgsql-general |
|---|
| From: | SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Needs Suggestion |
| Date: | 2011-04-27 14:35:12 |
| Message-ID: | BANLkTikKhYCuRo83QF3pmWtSFv31Y73iyg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Can I calculate the *Buffer Hit ratio* *of a particular query* in postgres?
That is *how many times it finds the required page* in its buffer cache,
pg_buffercache or the
linux buffer cache.
--
Thank You,
Subham Roy,
CSE IIT Bombay.
| From: | Andy Colson <andy(at)squeakycode(dot)net> |
|---|---|
| To: | SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Needs Suggestion |
| Date: | 2011-04-27 17:40:08 |
| Message-ID: | 4DB854F8.7050303@squeakycode.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On 4/27/2011 9:35 AM, SUBHAM ROY wrote:
> Can I calculate the *Buffer Hit ratio* *of a particular query* in
> postgres? That is *how many times it finds the required page* in its
> buffer cache, pg_buffercache or the
> linux buffer cache.
>
> --
> Thank You,
> Subham Roy,
> CSE IIT Bombay.
>
There are plenty of stats per table, but I dont think you'll find
anything per query.
However, you could clear the table stats, run the query a bunch of
times, then look at the table stats. Also, it'll tell you pg buffer
hits, but you wont really know if it hit the linux cache or actual hard
drive.
Google can help you out with table stats: postgres cache hit
-Andy
| From: | Magnus Hagander <magnus(at)hagander(dot)net> |
|---|---|
| To: | SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Needs Suggestion |
| Date: | 2011-04-27 17:55:14 |
| Message-ID: | BANLkTi=cU6i3da=pEdTUKBQySdE9n6_drw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Wed, Apr 27, 2011 at 16:35, SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> wrote:
> Can I calculate the Buffer Hit ratio of a particular query in postgres? That
> is how many times it finds the required page in its buffer cache,
> pg_buffercache or the
> linux buffer cache.
You can get the information from the pg cache using
EXPLAIN (ANALYZE, BUFFERS) <your query>
It won't get you the stats from the linux filesystem cache though. You
can perhaps use pgfincore for that in some way.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
| From: | SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> |
|---|---|
| To: | Magnus Hagander <magnus(at)hagander(dot)net> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Needs Suggestion |
| Date: | 2011-04-27 18:11:32 |
| Message-ID: | BANLkTingu=KRrTMa5mDTAv+DubJOOTm0vg@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
I am using postgres 8.4.8, the above command explain(analyze,buffers) is not
working. Is there a way to do that.
--
Thank You,
Subham Roy,
CSE IIT Bombay.
| From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
|---|---|
| To: | SUBHAM ROY <subham(dot)iem(at)gmail(dot)com> |
| Cc: | Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Needs Suggestion |
| Date: | 2011-05-02 23:26:22 |
| Message-ID: | 4DBF3D9E.3050002@postnewspapers.com.au |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On 04/28/2011 02:11 AM, SUBHAM ROY wrote:
> I am using postgres 8.4.8, the above command explain(analyze,buffers) is
> not working. Is there a way to do that.
Nope, the (BUFFERS) syntax was added in 9.0. Try the other suggestions
about using table stats to determine what you want to know.
--
Craig Ringer