Years ago, still running on the old good 12.5 binaries of ASE, I remember coming across the article named “Top 10 Reasons for Developer to Upgrade to 15.0.” I recalled it recently why working on yet another migration project which entered a crucial stage of decisions and questionings: “Why should we upgrade at all? We are safe and pleased with the current version anyway.”
This course of argument is well known and is easily understood: software vendors will forever go forward (technology changes, one needs must to adjust). Customers will forever try to keep back (who likes vendor-driven projects anyway). Migrations are never straightforward. Some go smoothly. Some face obstacles. When they do – unfortunately there is a gap between what the vendors point of view is and what is the point of view of the customers struggling with the new software version. Customers get angry at the vendors not keeping 100% backward compatibility for their products. Vendors get angry at the customers not seeing that if they adjust, in the long run they will benefit from so, so many improvements in the software they use. Testing is the king. Stamina is indispensable. But sometimes what might have helped to close the gap is for the vendors to show the customers in clear numbers that the customers understand why the efforts the customers must put into a vendor-driven project are, in the long run, good mainly for the customers.
I have myself went through a non-trivial migration something like half a year ago. It has been an enlightening experience. I have learned much. I have suffered much. πάθει μάθος. For me than it was also an act of faith. I knew where I was coming from. I did not know where I’m heading. I definitely knew from the white-paper material what I may expect. But “seeing is believing.” I did not see.
Now I do. And I want to share what I see now with those who (still) do not and (still) keep circling around the question “why getting the trouble of…”. I want to show some simple numbers and graphs that will help to see ahead of believing.
Below is a summary of the statistics taken for a period of a year from the ASE systems of a customer that went through migration from 12.5.4 to 15.7. The migration has taken place in June. In the graphs, the data is not distributed equally (sorry for that). Still, the graphs are accurate and straightforward. The data is taken from sp_sysmon reports taken over a year. The titles in the graphs are taken from there – so it will be easy to understand what things stand for.
I’d start from “the gains” – the throughput.
Committed Xacts/Rows Affected/Procedure Requests – may indicate an overall throughput of the DB server. Since the migration in June, the number of transactions (xact) performed per second has almost doubled (1K to 2K), the number of rows affected (insert/update/delete) – general volume of work ASE performs – almost tripled (leaped from 15K to 40K), procedure requests rose 50% (from 2K/sec to 3k/sec). This indicates that overall, 15.7 migration has almost doubled the throughput of the DB systems. You may interpret it differently. Still, based on the reports from end users, since the migration there has been an unequivocal consent that the applications run much more smoothly and much faster. However inaccurate the comparison is (since it includes both user & system activity within the server), the end result is very positive. No question marks here.
What has is “costed” the server?
DB Load: There has been a 50% leap in DB Load since the migration in June (40% to 60%).
The leap in IO busy is something post-migration DBAs often observe. I’d say that this indicates how much more effective ASE is now to handle IO requests. Less waits – more work being done. ASE is sitting much more tightly over the OS now and squeezes much more juice from the same HW it runs on. The leap in CPU busy may initially be taken as a warning sign. Actually, it is and it is not. What it means, simply, is that ASE now works better – waits less, spins less, context switches less. It services the calls – more you send, more you will receive. Yes, it does pose questions as to how well your HW will cope with the workload that is shifted back to it. If before the migration weakness of your HW may have been masked by the internal constraints in ASE, now it will be disclosed. I’d say – be sure your HW is strong enough for the workload your application may generate. ASE will dispatch the workload to OS much more efficiently.
You may see it here:
I think the numbers speak for themselves. By the way, it is so good ASE now reports in sysmon both on its load and on OS load. I have seen customers that see OS load being reported as much higher than ASE load. I’d recommend these to question their ASE HW. It is about a time.
Shift in locking is somewhat marginalized by other crucial changes in ASE code, but it is not something you should ignore. Consider this:
There was no shift in the DB server to row level locking for user tables. The change reported by the graphs is pure result of the changes in ASE internals. I’d like to throw this as a challenge to someone from the ASE developers team to explain the numbers. I’d point a finger at tempdb…
Locking has benefited from this RLL shift too:
I will stop here. There are more things to post, but the graphs above will suffice for this retrospect.
It is very interesting to observe how the profile of ASE one has been used to has changed after migration. Also named identically, moving to the 15.7 DB version means moving to the DB system rewritten almost completely from scratch. DB optimizer, the crucial code-line for a DB directly influencing the way the same application code performs, has been rewritten from scratch. DB kernel, another crucial code-line for a DB, directly influencing the way DB interacts with the same operating system and the way in manages its internal tasks, has been rewritten too. It is frequently ignored by Sybase ASE customers/DBAs, but upgrade from ASE version 12.5.x to ASE version 15.7 is equivalent to moving to a new DB system, albeit with almost complete backwards compatibility for the code the client applications run and requesting relatively mild learning curve.
So, to summarize, yes it sometimes hurts and yes sometimes one may start questioning decisions and getting heated up. What one has to keep in mind is that if one is systematic at checks and works tightly with vendor’s support, in the not very long run one will benefit from the migration – manifold. Here I have given an example of an overall increase in throughput for the same application running on the same HW host – speaking in gross numbers. What about non-general tasks? There has been a task I remember in the old 12.5.4 systems to re-think the way we update table statistics, since it has started to overflow the maintenance window of… 36 hours (!). The task is forgotten now. Not because there is nothing to rethink (hey, there are new features, aren’t there?), but because its urgency subsided. It fits neatly into less that 20 hours out of the 36 hours window. Other things may be spent time on…
One final note. Customers (and DBAs from the customer’s side) often blame tech-support for not being there, or for not understanding the urgency of the situation &c &c. It is very easy to forget that no same thing looks the same to all. It is always a matter of context and always a matter of point of view. I am happy for being involved in the past migration to 15.7 not only because in the long run it has been an event that benefited the customer I worked for, but also because I have been able to understand the people I worked with on the other side of the project better and I have learned how to communicate my needs right so that they get the right light in the perspective which is not mine. I have also made some good friends on the way, which is always worth the effort. Mine paid well.