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