connection with name INT:flushstats

[kimon_moschandreou]

in IQ 15.4 ESD#3 we have a process with the following sp_iqconnection and sp_iqcontext. It seems it is a failed system process and I need to find why it appears and how to remove it. I cannot kill it and for the moment I need to restart IQ. I shall enable some monitoring in order to catch the moment it appers, but maybe someone may help us resolve this

Thank you in advance

Kimon Moschandreou

sp_iqconnection

ConnHandle

Name

Userid

LastReqTime

ReqType

IQCmdType

LastIQCmdTime

IQCursors

LowestIQCursorState

IQthreads

TxnID

ConnCreateTime

TempTableSpaceKB

TempWorkSpaceKB

IQconnID

satoiq_count

iqtosa_count

CommLink

NodeAddr

LastIdle

MPXServerName

LSName

1000027833

INT: FlushStats

unknown (0)

NONE

0001-01-01 00:00:00.0

0

NONE

0

0

2013-04-17 07:42:52.0

0

0

30725

2

0

NA

NA

0

(NULL)

(NULL)

sp_iqcontext

ConnOrCursor	ConnHandle	Name	Userid	numIQCursors	IQthreads	TxnID	ConnOrCurCreateTime	IQconnID	IQGovernPriority	CmdLine	Attribute
CONNECTION	1000027833	mis	SYS	0	0	0	2013-04-17 07:42:52.0	30725	No command	NO COMMAND	NULL

[markmumy · Accepted solution]

Can you also let us know how large the catalog (.db) and transaction log (.log) files are?  I have been seeing this issue at another customer where the transaction log is rather large. 

Also, send us the contents of your CFG file.  That same customer also had issues with there not being enough threads in the catalog to process this.

Once the tran log was pruned and catalog thread resources increased the issue "appears" to have gone away.

[kimon_moschandreou · Accepted solution]

.db is 28MB and .log 14MB, but the number of threads is not set, there is neither -gn or -iqmt in the cfg. I shall monitor the number of used threads  during the day not it uses 371 out of 917. In the past (version 12.7) we had a thread issue (IQ freezing when number of used threads was reaching the limit) and we had set a really big value which was removed when the server moved to a machine having the double cores (from 4 to 😎 At this point we found that without any setting in cfg the system was never using more than 60% of the default number of threads and we removed -iqmt. I am afraid we should be more careful and had set some value after going to IQ 15.4

[saroj_bagai · Accepted solution]

You can check -gn  value from ..stderr and then adjust it to atleast  1.5 times gm value

[markmumy · Accepted solution]

Generally, you don't need to set -iqmt.  So that is fine.  Not having -gn set in IQ 15 is not recommended.  We documented that we want -gn set to 1.5 x -gm.  It wasn't implemented in certain versions start_iq, so its up to customers to put it in there right now.

Your log file and catalog don't look big at all, so that's not the issue.

Are you sure that all 8 are true cores?  If the machine has hyperthreading turned on this will make IQ think there are 2x as many cores.  All sizing is based on physical cores, so when we see 2x we size for 2x.  If you have hyperthreading enabled, we urge customers to set -iqnumbercpus to match the physical core count.  If you are unsure, you can always run $SYBASE/SYSAM-2_0/bin/cpuinfo.  That will list the number of physical and logical cores.

[kimon_moschandreou]

After adding -gn in cfg the phantom process didn't reappear. I keep monitoring it thank you very much for your help

[kimon_moschandreou]

I am going to set -gn to 1,5*-gm, thank you for the notice.

Concerning the CPUs, yes they are physical cores, hyperthreading is disabled

[saroj_bagai]

INT:FlushStats internal connection from mpx event connection such as ev_opendb, ev_iqmpxstatus (internal procedures used by the inter node communication layer to keep nodes in an mpx in sync. Internal conneciton cann't be killed, Did you recently upgraded your server? did you alter database upgrade using dbisql

[kimon_moschandreou]

Hello Saroj,

The server was recently updated from 12.7 version to 15.4. Should we run alter database upgrade?

[saroj_bagai]

how server was upgraded? please follow complete upgrade instructions from installation guide. Save copy of .db and .log. Coordinator should be started with -iqmpx_sn 1 and -iqro 1 and restrict user access to IQ server. and run alter database upgrade using dbisql

[kimon_moschandreou]

This is a simplex and the upgrade was done by following this http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30056.1540/doc/html/san12...

There is no "alter database upgrade" since we had a 12.7 IQ and migrated to 15.4. The migration took place during the weekend and this phantom appeared during monday night after a day of extensive monitoring. The specified ConnHandle, IQconnID do not appear in iqmsg at all. On Tuesday we restrted IQ in order to remove this process but during the night another phantom appearred, again without any connhandle or IQconnID mentioned in iqmsg. This is the phantom I mention in this case. Yesterday I had an event running for the whole day recording every one minute sp_iqconnection in a table in order to catch a new phantom, but no phantom appeared. We know that we can remove it with an IQ restart but I am afraid it will appear again if we don't find an explanation. We are lucky it doesn't create any versions

[saroj_bagai]

And also make sure -gn is set to 1.5 times the value of -gm

[kimon_moschandreou]

-gn is not set and I am going to do it.

[markmumy]

Can you also let us know how large the catalog (.db) and transaction log (.log) files are?  I have been seeing this issue at another customer where the transaction log is rather large. 

Also, send us the contents of your CFG file.  That same customer also had issues with there not being enough threads in the catalog to process this.

Once the tran log was pruned and catalog thread resources increased the issue "appears" to have gone away.

[saroj_bagai]

INT:flushstats is internal event and is being investigated by engineering. increasing -gn value and managing tran log might fix this issue.

[harpreet_chaudhary]

The next time you see the phantom process and it has been around for a while, try executing drop connection , if that fails then can you collect two pstacks at 5 minute intervals. I would like to see what the threads are processing at the time. 

[harpreet_chaudhary]

apologies, since this is an internal connection, drop connection will not work. Just collect 2 pstacks at 5 min interval after you see the connection around for more than an hour or so.