No - I agree - Analysis cache hit rate as a single indicator is dangerous. You can easily increase cache hit rate by de-optimizing a good query so it uses more CPU cylces, and therefore has a higher cache hit rate. All information has to be taken as a whole when performing optimization on a system. Cache hit rate is just one factor. For data warehousing, it's obviously that you are going to have a lower cache hit rate because you are often performing scans across large data sets that will never fit in memory. But for most system, not necesarily just OLTP, a high cache hit ratio is acheivable. Cache hit ratio is just one small indication of performance.
Relating to that - How to extract this kind of information from postgresql? Is there a way to get the cache hti ratio, or determine the worst 10 queries in a database? Alex Turner NetEconomist On 12 Jan 2005 12:25:23 -0500, Greg Stark <[EMAIL PROTECTED]> wrote: > > Alex Turner <[EMAIL PROTECTED]> writes: > > > Infact the cache hit ratio that Oracle suggests is the minimum good > > value is 95%. Anything below that is bad news. > > Well that seems very workload dependent. No amount of cache is going to be > able to achieve that for a DSS system chugging sequentially through terabytes > of data. Whereas for OLTP systems I would wouldn't be surprised to see upwards > of 99% hit rate. > > Note that a high cache hit rate can also be a sign of a problem. After all, it > means the same data is being accessed repeatedly which implicitly means > something is being done inefficiently. For an SQL database it could mean the > query plans are suboptimal. > > On several occasions we found Oracle behaving poorly despite excellent cache > hit rates because it was doing a sequential scan of a moderately sized table > instead of an index lookup. The table was small enough to fit in RAM but large > enough to consume a significant amount of cpu, especially with the query being > run thousands of times per minute. > > -- > greg > > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend