Buffer Cache hit rate

[Roger Baptiste]

hi,

On a N class server, with 6Gb ram and
dbc_max_pct as 20% and dbc_min_pct as 5% ,
the read and write rates of buffer cache are very poor. Here isa sample sar -b output

18:21:11 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
18:21:16 8017 8668 8 632 642 2 0 0
18:21:21 7887 8847 11 616 632 3 0 0
18:21:26 8491 9352 9 663 678 2 0 0
18:21:31 7572 8151 7 759 768 1 0 0
18:21:36 7696 8543 10 720 734 2 0 0
18:21:41 7812 8925 12 771 785 2 0 0
18:21:46 7452 10420 28 776 872 11 0 0
18:21:51 7491 11415 34 807 943 14 0 0
18:21:56 7739 8763 12 949 954 1 0 0
18:22:01 7882 8431 7 756 768 2 0 0

Average 7804 9150 15 745 778 4 0 0
***

15% and 4% !! . Any idea what could be the problem. I was thinking of reducing dbc_max_pct from 20% to 10%. But found that the hit rates were bad even with 20% memory allocated for cache.
There aren't many pageouts in the system.
Swapuseage is around 60%. Pseudoswap is enabled. Memory usage is around 70%.

The point is why the cache hit rate is so poor.
Any clues at all?

thanks
-ra

[Mark Greene_1]

what is the server being used for? If the read/write activity is spread-out enough across your drives, then this may account for what you are seeing.

mark

[harry d brown jr]

Rajman,

With 6GB, I'd reduce the MAX to 5%, which is 300MB, and MIN to 3% which is 180MB. Usually 200MB is more than enough.

live free or die
harry

[Sandip Ghosh]

Hi,

What type of application you are using? You can have a look at gpm to see the shared memory required/available. May be your required shared memory is to high and available is too low. Otherwise 20% of dbc_max_pct is more than enough.

Sandip

[Carlos Fernandez Riera]

i can imagine two reasons:

1 - I think nbuf must be set to 0 on kernel configuration.

2- Your files are never the same files, so cache is not usefull. Or maybe files are too large.

run sar -v to see how much files are opened, and also sar -a ( maybe is other flag) to ndirs and gets , two parameters that show filesystems activity.

Use glance or gpm to see amount of cache used.


Is this issue for large or short periods?

[Roger Baptiste]

hi,

The system hosts three databases, mostly read-intensive. Cache usage is 1.2Gb on glance.
Total Sharedmemory usage from ipcs output shows up as 1043Mb.

The figures i gave earlier from sar output were for short periods, but running over sar over a longer stretch still gives poor values.(around 60% for read and 35% for writes).

shmmax kernel parm is 1Gb.

-raj

[Paula J Frazer-Campbell]

Hi

Your sar -b output for %rcache shows that the success rate for the buffer cahche is low.

Basically the systen is not finding what it wants from memory very often.

Your dbc_max_pct of 20% is therefore two low, but what to change it to is initally a gustimate.

The system default is:-

dbc_max_pct 50
dbc_min_pct 5

I would rrset to these valules and monitor usinf sar -b and aim to get a average in the high 90s for %rcache.

If it is almost always at 100% then you can reduce the dbc_max_pct in small steps to achieve this high 90s figure.

Paula

[Steven Gillard_2]

What sort of database? Most databases have their own caches so its probably not a statistic to be concerned with. I would look into this before making any changes - if the database buffer cache hit ratios are high then you can probably reduce the system buffer cache as 20% of 6Gig is high and its obviously not being used effectively. If you're running Oracle you can get some stats by running the utlbstat / utlestat scripts over a period.

Are you experiencing a real performance problem or is this just a routine check? If it ain't broke, don't fix it!!

Regards,
Steve

[Steven Gillard_2]

...and if your database is doing the caching it would be a good idea to set:
mincache=direct,convosync=direct

on your database file systems. Have a read of:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xf97e37f45ef7d4118fef0090279cd0f9,00.html

Doing that should improve your system cache hit ratio.

Regards,
Steve

[Paula J Frazer-Campbell]

Steven

Whilst I agree that many data bases manage their own caching. They are sat on top of HP-UX and with this in mind the uinderlying ststem should be as sweet as possible.

Every time the system fails to find what if wants in buffer cache it them must use a slower medium to seek and get the info required.

By using a slower medium the system will therefore be slower.

