Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS level that a lot of I/O is taking place, therefore I suspect that a lot of cache misses are taking place.

If you look for "cache miss" or "cache miss postgresql" or similar searches in the Internet, you will find a lot of references to "cache_miss statistics". But nowhere is explained how to get them! I kind of understood, that that value has to be calculated subtracting hits from fetches. But since I'm no experienced DB admin, I don't really understand with values are meant :-S

I found the PostgreSQL - Monitoring Database Activity documentation, but I'm not sure if following formula is all I need:

cache_miss = "result_of" pg_stat_get_db_blocks_fetched(oid) - "result_of" pg_stat_get_db_blocks_hit(oid)

An explanation for dummies would be highly appreciated.

Thank you in advance!

share|improve this question
    
How large is the database? Does the database fit in memory? Are you checking vmstat / iostat on a cold boot? Remember that the database has to be loaded in memory before caching can be effective. –  François Beausoleil Jan 20 '12 at 14:36
    
Unfortunately the DB will never fit in memory, because it is over 200GB big! =-O I didn't check vmstat/iostat on a cold boot and will have a look at it. But it would only give me the Kernel cache misses, isn't it? I'm starting to think that the DB cache missing is the field blks_read from the view pg_stat_database –  silvavlis Jan 23 '12 at 21:17

1 Answer 1

There is a paragraph in PostgreSql's documentation: (Here)

Note: pg_stat_get_blocks_fetched minus pg_stat_get_blocks_hit gives the number of kernel read() calls issued for the table, index, or database; the number of actual physical reads is usually lower due to kernel-level buffering. The *_blks_read statistics columns use this subtraction, i.e., fetched minus hit.

This may explain your doubt.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.