Solved

Sybase network engine

Posted on 2006-11-28
14
570 Views
Last Modified: 2013-11-15
How to find out which engine is the network engine for a session?

Also, if I defined a listener on a set of engines, say engine 1, 2 and 3. When a user connects through that listener, would only one of those engines be assigned as the network engine of the connection?
0
Comment
Question by:nbkd72e
  • 9
  • 5
14 Comments
 
LVL 19

Expert Comment

by:grant300
Comment Utility
ASE 12.5.1 introduced the dynamic network listener facility so I presume you are on that version or higher.

The answer depends on whether you are running 12.5.1+ or 15+.  Prior to v15, I believe you are out of luck.

In ASE 15, there is an additional field on the SYSLISTENERS table; NODEID.  It is also present on the SYSPROCESSES, SYSSESSIONS, and SYSENGINES tables.  You can join SYSPROCESSES to SYSENGINES and SYSLISTENERS to get what you want.

Regards,
Bill
0
 

Author Comment

by:nbkd72e
Comment Utility
I am running ASE 12.5.3
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
I don't have 12.5.3 handy to look at but I am willing to bet they did not make those changes to the system tables prior to v15.

You kind find it by doing:

use master
go
sp_help syslisteners
go
sp_help sysprocesses
go

You are looking for the nodeid field in those two tables.  If they are not there, you will have to wait for v15.

BTW, why are you trying to find out what connections are on which listeners?

Regards,
Bill
0
 

Author Comment

by:nbkd72e
Comment Utility
Unfortunately, nodeid is not available.

But, is it safe to say that if the process status is "recv sleep" or the cmd is "AWAITING COMMAND", the engine # is the network engine #?
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
It might, but I would not count on it.  Unless you explictly create an affinity for a login to an engine, it will float.

Again, what would the information net you?  Can you tell us what you are trying to accomplish; perhaps there is another way....

Regards,
Bill
0
 

Author Comment

by:nbkd72e
Comment Utility
I don't think the network engine for a login will float after the initial hand-shaking.
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
What si this; some kind of bet two DBAs made in a bar one night after 9 Coronas? ;8-)

Seriously, why would anyone care?

Bill
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:nbkd72e
Comment Utility
This is a serious business case.

We have a extremely high volumn financial system which unfortunately runs on a ASE 12.5.3. (If it is Oracle, things may be much simpler).

The ASE server machine is a very high-end Sun box, with 48 CPUs and 100 GB memory. The storage is SAN. For some reason, Sybase doesn't scale beyond 32 engines. So even if we have 48 CPUs on the machine, we only configured 32 ASE engines.

There are two types of clients accessing this ASE:
1. Automated jobs which runs in a machine farm (with several hundred machines) doing complicated financial calculations, from time to time. Those jobs only need to read data from the database.
2. About a hundred business users who access the ASE through their financial applications, which need to read and update the database.

The problem is when the automated jobs kick off, they send a huge volumn of queries to the database. We have observed several thousand requests per second. Most of the queries are very simple, with only a few tables in the join and always against the primary of the main table. The average execution time for a query is less than 100 ms.
Due to the huge load introduced by the jobs, the business users could not get their work done, as the application is taking too much time to do any database access.

We tried setting up two engine groups and execution classes. Then bind the jobs to one and the business users to the other. But this didn't work. Sysmon report shows that a large portion of the task switches are network related. As the client connection would be assigned to any of the engines and stick to that one for all its network I/Os. The execution classes and task engine affinity only affects the query execution. So when a business user connects to the database, ASE would assign the engine which has the least amount of load at that moment to the user connection, which may end up with one of those eninges in the jobs engine group, if the jobs are not running at that time.

What we are looking for is a solution which will allow us to control the network eninge allocation, as well as the mechanism to verify it.

I hope this provides the information you are looking for.
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
Now I recognize the problem; you have another open question about Sybase scalability that Joe Woodhouse and I have commented on.  This is something like a SUN 15K box.

I remember seeing the large number of network-related task switches in the information you provided for the other question.  You might want to try increasing the size of the default network packet and making sure your client apps are setup to use it as well.  BTW, that is Sybase's network packet size as, obviously, ASE has no control over how big the packets are on the physical network.  What increasing it does is push the overhead of breaking a transfer into bite size chunks that can fit in a physical network packet down to the protocol stack and/or the network adaptors instead of making Sybase do that lower level bit twidling.

You may find that things get somewhat better just by doubling the default from 512 to 1024 bytes.  I have had good success on applications that move larger chunks of data per query using network packet sizes as high as 2560.

If you look at the task switches with and without the application farm running and/or can charactorize the amount of data associated with each typical query, you can tell if this will do you any good or not.

BTW, another way to segment the connections is by having ASE listen on different ports and having the applications connect with different ports.  Make two entries in the interfaces file on the server, each with a different port number.  Then, out on the application farm, change that interfaces file (hopefully it is shared off of a network drive somewhere) to point to the new port number.

