cache reads vs. disk reads

From: Gerd König <koenig(at)transporeon(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: cache reads vs. disk reads
Date: 2009-07-01 09:20:45
Message-ID: 4A4B2A6D.3040202@transporeon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm currently have to investigate some time to anyalyze how often the database
has to read data from disk compared to the number of cache(shared buffer) accesses.

I got the following key figures for an example table:

a) pg_statio_user_indexes=>
-[ RECORD 12 ]+----------------------------------
relid | 42535
...
idx_blks_read | 20504593
idx_blks_hit | 17756649

-[ RECORD 14 ]+----------------------------------
relid | 42535
...
idx_blks_read | 146942531
idx_blks_hit | 48752405641

Regarding "RECORD 12" I can't explain why the number of blks_read is higher than
the number of blks_hit. Why should a page be read if we can't find a requested
tuple there ?
The numbers in "RECORD 14" seems O.K., we read some pages and we find several
tuples we need in each page (for average). Is this assumption correct ?

b) pg_statio_user_tables=>
-[ RECORD 2 ]---+------------
relid | 42535
..
heap_blks_read | 1572252620
heap_blks_hit | 32724990601
idx_blks_read | 197453378
idx_blks_hit | 49240726062
toast_blks_read | 0
toast_blks_hit | 0
tidx_blks_read | 0
tidx_blks_hit | 0

heap_blks_read is the number of disk blocks read for that table (excluding index
access), does heap_blks_hit mean the number of accesses to the cache for that data ?
...and is the number of heap_blks_read in heap_blks_hit included, or is this
number the additional accesses, after reading the data from disk to buffer ?

Let me try to explain my question with the numbers of the example table:
number of disk reads = 1572252620
number of cache reads = 32724990601 OR (32724990601 - 1572252620) ???

any help appreciated....

many thanks in advance...GERD..

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Henry 2009-07-01 09:29:43 Re: Regex Character-Class
Previous Message Scara Maccai 2009-07-01 09:15:14 Multi - table statistics