Pinned topicSlow Response Times

‏2010-08-24T11:46:27Z
|Tags:

Answered question
This question has been answered.

Unanswered question
This question has not been answered yet.

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.

The I/O queues seem to be allright. Though bufwaits may be too big. Try to compare it against bufwaits on other production instances which don't have any performance issue. If the difference is huge it may worth to add some more buffers (BUFFERPOOL parameter).

You should also check that you have enough poll threads to service the amount of connections you have. It is recommended to have 1 poll thread for ~250 connections.

Re: Slow Response Times

The I/O queues seem to be allright. Though bufwaits may be too big. Try to compare it against bufwaits on other production instances which don't have any performance issue. If the difference is huge it may worth to add some more buffers (BUFFERPOOL parameter).

You should also check that you have enough poll threads to service the amount of connections you have. It is recommended to have 1 poll thread for ~250 connections.

Re: Slow Response Times

The number of poll threads & number of connections can be set via NETTYPE parameter.

For example:

NETTYPE onsoctcp,1,200,CPU

In the above example 1 means one poll thread, 200 - maximum number of expected connections, CPU means that poll threads will be handled by CPU VP(s). For more than one poll thread it is recommended to set NET instead of CPU if you have more than one poll thread.

'IO idle' is the normal state for i/o threads, which means there is no work for them so far.

Regarding the BUFFERPOOL. As I already said, I suspect that 'bufwaits' may be too big, but I can't tell you that I'm sure without real experience with such instance. You mentioned that there are other similar instances which work ok. If there is a big difference between "bufwaits" of this and "normal" instance, I would suggest you to increase the amount of buffers (+10-20k). Be aware that this will affect IDS memory utilization! And you have to bounce the instace so parameter change could take effect.