Scalability of Sybase ASE 12.5

Posted on 2006-11-17
Medium Priority
Last Modified: 2012-06-27
We are running a very large finacial system on Sybase ASE 12.5. At peak time, there could be thousands of queries per second.

The database is running on a 48 CPU Sun box with Solaris 8 and 100 GB memory. Now the whole database is cached, and no physical I/O is detected.

However, we found out that with more than certain amount of CPUs, the overall performance degraded. So even if the machine has 48 CPUs, we only gave ASE 34 of them.

Does anybody have similar experience with ASE? Is this a Sybase scalability issue? Any idea how to solve it?
Question by:nbkd72e
  • 2
LVL 24

Accepted Solution

Joe Woodhouse earned 900 total points
ID: 17970789
Ok, you've got two separate issues here.

Firstly, is ASE scalable to more CPUs?
Secondly, will your environment benefit from more ASE engines?

The first question is simple - yes, ASE 12.5+ can go well above 48 CPUs, and all else being equal, you'll continue to see close to linear scaling.

So why is performance degrading when you put the number of engines up?

Almost certainly this is a concurrency problem - more engines means more database processes running simultaneously.

This increases lock contention - first because more processes are attempting to acquire locks at the same time, and secondly because any one process might now be waiting longer to get its lock.

It also increases contention on the transaction log of your database, if the processes are doing writes.

Tempdb also becomes an issue as you push the number of concurrent users up.

We don't yet have enough information here to say which of these is your problem - and it could be all of them, or any combination, or plenty of other things.

It's time to go into major performance troubleshooting mode! Push the number of engines up to a number that you know causes problems (maybe 40?), fire the application up, give it 15 minutes to warm up the caches, and then run (in separate sessions, at the same time):

-- session 1
sp_sysmon "00:10:00"

-- session 2
sp_object_stats "00:10:00"

-- session 3, after the first two have finished
exec sp_monitorconfig "all"
exec sp_countmetadata "open databases"
exec sp_countmetadata "open objects"
exec sp_countmetadata "open indexes"

Post the output here! We'll know a lot more about what the problem is at that point. Some of it you can fix pretty easier with some ASE tuning. Some will require changing your database schema (locking schemes, possibly indexes). Some (most?) of it will be due to transaction and code design in your application, which you probably can't do much about...
LVL 19

Assisted Solution

grant300 earned 600 total points
ID: 17972329
I agree with Joe; you have one or more kinds of resource contention.

ASE gives you all kinds of control over internal resources in order to reduce contention.  For instance, you may have a problem with SPINLOCKS on the cache(s) as you have more engines and therefore more threads contending for the same resources.

Try partitioning your caches so that there is a spinlock on each partition.  This can alleviate the problems in this area.

Tempdb is another area that may be giving you problems.  Try creating at least one additional tempdb and see if that helps.  There are a couple of different ways to assign processes to tempdbs where you have more than one; pick the one that works best for your situation.  Obviously, doing it based on login id is no good if this is a web application with a single internal login between the app server and the database.

Log contention can be another issue.  You don't seem to have an I/O problem but then again you are on a very big machine.  You could have log contention internal to the database and not see an I/O bottleneck at the hardware and O/S level.  There are some games you can play with Log Caches, log buffer sizes, etc.  

In the most extreme case where you can't solve log contention any other way, you can split the tables up into multiple databases and abstract them into a single database with views.  This helps because each database has it's own log.  You do have to balance the hot objects between databases for this to work otherwise you have just moved the problem around.  If you have one hot object, you might try moving just that one table off onto another Db to see what happens.

Not know much about your application or it's architecture makes it kind of difficult to come up with specific suggestions.  Do Joe's sp_sysmons and lets see where you are.


Author Comment

ID: 17973122

When you say "ASE 12.5+ can go well above 48 CPUs", have you seen this type of systems? Sybase could not even give us a reference of its clients who run systems of such size.
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 900 total points
ID: 17973631
Uh. I thought Sybase have benchmarks on systems of that size and above... Lemme see. Biggest unclustered ASE I've personally put my hands on was 30 engines on a 32 CPU box. I'm sure I've seen benchmarks published for 64 CPUs although I can't seem to find them through any of my searches... I can keep looking.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This is a Step by Step guide to performing a Non-Destructive Windows 10 repair which answers frequently asked questions as to exactly what is lost, and what is kept. Enjoy...
One day, as you try to send an email from your SharePoint account, an error message appears saying: “E-mail Message Cannot be Sent.” You may have outgoing email settings misconfigured.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
If you are looking for an automated tool which can generate reports for Outlook emails and other items from PST file, then you can go for Kernel PST Reporter tool. The reports which are created by this tool are helpful to analyze and understand PST …

590 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