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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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

New style of hardware planning for Microsoft Exchange server.
Learn how to use the free Acronis True Image app to easily transfer data between iPhones and Android phones.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 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