Обсуждение: pg_statio_all_tables columns
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
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
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: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.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?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.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?
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
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
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.2Do 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
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.2Do 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