Solved

Scalability of Sybase ASE 12.5

Posted on 2006-11-17
4
535 Views
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?
0
Comment
Question by:nbkd72e
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 300 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"
go

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

-- 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"
go


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...
0
 
LVL 19

Assisted Solution

by:grant300
grant300 earned 200 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.

Regards,
Bill
0
 

Author Comment

by:nbkd72e
ID: 17973122
Joe,

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.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 300 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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

10 Experts available now in Live!

Get 1:1 Help Now