Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working from home is a dream for many people who aren’t happy about getting up early, going to the office, and spending long hours at work. There are lots of benefits of remote work for employees.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

610 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