[Packer01] Chapter 19. Major Contributors to Poor Performance

Chapter 19. Major Contributors to Poor Performance

Sadly,good performance is a goal to be achieved rather than an inalienableconstitutional right. And there are many potential pitfalls on the wayto the goal.

Some of the most common problems in database server environments are:

• Inadequate hardware.

• Internal operating system architectural design problems.

• Poorly designed applications (including poorly written SQL).

• Problems with the user environment.

• Internal architectural inefficiencies in the DBMS being used.

• Poor data layout.

• Poor database design and implementation (including schema, choice of indexes, etc.).

• Operating system tuning.

• Database tuning.

Inthis chapter we briefly examine some of the major performanceroadblocks. The emphasis is on identifying rather than resolvingproblems since many of the problems outlined above are beyond the scopeof this book.

Performance Problem Identification

Don’tdecide your hardware is underconfigured until you’ve eliminated theother possibilities. Application issues can be major causes ofperformance problems and are an excellent starting point for aninvestigation.

Beginby understanding your limitations. Unless you are a kernel engineerworking for Sun or Oracle, you probably can’t change the internalarchitecture of the operating system or the database, so remove thisambition from your checklist. It makes sense to focus on the things youcan fix and bypass the things you can’t. But you might be able to fixproblems with the user environment, with database layout, or withtuning.

A listof issues follows in order of importance. Decisions about relativeimportance are subjective, of course. These are my opinions and yourmileage may vary!

Poorly Designed Applications

Theimpact on performance of a poorly designed application can beastonishing. Performance can be degraded significantly in theworst-case scenario.

Iremember being called to a customer site where a remote terminalemulator was being used for a stress test on a new application. With asmall number of users the system behaved normally, but as the loadincreased, response times climbed dramatically and throughput slowed toa standstill.

We ultimately discovered the cause of the problem after noticing a high number offork(2) system calls. The application was logging each databasetransaction; the log included a timestamp and the terminal number (tty)of the user connection.

The developer who coded the transaction logging function knew only one way of identifying the tty name: the UNIXtty program. So, within his C program he used thesystem (3C) function to execute thetty program and redirect the output to a temporary file. He then opened the file, read in thetty string, and closed the file. Finally, he deleted the temporary file, once again using thesystem function, using therm program since he was unaware of any better way of deleting a file from within the C programming language (such asunlink (2)).

Consequently, every database transaction required twosystem function calls, resulting in an expensivefork andexec (2) operation each time. The code ran to about 60 lines.

We replaced the 60 lines with two lines of code using thettyname (3C) function, eliminating bothsystem calls. Once the application was recompiled and deployed, the system behaved normally under load.

Thisexample illustrates the potential of poorly written applications tobring your system to its knees. It also highlights the value ofstress-testing applications before deployment. If you develop your ownapplications, I would strongly recommend using a remote terminal orbrowser emulator to test them during all phases of development toensure that performance goals are met. (The book website includes theHandsOff Remote Terminal Emulator.)

Unfortunately,the reality is that while application functionality is tested bydevelopers, application performance often remains untested, even forsome major software packages. Consequently, performance problems aresometimes detected only when the application is deployed.

Poorlywritten SQL is another potential performance black hole. I have seenSQL consultants used to good effect as part of an applicationdevelopment review cycle. In an ideal world, database optimizers wouldfind efficient ways of implementing even poorly coded SQL. That is thegoal, but we’re not there yet.

Poor Database Design and Implementation

Anexcellent logical design can prove inadequate under load. I have seen awidely used sequence number, read and incremented from the same heavilyaccessed row in the database, cause a major performance bottleneck.Heavy table inserts can also cause problems.

Someinitial investment in profiling performance is usually worth the effortin the long run, as is an independent review by expert consultants.

Problems with the User Environment

User environment settings (typically established by the.login,.cshrc, or.profilefiles) may not seem an obvious place to look for performance problems.Yet a system can be crippled by poorly constructed environmentvariables.

Iremember one occasion when a customer was unable to simultaneously login 100 users to their server. The problem proved to be thePATH environment variable. The major application programs were on another server, accessed through the automounter. ThePATH environment variable pointed first to several different automounted directories, then finally to/bin,/usr/bin, and other directories. The.profile contained some complex script code, including extensive use ofawk,sed,ls, and other utilities.

Before any one of these UNIX utilities could be used, it first had to be located from the list of directories inPATH. So, to findawk, for example, the system first searched all of the automounted directories before finally locatingawk in/usr/bin. Many users logging in at once caused the automounter to become a severe bottleneck.

Once the automounted directories were placed at the end ofPATH instead of the beginning, user logins proceeded normally.

Aswith application performance issues, user environment problems can bemost easily highlighted by a remote terminal emulator before they causea problem in production systems.

Poor Data Layout

Wehave already considered the need for a carefully planned data layoutstrategy. The implications of poor choices here are not difficult toimagine, especially since they are so commonly experienced!

• 
• Create Bookmark (Key: b)Create Bookmark
• Create Note or Tag (Key: t)Create Note or Tag
• Download (Key: d)Download
• Email This Page (Key: e)Email This Page
• PrintPrint

• Html View (Key: h)Html View
• Zoom Out (Key: -)Zoom Out
• Zoom In (Key: +)Zoom In
• Toggle to Full Screen (Key: f)
• Previous (Key: p)Previous
• Next (Key: n)Next

Related Content

Determining whether aggregates are required
From: Best Practices for SAP Business Information Warehouse on DB2 UDB for z/OS V8

Performance
From: Windows 2000 Administration in a Nutshell

Performance
From: DB2 for z/OS Stored Procedures: Through the CALL and Beyond

Performance
From: DB2 for z/OS Stored Procedures: Through the CALL and Beyond

I/O performance management
From: DB2 for z/OS Stored Procedures: Through the CALL and Beyond

System performance
From: IBM eServer Certification Study Guide - pSeries AIX System Administration

Performance Management
From: Windows 2000 Performance Guide

Performance management
From: AIX 5L Differences Guide Version 5.2 Edition

Performance
From: DB2 UDB for z/OS V8: Through the Looking Glass and What SAP Found There

Performance
From: Programming .NET Windows Applications