When users complain of generic slowness (without specifics)...

Informix 12.10.FC14 Solaris 10 1/13 ...what are the first three things you check? Thank you. DG ------------------------------ David Grove ------------------------------

Download the ratios.shr package which you can download from my web site at My Utilities if you do not have it already. Run the ratios.sql in sysmaster to create the stored procedures that the newratios.ksh script depends on, and run newratios.ksh. In the report, look at the BR (Bufwaits Ratio), the BTR3 (Buffer Turnover Rate #3 ), the LWR (Lock Wait Ratio), and SSR (Sequential Scan Rate). You want to see BR < 6%, LWR as close to 0% as possible, and SSR only a few percentage points (though this one is very much dependent on how your site works, so it is best compared historically over time). BTR3 should be less than 10 turns per hour, the lower the better. The script reports on each buffer cache separately and in total/average across all. High SSR can indicate missing indexes of tables. There is a sysmaster table (sysptprof) that can be used to determine what tables are experiencing large numbers of scans. High LWR may indicate poorly written applications that are holding locks longer than necessary. High BR indicates contention for the LRU Queues. Increasing the number of LRU queues can relieve this contention. High BTR3 indicates that you need more buffers in that particular buffer cache. Another thing might be that your data distributions are either stale or insufficiently detailed, especially on very large tables or columns that are skewed heavily to a few values. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

When non specific, and you want 3 things...then I would check general items: 1) CPU idle on the box (at high level and individual cores) 2) Ready Queue (onstat -g rea) 3) Sessions not waiting on network (onstat -u | grep -v " Y--"), e.g. sessions waiting on locks. if nothing revealed there, then look for memory swapping/paging, and disk service times. Then look for long running queries, but then we start getting specific, like looking at individual queries, and what queries have been running for a long time and asking if there is any commonality between the things that are running slow. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Thank you, Art and Mike, I don't think there is a problem with Informix. Because there almost never is. Usually problems with the network. Or the app servers. Or something. But, almost never Informix. However, the first thing that everyone always asks about (with suspicion) is, "Hey, is there something wrong with Informix". So, I usually poke around, don't see anything, and after a while everything seems to settle down, and business proceeds as normal. (And, I have done nothing.) Anyway, BR= 0.11% BTR3= 0.20/hr LWR= 0.00000% SSR= 8.63% vmstat; cpu id = 97 nothing in the ready que no problem with waiting sessions vmstat, pi po are 0 I think this is the usual drill of "users complain; everyone points to the database", but there is isn't any problem with the database. Think I'll go get a cup of coffee. :) Thank you. DG ------------------------------ David Grove ------------------------------

Oh, and no long checkpoints or foreground writes. Or even LRU writes. I just posted this thread for a sanity check to find out if I was "not thinking right". Thank you, and let's not waste any more time on this search for a left handed monkey wrench. DG ------------------------------ David Grove ------------------------------

I tried sending this via email, but...mail problems at my ISP's mailserver. Anytime someone says "the system is slow" and a network is involved, the *FIRST* thing you should check is DNS. If a nameserver fails, there is a lengthy timeout (a few seconds) until it falls through to the next one, assuming you have a second nameserver configured. Have the end user do a "nslookup dbserver.example.com" (substitute for your database server's FQDN) from a command prompt. This can be done on Windows, Unix, Linux, MacOS. You need to make sure they are getting the correct response and in a timely manner. It should respond almost instantaneously. If it does not, you have a problem with the primary nameserver. If you are traversing networks over a WAN, mtr(8) is a nice tool to show where packet loss is happening. It runs like top(1), fire it up and let it run a while. http://www.bitwizard.nl/mtr/ scot

Good point on checking DNS performance, esp. when users' perceived performance problems might be related to (remote) connection performance. Yet there are two sides to this that should be checked: your database server's FQDN - from clients' perspective, so perform this test from client host your clients' IP address (reverse lookup for verifying client's alleged host name): run this test (nslookup <client_ip>) on the db server host The later being slow would clog the msc VP trying to perform traditional (OS) user authentication on behalf of new sessions. ------------------------------ Andreas Legner ------------------------------

Thank you, Scot and Andreas. Usually, when this happens, it is some network issue, and it just "goes away" after a while. But, because, for whatever reason, this one got my boss' attention, I did pay attention to it. However, the complaints have disappeared, so it appears to be just business as usual. In Alaska, networking issues (particularly WAN) crop up regularly. For example, here is an excerpt from an email that was sent out yesterday: " Due to a fiber optic event all of western Alaska is experiencing internet connectivity issues. AMCC does not have internet at this time. Phone lines are operational to the best of our knowledge. ATT is down for cell service..." Western Alaska is a huge area. (Remember, Alaska is more than twice the size of Texas.) That's just life in the Arctic! :) DG ------------------------------ David Grove ------------------------------

