Or to put it another way - if you find poor SQL in an application, it might be as much to do with poor database and application design than plain, old poor programming.
Ever since I had to use COBOL briefly many years ago, I've learnt the lesson that often a good design can make the programming easier. By carefully designing and defining the DATA sections at the top of your COBOL program, you could make the later manipulation of the data itself that much easier.
Time and again I have seen the results of both practices - good design leading to a good, efficient implementation; and bad or weak design leading to a poor implementation. Not only can a good design help achieve a good implementation, but a poor design will often hamper the programmer and reduce the options available to them, restricting what choices they have. And so the result of a poor design is a poor application with poor SQL in it.
The SQL to access data goes hand in hand with the design of the database containing that data. You cannot separate the application code from the database design. Put the data in the right place, and the SQL becomes simple and straightforward. Put the data in the wrong place, and the SQL becomes more complicated, joining tables together and adding extra constraints and conditions, and possibly needing to use more horrible things such as outer joins and unions. I'm not saying that these are always unavoidable, but I am saying that the core design of a database for an application should not need them.
This is why good database design is important, and how it can impact application performance as much as the application code itself. Time spent doing a good database design is repaid by a good, well performing application. A skimped, quick database design often results in a longer development cycle, probably needing changes to the database design to correct things missed out earlier, and extra effort by the developers to write the SQL that gets them the data they need when they need it.
I'm saying all this because I have seen systems with poor SQL that was causing poor performance, and it turned out to be due to the poor database design, and not the programmer's ability to write good SQL. The SQL was in fact as simple and direct as it could be, but it had to go to multiple tables and join them together and apply different restrictions to each table, to get the data it needed.
In fact, some of the worst impact SQL I have seen on a system was incredibly simple, and was written to continually count the rows in a table to see if any new records had arrived. Of course it was not that simple - only certain rows in a certain state were to be counted, and these also depended on having matching data in another table, and so on. The net result was a SELECT that queried 5 separate tables to determine if any data at all was in a 'ready' state. And because it was a count, it actually needed to touch a lot of data or index records.
Although the joins were efficient (using indexes), the constraints were very few, so that Oracle was retrieving most of the rows that matched in most of the tables, before rejecting them and counting what was left. Such a query cannot avoid a lot of row accesses, even if they are logical and not physical I/Os due to being in the buffer cache. It still takes time to access these rows in the tables and count them all up.
What made this case worse, was that for various reasons the query also did a self join. New entries could be sequenced, and so to count a record as being 'ready' it also had to have no predecessors in its sequence which were also 'ready'. This doubled the amount of work being done by the query, in terms of data records accessed.
As you can imagine, this made it a slow query. And this was being run multiple times a second to see if any new work had arrived. So it became the largest resource consumer in Oracle from the application, even though it was not doing any real work - just counting how many jobs were ready to be processed.
The solution? A redesign. We tried everything we could about using indexes and caching data, and pushing joins down into sub-queries to steer the optimiser one way or another. But still the query involved a lot of buffer gets, which took time, and it was being run very frequently. Which only left a redesign as a viable solution. By moving around where data was stored, and in what form, we were able to eliminate many of the joins, and drastically reduce the number of buffer gets per execution.
And the biggest win was to turn the design completely on its head. Instead of counting rows in a table, we had a counter column in another record that was updated every time a new record was inserted. Although this meant changing the application in more than one place (adding an UPDATE of the counter when a new record was inserted and later when processed), the original query was now reduced to a single record fetch. The remaining joins were now only joining to a single row, and not to every row in the original table, and the self join disappeared too.
The result was vastly improved performance - of both the query and the overall system - and in fact it reduced the total resource consumption on the system, so that CPU utilisation when down while throughput went up.
And all because of a poor initial database design, restricting a programmer to writing a poor piece of SQL, that performed inefficiently and used too many resources.
Monday, 18 February 2008
Tuesday, 15 January 2008
Limits, Contention and Capacity
One of the key concepts in performance tuning and analysis is that of the "bottleneck". At low levels of throughput everything looks okay - as you increase the incoming requests so the output of completed work increases at the same rate. But as the throughput of the system rises you will eventually reach a point where the output levels off and no longer increases inline with input increases. At this point some part of the system is saturated and has become a bottleneck - you cannot squeeze any more through it, even though the rest of the system has capacity to spare.
Tuning a system is often about identifying this bottleneck, and establishing that it is indeed the bottleneck rather than just a slow component in the system. Fixing the bottleneck involves somehow increasing its capacity. Bottlenecks are normally considered in terms of the hardware in the computer system, but they can also be caused by the software being run.
In my view there are only 2 fundamental ways to fix any bottleneck:
1. Increase the capacity of the resource itself i.e. buy more or faster hardware, or change the software algorithm.
2. Reduce the usage of the resource by the application, freeing up capacity.
Which action you take depends on the nature of the bottleneck. If there is no contention and it is just a slow resource, then the first option is the most viable one. The second option is best when there is contention for the resource and a backlog queue of outstanding requests is forming in front of the resource for access to it. Reducing the use of the resource will reduce the size of the queue, which in turn reduces the time any request has to wait to gain access, and so has a direct impact on performance and throughput. But, as stated, this only works when the resource is overloaded and there is contention for it, which you have to identify.
Equally, the first option of replacing the resource with a faster one can work just as well for a contended resource that is the bottleneck. Being able to process each request quicker will reduce the contention and reduce or eliminate any queue of waiting requests, increasing performance and throughput. This is why many people often take the simple, brute force approach of upgrading hardware to make a system perform faster. However, it does not guarantee to remove the contention or the queuing, just to reduce it. There is the possibility that processing requests faster just leads to even more requests being submitted at a higher rate, and in turn back to having a queue of outstanding requests. Which is why the application behaviour needs to be considered.
The second way to fix a bottleneck can actually be approached in two different ways:
1. Optimise and tune the application's configuration from the outside.
2. Change the application itself from the inside.
The first approach typically involves changing configuration settings, such as increasing the number of records to read at a time to reduce the number of data reads, or creating an extra index on a table. The second approach typically involves rewriting part of the application to use more efficient algorithms that use less resources, such as reading a set of data into an array in memory once, and doing in memory lookups for validation instead of reads from disk.
What you do is dependent on the nature of the bottlenecked resource, the application software, and how much money and time you have available.
Another key point to be aware of, is that fixing one resource simply moves the bottleneck to another one. The bottleneck never actually goes away, it just moves somewhere else within the system. Presuming you are able to increase the effective capacity of the resource that is initially the bottleneck then throughput will increase, which is good. But eventually you will reach a point where the carrying capacity of that resource is now greater than another resource in the system. And now you have to repeat the analysis of the system again to identify the new bottleneck. And again work out the best way to either increase its capacity or reduce the number of times it is used.
Which is why performance tuning is so often an iterative exercise. You fix the first problem, but the bottleneck just moves somewhere else, and you repeat the analysis again, and again.
A valid alternative is to calculate in advance how much of each resource is available in the system, and how much can be used if performance targets are to be met, such as a given number of transactions per minute. This is more of a capacity planning exercise, but is directly useful in such performance tuning situations with poor performance. We can run the application under a relatively light workload and measure the actual amount of each resource used. These measurements can then be scaled up to the target workload and compared to the calculated resource requirements from before. This will relatively easily identify which resources will be saturated and overloaded, and by how much. This is a much easier approach than the repeated iterative analysis and idenfication of bottlenecks, and also establishes how much of a gap there is between desired performance and actual achieved performance. If the gap is known to be too wide, then simple tuning may not be the answer, and may be a wasted effort. Something more drastic and radical may be required. But you can only know this by doing the capacity planning in advance of deploying the application, and then comparing the two results.
Tuning a system is often about identifying this bottleneck, and establishing that it is indeed the bottleneck rather than just a slow component in the system. Fixing the bottleneck involves somehow increasing its capacity. Bottlenecks are normally considered in terms of the hardware in the computer system, but they can also be caused by the software being run.
In my view there are only 2 fundamental ways to fix any bottleneck:
1. Increase the capacity of the resource itself i.e. buy more or faster hardware, or change the software algorithm.
2. Reduce the usage of the resource by the application, freeing up capacity.
Which action you take depends on the nature of the bottleneck. If there is no contention and it is just a slow resource, then the first option is the most viable one. The second option is best when there is contention for the resource and a backlog queue of outstanding requests is forming in front of the resource for access to it. Reducing the use of the resource will reduce the size of the queue, which in turn reduces the time any request has to wait to gain access, and so has a direct impact on performance and throughput. But, as stated, this only works when the resource is overloaded and there is contention for it, which you have to identify.
Equally, the first option of replacing the resource with a faster one can work just as well for a contended resource that is the bottleneck. Being able to process each request quicker will reduce the contention and reduce or eliminate any queue of waiting requests, increasing performance and throughput. This is why many people often take the simple, brute force approach of upgrading hardware to make a system perform faster. However, it does not guarantee to remove the contention or the queuing, just to reduce it. There is the possibility that processing requests faster just leads to even more requests being submitted at a higher rate, and in turn back to having a queue of outstanding requests. Which is why the application behaviour needs to be considered.
The second way to fix a bottleneck can actually be approached in two different ways:
1. Optimise and tune the application's configuration from the outside.
2. Change the application itself from the inside.
The first approach typically involves changing configuration settings, such as increasing the number of records to read at a time to reduce the number of data reads, or creating an extra index on a table. The second approach typically involves rewriting part of the application to use more efficient algorithms that use less resources, such as reading a set of data into an array in memory once, and doing in memory lookups for validation instead of reads from disk.
What you do is dependent on the nature of the bottlenecked resource, the application software, and how much money and time you have available.
Another key point to be aware of, is that fixing one resource simply moves the bottleneck to another one. The bottleneck never actually goes away, it just moves somewhere else within the system. Presuming you are able to increase the effective capacity of the resource that is initially the bottleneck then throughput will increase, which is good. But eventually you will reach a point where the carrying capacity of that resource is now greater than another resource in the system. And now you have to repeat the analysis of the system again to identify the new bottleneck. And again work out the best way to either increase its capacity or reduce the number of times it is used.
Which is why performance tuning is so often an iterative exercise. You fix the first problem, but the bottleneck just moves somewhere else, and you repeat the analysis again, and again.
A valid alternative is to calculate in advance how much of each resource is available in the system, and how much can be used if performance targets are to be met, such as a given number of transactions per minute. This is more of a capacity planning exercise, but is directly useful in such performance tuning situations with poor performance. We can run the application under a relatively light workload and measure the actual amount of each resource used. These measurements can then be scaled up to the target workload and compared to the calculated resource requirements from before. This will relatively easily identify which resources will be saturated and overloaded, and by how much. This is a much easier approach than the repeated iterative analysis and idenfication of bottlenecks, and also establishes how much of a gap there is between desired performance and actual achieved performance. If the gap is known to be too wide, then simple tuning may not be the answer, and may be a wasted effort. Something more drastic and radical may be required. But you can only know this by doing the capacity planning in advance of deploying the application, and then comparing the two results.
Friday, 7 December 2007
Oracle SQL Optimization and Variables
I was going to get around to writing something up about the Oracle Optimizer in the future, and how it can sometimes choose non-optimal plans for what it thinks are perfectly logical reasons. But suddenly today I find that the optimizer development group inside Oracle have done their own post on this. So I thought I would point you at it as an excellent description of the issues I was going to discuss, and so that you can see that in 11g Oracle have finally got around to addressing a problem caused by previous enhancements to the optimizer.
What I'd add is that I see the nature of the problem of these non-optimal SQL execution plans as being one of the consequences of previous attempts by Oracle to improve performance for the majority of SQL queries. Although most queries have indeed benefited from better and faster execution plans, some have not and have actually got worse between versions of Oracle.
Originally there was no Cost Based Optimizer (CBO) and only the Rule Based Optimizer, which was unaware of even things like table sizes. So Oracle added in the CBO and it used some simple data on table sizes and indexes on columns to make better execution decisions. Over time Oracle added more and more features to the CBO to improve the performance of most SQL statements most of the time, and added extra sets of statistics to make more accurate predictions about query execution behaviour. Unfortunately, while the vast majority of queries have indeed benefited from these changes to the way the CBO works, there have always been examples of queries that have suffered and actually got worse. The example given in the post by Oracle is just one. I know of many others.
The problem is really due to two issues, now solved by 11g, hopefully. The shared execution plan in the library cache, means that the same execution will always be used for the same SQL statement since it was first parsed ('same' meaning identical text of the SQL statement). Initially this was okay. But then Oracle introduced bind variable peeking, to try and optimize the query even further. By looking at the actual value supplied for a bind variable in a query, and using statistics on the value distribution of the queried column, Oracle could better optimize that particular query. But now all subsequent executions of the same query, regardless of the value used in the bind variable, would be forced to use the same, original execution plan. Which may not be optimal for those subsequent query executions.
There is in fact another, more manual, solution to this problem. Do not use bind variables in queries you know are sensitive to the query values used, and for which you want distinct execution plans. By putting the query value itself directly into the SQL statement, Oracle will treat each of them as distinct SQL statements and parse and optimize them individually, each with their own entry and associated execution plan in the library cache. Unless, of course, you have enabled forced cursor sharing, where Oracle dynamically rewrites SQL statements to remove hard coded values and replace them with references to bind variables. Which would simply map them all to the same, single SQL statement and execution plan in the library cache. Something to be aware of.
I've recommended this solution a few times to customers with data sets that have only a few values and very skewed distributions, and queries that do not strongly constrain on other columns. The example in Oracle's post is one such case - no other constraints other than on department, which is highly skewed. These cases are very few and far between, but often they do occur in critical pieces of code e.g. pick the next request to process that is in a 'ready to run' state. The longer such a query takes, the more time that is wasted before the application gets on with processing the request itself. And this can exasperate things when picking the request ends up taking longer than processing the request itself.
It is nice to see that Oracle have got around to addressing this problem of queries that are sensitive to the constraining values used in the query.
What I'd add is that I see the nature of the problem of these non-optimal SQL execution plans as being one of the consequences of previous attempts by Oracle to improve performance for the majority of SQL queries. Although most queries have indeed benefited from better and faster execution plans, some have not and have actually got worse between versions of Oracle.
Originally there was no Cost Based Optimizer (CBO) and only the Rule Based Optimizer, which was unaware of even things like table sizes. So Oracle added in the CBO and it used some simple data on table sizes and indexes on columns to make better execution decisions. Over time Oracle added more and more features to the CBO to improve the performance of most SQL statements most of the time, and added extra sets of statistics to make more accurate predictions about query execution behaviour. Unfortunately, while the vast majority of queries have indeed benefited from these changes to the way the CBO works, there have always been examples of queries that have suffered and actually got worse. The example given in the post by Oracle is just one. I know of many others.
The problem is really due to two issues, now solved by 11g, hopefully. The shared execution plan in the library cache, means that the same execution will always be used for the same SQL statement since it was first parsed ('same' meaning identical text of the SQL statement). Initially this was okay. But then Oracle introduced bind variable peeking, to try and optimize the query even further. By looking at the actual value supplied for a bind variable in a query, and using statistics on the value distribution of the queried column, Oracle could better optimize that particular query. But now all subsequent executions of the same query, regardless of the value used in the bind variable, would be forced to use the same, original execution plan. Which may not be optimal for those subsequent query executions.
There is in fact another, more manual, solution to this problem. Do not use bind variables in queries you know are sensitive to the query values used, and for which you want distinct execution plans. By putting the query value itself directly into the SQL statement, Oracle will treat each of them as distinct SQL statements and parse and optimize them individually, each with their own entry and associated execution plan in the library cache. Unless, of course, you have enabled forced cursor sharing, where Oracle dynamically rewrites SQL statements to remove hard coded values and replace them with references to bind variables. Which would simply map them all to the same, single SQL statement and execution plan in the library cache. Something to be aware of.
I've recommended this solution a few times to customers with data sets that have only a few values and very skewed distributions, and queries that do not strongly constrain on other columns. The example in Oracle's post is one such case - no other constraints other than on department, which is highly skewed. These cases are very few and far between, but often they do occur in critical pieces of code e.g. pick the next request to process that is in a 'ready to run' state. The longer such a query takes, the more time that is wasted before the application gets on with processing the request itself. And this can exasperate things when picking the request ends up taking longer than processing the request itself.
It is nice to see that Oracle have got around to addressing this problem of queries that are sensitive to the constraining values used in the query.
Thursday, 6 December 2007
Some Basics
This blog will be about anything to do with the performance of computer systems, especially when running relational database products. In fact the majority of it will be about the Oracle database on multi-processor UNIX systems (such as Solaris and Linux), and how to measure and monitor the performance of such systems and how to tune them. There is a lot of information out there already on these topics, and I hope to add to it with a pragmatic approach suggesting real things that you can do about the performance of your systems and the applications running on them.
Before I get going and dive into deeply technical things I thought I would cover a few basic points. Don't worry, I don't want to repeat a lot of stuff that is already out there in numerous publications. I'll just cover some of the things that I feel are most important. And I do want to get straight what I mean by performance, and how I go about any performance analysis exercise.
Nowadays we use the term "performance" as if it were a real physical thing - a resource that we can have a quantity of. We talk of "enough performance" and "needing more performance". And in the same way as for physical things, we need to be able to measure the performance of a system. As the saying goes - you cannot control what you do not measure - which in turn means that if you can't measure it you can't improve it.
Adequate performance really means that you are able to achieve your goals. And poor performance means that your goals are not being met. So all performance related work starts with goals. Without goals you cannot truly say whether performance is good or bad, or how close you are to achieving what is needed.
Performance analysis needs to be done in an objective fashion, with well defined goals, which can be measured. No goals, means no measurements, means no ability to determine where you are. Everything becomes subjective - "too slow" - and impossible to pin down. Often relative terms are used without a context - "slower". You have to define up front what "fast enough" means. And these goals are best defined in terms of the business requirements the system was deployed to satisfy. The higher the level you can define the goals at, the closer the result is to something that makes sense to the business and directly benefits it. A low level performance measure is often not particularily relevant to an overall business process.
Performance analysis and tuning needs to be done top-down, not bottom-up. Top down tuning focuses on those activities that have the greatest impact on the performance goal i.e. the business goal of the system. Bottom-up tuning often focuses on low-level metrics and tries to identify those that are out of bounds and fix them. Often many "rules of thumb" are used. However, there is no guarantee that this tuning effort will have any impact on the business goal at all. In my view all performance tuning actions should be linked back to the expected impact on the business goals of the system.
When analysing a system there are two ways you can look at the system's performance and the resource usage on it:
Performance itself can only really be measured in 3 ways:
And luckily Oracle has made significant improvements to the measurements it takes within itself between versions, which help with this top down analysis and identifying the biggest resource consumers. It too has moved from a bottom up approach of reporting low level utilisation figures, to a top down approach showing the relative proportion of resources consumed by which SQL statements and even today being able to estimate the net impact on performance of certain changes to the database.
I'll get onto specific tuning issues for both Oracle, Solaris and computers in general in future entries.
And what about me? I've been using Oracle since about 1988 (version 5), but really got into it with version 6 and every version since then. Since starting in application development (C on UNIX) many years ago, I then moved into technical support and more lower level details of how systems actually work and achieve what they do behind the scenes. And this led onto benchmarking and performance tuning and problem analysis of Oracle based systems, which is what I have continued to work on since about 1990, one way or another. I keep trying to expand and move into new fields and technologies, but Oracle and performance issues never seem to go away, and I keep coming back to them time and time again. I have worked for a number of computer hardware manufacturers (including Sequent and Sun) and in a variety of benchmarking, consultancy and delivery roles, often a mix of architect / design work and hands on implementation. This has given me exposure to some of the largest Oracle based systems in the world in their time, and to the latest versions of Oracle and tuning the system to squeeze out the last drop of performance. No matter how good the technology gets, people still keep getting performance problems. And it looks like they won't go away in the future too, which will hopefully keep me busy.
Before I get going and dive into deeply technical things I thought I would cover a few basic points. Don't worry, I don't want to repeat a lot of stuff that is already out there in numerous publications. I'll just cover some of the things that I feel are most important. And I do want to get straight what I mean by performance, and how I go about any performance analysis exercise.
Nowadays we use the term "performance" as if it were a real physical thing - a resource that we can have a quantity of. We talk of "enough performance" and "needing more performance". And in the same way as for physical things, we need to be able to measure the performance of a system. As the saying goes - you cannot control what you do not measure - which in turn means that if you can't measure it you can't improve it.
Adequate performance really means that you are able to achieve your goals. And poor performance means that your goals are not being met. So all performance related work starts with goals. Without goals you cannot truly say whether performance is good or bad, or how close you are to achieving what is needed.
Performance analysis needs to be done in an objective fashion, with well defined goals, which can be measured. No goals, means no measurements, means no ability to determine where you are. Everything becomes subjective - "too slow" - and impossible to pin down. Often relative terms are used without a context - "slower". You have to define up front what "fast enough" means. And these goals are best defined in terms of the business requirements the system was deployed to satisfy. The higher the level you can define the goals at, the closer the result is to something that makes sense to the business and directly benefits it. A low level performance measure is often not particularily relevant to an overall business process.
Performance analysis and tuning needs to be done top-down, not bottom-up. Top down tuning focuses on those activities that have the greatest impact on the performance goal i.e. the business goal of the system. Bottom-up tuning often focuses on low-level metrics and tries to identify those that are out of bounds and fix them. Often many "rules of thumb" are used. However, there is no guarantee that this tuning effort will have any impact on the business goal at all. In my view all performance tuning actions should be linked back to the expected impact on the business goals of the system.
When analysing a system there are two ways you can look at the system's performance and the resource usage on it:
- From a capacity perspective of how much of each resource is being used, and identify saturated resources
- From a usage perspective of identifying which processes and tasks are consuming the resources
Performance itself can only really be measured in 3 ways:
- Latency - the elapsed time to perform one unit of work.
- Throughput - the amount of work done in a period of time.
- Utilization - the amount of a fixed capacity resource that was used.
- Latency measures the quality of the service - fast or slow?
- Throughput measures the quantity of the service - how much?
- Utilization measures the efficiency of the system when delivering the service - what did it cost? how much did we use?
And luckily Oracle has made significant improvements to the measurements it takes within itself between versions, which help with this top down analysis and identifying the biggest resource consumers. It too has moved from a bottom up approach of reporting low level utilisation figures, to a top down approach showing the relative proportion of resources consumed by which SQL statements and even today being able to estimate the net impact on performance of certain changes to the database.
I'll get onto specific tuning issues for both Oracle, Solaris and computers in general in future entries.
And what about me? I've been using Oracle since about 1988 (version 5), but really got into it with version 6 and every version since then. Since starting in application development (C on UNIX) many years ago, I then moved into technical support and more lower level details of how systems actually work and achieve what they do behind the scenes. And this led onto benchmarking and performance tuning and problem analysis of Oracle based systems, which is what I have continued to work on since about 1990, one way or another. I keep trying to expand and move into new fields and technologies, but Oracle and performance issues never seem to go away, and I keep coming back to them time and time again. I have worked for a number of computer hardware manufacturers (including Sequent and Sun) and in a variety of benchmarking, consultancy and delivery roles, often a mix of architect / design work and hands on implementation. This has given me exposure to some of the largest Oracle based systems in the world in their time, and to the latest versions of Oracle and tuning the system to squeeze out the last drop of performance. No matter how good the technology gets, people still keep getting performance problems. And it looks like they won't go away in the future too, which will hopefully keep me busy.
Wednesday, 26 September 2007
New Blog
This is a new blog for posts about relational database performance on UNIX multi-processor systems - mainly Oracle, but possibly others too. I've done a lot with Oracle on large systems over the past 15 years (mainly Sequent and Sun servers), and will post various things I continue to learn as a database developer.
Subscribe to:
Posts (Atom)
