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.
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.
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.
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.