If this is important to you get someone to setup https://oss.oetiker.ch/smokeping/ from the users site(s) to the server site. That way if someone says oh it was slow an hour ago you can look and immediately see if it’s network latency. As other say DNS and in particular reverse dns - if forward dns fails it never connects - if reverse fails it's a 30 ssecondend wait for a timeout. Regards, — Clive EIsen

Hi Art, I installed the ratio.shr and run newratios.ksh, but the results are like this: informix.PRD-IFX-02.ifx_prd_tcp_01 $ INFORMIXSERVER=ifx_prd_tcp_01 ./newratios.ksh Metric Ratio Report For 2K Cache Bufwaits Ratio: 0.000000% Buffer Turnover Rate: 0.00/hr Used Buffer Turnover Rate: 0.00/hr Experimental BTR #2: 0.00/hr Experimental BTR #3: 0.00/hr Metric Ratio Report For 8K Cache Bufwaits Ratio: 0.000000% Buffer Turnover Rate: 0.00/hr Used Buffer Turnover Rate: 0.00/hr Experimental BTR #2: 0.00/hr Experimental BTR #3: 0.00/hr Metric Ratio Report For 10K Cache Bufwaits Ratio: 0.000000% Buffer Turnover Rate: 0.00/hr Used Buffer Turnover Rate: 0.00/hr Experimental BTR #2: 0.00/hr Experimental BTR #3: 0.00/hr Metric Ratio Report For 16K Cache Bufwaits Ratio: 0.000000% Buffer Turnover Rate: 0.00/hr Used Buffer Turnover Rate: 0.00/hr Experimental BTR #2: 0.00/hr Experimental BTR #3: 0.00/hr Metric Ratio Report Summary For All Caches ReadAhead Utilization: 0.000000% Bufwaits Ratio: 0.000000% Buffer Turnover Rate: 0.00/hr Used Buffer Turnover Rate: 0.00/hr Experimental BTR #2: 0.00/hr Experimental BTR #3: 0.00/hr Lock Wait Ratio: 0.00000% Sequential Scan Ratio: 0.00000% Statistics reset at: 2023-05-18 17:04:21 Elapsed time since reset: 641:43:03 Should i do something else? Best Regards, Igor ------------------------------ Igor Gomes ------------------------------

Igor asked me this question directly offline. My response for others is: It might be that the version of awk that Igor has since he is running on Solaris. The basic System V awk that comes with Solaris is broken. Some features do not work right in it and the script may be using some of those broken features. If you have nawk or gawk (Gnu AWK) change the script to use that instead of the basic awk. If you do not, you can download GNU AWK from the internet. Most Solaris installations do have nawk install in my experience. That might fix the problem. FWIW, the default AWK on AIX is even worse, but it's failings don't seem to affect this particular script. ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Just for closure... Problem was resolved. Turned out that the network guys had changed the IP address of the DNS servers, and didn't tell us. (So much for communication.) Upon udating "/etc/resolv.conf" with proper (new) IP addresses, all is good. So, everyone that mentioned that-- you were right. I discounted it because it has been working for 10 years, and I figured it couldn't possibly change without us being notified. Gotta love it. DG ------------------------------ David Grove ------------------------------

Hi Did you see any messages like this? https://community.ibm.com/community/user/datamanagement/discussion/informix-dns-warning "02/24/20 13:31:02 WARNING: Detected slow or failing DNS service response 1 time(s)." in the online.log? Regards, David. ------------------------------ David Williams ------------------------------

Hi, 3 things I check? 1. sysmaster syssesprof table. Anything doing lots of bufreads at the time? This usually indicates a query plan has gone bad and is table scanning a large table. 2. Cpu Time for Informix vs the VM/machine If something on the VM/host is taking CPU time from Informix then we have an issue. I have seen network monitoring daemons and local Informix scripts stealing CPU time from Informix. I will also include vmstat steal time in there and /proc/PID/schedstat 3. iostat service time (even though the latest Linux manals say this value is no longer useful https://linux.die.net/man/1/iostat "The average service time (in milliseconds) for I/O requests that were issued to the device. Warning! Do not trust this field any more. This field will be removed in a future sysstat version." Really? Regards, David. ------------------------------ David Williams ------------------------------

Thank you for the tips. DG ------------------------------ David Grove ------------------------------