The IP address or machine name AND the port is included in the ADDRESS_INFO field in the SYSLISTENERS table.  Unfortunately that doesn't allow you to tie the information to the SYSPROCESSES table until ASE 15.

I presume you are already having your application farm applications set the APPLICATION_NAME field in SYSPROCESSES.  This is done via Ctlib at connection time.  I also presume that you are using a different login (SUID) for the application farm apps as opposed to the end users.

I will think about this a bit more and do some research to see if we can come up with some way to enforce affinity for you.

Regards,
Bill
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
O.K. here is an approach that should work for you.

I am going to outline the approach and let you dig through the syntax in the manuals.

1)  Define execution classes for your interactive users and your app farm.  You can start with the pre-defined EC1, EC2, and EC3 classes or make your own custom ones
2)  Bind the execution classes to the logins.  This requires that you have different logins for the users and the app farm applications

This alone might be enough to solve your problem.  You can give higher priority to the interactive users logins than that off the app farm.  Should you still need finer grain control, you can:

3)  Create two engine groups and add the appropriate number of engines to each.  One group is for the uses and the other for the app farm
4)  Create an affinity between the execution classes and the engine groups

What you have now is a firm segmentation between the engine resources that the app farm can use and those that the interactive users will have.  The down side is that you have lost the ability to let the resources float where they are needed.

Try steps 1 and 2 first and see if that doesn't even out the response that your end users are seeing.

BTW, you can find references to this kind of stuff in the "Performance and Tuning - Basics, Chapter 5 - Distributing Engine Resources" and, of course the SP reference for specifics on the system procs to set this up.

Regards,
Bill

Regards,
Bill
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
Hi again:

If you run sp_sysmon over a busy period of time with the current number of engines and again with a larger number of engines, we might be able to figure out where the contention is.  You ultimately want to run more engines since you have CPUs sitting there doing nothing.

BTW, how many network adaptors are use on this machine and how is the network configured?  I am wondering if you have all the traffic going through a single Gig-e connection if you are not starting to see contention on the network adaptor.  Just a thought.  If that is the case and you can get your network guys to help, you could split the interacitve users and app farm between adaptors and only have to change the interfaces files.

Regards,
Bill
0
 

Author Comment

by:nbkd72e
Comment Utility
We have already tried that approach, and it doesn't solve our problem just by itself.

The problem is still the network engine. Please see the details in my previous post.

We are now trying this approach, which seems to work well:
There are two network cards on that machine. So we created two listeners for each card (IP). Each listener is also enabled on just one engine group. For example, listener #1 is on engine 0-15, which are part of the jobs engine group (group 1) and execution class (EC1). Listener #2 is on engine 16-31, which belong to the second engine group (group 2) and execution class (EC2). We bind the jobs logins to EC1 and the business user logins to EC2.

Again, now we need some mechanisms to verify our hyperthesis on how the network engines are assigned to connections.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 250 total points
Comment Utility
So you have two network cards and two listeners for each card.

How are you distinguishing the jobs versus users connections besides login?  If you use seperate IP addresses and/or Port numbers to segregate the jobs and users connections, you can ensure that the connection comes in on the listener you want it to.  If they are mixed, a jobs connection can come in on the users listener (running on the users engine group) and then the EC binding will schedule the actual work on the opposite engine group.

Some other things to look at include:
1)  Set TCP_NO_DELAY to ON.  The default behaviour is for the TCP stack to wait a short period to see if more traffic headed for the same node comes in and can be added to the existing phyiscal packet.  In your environment and with the 100ms response times you are seeing, you should definitely configure this to no delay.

2) Make sure your network (SUN network cards on out) are not the bottleneck.  Two network cards, even Gig-E may be more than enough to handle the aggregate average traffic volume in bytes per second but they could still be introducing latencies.  Have a network person look at the overall and peak loads.  Also, as a quick experiment, see if SUN will loan you a couple of additional network cards.  The rule of thumb is that it takes about one Hz of CPU capacity per Hz of Ethernet/TCP/IP.  That means a Gig-E card running hard is going to consume most of a CPU.  You may have to spread the load out just to avoid swamping the network driver thread.

BTW, what model machine is this and how fast are the CPUs?  (SPARC III at 1.2GHz???)

3) You might try lighting up more network listeners.  Right now you have four listeners which means all of the task switching is happening on just four of the 32 available engines.  More listeners spreads the load out and reduces the number of task switches on any one engine/CPU.  It also lowers contention for the network resources.  Listeners are free and easy to implement.

That's it for the moment.

Regards,
Bill
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
Thanks for the credit.

I am curious what the outcome is/was.  
  Were you able to lower the network context switches?
  Have you gotten past the 32 engine point?

Regards,
Bill
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now