Put simply, holistic tuning is tuning based on the knowledge that an application is not just code and a database. There are numerous interconnected components that work together to support data retrieval, storage and presentation.
Obviously well written code and proper database configuration are important parts of the performance puzzle, but issues with other components can still negatively impact the application. In addition, issues with other components can often be confused with database issues, leading to wasted time and unnecessary hardware upgrades.
The intent of this guide is to provide the following:
- Descriptions of the various components and how they interact with each other
- How to determine which component(s) need tuning
- Tuning options available for the components
- A logical approach to avoid common tuning mistakes
Applications And Relay Races
Just like relay races, applications are only as fast as the slowest runner.
Unlike relay races you can control the length of the race and how many hand offs are required to complete the race.
At a high level the data flow of an application seems straightforward. The application requires a record and asks the database for data and then the database returns the data.
As with so many things, reality is much more complicated than that. Knowing the relationships and inner workings of the components will allow you to make better decisions when attempting to tune an application. Each of these will be discussed in more detail on the pages dedicated to that component.
The operating system (Windows, Linux, Unix, etc.) controls access to memory, CPU cycles, the network and disk storage. Other components never access those resources directly, instead they request them from the OS. Improper operating system settings can defeat otherwise well tuned applications.
The network allows various components to communicate with each other. In most cases latency rather than bandwidth is the cause of network related performance issues. Latency is the time it takes for a single network packet to make the round trip from the source to the target. Bandwidth is how much data you can transfer on the network at one time.
Your disk subsystem is the entire infrastructure that supports reading and storing data from permanent storage. In simple environments this may be limited to a disk adapter and locally attached disk drives. In more complex environments this would include multiple disk adapters connected to multiple connection points on a SAN and the internal adapters, cache and disk drives of the storage servers.
The actual 4GL(ABL) code that a client uses to retrieve, store and manipulate the data. Because OpenEdge uses a rules based optimizer and not a cost based optimizer it is much more important for developers to understand the indexing rules than in other databases.
The actual database stored on disk as well as the various processes and memory structures used to support multiuser database access. There are numerous configuration options and startup parameters available to help eliminate common bottlenecks.
Before You Tune
When presented with a performance issue you should answer the questions below before deciding on a solution. The steps you take to identify the root cause or at least the priority of those steps will vary based on your answers.
Can the issue be accurately defined?
Often users will simply report that "The system is slow". You need to press for more detailed information or check for yourself by running the application. There is a big difference between "The system is slow" and "The customer order report for ABC Inc. takes 2 hours to run when you choose the detailed report option". Even if the entire application is having performance issues you still need to gather specific examples.
When did this issue start?
Knowing when the issue started will help you identify or eliminate changes that might have created or contributed to the issue. Don't limit yourself to application changes; make sure to include any SAN, network, operating system or hardware changes. Investigate those changes to eliminate them from suspicion.
Is the issue common to all users?
Determine if this issue impacts all users or just a certain group of users. If the issue appears to be regional you might have a network issue to resolve. If the users have different permissions or workflow for the application then you may be able to limit the code you have to investigate.
How do you define success?
Performance tuning without a clearly defined measure for success is almost always a bad idea. Knowing exactly how much of an improvement is required will help guide your decisions. Just like any other project it is also important that you and the users agree on what the final product will be.
Once you have answered these questions you should have a much better idea of where to start investigating the performance issue. This does not mean that you always pick the right starting place but in many cases you will.
When in doubt run the Profiler Control Tool against portions of your application to show the exact lines of code taking the most time. The Profiler is mostly geared towards application coding issues but you can still get important information about other issues by evaluating the results and your code.