Hi All,
In one of our database the buffer hit ratio value of 32% , So I increased value of -B parameter from 1000 to 1100 by editing the parameter file. First two days hit ration was showing 100% , suddenly it again decreased to 50% .
Please suggest whether to increase -B value further. Editing pf file to increase -B value is the good method or we can use proutil <dbname> -C increaseto command.
How large is your database? -B of 1000 or 1100 is nothing. Try 10000 or 50000 to see a first result. But monitor your free RAM. Don't increase -B for causing paging.
Am 1. Juli 2015, um 06:50, schrieb "vithal.kattikar@hgst.com" <bounce-vithalkattikarhgstcom@community.progress.com>:
Hi All,
In one of our database the buffer hit ratio value of 32% , So I increased value of -B parameter from 1000 to 1100 by editing the parameter file. First two days hit ration was showing 100% , suddenly it again decreased to 50% .
Please suggest whether to increase -B value further. Editing pf file to increase -B value is the good method or we can use proutil <dbname> -C increaseto command.
Flag this post as spam/abuse.
As Mike says you don't want to increase -B more than free RAM, but in any case, small increases in this value will have next to no impact. Doubling it will have an impact. Increasing by 10% will be almost imperceptible.
In sizing -B, remember it's the number of DB Blocks. You can calculate how much memory that will take by multiplying your figure by the block size of your DB. So with a block size of 8192, a -B of 10000 would give you 81,920,000 bytes or approx 80MB.
I see you have another thread about migrating to 11.2. Is this on your old 10.1C DB? If so, when you move to 11.x, set -lruskips to a value of say 50. Check the documentation for what it does.
In terms of where to set it, using increaseto is only ever a temporary solution. Once you find the value you want you MUST put it in the pf too or else it will get lost the next time you restart.
Vithal, it's not possible to answer your question without knowing your statistics (namely, promon's screen where you got buffer hit ration) and your Progress version. I can only guess that you don't need to increase the -B and low buffer hit ratio is the result of db request counter's overflow. Check statistics on a shorter interval.
The database size is around 8 GB. Please find the details of buffer ratio screen shot.Activity - Sampled at 07/02/15 02:12 for 98:33:21.
Event Total Per Sec Event Total Per Sec
Commits 100 0.0 Undos 0 0.0
Record Updates 0 0.0 Record Reads 281979 0.8
Record Creates 100 0.0 Record Deletes 0 0.0
DB Writes 18 0.0 DB Reads 1409920 4.0
BI Writes 104 0.0 BI Reads 24707 0.1
AI Writes 0 0.0
Record Locks 167097 0.5 Record Waits 0 0.0
Checkpoints 0 0.0 Buffs Flushed 8 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 0 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 33 %
DB Size 5931 MB BI Size 128 MB AI Size 0 K
FR chain 1261 blocks RM chain 2 blocks
Shared Memory 12705792 Segments 1
3 Servers, 3 Users (1 Local, 2 Remote, 1 Batch),0 Apws
RETURN - repeat, U - continue uninterrupted, Q - quit:
Please suggest us wat we can further do to make buffer hit ration optimum.
Ø Please suggest us wat we can further do to make buffer hit ration optimum
The database size is around 8 GB. Please find the details of buffer ratio screen shot.Activity - Sampled at 07/02/15 02:12 for 98:33:21.
Event Total Per Sec Event Total Per Sec
Commits 100 0.0 Undos 0 0.0
Record Updates 0 0.0 Record Reads 281979 0.8
Record Creates 100 0.0 Record Deletes 0 0.0
DB Writes 18 0.0 DB Reads 1409920 4.0
BI Writes 104 0.0 BI Reads 24707 0.1
AI Writes 0 0.0
Record Locks 167097 0.5 Record Waits 0 0.0
Checkpoints 0 0.0 Buffs Flushed 8 0.0
Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 0 % Writes by BIW 0 % Writes by AIW 0 %
Buffer Hits 33 %
DB Size 5931 MB BI Size 128 MB AI Size 0 K
FR chain 1261 blocks RM chain 2 blocks
Shared Memory 12705792 Segments 1
3 Servers, 3 Users (1 Local, 2 Remote, 1 Batch),0 Apws
RETURN - repeat, U - continue uninterrupted, Q - quit:
Please suggest us wat we can further do to make buffer hit ration optimum.
Flag this post as spam/abuse.
Vithal,
1) Progress version is old (less than 10.2B);
2) Sampling interval is too large (98:33:21 - more than 4 days);
3) Average activity (per long interval) is next to zero. Total Record Reads: 281979 - it can be done in a few seconds rather than in 4 days.
You don't have the reasons to worry about the buffer hits.
Thanks George. But as i understand buffer hit should be greater than 90 % , Please suggest so that buffer hit ratio should be 90% and it should not create any paging or other issues.
Buffer hit ratio should ideally be in the region of 99%. But George's point is that your database is hardly being used at all and having your buffer hits so low won't be making much of an impact at all.
If you really want to improve your % then you need to increase -B. And not in the order of 10% like you've tried. Have you tried setting it to 10000 as suggested?
The difficulty we have is that we don't know how much memory is on the server, how much is available, what your DB Block size is, or lots of other stuff. So it's hard to advise. But I'd be very surprised if setting it to 10000 would make much difference to memory as per my calculations above.
Picking a metric to arbitrarily "improve" to a number that has no meaning to users or the business is not the road to successful tuning.
The buffer hit% is a terrible metric to get much meaning out of. It has some value as a "canary in a coal mine" but your database doesn't seem to be a coal mine -- it looks more like a desert (little or no activity).
Yes, as a "rule of thumb" the buffer hit percentage usually ought to be 99% or better. But there exceptions and situations where it doesn't make much sense.
It's just impossible to increase the buffer hits if the session do not /re-use/ the same data many times. Your sessions read only 281979 records from 8 GB db. Let's assume that the average record size is 300 bytes. So the sessions read just 80 MB or 1% of your database. What are the chances that next sessions will read data from the same 1% of your db?
Having said that... if your goal is to have a very impressive hit ratio that is very easy to accomplish. Even easier in a low activity database. Just run this in the background:
/* fix_hr.p
*
* mbpro dbName -p fix_hr.p > fix_hr.out &
*
*/
do while true:
for each _file no-lock.
end.
end.Like others have said... just tuning a metric to a number isn't always a good thing... especially buffer hits over a 4 day period.
Do you actually have a performance issue or are you just trying to "fix" the buffer hits?
There is no performance issue . I am trying to fix the buffer hit ratio
It looks fix_hr.p is reading the system files . U mean it should be scheduled in cron tab.?
Then you shouldn't waste your time. No performance issue means nothing to fix. Performance tuning isn't just making certain percentages or ratios match what you think they should be (or that somebody posted they should be).
Performance tuning is finding the biggest bottleneck and correcting it... and then repeating that until you are pleased with the outcome.
When people speak in general terms like "your buffer hits should be 99%"... they don't mean for the life of the database and they don't mean it should always be that number. It means when your system is processing at peak workloads you should see numbers as close to that as possible using a reasonable sample time. It also assumes your users never run reports... or data extracts... and you have perfect code.
If you are determined to make the buffer hit that high for such a low use database you have a few choices...
1) Hack promon to always show 99%
2) Stop people from using the DB
3) Run code like Tom posted that just reads the same nonsense records over and over again
4) Set -B larger than your entire database and load up every block in the database when you start it up... then never shut it down
The point we have all been trying to make is that the metric doesn't always matter and tuning isn't something you can do with a checklist. If it was every DB vendor would have automated it by now and lots of us would be out of work. Take joy that you don't have a performance issue and start working on the issues you actually have.
Thanks James .
Below is the memory status of the my HP-UX server got by using TOP command.
Memory: 2635840K (2090440K) real, 8994952K (8279840K) virtual, 79976K free Page# 1/35.Please elaborate on real,Virtual and free terms here.
In this server , we have other 6 database .Among these other 6 databases , one more database has 68% buffer hit whose -B parameter value is set to 500 .
Database block size 8192.
I haven't tried it to setting 10000 .Will this not cause paging ?
Finally want to fix this buffer hit ratio by making hit ratio more than 90%.
Please
Tom was joking about the fix_hr program.... it is something he uses to show the absurdity of obsessing over a metric and not the real performance issues. It will give you a high buffer hit but will not fix any issues.... There are systems with high buffer hits that are slow as dirt and there are systems with "bad" buffer hits that run at a high level of performance.
Then you shouldn't waste your time. No performance issue means nothing to fix. Performance tuning isn't just making certain percentages or ratios match what you think they should be (or that somebody posted they should be).
Performance tuning is finding the biggest bottleneck and correcting it... and then repeating that until you are pleased with the outcome.
When people speak in general terms like "your buffer hits should be 99%"... they don't mean for the life of the database and they don't mean it should always be that number. It means when your system is processing at peak workloads you should see numbers as close to that as possible using a reasonable sample time. It also assumes your users never run reports... or data extracts... and you have perfect code.
If you are determined to make the buffer hit that high for such a low use database you have a few choices...
1) Hack promon to always show 99%
2) Stop people from using the DB
3) Run code like Tom posted that just reads the same nonsense records over and over again
4) Set -B larger than your entire database and load up every block in the database when you start it up... then never shut it down
The point we have all been trying to make is that the metric doesn't always matter and tuning isn't something you can do with a checklist. If it was every DB vendor would have automated it by now and lots of us would be out of work. Take joy that you don't have a performance issue and start working on the issues you actually have.
Flag this post as spam/abuse.
"Finally want to fix this buffer hit ratio by making hit ratio more than 90%."
As everyone else already tried to explain: Stop trying to fix something that isn't broken.
1. If you do have a performance issue, a low buffer hit ratio indicates your buffer pool is too small.
2. If you don't have a performance issue, a low buffer hit ratio means absolutely nothing and trying to force it up can only backfire.