Tracking Down IBM i (AS/400, iSeries) Performance Problems

by Dave Flint

KAMPGROUNDS OF AMERICA, INC. (KOA) is a franchiser or more than 400 campgrounds in North America and directly owns and operates 18 campgrounds. Founded in 1962, the company headquarters in Billings, Montana, coordinates services, reservations, finances, information services (IS), and other corporate activities. KOA uses two AS/400 servers, a model 270 running V5R2 for use within its headquarters and a model 810 that hosts a campground-management application for use by its franchises and company owned campgrounds.

In the spring of 2003, KOA was using a model 730 that began to experience performance problems as the host for its reservation application used by approximately 150 of its campgrounds. Because the system was using and outdated disk controller, KOA carried out an emergency upgrade of the 730 to a model 810 to fix its disk throughput issues. After installation of the newer hardware, though, KOA realized there were still some additional underlying performance problems. KOA decided that those issues and the expected increase in its workload over the next few years required a deeper look at, and better measurement of, system performance. KOA's IS staff had been getting assistance from IBM's performance group on an ad hoc basis but determined that IS needed to take a more formal approach to get the tools necessary to support development and monitoring efforts.

Performance Improvement on a Budget

A major factor in determining which products to evaluate was price. KOA hadn't budgeted for performance tools at the beginning of the year and so was limited by product cost constraints. However, at least one product, MB Software & Consulting, Inc.'s Workload Performance Series, met KOA's price criteria after a Web search for iSeries performance products.

During a demonstration of the product, KOA's IS staff was able to learn enough performance-enhancement basics to make a measurable performance increase in one of its main in-house application programs even before purchasing the software. The developers commented enthusiastically about the quality of a product that could provide that much useful information before the company had even signed a purchase order for it.

Tracking Down Causes

Because KOA's original major performance issue had been disk throughput related, that was the first area on which its IS staff concentrated after product purchase. Accordingly, KOA began working with Workload Performance Series' Query Optimizer component. Query Optimizer runs in a background subsystem and collects database query performance statistics. MB Software recommends running the tool for just 24 hours and then stopping to analyze the results because its potential for capturing large amounts of data can be problematic for systems with few resources to spare. After developers make any application changes indicated by the data, they can run Query Optimizer again to measure the performance improvements. Query Optimizer makes performance results analysis available in two formats, either as Web-based performance charts that give an overview of the data or as detailed inquiry screens that provide a drill-down capability to isolate specific jobs, users, or programs causing performance issues.

After the first day of using the disk analysis tools, particularly the report that showed the top five jobs in terms of skipped record reads (skipped records being those that the query has to read from disk before IS can determine whether the data met the search criteria for a given query), the IS staff was able to identify which subsystems and which jobs within those subsystems were responsible for the majority of 'wasted' disk reads. By creating two new logical files and changing three application programs to use the newly created files, KOA developers were able to eliminate more than 80 percent of the total number of skipped records for the entire system. In addition, one of the Query Optimizer's subsidiary reports even pinpointed what the access paths should be for the new logical files. Out of the hundreds of files and programs on the system, tuning just three heavily used programs made a major impact on overall system performance.

Another Batch of Trouble

Next for attention was analysis of the number of physical disk I/Os. A Query Optimizer result chart let KOA find a problem within a batch job that ran every 10 minutes and updated a large number of records. A logic change within that one program caused a reduction of 26 percent in total system wide I/Os. The combination of eliminating the skipped records problem and fixing the disk I/O inefficiency caused a noticeable decrease in average interactive response times. These subtle problems existed even though experienced programmers who knew the basics of AS/400 performance tuning wrote the applications causing them. The developers simply missed the performance implications during coding and testing, and it was only after the programs were in production (and running against large databases) that actual performance became a problem.

Tweaking Performance Still More

Even after these performance increases, end users still complained about a few individual screens that took too long to refresh for new input. To determine the cause, KOA used Workload Performance Analyzer's Application Optimizer module, which displays the percent of CPU or disk use by an object or lines of source code. As with the Query Optimizer module, product menu options facilitated performance test setup and data capture for a specific set of applications.

One of Application Optimizer's key features is its ability to track multiple objects during the same analysis run, enabling users to spot performance problems hidden in an obscure corner of the code. In most cases, a programmer familiar with the system can tell from an object-level report which programs need fine-tuning and can often fix the problems with just that information. KOA discovered that the underlying cause of the screen-refresh problem was created by some earlier database design changes made for business-process reasons.

The Application Optimizer also provides a Source Level Analysis function that lets developers pinpoint the line of code causing excess CPU or disk activity. By using this tool, KOA identified another problem SQL query that was causing 80 percent of the CPU consumption used by the entire program that contained it. The program was building access paths on the fly. By changing the SQL query to use preexisting access paths, developers dropped the screen response time from four or five seconds to between one-half and one second. Analysts incidentally discovered that the SQL query code had been included in the program by using a copy member, which meant the problem could never have been spotted by simply desk-checking the code. Another feature that proved useful to KOA is the product's built-in set of automated reports, review of which on a regularly scheduled basis can prevent performance surprises.

Starting with the initial demonstration of Workload Performance Series and continuing to the present, KOA's IS staff feels that MB Software has provided excellent technical support. All product training was handled completely via telephone during scheduled support calls with MB's technical experts. MB continues to provide KOA with technical help as needed on an ongoing basis and has let KOA preview some planned new features for the Workload Performance Series, such as a real-time alert capability that uses a built-in email function.

Extra Benefits

One unexpected benefit KOA got from using MB's product was an increase in security. By fine-tuning its applications, KOA was able to decrease their server workloads and develop an excellent feel for the daily performance patterns. This led to increased detection of security incidents. For example, while investigating spikes in CPU use, KOA analysts noticed that some security mechanisms were consuming more resources than normal because they were monitoring and rejecting some unauthorized access attempts. While the security software correctly handled the unauthorized traffic, KOA personnel felt it was reassuring to know that their new performance monitoring abilities provided an additional safety net for detecting unauthorized access attempts.

Although KOA has made no specific cost/benefit analysis, the IS staff is confident that Workload Performance Series' benefits have more than justified the product's cost. For example, KOA avoided at least one CPU upgrade, which would have pushed that system into the next software tier, costing KOA more for the increased interactive capacity. In addition to direct cash savings, KOA also benefits from predictable response times and a lack of applications that hog CPU or disk resources. The small number of programs that consume large amounts of CPU or disk have either been rewritten or are now submitted to batch, keeping end-user response time within acceptable limits, even during the busiest times. The beneficiaries are KOA's franchises and campers, who are unaware of how this is accomplished but are able to finish their reservation or registration transactions as quickly as possible.

KOA continues to use Workload Performance Series daily, and its IS staff is confident that IS won't be hampered for long by any performance issues in the future.

Dave Flint is a programmer/analyst for KOA Kampgrounds of America, Inc.