Ok. So ASE 15.7 has been around for quite a while now. But how many of us are really familiar with this new kid around the block? How many of us have risked putting this beast to work in large production environment (by “us” I do not mean SAP users, but traditional ASE users community) ? How many of us have performed in-depth analysis of the distinct behavior of ASE 15.7 – in either threaded or process kernel mode? Are we ready to use it without professional skepticism?
I have been playing with ASE 15.7 in my lab for quite a while. But the things you learn in a lab are still quite different from the things you learn when you take a full-sized ASE built over enterprise hardware and stress it with real application code. Hm… How much can you really learn about a product before putting it to hard work in real-time environment?
I have found through my professional experience that ASE 15.7 is still looked at with caution by ASE users (despite the fact that SAP has been selling by far more new installations on ASE than on Oracle since its decision to include ASE as its default DBMS system). There have been good reasons for this caution. Financial companies seek stability and ASE 12.5.4 has been a faithful stallion for years – stable as a rock. Migration to ASE 15.x has been a serious project ever since ASE 15.x was released – adapting to new optimizer, learning to live with difference statement cache management, RLL system, sundry new features &c.
With ASE 15.7 things got even more complicated. If originally moving to ASE 15.x from 12.5.x involved learning to live with new optimizer version, now one has also to start accommodating to the new kernel! More things to check. More open questions. More uncertainties.
I have done comparative checks for both 15.0.x and 15.5 and now I am in the process of performing extensive checks for 15.7 version (threaded + process kernel modes). From what I’ve seen so far (and I only start performing the tests), 15.7 begins to look like another workaholic. You will see it by yourself in a moment.
I have started to work on an upgrade project (12.5.4 -> 15.7) for a large financial company some time ago. I decided to share findings from this crucial project I lead with the professional community. I think you may find it informative for similar upgrade projects you face.
A brief description of what we are dealing with. I have set up two ASE servers on the same enterprise hardware (expensive Sun SPARC VII+ server running on enterprise EMC SAN). Both servers have identical HW to utilize. Both servers are configured in a similar manner (caches, devices, general configuration settings). I have recorded workload of the PRODUCTION environment that will be upgraded ultimately – something like 3-4 hours of workload from the peak period. The PRODUCTION dump has been loaded into both ASEs. ASE 15.7 data underwent recommended upgrade procedures. Based on the workload recorded before I am stressing the two otherwise identical ASE servers from the identical amount of clients running the load. This is almost pure laboratory environment – very stable for testing. While the servers are being stressed, I am recording performance characteristics using various tools. I am stressing the systems differently to push the servers into situations that have caused problems in earlier 15.x version. Ultimately I will have a pretty extensive picture of the way 15.7 ASE behaves under the same stress with different configurations (different kernel modes, different configuration setting, &c). In the end I will have to decide (a) whether 15.7 ASE will be able to face the workload ASE 12.5.4 is facing for years without any issues, (b) which kernel mode is it better to use for this client and (c) how to configure the server in order to get the most of what it has in it. Quite exiting…
What I will present here is some initial differences in respect to certain performance metrics visible through sysmon reports (these are still useful to get an overall sense of ASE behavior, before diving deeper into MDAs).
I’ll start with a general picture: ”the same” ASEs stressed by “the same” code in “the same” manner. Below is the throughput both servers get to – the left side throughout the post belong to ASE 15.7 – threaded, the right side is 12.5.4. Ignore the specifics of the load (there is an explanation for the waves in the load, irrelevant here) – what is of interest presently is the comparison.
What we may see above is that “the same” workload generates different throughput on 12.5.4 & 15.7. Whether 12.5.4 succeeds to pull an average of 35 transactions per second, 15.7 pulls through almost 90 – almost three times more. Whether 12.5.4 succeeds to execute 600 procedure requests per second, 15.7 get close to a 1000. Almost twice the throughput for the same workload. I will increase the load in the future, so the things will get even more interesting. The ratio is even greater. Here this high number of procedure requests is the LWPs ASE is generating while being bombarded with dynamic prepared statements from a JAVA client.
We all know that the threaded kernel gets the IO requests treated completely different. We may see that this has contributed to the reduced task switching activity within ASE:
In general, ASE 15.7 makes only a fifth of the task switching compared to 12.5.4. Great news.
Another interesting issue is to see what the RLL has done to the ASE. We all know that ASE 15.x has moved its system from APL to RLL (though to me this thing is not given due stress in migration literature). Quite a change. Has anyone considered what it does to the ASE? E.g, the impact on TEMPDB – this busiest DB – which is now dealing with RLL system tables rather than APLs? Take a look at the way ASE behaves now with its catalog moved to RLL. Again, we have the same workload:
ASE 15.7 has almost zero shared table requests (20 times less), and 40 times more shared row requests. It is difficult to compare these metrics across different servers, but since we are talking about the same workload on the same DB, the difference in the numbers here is a pure footprint of the changed system table locking.
How about the heaps? Or, how about a general amount of data ASE’s inserts/scans due to its internal differences?
I was always wondering, why my systems had such a great number of APL HEAPs? Well, it is not user DB design responsible for it, but its system databases (TEMPDBs including).
An interesting thing about 15.7 is its ULC utilization. What I have noticed from the start is that ASE 15.7 requires four times as large ULC than its 12.5.4 brother to handle user requests in a comparable manner. Take a look at this:
ASE 15.7 required 16K ULC to accommodate “the same” transactions that the code generated as opposed to 4K ULC that has been sufficient for ASE 12.5.4 to get ~50% End Transaction ULC flush ratio (Both ASE are still 2K – due to the setting of the target production database to be migrated).
Do you remember LRU/MRU strategies? Did you catch ASE using it in 12.5.x? Quite seldom (unless you pushed it there). ASE 15.7 seems to start using this strategy with more intelligence. For example, you may notice throughout the graphs that there has been high CPU utilization in the start that went down later on. What caused this utilization peak was SQL code executed very frequently which was used to retrieve its few records using very inconvenient index (situation quite common and hard to catch – no table scan, canonical access plan, though the one that may be improved VERY significantly). In addition to the surge in CPU utilization, what it has caused in 12.5.4 is constant cache flushing. 15.7 kernel “has understood” the problem on its own and activated the right cache strategy:
What about large IO and data caches? 15.7 used it more frequently for tempdb caches than for default data cache. Seems an intelligent decision again. 12.5.4 due to its numerous APL HEAPs has often cached more of its temporary data than its user data:
Few last comparisons: ASE 15.7 gets fewer IO requests, but uses much more memory allocation internally:
And a final comparison for today: ASE 15.7 is much more sensitive to bad statement cache/procedure cache configuration. ASE 12.5.4 will perform badly with statement cache on or over-sized. ASE 15.7 will fare even worse. So DO TAKE GREAT PRECAUTION with statement cache/procedure cache sizing. Test before you decide on the correct size for each and watch for spinlocks (in 15.7 you have its own MDA at last, though it is still possible to monitor it with a few old dbccs). Procedure cache/procedure manager spinlocks:
At the same time, SSQLCACHE spinlock and statement cache utilization have been treated well:
Quite a few changes. Why should anyone care? First of all, when you are sensitive to how your ASE box works you may address with greater care those specific areas where behavior has changed most. For example, ULC sizing? Data-cache sizes and pool divisions? Statement cache and procedure cache sizing?
In ASE 12.5.4 there has always been an issue with large data cache pools for default data cache. What this usually caused your ASE is to perform bad table/index scan, often using large IO, thus flushing your cache using badly utilized LRU strategy. ASE 15.7 is more intelligent here. This is not to say that you do not have to monitor your server anymore and see which configuration fits it better, but at least your ASE now protects its data caches better.
I have seen a few upgrades failing due to over-sized statement caches. I have seen some installations that caused LWP creation rates going very high and killing ASE engines. In 15.7 an over-sized statement cache is still an issue (with or without streamlined/reuse options set). But LWP creation rate has been better addressed.
What I have posted here is very raw and partial. It will take me much more time to refine the data and replenish it (e.g., threaded vs process). I will post more information here in future . So far I may say that I am getting to love what I see. ASE 15.7 is quite a stallion too – a competitive alternative to its older brother. It gets better throughput. It works more economically inside. And for those being afraid of seeing one OS process running threaded ASE instead of tens running 12.5.4 please DO take a look at the number of LWPs the dataserver process spawns in both versions. You will find that the threaded 15.7 ASE gets much more work done with only a fraction of OS LWPs as opposed to ASE 12.5.4. Keep it simple idea? Looks lovely.
ASE 15.7 seems now to be truly “production ready.”
I hope I have made you at least curious and eager to test it through.
I will update you more on this new beast in the near future.