Scalability of Sybase ASE 12.5

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?
Who is Participating?
Joe WoodhousePrincipal ConsultantCommented:
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...
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.

nbkd72eAuthor Commented:

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.
Joe WoodhousePrincipal ConsultantCommented:
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.
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.

All Courses

From novice to tech pro — start learning today.