Обсуждение: cache reads vs. disk reads
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..
On Jul 1, 2009, at 4:20 AM, Gerd König wrote: > b) pg_statio_user_tables=> > > 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 ? Take a look in the manual; there's a pretty clear explanation in there. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828