This system has a low success rate on buffer cache and so seeks elseware.


Paula

[Roger Baptiste]

Paula,

The cache size is already 1.2Gb! and i wouldnt want to give more of it from the memory.

Steve,

What sort of info should i look for from the utlb* scripts? Yes, i am considering the mount option (min,sync..). But, that still doesnt address the issue of having such poor cache rates. I have many database systems which use standard mounts and run fine with a lesser cache and this is the odd one giving the low hit rates.

thanks
-raj

[Steven Gillard_2]

Raj,

Look at the system statistics section in the report.txt output. Take note of the following values:

- physical reads
- consistent gets
- db block gets

The formula is then:

hit ratio = 1 - ( physical reads )
( ------------------------------- )
( consistent gets + db block gets )

If that doesn't turn out readable I've attached a doc which describes the interpretation of these results in more detail. Section B is the one you want, it contains the above formula.


Paula,

The point is if the database is doing its own caching then you don't want the OS doing it as well. That is double caching and a waste of memory for no performance gain. In fact if the DB cache is working well I would expect the OS cache stats to be bad because the DB will only need to go to the OS for infrequently accessed data which is not going to be in the OS's cache anyway.

Its best to let the DB cache handle DB data and the OS cache handle everything else - by using mincache=direct on your DB filesystems you can achieve this.

Regards,
Steve

[Carlos Fernandez Riera]

Now all is clear...


Your databases are using filesystems and you have not configured vxfs filesystems to skip buffer cache.

Then configure vxfs , as others said , and fix dbc_max_pct, and min, to aceptables values, about 100-300 Mb.

Also use that memory to increase db caches, as requied for each one.
___

For that periods when cache ratio is realy poor, i guess that backups were running, putting into cache more and more files...

[Ian Dennison_1]

Rajman,

I tried to follow the 400MB rule a month or so ago, and ended up throttling the system right back (its an 4 GB memory, oracle system). I ended up adding back Buffer Cache gradually until I got to 30% (1.2GB) and it worked fine.
Oracle was working fine inside its SGA (93% hit rate on Buffer Cache).

I have since become dubious about rules-of-thumb, but have created one of my own,...

If you have enough memory, use it shared between the Application and the Buffer Cache, depending on what ratios they have to each other. If you have to restrict memory usage, knock back Buffer Cache first.

Share and Enjoy!

[Sandip Ghosh]

What is the Cache Hit Ratio of your Database? There is a tool called iwatch. You can have a look through that about the cache hit ratio of your database.

Sandip

[Volker Borowski]

Well,

may be this server is somewhat "overoptimized" for database usage. Since logical reads nearly beeing the same as physical reads, it looks to me that

a) every filesystem is nocache-mounted (which it should only be for the database log and the database containers, but not the executables and workareas).

b) filesystem usage is a read once (application-excutable to memory) and never being called again (otherwise you would have more logical reads)

In addition it might be interesting to look a bit at v$filestat if this is oracle. You might need to join it with v$datafile to find out the names. Just to find out if you have more read or write activity in your database.

Further more, you should find out, how many checkpoints you have. If you have so much memory and may be very big online logs and may be very few writes and write-commits, may be everything is done in memory (that would be nice!)

Do not know if this helps
Volker

[Roger Baptiste]

Steve,

Thanks for the info. I will look it up and get back.

Ian,
I am not sure having a huge buffer cache is a good idea. Infact, i have lot of database systems which work just fine with 10% buffercache.

Sandip,

where is iwatch available?

Volker,
Yes, every filesystem is no-cache(the regular) mounted . But, that is the same with
other database systems and there is no problem with them. I have read/heard a lot about the
mindirect/convsync and related options for the mount command on db filesystems. But, the jury has been mixed. I know folks who said it didnt make too much of a difference. Thanks for your inputs. Will check regarding the checkpoint stuff.

-raj

[Deshpande Prashant]

Hi Raj
You might be able to improve the buffer i/o with lesser syncer value.
The default syncer value is 30 sec, try reducing it to 15sec by modifying the file /sbin/init.d/syncer as "/usr/sbin/syncer 15 && "

Make sure your data files are on seperate physical disks on your disk array system.
I'm not sure about XPs, but on EMC you will be able to look at hit ratio per DA with ECC or with help of your EMC eng.

If you have lots of random reads, more cache on disk system and multiple paths might help.

Good luck..
Prashant.