I have a question with regards to monitoring buffer wait ratio (BR). When checking this, bufwait / (pageread * bufwrits), should we issue an onstat -z everytime we would want to find out the value, like:
or would it be ok to perform the reset only once and still get an accurate BR, when we compute, for any time of the day? Currently, if we reset the profile, we are getting peaks of +8% for a two minute interval. If we perform a reset once in the morning and sample it every two minutes, we sometimes see the BR going as high as 38%.
Is there any good sites we can visit for extensive read on buffer waits and it's impact on performance.
Currently we have MAXed out our LRU (127) and Buffers, but still getting high Buffer Wait Ratio. Our Informix Dynamic Server Version 7.30.UC8 is experiencing slow response time during peak period (when a lot of users are working), wherein a process that takes a few seconds would take a few minutes.
Aside from the BR issue (always goes above 7%), the rest of the engine appears to be properly tuned.
What's the value of your LRU_MIN_DIRTY and LRU_MAX_DIRTY?
Normally you don't need to issue an onstat -z everytime you want to check. The server cumulates it himself.
If you do an onstat -z, you do have the exact value of the latest 300 seconds.
check the table sysptprof in sysmaster database to check what tables have the most buffer waits.
Later versions of IDS have other priority mechanisms for flushing buffers.
Is it possible that you don't have lots of disks in your system?
Check the outputs of 'onstat -FR' to monitor LRU activity during the heavy loads.
The LRU_MIN_DIRTY = 1 and LRU_MAX_DIRTY=2. I looked at the sysmaster:sysptprof table and found no column for bufwait, can it be derived from the value of the columns in this table?
With regards to disk, we have around 15 disks on our server.
We did an onstat -z yesterday morning just before running a script that logs the buffer wait ration for the day and our highest reading yesterday was 18%. Would that value be considered pretty high for an online system?
You have to monitor the bufreads and bufwrites to see what table is causing this.
sysptprof also contains a field seqscans. This could also be a cause of yr problem.
I usually don't tune the BR. You see it immediately in the onstat -p what values are too high.
Is your read and write cache ok?
Did you check the performance guide from informix?
I think it might be interesting to consider some tests on a version 9 of IDS, to see hos it behaves with your application.
You have more than 500000 sequential table scans! If these are scans of tables with lots of rows, I can see why you have a lot of bufwaits.
find out what tables are sequentially scanned: sysptprof, join them with sysptnhdr, to see the size of the table.
Your write buffer cache could be improved, but I assume that's because you do a lot of background writes...
About the lockwaits. I personally don't see any problem if I look at your onstat -p output, but...
Check what table is causing the lockwaits.
Check if that table has page or row locking...
Usually we set the table with the most lock requests to row locking. Especially when the row size is small.
I checked the the nrows and seqscans, there's a 193,600 seqscans with 1,200,400, some has a smaller seqscans but higher nrows. Is there a formula to follow for these two columns, and indicator that would say that if we reach a certain percentage would cause bufwaits.
No there isn't. It 's just that sequentail table scan of tables with lots of rows cause buffers to fill, and maybe unneccessary buffers to get thrown out... That can cause bufwaits.
I think some SQL tuning/ adding indexes will solve your problem.