Hi,

I'm new to Informix, but I've got an instance (OLTP system) that has been performing fine for months now, but recently has be displaying slow repsonse times for any/all SQL queries executed on it. These queries are for CC authorisations, that require sub-second responses (typically around a 1/10th of a second), but recently they've been taking up for 12 seconds!!

Therefore, can someone please suggest where best to look for probable causes, as the AIX server is only running at 50% CPU/memory, and the other 14 Informix instances running on the prod server aren't having any performance issues. Our network team have ruled out any on the connection from the application <-> db server too.

thanks in advance.

running less cpu memory is additional action i found.

So are you saying to try running with less CPU memory? Would you mind clarifying your post for me please? Thanks.

Okay so you are having queries that are now much slower than before, right?
Either this has to do with the connection having been influenced somehow, which your team ruled out though, or it has to be some internal things going on.
I'm not that sophisticated yet, but could it have been that the queries have changed during the last time? Or tdid the server have an update? Did you change ANYTHING during the time between "querytime okay" and "querytime much too long"?
Take a look at what could have changed and how those changes could have influenced the speed.
Even if at the first sight a small change could not have done the damage, it's often those small things that make the difference.

Well, according to the network, application and AIX support teams, nothing has changed - hence why they all believe it's a database-related issue that's causing the degredation in performance.

Many things can cause IDS performance to degrade so here's a few you might want to look at.

Have you tried updating statistics so the query optimizer will choose (hopefully!!) the correct indexes so it can "seek data" rather than "scan tables"?

Have you checked for excessive table fragmentation? I've read that when fragmentation exceeds 8 - 10 extents performance will start to degrade.

If you have multiple virtual ("V") segments performance will suffer , onstat -g seg will show you this.

Looking at the segment summary, there doesn't appear to be multiple "V" segments present:

ukbiprodmvrs01[/home/informix]$ onstat -g seg

IBM Informix Dynamic Server Version 11.50.FC3 -- On-Line -- Up 11 days 00:54:30 -- 1850848 Kbyte s

Segment Summary:
id key addr size ovhd class blkused blkfree
1048731 525d4801 700000000000000 595034112 7406392 R* 145269 3
1048730 525d4802 700000030000000 1300234240 15238912 V* 209641 107799
Total: - - 1895268352 - - 354910 107802

(* segment locked in memory)


Looking at the tables, the statistics are updated nightly. But some tables have multiple extents - what onstat command would show table fragmentation?

I'm looking at an old AIX server with legacy data and it's been a while since I've dealt with IDS but this is IDS 9.3 which is ancient compared to what you're using. Glad I kept a few old notes because I haven't used this in a long time.

Something else to take a look at .... execute onstat -F | pg and look at the top of the listing. You should have mostly LRU writes, some chunk writes, and no Fg (foreground) writes.

You can check your largest / most active tables individually by executing this:

oncheck -pt your_DB_nameB_owner.table_name | pg

This will show you how many extents the table has, the size of the first extent and the next extent size.

If you want to check all your tables, review the attached text file and, if you find it to your liking, change it from .txt to .sql and execute it using dbaccess. Check the results in a spreadsheet & take action as necessary.

I can recommend a few on-line sources for IDS OLTP tuning, let me know if you'd like to see them.

Thanks very much for the file - I'll run that and see what resutls I get.

If you could give me the IDS OLTP recommendations, then that would be great? Thanks again

Tough to make specific suggestions when performance tuning (DB size, hardware, user load, etc...) but the majority of these articles & attachments find common ground on most onconfig settings while some will differ a bit so YMMV.

IBM Redbooks | Performance Tuning When Using Informix Dynamic Server With WebSphere

Oninit: references tab has a wealth of good info

PRS Technologies, Inc. and Peter Schmidt - a resource for Informix Database Administration