DEMYSTIFYING DATABASE TUNING

Performance testing is a powerful tool for evaluating the efficiency of an application, but what about the unsung hero behind the scenes i.e. the database?

Many performance testers tend to treat the database as a black box, leaving it to the experts, however, if a performance issue is lurking anywhere in your application, it's most likely hiding in the database.

The good news is that evaluating and fine-tuning your database doesn't have to be a mysterious task, as it all boils down to a couple of key concepts.

MINIMISE PHYSICAL I/O

Physical I/O, or input/output, is a significant performance bottleneck. It's not only slow but also a resource hog, consuming valuable CPU. To tackle this, you can take several steps to reduce physical I/O:

•  Ensure your database has ample memory to store frequently accessed data in a data cache.

•  Optimize data retrieval queries to efficiently locate target data without sifting through extensive tables.

Minimizing physical I/O is a foundational step in enhancing database performance. By reducing the need for data to be fetched from disk, you can significantly speed up database operations and reduce resource consumption.

CPU UTILISATION : FINDING THE RIGHT BALANCE

While a low CPU utilization might seem like a good thing, it could indicate a poorly configured database. If your database is I/O bound or inadequately configured, it may not be able to handle data requests efficiently, leading to queuing.

In a well-configured database server, you'll see CPU utilization hovering around 80%, with occasional workload peaks pushing it closer to 100%. If your CPU utilization is consistently low, it's time to investigate and fine-tune.

Here are some areas to explore:

1). SQL QUERY PARSING

When a data request reaches the database, the SQL query must be parsed, meaning the database engine decides how to access tables to fulfill the query. Reusing a previously parsed query stored in a cache is far more efficient than parsing it from scratch.

To achieve this, you should ensure that your cache is large enough to store the required number of queries. Additionally, use bind variables to prevent the query's where clause from being affected by different data variables. For example, use "where name = :b1" instead of "where name = smith."

2). QUERY EXECUTION

After parsing, the query is executed, and the database must efficiently locate and retrieve the requested data. The access method is determined during query parsing. By using tools like "explain plan," performance testers can determine the access path.

An efficient query will require the retrieval of only a small number of rows, preferably from the database's buffer cache, rather than from disk. Inefficient queries, on the other hand, demand fetching a large number of rows, even if they reside in the buffer cache. This still consumes CPU power to locate, retrieve, and potentially sort the data.

Understanding and optimizing these key aspects of your database's performance is crucial to ensuring your applications run smoothly and efficiently. In the world of performance testing, the database is not a black box but a vital component that can make or break the user experience.

If you're looking for expert assistance in database tuning and performance testing, consider reaching out to us. We are a testing consultancy with a focus on helping you meet your performance testing requirements. Our performance tuning services can help ensure your database runs at peak efficiency, benefiting both your applications and your end users.