anyway to know iqgovern queue status in IQ 1270 ?

[Former Member]

Hi All,

From reference manuals, I can know sp_iqshowpsexe can be used to display existing processes's iqgovern_priority status in Sybase IQ 1270 ;

however, if we would like to know whether iqgovern queue is full of processes or not?

If we can know that, it might be helpful to let client know why their quey do not get response sometime ASAP !

Is there any way to know iqgovern queue status in IQ 1270 ?

thanks in advance !!

Regards,

Robert

[markmumy · Accepted solution]

Robert,

I don't have an IQ 12.7 system I can test with and the manuals are a little thin in this area for that release.  My point being that the counters that I will talk about may not available until much later in the product (15.x).  So, let's assume that they are available in 12.7. 

Use the procedure sp_iqsysmon to captures stats (sp_iqsysmon start_monitor).  Collect data for all sections.  You can decide if you want to do it in real-time or have the output dumped to flat file at set intervals.  Then stop the collection (sp_iqsysmon stop_monitor OR sp_iqsysmon stop_monitor 'server' more on 'server' later).

In the full output there should be a section (towards the bottom) called "Context Server statistics".  This is the section that gives you details about the resource gate and governor.

For reference to this section use this link: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00169.1520/html/iqperf/BA...

StCntxCondVar -- Resource gate lock.

StCntxLock -- Database context lock.

StCntxNAdmitted -- Total cost of completed commands.

StCntxNOrigResource -- Maximum number of concurrent commands.

StCntxNResource -- Number of available concurrent commands.

StCntxNWaited -- Number of commands that waited at resource gate in monitor interval .

StCntxNWaiting -- Number of commands currently blocked at resource gate.

StCntxNumConns -- Current number of connections.

The counter StCntxNOrigResource is the total number of slots at the resource gate.  Basically, the total number of IQ commands that can run at one.  StCntxNResource is the current number of slots available.  If these two values are equal, then commands are waiting at the gate.  That is also shows in the counter StCntxNWaiting.

The key to this data is that it is valid for the length of the sp_iqsysmon interval.  Some counters (like StCntxNWaited) are cumulative over the duration.

If you were to run these two commands back to back, you would get the "context server statistics" section immediately.

sp_iqsysmon 'start_monitor';

sp_iqsysmon 'stop_monitor', 'server';

For example:

==============================,,

Context Server statistics,,

==============================,,

,,

STATS-NAME               ,VALUE                    ,

StCntxNumConns           ,1               ,

StCntxNResource          ,14              ,

StCntxNOrigResource      ,14              ,

StCntxNWaiting           ,0               ,

StCntxNWaited            ,0               ,

StCntxNAdmitted          ,3               ,

StCntxLock               ,Locks 0      ,Lock-Waits 0 (   0.0 %)

StCntxCondVar            ,Locks 0      ,Lock-Waits 0 (   0.0 %)

From this output, my system allows a total of 14 users to execute simultaneously (-iqgovern 14).  There is 1 connection (StCntxNumConns).  There are 0 waiting connections (StCntxNWaiting).

Hope this helps!

Mark

[Former Member · Accepted solution]

Hi Mark,

thank you so much for your great advice !!

It is really helpful to me !!

Besides, do you know where I can get descriptions for all monitor fields in iqsysmon ?

thanks in advance !!

Regards,

Robert

Hi Mark,

Please ignore above email since I overlooked your reply !!

I rechecked your reply and found your have mentioned some field descriptions on your reply !!

Now, I can find all those reference on Sybase IQ 1520 Performance Tunining Guide ( http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00169.1520/html/iqperf/BABHIEBB.htm) !

Thanks again for your assistance !!

Regards,

Robert

Message was edited by: ROBERT Chu