First, I commend you for making the effort to test the query directly to Informix, presumably using dbaccess, while there was an apparent slowdown of the query response from the linked SQL Server access it. To me that says that the issue was something going on in the Informix server. There are many possibilities:
- The query was issued during a long checkpoint that blocked it from starting. This is a possibility, but the fact that you were able to recreate the performance issued several seconds or minutes later in dbaccess tells me that this is unlikely, but if it happens again, run "onstat -g ckp" shortly after seeing the issue start to see what was happening to the checkpoints.
- If you have it, run my newratios.ksh script (from my package ratios.shr_ak which you can download from my web site: https://www.askdbmgt.com/my-utilities) and look at the BTR3 and Bufwaits Ratio (BR) metrics. The BTR3 will tell you quickly if your buffer cache is too small while the BR will tell you if you are experiencing contention for the LRU queues that manage the buffers. You should reset the underlying statistics with "onstat -z" at least once a week, but for this exercise once a day at the end of your normal activity period until you figure this out. The metrics that new_ratios.ksh reports are most accurate after a full day's activity.
- Another thing to check is your IO performance. The "onstat -g ioh" output reports IO performance by chunks every minute for the preceding 60 minutes. If you find any chunk that reports a read service time (the "op time" column) greater than 0.010 that is a red flag for any SAN or other high speed storage (0.020 if you are using single local spindle drives). Especially true if that/those chunk(s) are involved with the tables and indexes included in the query that was slow.
- If you are not using KAIO, look at the "onstat -g iov" output at the aio vp lines. The io/wkup for them should show that at least one of the aio vps has an io/wkup less than 1.0. If not, then you could use more aio vps. Especially important if you use file system based chunks and do not set DIRECT_IO on (and even if you do have it set to non-zero, if it is not set to 5 on Linux or Windows and the query takes advantage of temp tables).
- Run the query under "SET EXPLAIN ON;" (making sure that EXPLAIN_STAT is set to 1 so you get the execution time report at the end of the output). That may tell you where the hold-up is. If the reported run times are short but the actual clock time you experience is longer, then that points to the first point about long checkpoint blocking.
Feel free to post any of these outputs here for the community to look over if you are not sure what you are seeing.
Also note that you could pay me to perform an Informix Server Health Check project if you cannot find the issue yourself and would like help optimizing your server's performance.
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.www.askdbmgt.com
Sent: Thu February 02, 2023 07:53 AM
From: tahir makhdoom
Subject: Query Response Time Hike suddenly
I am using Informix 14 database . I am accessing Informix using SQL Server Link Server. Some time where there is a long running query there all database response time hike i-e i was running a query it was taking 1 min and suddenly it start taking 5 min.
At that Hike time when i run query directly in Informix using server studio it is giving me 5 min.
So I am still not clear whether this is the issue with Link Server or Issue with any Informix Setting whether interim of memory or something else.
Please suggest if any one facing same issue.