Hello,

I hope this message finds you well.

I wanted to bring to your attention that we have recently observed a significant decline in the performance of all processes executed within the database. This slowdown is impacting our overall efficiency and productivity.

Below, I have attached a report detailing the performance metrics and ratios that we have been monitoring. These indicators provide insights into the current state of our database operations.

How to resolve this issue ?

Metric Ratio Report For 4K Cache

        Bufwaits Ratio:             1.000000%
        Buffer Turnover Rate:       545.00/hr
        Used Buffer Turnover Rate:  545.00/hr
        Experimental BTR #2:        161.00/hr
        Experimental BTR #3:        496.00/hr

Metric Ratio Report For 8K Cache

        Bufwaits Ratio:             2.000000%
        Buffer Turnover Rate:         2.00/hr
        Used Buffer Turnover Rate:    7.00/hr
        Experimental BTR #2:          3.00/hr
        Experimental BTR #3:          7.00/hr

Metric Ratio Report For 16K Cache

        Bufwaits Ratio:             2.000000%
        Buffer Turnover Rate:       842.00/hr
        Used Buffer Turnover Rate:  842.00/hr
        Experimental BTR #2:        842.00/hr
        Experimental BTR #3:        842.00/hr

Metric Ratio Report Summary For All Caches

        ReadAhead Utilization:      46.000000%
        Bufwaits Ratio:             1.000000%
        Buffer Turnover Rate:       477.00/hr
        Used Buffer Turnover Rate:  530.00/hr
        Experimental BTR #2:        195.00/hr
        Experimental BTR #3:        487.00/hr
        Lock Wait Ratio:            0.00000%
        Sequential Scan Ratio:      1.00000%

Statistics reset at: 2025-03-17 20:30:15
Elapsed time since reset:        0:59:45

------------------------------
khalid damane
------------------------------

Khalid:

I would STRONGLY recommend that you contract with me, or some other competent Informix consultancy, to do a server Health Check on your system. That said, I will tell you right now, for free, that your 4K and 16K buffer caches are WAY to small for your current workload and while likely not the only issue, that is the biggest issue.

I can tell you that your environment is fairly balanced between reading and writing, with a slight write bias. The fact that the 4K BTR is higher than the 4K BTR3 but with a high BTR3 tells me that and that the bulk of the inserts are going to the 4K cache. The 16K activity is balanced, indicated by how the BTR and BTR3 are the same. It looks like the 8K cache is mostly read heavy because the BTR is lower than the BTR3 but only just.

You need to increase the sizes of the 4K and 16K caches by several times their current size. With access to the servers and some captured historical data that would be captured during a Health Check, I could give you good starting numbers for that, but it is good that you posted the output from my ratios.shr_ak package here. Note that the 8K cache is a bit small, but that is minor and likely not what is driving the slow down.

As I indicated, I have no way to know if or what else is affecting your performance, but it is clear that your workload has changed since the server was last tuned (or was set up) and all of your configuration needs to be examined. Start with the obvious and take it from there. Reach out to me (or someone) if you want consulting help with that.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Hi Khalid,

yes, I confirm to Art. You have to do a health check. Maybe some other good tune-up topics we can find together.

If you searching for a EU based and GDPR certified Informix Professonal Service and Consulting, please contact me via leolo.de 

Best Regards
Henri

------------------------------
Henri Cujass
leolo IT, CTO
Germany
IBM Champion
henri.cujass@leolo.com
------------------------------