October 10, 2023
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.

Posted on:

October 10, 2023

in

Performance testing

category.

Is there a project You'd like to discuss?

related insights

Securing cloud-based applications

Usability and Accessibility Testing - What is the Difference ?

Incorporating Non-Functional Testing Early in the Software Development Cycle

Benefits / Drawbacks of Performance Testing in Test / Scaled Down Environments

Incorporating Performance Testing within CI/CD Pipelines

Obtaining Buy-In for Non-Functional Testing

Troubleshooting Performance Issues in Test Environments: A Real-World Scenario

‍Functional Test Automation: Why companies often feel let down by the outcome of their investment

The OWASP Top Ten - The Top 10 Web Application Security Risks

Avoiding Artificial Bottlenecks / Performance Issues in Performance Testing

Accessibility Guidelines - Understanding WCAG 2.1, the Upcoming WCAG 2.2 and Future WCAG 3.0 Updates

What is Volumetric Analysis ?

The Performance Testing Cycle Explained

Service Level Agreements vs. Non-Functional Requirements for Performance Testing

Applying Automated Test Solutions

Combining Performance Testing and Chaos Engineering

Non-Functional Testing Strategy for Performance

Explaining Penetration Testing

Explaining Performance Testing

Explaining Accessibility Testing

Explaining Penetration Testing

Silk Central Upgrade - "It's just a simple upgrade...."

Virtual Machine LoadRunner Load Generators on Azure Setup

How Selenium WebDriver can be used for Performance Testing

Performance Testing with SSO, OAuth

16 Tips Before You Automate

What is Automated Software Testing?

Load Testing and Performance Testing Tools

10 Top Tips for Automated Performance Scripts