By Julian Stuhler
In this article, I want to take a look at database performance, and the reasons why it remains one of the most important topics for any DBA, developer or systems administrator. I'll also cover some golden rules for performance tuning, which tend to apply regardless of the specific platform or application you're responsible for.
So, what's the big deal with database performance? Fundamentally, database performance tuning is done for two basic reasons, and either or both can apply for any given situation:
• Reduce response time. With today's internet-enabled, customer facing applications, providing sub-second response times for OLTP applications has never been more critical. A few years ago a skilled telephone customer support operative could hide longer response times for account queries by chatting with the caller, but now that the customer has direct access to those same systems any delays become much more obvious. Many studies have demonstrated the link between response time and customer satisfaction for online B2C applications, and when your nearest competitor is only a click away, keeping customers happy is paramount. Although database performance is just one component of the overall end-to-end response time experienced by the customer, it is often the most critical and variable one.
• Reduce resource usage. Running any sort of query against a database generates a load on the database server, and the less efficiently that query runs the more CPU and I/O it will require. These are finite resources for a given server configuration, so the transaction throughput can be limited at peak times (potentially leading to a response time issue as described above). Tuning a system to reduce the CPU and/or I/O resources required can have a significant impact on the transaction throughput for a given server, potentially allowing expensive upgrades to be avoided or deferred. Reducing resource usage can be even more important in a mainframe environment, where most customers are on some form of Monthly Licence Charge (MLC) model. With MLC, the amount a System z customer pays depends on the amount of processor resource consumed during the previous period, measured in MSUs (Million Service Units). So, the monthly cost paid by most customers for their IBM software is directly related to the amount of work their CPs process: the higher the workload, the higher the monthly cost. Tuning the application to reduce CPU costs can generate immediate savings in software licence fee charges.