Monday, 15 September 2008

Persistence engines

How things came to be this way


Anyone involved long enough in software development ends up discovering a truth that is long ago known by other craftsmen and engineers: each problem is best solved with the tool more appropriate to solve it. Only problem for software is, it is such a young discipline that is all the time trying to tackle new problems not previously solved. Thus, sometimes it's very difficult to know beforehand if a tool is going to be adequate or not until you actually try it.

All software development tools, from assemblers to compilers to scripting languages, are a testament of this constant evolution of software to meet the ever increasing requirements of solving new problems using computers. It's easy to see that over time, as long as the more generic tools are developed and consolidated, more and more tools are created to attack more and more specific problems.

Structured data persistence was one of the most often attacked problems since the inception of the first computers, so at some point (Codd et al?) invented the concept of the relational database. This was so successful that the concept has survived to this day and is used extensively as a standard means of storing and retrieving data.

Reuse, encapsulation, inheritance and polymorphism was another recurring problem that software projects, specially at large scale, had to face. Thus, the object oriented programming model was invented and expressed in a number of different languages. Some of the more successful ones (C++, Java, C#) are the standard means of developing software today, usually supported by a large number of pre-built components that provide a number of useful features that can be reused across many different projects.

Sometimes when facing a new problem, software designers even find worthwhile to create a new tool just for the purpose of solving that specific problem. That's how small (and sometimes big) scripting languages are born and a lot of other tools (parsers, code generators, etc) are created. Other kind of tools that are also in wide use are "Frameworks", which are collection of software components that work together to solve a problem. The distinction between frameworks and applications lies mainly in that frameworks are designed to be used in applications, but they don't usually perform any function directly useful to the business objective of a piece of software.

Frameworks are also means of abstracting complex subjects into more manageable pieces. Specially with object oriented languages, properly designed frameworks allow programmers to selectively use the parts that they are interested in without having to learn the whole thing at once.

So, in a perfect world, developers have a wide range of options to choose form when creating a new application. In practice, this is not the case and often business standardize around a set of tools that are used across projects. Three factors conspire together. First, the complexity of tools mean that learning them represents a usually significant investment in time. Second, the biggest the number of skilled resources available for a tool makes much easier and cheaper to hire people for your shop. Third, humans have a natural tendency to avoid change and thus once you find something that has a reasonable price and works for you, you tend to keep it forever.

The end result is that usually applications are built using a set of components consistently across the same or many applications. One of the latest category of frameworks that have been consolidated are the persistence frameworks (or engines) Those are a set of classes that make your objects get stored or retrieved from a relational database without you (the programmer) even being aware of all the exchange among the application server and the database.

The object-relational impedance mismatch


Wonderful as they are, persistence engines have a problem: they are designed to store objects into relational tables. Objects have methods and data put together, plus (depending on the language) visibility rules. Tables are just tuples of data with relationships defined between them. (Digression: now it's time for me to hide from object/database purists looking to kill me since I've oversimplified concepts tremendously, but for the purpose of this discussion it's enough) Now it's easy to see that somehow in the translation, but suffice to say that the relational model is a subset of the object model, in the sense that everything that can be expressed with tables and relations can be expressed with objects. But not the other way, as you cannot express methods or polymorphism with relational structures.

Notice that there are some Object Databases that do away with the persistence engine and allow you to store and retrieve objects without losing anything on the way back. But, as you've guessed, these are not relational. While these engines someday may enjoy popularity, they are not presently part of the standard tool set in the majority of shops. And that's not likely to change in the near future. So for now we're stuck with relational databases. There have been some attempts, most notably from Oracle, to introduce objects in the relational database. While the technology does have its merits, it has never been very popular and the rest of the major industry players have not followed suit.

The performance compromise: how bad is that?


As always, there is no free lunch. For the persistence engine to work as transparently as possible, there has to be some kind of compromise. You give something, you get something. Since this is a blog about database performance, you are probably expecting me now to tell you how bad is it. Well, surprisingly not too bad. Provided that you use the persistence engine for what is intended to be used.

Here is the list of things that persistence engines are intended to be:

  1. A way to abstract reading and writing objects to your database

  2. A way to map your database tables into objects

  3. A way to perform basic operations (CRUD: Create, Read, Update, Delete) on single instances of objects (read: database tables)

  4. A way to abstract business process rules in your object layer instead of doing it in your database layer. This means that things that "sales that are above x limit should be approved by manager, and those above 2x should be approved by Finance Director" are much better easily expressed in object methods than in SQL triggers


  5. Here is a list of things that persistence engines are not intended to be:

  6. A way to avoid the relational database model, SQL language, or basic principles of database design. Sorry, you'll have to concede some things to your object model, because it is stored in a relational database. Please don't believe claims like the ones of the EVL design, they are simply not true.

  7. A way to solve every database related problem under the sun. It's always best to choose a simple engine that solves 80% of the problem that a complex one that solves the 100%. The logic here is that the remaining 20% probably has a better tool around.

  8. A way to forget about performance, the persistence engine takes care of that, does it?

  9. A way to perform analytical queries that allows you to avoid writing SQL


Please do not disregard persistence engines as useful tools just because of points 5-8. The strengths of the first for make more than enough for them, as most applications have most of their bulk in the advantages. In short, properly used persistence engines can make your application much easier to write and will not cost you too much in terms of performance. But the engine will not solve all of your problems. Special mention should be made of so-called analytical queries (those that have SUM(), GROUP BY, etc) Those are specially badly done using persistence engines, as they were not designed to do that. Consider that:


SELECT SUM(sales) as TotalSales FROM ORDERS WHERE CustId = xxxx;


Is hundred times more efficient than:

resultSet = engine.Query("SELECT FROM ORDERS WHERE Customer = ", xxxx);
totalSales = 0
foreach( rec in resultSet ) {
totalSales = rec.Sales + totalSales
}

Chainsaws are not good for juggling


The golden rule to avoid performance problems with persistence engine is to apply the same logic that you would apply if you were using a hammer. I'm sure that when facing a screw, even if you've pounded thousands of nails before, you'll not even think of using the hammer, would you? The moment you feel that you're forcing a tool to do something it's not suited to do is the time to stop and reconsider your choice of tool for the task.

In short, avoid fighting with the tool. When you start fighting with the tool, it's a symptom that you should stop and think.

Monday, 28 July 2008

Please don't swap

While browsing the database forums, it has come to my attention that some people are looking closer at their database system memory management and trying to adjust its configuration settings.

Of course, the more complex the database the more parameters you can change. RDBMS's like Oracle for example have dozens of different parameters that can alter the memory usage of the database to better fit with your application needs. This is reasonable, and I've seen spectacular improvements on specific systems just because a single memory setting was changed.

As always, the usual rules of tuning apply here, change only one thing at a time and change only when you need to do it. And like any other tuning effort, the process should iteratively make a change and test it.

Different operating systems and databases have different means of checking the results of your changes. The most expensive ones like Oracle and DB2 have even dedicated packages from third parties that can help you monitor detailed usage of resources, CPU, disk IO, cache and memory usage. The less sophisticated ones may lack those capabilities, but usually the operating system has already enough facilities (/proc filesystem, Windows Performance Monitor) to give you at least a good overview of the resources that are being used and how much.

It therefore follows that each change is usually followed by a number of tests that try to reproduce the most stressing scenarios and evaluate the performance of each. Let me say that this is sometimes a nightmare. I'm happy with the scenario "I have this 2 secons query and I need to execute it in 0.02 secs" because that makes for quick and short tests, and those allow a much more rapid change/test cycle. But sometimes when you're dealing with processes that take eight hours to execute, it's a pain in the neck to have to wait from one day to another just to know the effects of a change.

One of the closely monitored parameters is always, or should be, swap file usage. But I've read some comments that somehow were implying that if your system was not using the swap file it was not correctly set up. Let me dispell that myth once and for all.

The origins of the swap file


In the beginning there was memory, just one kind of it. The primitive computers fifty years ago did not have the distinction between RAM and disk memory. All memory was just memory, and some rotating drum or other device provided that. Then the transistor industry began the craze and the distinction between fast memory (RAM) and disk memory was born. Essentially, the CPU can use a limited (but the word "limited" applies to 2GB here) address space of memory that can be used in a matter of nanoseconds. Were this memory the only available, most enterprise databases of today would not exist, as they easily exceed that size without a blink. That's why there's a second type of memory which is usually called "mass storage" or just the disk, that has much much greater capacity. However, as always there's no free lunch, and this memory is much much slower to access. And much slower means 100 or 1000 times slower.

That's why the operating system always tries to load into RAM the program instructions that have to be executed, as it would greatly increase the time of a program to run if its instructions had to be fetched from the slow memory instead of the fast one. Other data, such as the application own data, is usually stored on disk and recalled on demand. Of course, when that happens the program execution slows down enormously, as it has to wait for the disk to retrieve the requested piece of data, but not as much as if the instructions themselves were retrieved from the disk one by one.

This balance of disk memory and RAM memory has served the computing landscape well over the last thirty years or so. As applications became more complex, they required more and more memory for its instructions to be stored. As applications handled progressively biggest amounts of data, they required the auxiliary, slower memory, to be bigger. Technology was always provinding updates in the form of fastest CPUs, fastest RAM and fastest disks. Faster RAM has remained to this day a scarce resource in comparison, with disk drives reaching terabytes while RAM merely increasing in gigabite steps. Oh, and way more expensive than disk space. Thus, RAM was a resource that was carefully administered and assigned by system owners, because a program that could not allocate the amount of RAM needed in the machine would usually stop dead cold its processing thus frustrating the run.

However, as computers were applied to more problem areas, it became clear that it was just not possible to anticipate in advance how much RAM you were going to need. Perhaps in some situations yes, were the number of applications and its usage pattern was precisely know, it was possible to specify how much RAM you would need at each point and thus have enough of it in the machine.

With the advent of the workstation and the multiuser machine this became impossible. Since everybody could use the machine at any time with any mix of applications, it was no longer possible to delicately administer the RAM so that every application could run smoothly and not stop because of lack of memory.

Operating system and CPU designers realized that it was not necessary for a machine to actually have all the memory that was being demanded by the applications being executed. It was enough to appear to have it and provide on demand the regions of memory that were used at some point. This would also relieve from the need of manual intervention and adminstration of machine resources. They could just use the fast RAM memory until exhausted, and at that point start to use the slow disk based memory as applications demanded more memory. Of course, at the price of performance, but the reasoning was that it was better to have a slow running application than no application running. Thus, virtual memory was born.

Along those basic concepts, of course the technology has evolved to a point where virtual memory has become a mix of art and science, and every operating system that is in popular use contains a fairly sophisticated implementation of it. Jargon like "commited", "dirty", "shared", "working set" and other words are abundant in the literature, and I confess to know only the basic meaning of them, enough to get the work done. But it's a fantastic facility because it allows you to basically use your machine like it had no limits on its physical memory and get reasonable performance out of it. It's a fasciating dance to watch how the machine allocates and releases virtual memory as you open and close applications or even perform certain tasks with them.

Ok, I hear you ask, what is the "swap file" then? Generically, the place in the disk where the operating system puts the portions of RAM that are being faked as actual RAM is called the "swap file" In some operating systems this is not actually a file but a dedicated disk partition or even a different physical disk.

Why swapping is bad for your server


Of course, this virtual memory thing is all well and good, but it adds one element of uncertainity to the run time of a process, something that is sometimes undesirable. In fact, real time or close to real time systems usually forbid virtual memory altogether to be able to maintain its commitment to execution time. And it works to a point, because when your machine reaches the point of too many processes competing for the scarce RAM space, the machine spends more time bringing memory pages from the disk than actually doing any useful application work. This is what is called trashing and is bad, very bad for performance.

Workstations and servers - two different worlds


All the above applies very well to your workstation or PC machine, as you're usually using it without any predefined pattern or restrictions. You start your word processor now and need some numbers from your spreadsheet, then decide to put all together in some slides. You launch programs and close them without being really aware of how much RAM you're taking up. And you're willing to put up with a small performance loss as your systems start swapping (which is the word used to denote the point where your machine runs out of actual physical RAM and starts using the disk as a slow replacement), as it surely the convenience beats the need for yourself to organize and manage the execution order of your applications. As long as the illusion works, you're living in a fantastic world of almost unlimited memory.

Life of a server is very different. There are some characteristics that make a machine we call a "server". For one thing, it does not usually sits on your desk, but lives in a conditioned place with redundant power, protected from excessive heat or cold, and its access is restricted by a locked door for which only a selected few have the key. It's components are usually higher rated in reliability than its desktop workstation counterparts, even having probably not cutting edge performance.

All that is for a good reason, because a server usually performs some specific function on which the work of many people depends. There are file servers, application servers, database server, authentication servers, proxy servers, directory servers, name servers, and so on. Sometimes, the same machine is performing more than one role, specially where there are no resource usage conflicts between each service. But all of them are usually performing some essential function and either some other system or business process depends on their availability.

A server has to be available and usually have reasonable response time. A server runs a fixed set of processes or services, and usually does not need to deal with unexpected situations like someone sending you a 120MB PowerPoint presentation that you need to print in colour 12 times.

The corollary is, your server memory usage should remain more or less constant. Except under situations where the workload exceeds the planned capacity, your server should be happily working and its resources (CPU, memory, disk) should be used fully. The essential, unforgettable word here is "fully" Fully means completely, but does not mean "in excess"

And "in excess" when talking about memory means swapping.

Memory usage is the key


Memory is the fastest resource available to the CPU. So don't waste it. Check the documentation that comes with your database. The most mature ones have configuration parameters that allow you to precisely tune the memory usage of the database, and be aware that some of them (for example, MySQL) do have different parameters for different storage engines. So your memory usage with MyISAM tables is radically different than with InnoDB tables (by the way, try not to mix the two in the same instance) As always with database tuning, measure, tweak a single parameter, measure again and continue until you reach the good enough state.

But above all, don't allow the database to swap. You'll find documentation that says that having a swap file or partition of at least double the RAM size a good thing. It does not mean that you should use that swap space. It's there just in case, not for regular use.

How much memory should I use?


You may hear from other people that if you're not using the swap file you're not fully using your memory. They are wrong, it's a common misconception that comes from the confusion between physical and virtual memory.You may hear that if your system is not using 100% of physical RAM you're not doing it right. You may hear that if your database is using more than 50% of physical RAM your're not doing it right.

Those are the kind of "myths" that I hate. It all depends on the database server, and even the storage engine used in the case of MySQL. There is no "right" answer that applies unversally.

Oh, yes, there is in fact an universal rule. Repeat with me, don't swap.

Thursday, 10 July 2008

Procedural programming and SQL don't mix well

Some of the biggest performance problems I've seen came from perfectly reasonable decisions made from fairly intelligent people. Specially, good developers coming from procedural programming languages trying to adapt the paradigms they know to the SQL language. I think that there's a strong parallelism between these cases and some of the most cited problems with people new to object oriented programming: at first, they program with the OO language but they are not really using objects, creating instead one big class comprising 90% of application functionality that essentially contains a well structured procedural program.

Data sets versus control flow

At its hearth, SQL is a set oriented language. Its main constructs and the philosophy behind them is that you're manipulating sets of rows. Years of research have been invested so that you are as abstracted as far as possible from how the database actually does that, and to do that in the most efficient way. You just write the columns that you want, the relationships between your tables and the conditions that you want to meet and off it goes. Behind that there are sophisticated tuning engines, monitors, clusters, load balancers and god knows what else, all looking to give you the results in the fastest possible way without you having to know how they actually are performed. Years of research and tons of money have been throw at the problem of how to do that in the most efficient and safe way.

In sharp contrast we have the classic procedural languages. Basically, those are a set of constructs (data types, structures, control flow) that allows you to exactly specify to the machine the instructions you want to execute. Notice that the procedural programming language is not concerned with why you want to do this or that, much less with the correctness of your steps. It just wants a set of instructions to execute on a set of data. Over the course of time and thanks to some very bright people, procedural languages have been improving, trying to make the job of the programmer easier. Mostly by abstracting, encapsulating and hiding the parts and details of the problem not relevant to your main concern.

Those are very different concepts. On one corner, a language designed only for data manipulation in the form of sets of rows with columns and relations amongst them. On the other, a set of primitive instructions that, like Lego blocks, allow you to do anything.
Needles to say, procedural languages are very powerful. They allow you, depending on the level at which they operate, to specify all the details of what you want the machine to do. They are the lowest level of abstraction, and with that comes the price of complexity. Your domain specific language sacrifices flexibility and applicability giving you in exchange much more productivity as well as a view of the world that more closely matches the problem you're trying to solve.
SQL is a domain specific language, its domain being the manipulation of sets of rows with columns that have relationships amongst them. It happens to fit a lot of scenarios, specially business automation problems where machines are replacing and augmenting manual, well specified processes. But SQL sacrifices detail, from the concrete operation of the database to its inability to perform seemingly simple tasks. How do you open a window in SQL? How do you print a report in SQL? The answer is, you don't. Well, that's not strictly true, some extensions of SQL can allow you to do such things, but they are not really SQL. They are hacks built into the language so that managers and programmers convince themselves that they don't need to learn another language.

In fact, they are really learning to fit two paradigms in the same language, but marketing is calling it the same as what they already know so that it's easier to sell them the whole thing.

Some examples


The following are real world examples of these types of problems. I've found them everywhere, from SAP to custom built J2EE applications. Bear in mind, those are general rules and you'll sometimes have a valid exception for them. But surely it's not going to be the first, second or third time you encounter this. Exceptions to these rules are very, very uncommon.

Loops versus single SQL statements

Ignoring transaction isolation levels for a moment, you will agree that the following pieces of code are equivalent:

FOREACH R IN (SELECT * FROM T WHERE T.B=2)
UPDATE T SET T.A = 1 WHERE = R.
LOOP

UPDATE T
SET T.A = 1
WHERE T.B = 2;

And yes, they do the same thing. But the seasoned procedural programmer delving into SQL tends to write the first version, because of the feeling of having complete control. But, and this should not come as a surprise, the second version usually performs much better, or at worst on par with the first one, is shorter and easier to maintain. This, taken to the extreme, makes people write:

A = MIN_INTEGER;
FOREACH R IN (SELECT VA FROM T)
IF R.VA > A THEN A = R.VA;
LOOP;

instead of

SELECT MAX(VA) INTO A FROM T;

and this time, believe me, for enough rows in T the performance difference is spectacular. Of course, in favor of the SQL statement.

Function calls in WHERE clauses

The following two pieces of code are also equivalent

SELECT * FROM R WHERE SQRT(R.A) = 2;

SELECT * FROM R WHERE R.A = 4;


But as you have guessed from the subject, the second is usually much faster, because you can use indexes to look up the values of R.A directly. Oh, yes, the latest version of your favorite database has function-based indexes, I hear you say. Well, what if you need to look up values based on the results of three different functions? Are you going to create an index for each and every different operation you perform? Function-based indexes are one of those features that can save you from disaster but only if you're one of the selected few that actually needs them, but are not useful on the general case.

Function calls versus in-line calculations

This one has already been mentioned in this blog. It is very nice to have a function like getCustomerCredit(CustID) that calculates the credit rating for a single customer based on its order history. That way, your procedural code that deals with setting up the screen display for the order entry can just do a lCustCreditLabel.Value = getCustomerCredit(custID). But when the procedural programmer is tasked to create a printed report of customer ratings, the first version is always:

SELECT custID, custName, getCustomerCredit(custId)
FROM customers....

Probably getCustomerCredit looks somewhat like

SELECT Customer.creditLimit - SUM(orders.OrderValue)
FROM customers, orders
WHERE customers.custId = :custId AND orders.custId = :custId AND orders.stat = 'OUTSTANDING';

Instead, the following alternative will be far, far more efficient

SELECT custId, custName, creditLimit - SUM(orders.OrderValue)
FROM customers, orders
WHERE customers.custId = :custId AND orders.custId = :custId AND orders.stat = 'OUTSTANDING';
GROUP BY custId, custName
;

Replacing built-in SQL functionality

This is probably a repeat of all previous ones together. When something can be done with raw SQL, it is almost always faster and better to do it in raw SQL. Fancy frameworks may hide some of the complexity, but will make you paying a price. And this price is usually performance.

You can have some structure in your SQL


As you have seen in the examples above, the more efficient alternatives are breaking structured programming, abstraction, detail hiding and probably some other principles of structured programming in some way or another. After you've worked long and hard to encapsulate the business rules in a way that avoids repetition and hides complexity, I'm basically telling you to throw them away in favor of better performance. I don't have an answer, and I think that nobody has, to this dilemma. Possibly because SQL was not designed to abstract business rules or abstract data structures beyond tuples (rows) with attributes (columns)

That's not to say that you cannot have any kind of structure in your SQL. Quite the opposite, you should strive to have it. There's nothing worse and unmantainable than an application that is hand crafting SQL sentences and sending them to the database to execute for each little thing it does. But always keep in mind that abstractions are useful as long as they pay off. Don't abstract breaking the assumptions made at either language desing or database engine development.
For example, it's very common and good practice to abstract business rules (data manipulation and validations) in stored procedures.

I'm not advocating cut and paste programming or that you should not try to be as structured as possible in the coding and design or your applications. But you really have to be aware of the trade offs, and above all, don't try to reinvient the wheel. The designers of your RDMBS spent probably a long time thinking about how to make their engine efficient and easy to use. Don't fight with them, take the red pill and do things its way. It's better on the long term.

Thursday, 3 July 2008

Maintenance programmer and performance

It's almost universal. And unavoidable. Yet I'm not sure if it's right. But it's true. At almost any organization whose goal is to develop and maintain software, there is a knowledge based hierarchy. The best and brightest individual tend to take care of the more complex issues with programming and architecture. The juniors and newcomers are assigned lowest value added tasks. Usually, organizations tend to offer career options and other incentives to their best people, with the hope of retaining them and extract more value from the time they are in the office. The less experienced, productive or talented people are invariably relegated to jobs that are perceived as adding less value.

Agile/Extreme Programming methodologies try to get away with this segregation of categories by using "Pair programming" and making everyone accountable for all code in the system, but this is in practice difficult to achieve. It needs to have a more or less homogeneous team in terms of experience and capability, as without an equal footing "pair programming" ends up morphing into some sort of mentoring scheme. In that scheme, pairs are allocated in such a way that there's always a "master" on the subject working together with someone less experienced. In an ideal world, they will become true pairs given enough time, but time is money for a business that wants results here, now and cheap. Nonetheless, and although I don't have field experience on that, I think that pair programming properly done can be one of the greatest assets of a programming team.

It's after all the best business interest to maximize the return of every penny they put at risk (read invest in a software package), but this hierarchical scheme it does usually have an impact on the way the software maintenance is done. Because the best people is usually assigned to the "best problems", and maintenance is often consigned as a necessary evil.

I've never understood that point of view. All the literature about business software development is sending the same message about emphasizing readable and clear code based on field data that proves that maintenance is often half of the total cost of any application, if not more. It should follow logically that assuming that maintenance costs are high, maintenance taken as its own profit centre should be given the importance it deserves. But in fact it's not often done.

Part of the reason, I suppose, stems from the wide scope of the word "maintenance" From simple changes in user interface or report layout to whole data model changes, all of them often fall into the same bucket of "maintenance". While obviously the profile and capabilities needed for one task versus another are deeply different, the fact is that the same resources are assigned usually for both kind of tasks.

And that invariably impacts performance. And I'm sure that even strict adherence to the most rigid change management methodology will still leave business systems to unexpected performance problems, if only because assessment of the performance impact of changes is still an unexplored area.

Add a bit here, add a bit there.


Let's tell a story that illustrates this very well. Bob is an entry level developer assigned to minor enhancements in the company ERP system. One day, Bob receives a call from a change manager that has already completed the immense paperwork required to add a simple data point to a data entry screen. When entering a customer order, the system has to display the outstanding value of all orders pending to be delivered to the customer.

While Bob is relatively new to programming, he is able to complete the task and creates a simple subroutine (or method, or user exit) that computes the outstanding value of all orders for an individual customer. The change is tested, moved to live, and for Bob it's all well and good.

Months later, some internal control manager which is being part of the internal audit team needs to take action around a point remarked in the last audit around the excessive number of customer orders entered that exceed the per customer agreed order volume. He calls for a change so the data entry clerk can spot not only the amount but also the outstanding volumes.

Bob receives the request and changes the subroutine (or method, or user exit) to display that information on screen. It makes sense to do it at the same time that the order value is calculated, since they are closely related. The change is tested, moved to live, and for Bob its all well and good. So good in fact that he's asked to create a report listing all that information, since the customer service manager will start its daily operations meeting with that information on the desk for the team to arrange and prepare shipments.

Being smart, Bob creates a report that calls his function once for each customer in the customer master, checking for the outstanding order volume and adding it to the report if it has some. The report runs a bit slow, but that's not a problem because the report is run overnight. Change is tested, moved and everything is good.

Bob moves into other projects, and eventually leaves the company. He's replaced by Sean, an equally talented and motivated person as Bob, who later on receives the request to change the report. As the customer service manager focuses his daily meeting on the activities done in the next 24 hours, he needs to split the outstanding order volume by delivery date, so as not to waste time reviewing something that it's not an immediate concern.

Sean examines the report and without hesitation, changes Bob function so that it breaks totals by delivery date and adds them up at the end. Of course, the function is even slower and the report more so, but for Sean is the most logical way to complete the work since it involves the least amount of change and effort. Also, the report is giving 24 more times information, so it's reasonable that it takes more time. Oh, besides that the report is run overnight, so performance should not be too big of a concern...

Sean's change is tested, moved to live and all is good... well, not. Now all the data entry team is screaming because they cannot enter orders. The system has become inexplicably slow and it takes ages to do now what in the past was acceptable, if not exactly snappy. Of course, the explanation is that the same program logic is being used for two very different contexts (night batch report and live data entry) with very different requirements. Each time the program is changed it makes perfect sense in that concrete context, yet the cumulative changes lead to a problem.

I've noticed this pattern a lot of times happening in the real world (in fact, this story is true except for the person's names) The problem would have been avoided if close testing of the whole system were done, or if close inspection of code revealed its usage in multiple places, and possibly the conflicting uses of this.

Of course, had Sean or Bob been mentored, they would have included some comments or documentation somewhere. Had complete performance testing of the whole system been done the problem would have been detected. But who does that for a seemingly innocent change in some report output or for an information only label on a data entry screen?

The irony is that, when the problem is detected, poor Sean is blamed for its poor coding practices, while he had few choices in his way to proceed with the change. Had he created another procedure he would be seen as "copy & paste" programmer, not taking advantage of functionality already there and investing more time than necessary in the change. Had he said "look, it's very inefficient to loop over customers just trying to find one with outstanding orders, let's try to make this the other way as it will be more efficient" he would have been said that performance was not that important. Had he spotted the actual problem of reusing the code because it was used also in an interactive data screen, he would be said that he was not assigned to that part of the code.

The curse of the maintenance programmer


All of the above conspires against the maintenance developer. Regardless of their experience and skill level, they will be always caught in the middle of discussions. The IS side will argue about costs and resource limitations and the business side will argue about costs and deadlines.
Plus, an existing application forms part of a closely interweaved link of process, knowledge and business practices. It's not easy to justify any refactorings once an application is live, if only because the benefits are never apparent in the short term.

I've seen very talented developers despair when being assigned maintenance roles. Not because of the quality of the challenge or problem they have to face, as some systems can be more complex to maintain than to develop given the number of additions that have after its go live. No, the reason for their despair is that they have to deal with a codebase that is inherited from not only the original developers but also anybody else that has been it their chairs before. When they are asked to do anything with the code, and after overcoming the "complete rewrite syndrome", they may feel comfortable with it. However, when faced with change they are always compelled by management to take the shortest (read cheapest) route. As we know from the travelling salesman problem, this is not always the optimal solution.

Tuesday, 20 May 2008

Database tuning myths

Businesses are always looking for predictability. From financial plans that span three years to operational plans for next year, to five year strategic plans, there is a good amount of time and resources at any business devoted just to predict what the future is going to look like. Entire business functions are needed to create the plans and track its evolution. And the stock market regularly punishes companies that do not meet their financial plans. Ironically, they do it only the ones were the objectives are not met. Someday they should look at some medical disciplines and discover why growing faster than you should is not always good for your health, but that's another story.

It's not surprising that all those processes create a culture of predictability, where everything must be planned in advance and coordinated. Over time, and by repetition, those processes become so embedded in the company culture that people forget why things are done in a particular way. This has many benefits, as the process of planning itself becomes predictable. But it also transpire to other areas of the company, and everything that is either important or big enough ends up having a detailed process that everyone has to follow. Detailed and predictable outcomes reinforce the hearts and souls of the financial people because that full fills one of the basic tenets of capitalism: trust in the future.

Unfortunately, this model does not scale well if you want to keep an eye in all the details, even at the business process level. At some point, you have to forget about the details of how something is manufactured, or how the accounting is done. That gives you abstractions, and those are an essential tool for scaling up the complexity of any model, because without them you would get lost in the details very quickly.

Out of those abstractions, things that are know as "rules of thumb", or more corporate/engineering "best practices" emerge over time. Those are supposed to be generic rules or techniques that can be applied to any problem and give satisfactory, predictable results.

But as many are discovering, abstractions are not perfect. By their very nature, they ignore the details and focus on a few areas of interest to make them reasonably simple to use, its usefulness given precisely because they ignore the details.

If you've kept reading up to now (thank you very much), you may be wondering how does all this relates to database performance tuning. Well, this is one of the places where I see daily rules of thumb applied, often with disastrous results. Computer systems have evolved from the centralized mainframe to the distributed storage, processing and client access environment of today. Where in the past you had a machine and storage system with a thin operating system layer on top now you have to deal with anoperating system that attempts to coordinate a wide range of things connected to the system, plus a network stack, a database persistence layer, a business process model layer, a presentation layer and a client (thin or thick) layer.

What were in the past valid "rules of thumb" in one environment cannot simply be applied to a different, more complex one. Well, in fact, some of them were never applicable in any environment at all. Here's my "best practices hell" top three:

1- We just need to use fancy new hardware


The number one, because there is natural tendency to associate something physical with the performance of the system. And that's the box sitting in the data center. Just as you can get a faster car, why not just get a faster computer?

The answer is, yes as long as you make sure that you actually need it. Adding a faster CPU for a system that is disk bound is not going to improve its performance. Putting a faster disk to a machine that runs processes that eat up all its RAM will only make it swap faster. Adding more memory to a system that spends most of its time sending data across the network does not improve its performance.

In short, you need to diagnose before healing. You need to know your system and you need to know your application and how it's using the system. Without that knowledge, you are just guessing, and spending a lot of money at it. And I've watched this happening more than a few times. It's very frustrating to spend money and resources in an expensive hardware upgrade only to find that the results are unnoticeable.

The reason behind that is that you've reached some hard limit and that your whole system cannot be easily improved by throwing more hardware at it. Then it's time to examine your application and move its performance boundaries to other components. This sounds complicated, and it sometimes is. But often it means checking the application and making sure it's using the database resources with measure. The classic case is the 10.000 item grid that is completely filled up on applicationstart up . The network round trips necessary for this are huge and the application changes usually minor. The irony is that after this exercise, you'll probably discover that you actually did not need any new hardware at all.

But the myth stays firmly planted in the industry because often a hardware replacement means performance increases of all its components. Therefore, whatever the performance boundaries, the system has better performance after the hardware upgrade.

2- We just need to create a few indexes


This is one of the most popular, yet dangerous myths. Come on, let's finish this discussion taking the myth to the extreme. Go ahead, index everything in your database. And I mean every single column and every different combination of columns in each table. Now try to do a few queries. Wow, some of them are probably faster. Now try to do any INSERT or UPDATE on it and watch the extreme performance degradation. Convinced?

The explanation for this is that an index is just a table with a special organization that allows you to find things quickly in it. Yes, it's a small table because it contains only the indexed columns, but it's a table nonetheless. Each time you add an index you're creating one of those tables and increasing the overhead of keeping all of them up to date, not to mention the concurrency issues that your database has to deal with each time it's updating a table and it's indexes. Oh yes, I hear you saying, ha!, cluster indexes do not have this problem! Of course, but the problem with cluster indexes is that you cannot have more than one for each table, so it's very unlikely that you're going to cover all cases with them.

Remember, performance tuning is a trade off. It's about using available resources where they are more useful. And this is clearly not a way to profit from your system.

This is not to mean that indexes are not useful. Yes, they are. But anyone that is applying rules of thumb is sooner or later going to fall into this trap. Remember, you've been warned.

3- We did this the last time and it improved performance


As explained in the introduction, "rules of thumb" do not account for complexity. Face it, the systems you're dealing with are very, very complex. the performance of a system is dictated as much by its external environment and constraints as by its own internal characteristics.

So, unless the previous one was identical to the new one, this assumption is likely going to be false. In particular, this myth is fimrly implanted in single product specialists and consultants and is very visible when they move to something different. Remember, reject any changes that do not come with good background. Just saying "because I've a book that says so", "because the vendor recommends it" or "because in my last project at customer xxxx it worked well" are not valid reasons per se.

Wednesday, 14 May 2008

Having a baseline

While the technical part of database performance tuning is almost different in each case, on the business side of things I run almost always into the same conversations. My favorite one is the conversation with potential customers. These follows more or less the same structure. And it always begins with the same sentences:

-Customer: our application has performance problems, we're looking for someone to improve it.
-Consultuning: have you already identified specific parts of the application that are not performing as you need?
-Customer: well it's this screen/process/job that is being perceived as slow.
-Consultuning: ok, let's have a look at it. What exactly do you mean by slow?
-Customer: I mean awfully slow, our business cannot operate with these response times.

Ironically, you'll think that at this point the customer answer would be "it takes xxxx seconds", but usually that's not the case. It's unusual to face a customer that has actually timed it at least to the second. For interactive applications ("dialog steps" in the SAP world) this is understandable, as it was proved a long time ago by usability studies that anything longer than three seconds is perceived as "slow". For batch processes, since they are run unattended, the execution time is not usually monitored even if it's captured in the system logs.

But remember, if you don't measure it, you cannot tune it. Any performance improvement effort will be a balance of cost, time to implement, and resource consumption. These have to be weighted against the benefits you're getting. And unless you have some tangible numbers, you simply cannot do that. So you'll not have any basis to accept or reject any proposed changes. One of my rules of thumb is to reject any proposed change if it's not backed up by some measurements that prove that the performance gain is worth the cost. This is also one of the points of friction when dealing with people (or automated tools) trying to improve performance by applying "rules of thumb", as the cost and impact of changes is often ignored. But is worth the discussion.

And what about the targets?


Yes, ideally, you should have a performance target. The problem is, the very nature of performance tuning is not deterministic. Because if you already know what your target is and that you can reach it, you probably already solved the tuning problem. So the conversation follows...

-Consultuning: well, how fast you want it to be?
-Customer: of course, I want it to be fast enough.

My advice is that you try to define "fast enough", but be aware that you'll not get any commitment to reach that performance level from any tuning expert worth its title, except in the "been there, done that" cases, but those are very rare with bespoke applications. So we have two cases:

-You have a performance target. Stop tuning when you reach it. Save your tuning budget for later rounds.
- If you don't have a target in your head, keep this instead: each tuning step will yield less performance benefits than the previous one. At some point, the gains will be minimal, and the cost to implement them too high. This is the moment where you should stop tuning.

Wednesday, 7 May 2008

Sometimes a tenth of a second is important

As any other field where a certain skill level is reached, performance tuning for the uninitiated becomes some kind of magical black art beyond certain point. It's then part of your skill set to try to explain in terms that a non specialist can understand, what you're really doing. (and they are paying for)

This is the point where excessive simplification can, and in fact does, give you problems. A case in point was a tuning exercise done on a large import process for a database. The import process itself was iteratively doing on a row by row basis an SQL query that retrieved a single row. The application design was not optimal, as this access was not even partially indexed and the import process was agonizingly slow.

The data model was "customized" from the original, big application vendor, design to adapt it to business needs, and that customization had not taken into account all possible impacts of changing some meta data. But since the import process was also supplied by the vendor there was no way to change it. Neither calling the vendor for support was an option.

Ah, I said, no problem, we can create the necessary index and be done with it. But first, remembering that performance tuning is always a balancing exercise, I took the extra caution of checking the cardinality of the involved fields. The query in question had four columns in the WHERE criteria, but only three of them were significant in its selectivity. The fourth one just had a 2:1 ratio, while the other three together had more than 10.000:1 ratio. Thus, I created an index on the first three fields and left the fourth alone.

It is always a good idea to check the selectivity of your indexes, having unnecessary columns on them simply degrades performance without any benefit in the access times. It's also a very common, and wrong, performance optimization "rule" among non specialists that says "just add more indexes" as an answer to almost any database performance problem. The cardinality check is usually a good way to dispell that myth, because not always more indexes equate better performance.

After I did that, the results were good, the single row select was down from 10 seconds per row to .2 secs per row.

Well, that's it, I said. Only until later on the application developer came in and asked if I could further improve the performance. Since it was already 50 times better, I was in denial of adding the remaining column to the index. And when I did that, performance was down to .1 secs per row instead of .2. This kind of performance improvement, while spectacular in itself (how many times can you double something for such a small price?) was nowhere near the order of magnitude gained by the first one, and probably not worth doing in the general case.

Unless of course the single row SELECT is performed 100.000 times. Then it really makes a difference to wait 10.000 seconds or 20.000. In the first case your process will finish within normal working hours. In the second, you'll have to stay in the office late until it finishes. Case closed.

Over-specify to avoid performance problems

Nobody likes to stop the press, the train or the plane, or the cash collection, or whatever process their business is engaged on, waiting for a computer to finish its task. Nobody likes having to fit the process ("list of deliveries will not be available until 11:30 in the morning") to the timing of a machine. Those are usually the symptoms of a computer system not performing up to the business requirements.

Of course, this is more commonly seen in the business world. Software manufactured in other fields simply cannot afford to have performance defects. Think of life support or vehicle control systems, where performance is a requirement that is expressed in very precise terms. Think of video games, where jerky animations will mean that end users will not purchase them. Software used to support business processes is usually viewed as a constant balance between cost, time to market and quality. Seasoned project managers, when confronted with the previous sentence will instantly recognize it and say "you cannot have the three together, you have to pick two"

In the cost, time and quality triangle, where do you place performance? As veteran project managers and performance consultants already know, the answer probably lies somewhere between the three. Performance is surely going to be considered a quality item, but also happens to impact both price and time to market. Here's my experience in the field, note that your mileage may vary.

In the end, it's the quality that suffers


The traditional project life cycle in business environment is seldom completed as it was initially envisioned. Very few projects end up with their scope or schedule unchanged, if only because the traditional business practice of "optimizing" everything cannot resist the temptation of subtracting resources or shorten the time line if the prospects of a project finishing on time and budget are good. Very seldom business will drop features or accept to postpone delivery dates, perhaps because the business side of the project can understand very well those concepts, where "quality" is usually more abstract and harder to quantify.

Besides, this is software, right? Being software, it means that the same principles applied to other engineering disciplines are not applicable here. This is not the place to discuss if software is engineering or not, but certainly some rules that are usually applied to well established engineering practices do not apply. Can you imagine a civil engineers changing the width of a bridge once the construction work has started? Hardly. But the same civil engineers, when leading a software project, do not have any problems changing the specifications for data volumes while building the system. This makes even more unlikely that all the spec changes stay in sync, not to mention the performance ones.

To have any chance of surviving schedule and scope changes, performance testing, like unit, functional or integration testing, should be an integral part of the project plan. If you don't have performance requirements and testing at the beginning of the project, skip to another article, as the damage has already been done. You'll resort to monitor the live system performance and watch for performance trends to show up before even having a clue of what you need to do to keep acceptable performance levels.

But if you have performance testing as an integral part of the project plan, don't hold any high hopes either. In my experience, it's the "quality" part of the triangle that suffers. Outside of mission critical contexts with human health at stake, and in spite of all the "quality" buzzwords that surround modern management methodologies, changes in scope or schedule always will win over quality. Part because "quality" is a hard to define concept for business software and part because the number of defects in a software application is largely irrelevant as long as it fulfills business requirements today. And today means this quarter, not even this year. Since software is infinitely flexible, we can always fix defects later, right?

This philosophy largely permeates management because, much like performance tuning, everything is seen as a balance. Your application is perhaps not complete, has a few outstanding bugs and has not been performance tested. But the business benefits, and sometimes management reputations, are much preferable than moving the delivery date to have time to fix those problems Let's not even talks about performance testing, which except in cases where a good previous baseline exists, is almost impossible to estimate data volumes or densities.

But it is manageable, isn't it?


The answer is both yes and no. Yes, everything that can be measured is manageable (and even things that are not measurable should be manageable, it's only that you cannot easily rate how good you are at doing it) No, when you reach the point where managing an issue becomes more expensive than any losses you may incur by simply not trying to manage it until you are sure of what the issue actually is. That is, if you've to specify performance targets for each of your application processes, then you're probably going to spend much more time than just saying "no process should take more than 3 seconds" After stating that, you'll discover that 3 seconds is actually a very short time to generate a couple thousands of invoices, so you'll adjust your performance requirements for that. At the end your performance requirements probably will consist of a single word: "enough"

Paradoxically, this is the point where you've realized what are your performance requirements. It makes no sense to tune any process as long as it's performing well enough. It only makes sense to invest in tuning the processes where performance is not enough.

Trouble is, over a lifetime of an application, what is "enough" will change. Because the number of your customers can change, your two thousand invoices can become twenty thousand. Therefore, performance tuning becomes part of application maintenance. And that does not mean that you'll be always tuning your application, just means that you have to keep an eye on its performance, so it would be more precise to say that is monitoring what's actually part of the standard maintenance process. Actual tuning happens when the monitoring detects something going wrong.

But you want to avoid that, right? Because tuning is an expensive process that does not deliver a clear business case, unless it belongs to the survival dimension. The question is, can I really having performance monitoring at least outside of the normal maintenance activities?

And again, the answer is both yes and no. No, unless you're always right in your business forecasts and the numbers you're playing with don't change over the application lifetime. Remember, we're talking three to five years minimum application lifetime. Are you so confident?

And yes because there's an easy way of avoiding it entirely. In essence when you plan your system there is a stage that we'll call "capacity planning", which is what you're supposed to do when you specify your system hardware. The trick is very simple: take the hardware specs recommended by application vendor/developer/consultant and triple them. No, better make them four times better. In all dimensions, not just 4x disk space, but also 4x memory and 4x CPU speed. But don't set that system as your dev or test environment. Keep the test environment on the vendor/developer/consultant original specifications and make them comply with your performance requirements on that environment. Sounds extreme?

Perhaps you are thinking "why just make the performance requirements four times better and set up a test system that is equal to the live one? would that be more supportable?" If you're in the kind of environment where quality of application support depends on having the exact same hardware for test and live, you probably stopped reading the article a while ago, because you belong to the minority that develops operating systems, those pieces of software that isolate applications from the hardware they are running in. I'm not advocating that you choose different architectures, such as testing on Intel and deploying on SPARC, just that you get a scalable enough platform that allows you to set up different environments for test and live. Besides, in the long run, your test and live systems will in fact will be different. It only takes an expensive HW upgrade to realize that you don't need the fastest and more expensive disks on earth for your test environment. In the realm of business applications, that just does not happen.

You need some discipline, because invariably there will be performance tests that pass on the live environment and fail on the test one. Avoid being tolerant, this will become over time more frequent and will inevitably lead to performance problems because it will make the developer/vendor less disciplined. Set your performance goals on test, and have in your head some rough numbers that will allow you to predict how it will scale on live.

If you're willing to go this route, be prepared. First, the live system is going to be expensive. Very expensive. Second, the pressure will grow on accepting performance results as they are going to appear on the live system, not on the test one. Third, the 4x figure is based on experience, not hard data. After all, you're buying four times the system that is supposedly run your application well. Only a lot of experience and, if they exist, learnings from past experiences, will make your point strong enough to allow the increased hardware price.

It's your risk, you manage it


Did I mentioned that before? Yes, as always you're facing a balancing decision. Spend more than you theoretically need or face the performance problem later. In a business context, there's nothing that cannot be prevented or mitigated by javascript:void(0)adding money to it. It's just that sometimes it's too much money.

If you choose the traditional route, accept that at some point in time, your monitoring will scream tuning at you. Set aside some budget for performance improvements, it will be much easier to justify.

Friday, 18 April 2008

Tuning tools: beware

Older readers may remember the MSDOS extended and expanded memory standards. Back in the pre Windows 95 era, Intel machines were limited in the memory address space they could use. Not only because of the hardware, that until the 386 processor was limited to 16MB (yes, MB, not GB!) of RAM, but also limited by the MS-DOS compatibility constraints. There's a famous quote misattributed to Bill Gates saying that nobody would ever need more than 640K memory. This was, obviously, false. And the industry was busily developing solutions for the compatibility limits imposed by MS-DOS initial design. A number of software makers, from Quarterdeck to even Microsoft, started to release memory tuning utilities that could, in theory, maximize the usage of that precious 640K low memory region.

And they mostly worked. You would not even needed knowledge of what low and high memory was. Just running them gave you a system that was close to optimal in their low memory usage. And you could try to boast about your low memory size with your friends, only to find that they got exactly the same results as you. Trying them was very easy. You just ran the optimizer and accepted its suggestions. The you rebooted. After that, either your system hung or worked. In the first case, you booted with a diskette and undo the changes. In the second case, you checked low available memory. If you had a better value than before, then bingo.

Databases are no exception to the configuration game. Correct settings can give you a 10 times performance improvement over inadequate settings. And the cost of the change itself is close to zero. Just adjust some registry setting or configuration file, restart and it's done. Knowing the right value, however, is not that easy.

As databases continue to have more sophisticated features, the complexity of correctly managing them is increasing accordingly. What was in the past a mere job of checking a few memory parameters has become for the most part in a very delicate set of adjustments that balance the application behaviour and the machine resources and configuration. Database vendors recognized long ago that for their products to become a commodity they needed to somehow allow the average user to enjoy the product features without requiring from them a four month DBA course. Their solution was two fold.

Sensible defaults


First, experience derived a set of sensible default values depending on the intended usage (such as "OLAP" versus "OLTP") that allowed the product to work by default at least reasonably well. In my experience that is the part that has been most beneficial for performance. Having some sensible formulas, such as "allocate 80% of RAM to disk cache" mostly work, but those formulas are not designed to cater any special system or application needs and/or behaviour. A couple of my favourite examples are:

- A complex query that took more time in the parse and analysis stage than on data retrieval itself. The database did not have enough memory to cache the parsing and execution plan stages after a few more queries executed. The symptoms were easy to spot. A customer had a query that, when issued multiple times, was executing very quickly after the first time. However, as time passed on, execution times was equal to the time of the first query. Simply, the cached parse stage and execution plan were being recalculated too often.
- A single machine was hosting the database server and the application server. The database buffers were simply eating up available physical memory and the application server was also claiming its share of the RAM. The symptoms? The database system performed very well before starting the application server.

It's very unlikely that default values will ever be able to cover all the possible usage scenarios. Even if they could, they cannot control resource allocation of other components in the machine. That is, if your environment matches what the developers had in mind when defining the "defaults", those settings are not going to be too bad. And they usually provide a good starting point or baseline to measure against further changes you make.

Automated tools


Second, they started to release the database version of the MS-DOS memory optimizer. Software that looks at database response times and recommend changes based on that. There are many variations of those, from simple query analyzers to big brother-style services that collect the logs and watch activity in real time. These automated tools usually come as part of bigger packages and are intended to help the DBA keep a database system healthy and responsive.

These tools are very good at taking a lot of information and present nicely reported results that give an overview of system performance. The best ones even suggest improvements based on the information they collect. And therein lies their biggest danger. Much like the MSDOS memory optimizers, following their suggestion does not always improve performance. Unlike the memory optimizers, those suggestions when implemented can mean serious business disruptions. Those cannot easily be fixed by booting with a diskette and undoing the changes.

My favorite example are the query analyzers. One of the most heated discussions I had with a customer was around index creation for a medium size table. They had a long running SQL query over medium size tables and the advice from the tool was to create some indexes. However, without taking into account index selectivity, half of them were essentially not worth having.

This is an example, but you must keep in your head the global picture. Just taking a single query and inspecting its access plan just can, in the best case, improve the performance of that single query. But that does not mean overall system performance improvement. In fact, it can mean overall worse system performance. On some cases, I've watched also something that's even worse. Having one such tool that is sanctioned and supported by the application vendor creates an effect where the suppport staff is completely blindsided to anything not reported by the tool. The tool recommendations are implemented without a second thought, even if they don't improve performance at all or mean large downtime windows. The true performance problems are hidden behind the reasoning "this is what the vendor recommends, therefore by following their recommendations we have the best possible performance" Sorry, I cannot tell you the application vendor or the tool, but if you're running a big ERP package with thousands of database tables you know who it is.


Like the "sensible defaults" option, those tools are not bad per se, but in this case they are even more dangerous. If you're going to take their advice, be sure to have a good test environment where you can replicate in as much as possible your live environment conditions. Test, test, and test, before implementing the changes.

It's all in the context


While RDBMSs have been converging to what it's called "commodization", which is a term used to describe a market segment where different brands are interchangeable one with another because they have easily comparable features, the same cannot be said of applications that use the RDBMS as its storage layer. That makes the context wildy different, and thus, those tools alone cannot be used to improve performance.
Why you say that? It logically should follow that, by following their advice, my system will have better performance. To refute this, apparently, perfect logic reasoning, just look at the context. If there's a common flaw in both tool categories it's the context. In the sensible defaults case, the context is the rest of the processes running on the machine. The automated tools, being more ambitious, have even more context to take into account, from other processes in the machine to usage patterns, different modes of operation, changing data densities and business seasonality and priorities.
In the end, like all tools, they need to be in capable hands to be effective. So next time you hear someone proposing a change in your database, ask why. If the answer is "because this tool has recommended it", just reject the change until the proposal has a solid background.

Monday, 14 April 2008

The business case for performance tuning

One of the most difficult parts of the engagement with a potential customer is the time to discuss your rates. Specially in comparison with the rates usually paid for other kind of professional services involving technology. Let's explore some of the reasons and the economies behind database performance tuning efforts.

The rates


With the recent wave of out/off/shoring, rates for services in the IT sector have been, as the financial types like to say, “rebased”, which basically means that the IT market has evolved from one with high demand and scarce offer to one where the balance is more on the side of the demand. This has a lot of implications, most of them know already by the wise old IT crowd. But the focus on short term financial benefits can sometimes make the financially minded people deaf to the IT doomsayers.

Of course, you can find many examples where cost reduction in IT labor rates has not meant an associated reduction in quality, or increased maintenance costs, or increased opportunity costs. But this is probably not being noticed because what the media is focused on is the failures of these business models instead of the success. However, to be honest, the cases where out/off/shoring has provided gains in the financial bottom line AND at the same time increased quality of deliverables AND reduced on going maintenance costs are quite rare. Well, to be honest, I've yet to come across one of them.

Sorry, excuse me for the digression, back to the main topic. Well, not really, because the topic of cost is quite relevant when confronting someone who has already paid quite a bit of money to have a system up and running and now is being told that it has to pay even more to make the system survive to business organic growth. Note that the measure of “quite a bit of money” is entirely subjective. Even when employing the cheapest resources available, the system will have, from the point of view of the payer, cost a lot of money.

Now, try to open that without a locksmith


Tuning exercises are not cheap. Reasons include the fact that the job requires experience. A lot of it. Databases have lots of features, and SQL is a rich and expressive language. Each problem has multiple solutions and it takes a lot of experience as well as a lot of false starts to realize what are the best possible solutions for a performance problem.

Once you have experience, you need to drill into the data. Yes, performance problems do not have a “best” solution per se, and this is something I hope to develop in future posts. The same solution applied to different data patterns has completely different results. There are no magic recipes that can make a system go faster. Well, there are, but they can be like steroids for athletes. Abusing them is dangerous on the long term, but they give spectacular benefits on the short term. Mmmm... yes, abusing indexes is not going to make your life shorter. And no problem with sport authorities either. What a crap analogy, but the topic of magic tuning recipes will certainly be worth of future posts.

And finally, after all the experience and data knowledge, there has to be a lot of business engagement. Only by understanding the processes and background reasons for the a system design you can suggest good ways to evolve it performance wise. Business management are not comfortable with tech talk. They prefer to have communication in terms that they can understand instead of be mired into jargon.

The above skills are not easy to find. And it's likely to be at a professional level that is well above the cheap offshoring rates, both because the experience needed and the level of communication. So yes, it's difficult to understand. But last time you lost the front door keys while beign outside your house, you had to call a locksmith. The guy came and with a very small tool set and in very short time opened your supposedly burglar proof door. And it was expensive, very expensive.

At least that's what you remember. You surely don't remember that, without the locksmith' experience, skills, tools and time, you would had to pay for a new front door instead. Which would have been way more expensive.

Can I avoid all this by working smarter?



Hardly. First, it's very difficult, if not downright impossible, to anticipate the growth rate a business is going to have. Second, it's almost as difficult, if not more, to predict a system behaviour under load unless extensive background experience on similar systems is available. Or else, you're ready to set up a test lab. And you're ready to over specify the system hardware.

After all those reflections and a few back of the envelope calculations, you'll realize that it is just not worth even trying beyond having a reasonable expectation that the system will work in a year from now. Anything you plan to do today during next year is going to probably change in the next three months. If you are in those kind of business change pace, then you cannot positively absolutely set up any performance projections based on current business conditions.

Note that “change” in this context does not necessarily means business change but priorities as well. Even organizations that display the less amount of change to the outside observer can become literally paralysed by the rate at which priorities change internally. In fact, in some of them they change so fast that there is literally no time for the previous change to settle down or being completely finished (side note: those kind of business usually have applications that, in addition to performance problems, show some interesting common design patterns)

Even if your business environment is stable, you'll still have to make your back of the envelope calculations. Because more than a cursory examination will yield a result that probably is going to contain assumptions on data density, distribution and frequency.

Oh, yes, the sales manager says, each customer is invoiced monthly, just once a month. And the financial planning team has been spent six months detailing their processes perfectly fitted to the sales ones. After that big effort, they're not likely to change in years. Then comes a new sales director and customers are invoiced each time they get a delivery. And the financials for that are very different, so planners change their process. And the logistics need to update stocks daily. No, wait a minute, not daily but 48 times a day. All your careful performance assesment goes by the window in a minute.

Where's the business case then?



With all that said, probably the first question in the line would not pertain to database performance, but perhaps to application development methodologies. There are ways to prevent and mitigate the problem happening in the first place. But this discussion will always end in the same place, which is where to draw the line between business benefits and IT costs. And by applying sound business principles, the line will be much closer to the business priority side than you want. Face it, there's very little you can do about it.

But then comes the problem of justifying spending money on just making the application meet its performance requirements. If you're savvy enough, you already know that business arguments can happen in a golf course, while having a couple of drinks, or in the tennis court. And all those are valid ways for your arguments to win. But it's always best to have some rational arguments prepared, just in case your demands for additional money are rejected.

In theory, it should be easy. Just put a value tag on each of the activities that your application supports, add all them up and multiply by the number of times they could be done with better application performance, right?

In my experience, this is just the third best way of looking at the problem. Because the true business case for performance tuning usually is found on three fronts:

Survival cost - that's the easiest one. You need better performance to just keep doing business.
Opportunity cost - a bit more difficult. If you had better performance, what other process would you be able to support with the spare capacity?
Productivity - as said, the easiest one. Your plane has its engines turned on for a faster take off when loading is finished. Until the pick list is ready the plane cannot leave. How much fuel are you spending each minute you wait for the pick list?

Check your case, I'm sure that it falls in one of the three. And happy golf game.

Tuesday, 8 April 2008

You probably have a performance problem. You'll probably never know that.

Think about it. When was the last time you heard about your application being slow? Every day? week? month? When was the last time you heard from some business manager “we could do X more/better if the system could be more responsive”? Lots of times. How many processes in your business have their schedule and deadlines dictated by machine execution times? I'm sure that you'll find a few, some of them probably rated as business critical.

So you approach your dev team (either in-house or outsourced) and request from them some time to fine tune the application response or batch times. You always get some variation (or a mix) of the following two answers.

Answer 1: your application does not have any kind of problem with performance, even if somebody is saying that it is “slow”. None at all. It is already working 100% optimized. All the execution paths have been checked and tested, and each one of them is using the best possible algorithm. There are no places worth looking at in the code any more for performance improvements, none. If you want, or more likely need, to improve performance, just buy us some new fancy hardware.

Answer 2: we just cannot stop to look at performance. There is no time to do that here. If we stop and look at performance, we'll delay the next release, the next patch, the next whatever is in the works. We can always take care of performance later. Do you really prefer delay the next project milestone/business roll-out/bug fix in exchange of some nebulous performance improvement target? If you want better performance, just buy some fancy new hardware.

Frankly, both answers make perfect sense, and it would be very difficult to challenge them in a business context. First, you would need to have extensive knowledge of the inner workings of your application to challenge the first. Second, you would need to have a lot of muscle within your business organization to stand up and say “next release/next change process/next bug fix is not as important as investing some time in application performance”

This is business, not F1 racing. Just being the fastest does not give you any prizes. But performance is always on the back of your mind. You don't know quite why. Well, you do. But like the two answers outlined above, you are really lying to yourself. Because you know that, at some point in time, you'll be under the spotlight for your application performance. But is just better to postpone it because.... (insert some mix of Answer 1 and 2 here)

And therein the day comes where your application performance is no longer tolerable, even by the most generous and forgiving users on the face of earth. Even by the management board that have never, ever, seen even the application after its splash screen (well, they probably did in that demo, but they did not understood anything beyond that) And you'll be on the spotlight. And you'll go to your dev team, ask them to improve performance and you'll be told that... (insert some mix of Answer 1 and 2 here)

So you prepare an extra budget outside of plan, assemble a team of techies and buy some fancy new hardware. A migration project after that, your application is running on fancy new hardware. Surprisingly, performance improves. But only a bit. Not even remotely proportional to the money you've spent. What happened?

Simply put, answer 1 and 2, or some mix therein, are not true. Well, they are true, until they have to be proved. And that moment comes when you actually need better performance. This is the time when you realize that your application has a performance problem.

Granted, some applications are not like that. I'm sure that anywhere with human lives at a stake, from medical equipment to air plane controls, performance is taken very, very seriously. Outside of those fields, however, performance is just another factor in the balance of costs and benefits.