pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_statio_all_tables columns
6+ messages / 3 participants
[nested] [flat]
* pg_statio_all_tables columns
@ 2013-09-30 12:45 Xenofon Papadopoulos <xpapad@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Xenofon Papadopoulos @ 2013-09-30 12:45 UTC (permalink / raw)
To: pgsql-performance
I am trying to understand the heap_blks_read and heap_blks_hit of
pg_statio_all_tables in 9.2
Do the numbers refer only to SELECT, or they take INSERT into account?
Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of over
55% combined with a heap_blks_read value of over 50M indicate an issue with
the queries affecting that table, or it is normal if the table is heavily
written to?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_statio_all_tables columns
@ 2013-09-30 14:40 Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
parent: Xenofon Papadopoulos <xpapad@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Giuseppe Broccolo @ 2013-09-30 14:40 UTC (permalink / raw)
To: pgsql-performance
Hi Xenofon,
Il 30/09/2013 14:45, Xenofon Papadopoulos ha scritto:
> I am trying to understand the heap_blks_read and heap_blks_hit of
> pg_statio_all_tables in 9.2
> Do the numbers refer only to SELECT, or they take INSERT into account?
heap_blks_read and heap_blks_hit refer to number of blocks read from
disk layer and from page cache respectively during table usage,
independently if insert, select, delete, update operations are involved.
> Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of
> over 55% combined with a heap_blks_read value of over 50M indicate an
> issue with the queries affecting that table, or it is normal if the
> table is heavily written to?
High values of this ratio mean you have a well-cached database, since
disk blocks reads slow down database operations. You can performe it
increasing the cache available to your database.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_statio_all_tables columns
@ 2013-09-30 14:41 Xenofon Papadopoulos <xpapad@gmail.com>
parent: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
0 siblings, 1 reply; 6+ messages in thread
From: Xenofon Papadopoulos @ 2013-09-30 14:41 UTC (permalink / raw)
To: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>; +Cc: pgsql-performance
Hello Giuseppe,
do you actually mean I have a *poorly-cached *database?
Should this ratio stay low even in the case of a write-heavy table?
On Mon, Sep 30, 2013 at 5:40 PM, Giuseppe Broccolo <
giuseppe.broccolo@2ndquadrant.it> wrote:
> Hi Xenofon,
>
> Il 30/09/2013 14:45, Xenofon Papadopoulos ha scritto:
>
> I am trying to understand the heap_blks_read and heap_blks_hit of
>> pg_statio_all_tables in 9.2
>> Do the numbers refer only to SELECT, or they take INSERT into account?
>>
>
> heap_blks_read and heap_blks_hit refer to number of blocks read from disk
> layer and from page cache respectively during table usage, independently if
> insert, select, delete, update operations are involved.
>
>
> Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of
>> over 55% combined with a heap_blks_read value of over 50M indicate an issue
>> with the queries affecting that table, or it is normal if the table is
>> heavily written to?
>>
>
> High values of this ratio mean you have a well-cached database, since disk
> blocks reads slow down database operations. You can performe it increasing
> the cache available to your database.
>
>
> Giuseppe.
>
> --
> Giuseppe Broccolo - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> giuseppe.broccolo@2ndQuadrant.**it | www.2ndQuadrant.it
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.**
> org <pgsql-performance@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-p...;
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_statio_all_tables columns
@ 2013-09-30 16:30 Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
parent: Xenofon Papadopoulos <xpapad@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Giuseppe Broccolo @ 2013-09-30 16:30 UTC (permalink / raw)
To: Xenofon Papadopoulos <xpapad@gmail.com>; +Cc: pgsql-performance
Il 30/09/2013 16:41, Xenofon Papadopoulos ha scritto:
> Should this ratio stay low even in the case of a write-heavy table?
Yes, in my opinion. Before data manipolation, database pages are moved
on the shared buffer. heap_blks_read and heap_blks_hit are involved in
those operations, not directly in data persistance on hard driver.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_statio_all_tables columns
@ 2013-09-30 17:44 Jeff Janes <jeff.janes@gmail.com>
parent: Xenofon Papadopoulos <xpapad@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2013-09-30 17:44 UTC (permalink / raw)
To: Xenofon Papadopoulos <xpapad@gmail.com>; +Cc: pgsql-performance
On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos <xpapad@gmail.com>wrote:
> I am trying to understand the heap_blks_read and heap_blks_hit of
> pg_statio_all_tables in 9.2
> Do the numbers refer only to SELECT, or they take INSERT into account?
>
They take insert (and update, and delete) into account.
> Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of over
> 55% combined with a heap_blks_read value of over 50M indicate an issue with
> the queries affecting that table, or it is normal if the table is heavily
> written to?
>
There is really no answer to that. For one thing, some unknown number of
those heap_blks_read are really coming from the OS/FS's page cache, not
from disk. For another thing, we don't know how many queries, of what
kind, on how large of a table, those 50M reads are supporting.
Do you have a performance problem? If so, is it due to IO bottleneck? If
so, high heap_blks_read on a certain table might indicate where the problem
could be (although pg_stat_statements would probably do a better job).
In the absence of a specific problem to be diagnosed, those numbers don't
mean very much.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_statio_all_tables columns
@ 2013-09-30 17:50 Xenofon Papadopoulos <xpapad@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Xenofon Papadopoulos @ 2013-09-30 17:50 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
I do have a performance problem, and it is due to I/O bottleneck.
We don't have pg_stat_statements installed, we will check it out.
Thanks
On Mon, Sep 30, 2013 at 8:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Sep 30, 2013 at 5:45 AM, Xenofon Papadopoulos <xpapad@gmail.com>wrote:
>
>> I am trying to understand the heap_blks_read and heap_blks_hit of
>> pg_statio_all_tables in 9.2
>> Do the numbers refer only to SELECT, or they take INSERT into account?
>>
>
> They take insert (and update, and delete) into account.
>
>
>> Would a heap_blks_read / ( heap_blks_read + heap_blks_hit ) ration of
>> over 55% combined with a heap_blks_read value of over 50M indicate an issue
>> with the queries affecting that table, or it is normal if the table is
>> heavily written to?
>>
>
> There is really no answer to that. For one thing, some unknown number of
> those heap_blks_read are really coming from the OS/FS's page cache, not
> from disk. For another thing, we don't know how many queries, of what
> kind, on how large of a table, those 50M reads are supporting.
>
> Do you have a performance problem? If so, is it due to IO bottleneck? If
> so, high heap_blks_read on a certain table might indicate where the problem
> could be (although pg_stat_statements would probably do a better job).
>
> In the absence of a specific problem to be diagnosed, those numbers don't
> mean very much.
>
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-09-30 17:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-30 12:45 pg_statio_all_tables columns Xenofon Papadopoulos <xpapad@gmail.com>
2013-09-30 14:40 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-30 14:41 ` Xenofon Papadopoulos <xpapad@gmail.com>
2013-09-30 16:30 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-30 17:44 ` Jeff Janes <jeff.janes@gmail.com>
2013-09-30 17:50 ` Xenofon Papadopoulos <xpapad@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox