Multiplex dbo connections queueing up

[Former Member]

Hi All

I most probably need to log an indecent but I just need to get my head around the multiplex inner workings.

We have an IQ 15.4 ESD4.3 multiplex with 3 nodes (1 Query 2 Writer with Coordinator on the one 1 Writer)

As a norm there are 2 dbo connections named IQ_MPX_SERVER_H0 open from each node on the coordinator. When all is well there will be new connections created as

IQ_MPX_SERVER_Pnnn for each node , They execute and go away. Lately we get these connections queuing up and taking a long time to resolve.

This means that the iqgovern slots fill up and the server grinds to a crawl.

Can anybody explain the the process the multiplex follows and what these connections are doing or potentially waiting for?

regards

Johan Bornman

[tayeb_hadjou]

Hi Johan,

They are related to Internode communications (Heartbeat and Pooled)

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01839.1604/doc/html/san12...

Regards,

Tayeb.

[Former Member]

Thanks Tayeb. Now the question would be what are those Pooled connections doing and on whatt could they be waiting?

regards

Johan Bornman

[markmumy]

The pool connections are DQP work units.  When the leader node of the query optimizes and runs the query, it is broken into many, many fragments or work units.  These are distributed across the DQP system and sent to the worker nodes as worker threads, seen through the pooled connections.

If you are seeing queueing and waiting behind a resource gate, you can't do much other than increase the IQ governor.  I suppose you could cut your workload, but that's generally not possible.   

If you increase the governor you will have more active jobs.  But that could cause thrashing and lots of context switching on the IQ side so that performance could actually degrade.  On the other hand, you could decrease the governor giving fewer connections more resources, and consequently faster execution time.

Are all nodes grinding to a halt?  Or just the coordinator?  If its just the coordinator, you could simply remove it from the DQP pool of nodes.

Mark

[Former Member]

Hi Mark

This points me in the right direction. DQP was set to off all over and there was no shared temp. I only recently added shared temp.

Mind you this is when this queuing started. Strangely Only my user is fully dqp enabled. but it seem IQ is now trying to manage DQP even though it is not really used

I have pondered on the idea to have the coordinator on its own

regards

Johan

[Former Member]

Hi Mark

Thinking about this it makes sense that I would have to create a separate server for the coordinator.

Do you have sizing for this in your guide?

regards

Johan Bornman

[markmumy]

I don't address this one in the sizing guide.  But, yes, most of the time I prefer to have a coordinator off by itself for non-query work.  It seems a waste to let the coordinator just sit there and do coordinator type things.  So I always add in work like data loading and dab type stuff like index builds.  I am not a big fan of allowing the coordinator to participate in queries.  Every system is different, but generally speaking DQP will use the coordinator like any other node.  That means that it could overload it with queries and not have enough cycles left over for coordinator work.

Mark

[Former Member]

Hi Mark

I think I am going to log an incident. From our discussion I understand the process but I get this queuing of the pool  connections a lot even though at times there are only loads or the system not particularly busy.

regards

Johan Bornman

[markmumy]

If there is no query activity on any node, then you shouldn't see lots of pool connections in use.  Sure, they will hang around. They are pooled connections and have a life beyond just the query that was last issued on the connection.  But they won't be active.

If you are seeing activity without queries, I would get a case open so that we can look into exactly what's going on there.

Mark

[Former Member]

Hi Mark

Just on these connections. What does it mean if the ReqType = PREFETCH? I see this a lot for these connections and even other connections if the server is very busy

regards

Johan Bornman