[Info-Ingres] Cache hit ratio 20% ummm is that good?

[Paul White]

I was horrified to find a cache hit ratio of under 20% on one of my customer
servers recently. Upon investigation the story is more interesting...


Fix Calls  169880982
Hits        28015058
Ratio             16%


I run a trace point DM420 every hour to collect useful stats on the cache.
Somewhere between 10am and 11am the FIX CALLS reset back to zero. Then later
the next day the HITS reset back to zero. It seems 2^32 is the maximum.

Here are the numbers...

                           FIX CALLS   HIT         GREADS
Mon Nov 12 07:42:01 2007   3949759223 3841806178  679286965
Mon Nov 12 08:42:00 2007   3950965028 3842871163  680044889
Mon Nov 12 09:42:00 2007   4055466875 3941987464  743076552
Mon Nov 12 10:42:00 2007   4210180730 4085219792  886492945
Mon Nov 12 11:42:00 2007     39901379 4205029337  942871883
Mon Nov 12 12:42:01 2007     43095584 4207918093  943989347
Mon Nov 12 13:42:00 2007     45047097 4209699466  944931919
...
Tue Nov 13 22:42:00 2007    138024523 4294163984  972833308
Tue Nov 13 23:42:00 2007    138314328 4294437209  973074381
Wed Nov 14 00:42:01 2007    139392284     393704  973820915
Wed Nov 14 01:42:00 2007    140144545    1098031  974425118
Wed Nov 14 02:42:00 2007    140941271    1853547  974792540
Wed Nov 14 03:42:01 2007    141392624    2276876  975180628

If I use only fix/hit to calculate the cache hit ratio I get weird numbers
after that point in time. Also note the cumulative figure gives 97% ratio
which is not useful for measuring performance based on recent activity.

FIX CALLS	HIT	ratio
3949759223	3841806178	97%
3950965028	3842871163	97%
4055466875	3941987464	97%
4210180730	4085219792	97%
39901379	4205029337	10539%
43095584	4207918093	9764%
...
45047097	4209699466	9345%
138024523	4294163984	3111%
138314328	4294437209	3105%
139392284	393704	0%
140144545	1098031	1%
140941271	1853547	1%
141392624	2276876	2%

Normally I use the delta between daily readings in order to calculate the
cache hit ratio. Now I think if the delta is negative I should just add
2^32.

DeltaFIX	DeltaHIT	ratio
3949759223	3841806178	97%  (cumulative since the last restart)
1205805	1064985	88%
104501847	99116301	95%
154713855	143232328	93%
124687945	119809545	96%
3194205	2888756	90%
...
1951513	1781373	91%
92977426	84464518	91%
289805	273225	94%
1077956	923791	86%
752261	704327	94%
796726	755516	95%
451353	423329	94%


Have I bored you all to tears?  The server is rebooted monthly so the
problem only shows up after some very heavy processing (eg a process went
crazy)

ps.. Would anyone like to comment on my GREAD numbers? I think they are too
high for a transaction based database.


cheers

Paul