Informix instance performance issue
khalid damane reported slow performance across an Informix instance and posted buffer ratio metrics. Art Kagel recommended a health check and said the 4K and 16K buffer caches were far too small. Paul Watson and khaled Bentebal agreed to add memory but find what changed, such as new SQL, access paths, statistics or data volume. Henri Cujass offered consulting. No confirmed fix.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------
1000m oveview .... If it was fine before and is bad now then find what has changed, but from these metrics add more memory. Cheers Paul
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 ------------------------------
Hi Khalid, The performance issue can come from several things: CPU, memory, disk access, statistics, locking and concurrency, etc etc. From the info that you are providing, it looks like memory (lack of buffers) could be one thing to look at. Your system is needing more data to be loaded into the buffers. Since there is not enough memory for what you are doing, it is generating disk access. Adding more memory will certainly help but will not identify the root cause of this situation. The ideal situation is not going to disk as much but accessing memory (Informix Buffer cache). So you need to find out what generated this : - new applications or new sql requests that interact with your engine - the engine is not using the same access paths to your data ( was using some indexes and the access paths changed for some reason to identify) - statistics changed - the volume of data changed tremendously - etc We can look into with in more details if you wish to contact us. -- Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Tél: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr