Maximising Performance of Databases on Cloud
An average-sized online retail firm had migrated its application to the cloud for improved scalability and lower infrastructure maintenance. After several months of stable operation, customers began experiencing slow performance during product searches, order history checks, and purchases, particularly under higher workloads.
Bobcares investigated database logs, system metrics, and application reports to identify the causes. The analysis found inefficient queries, missing indexes, excessive database connections, and workload pressure. The database was then optimised through query improvements, indexing, connection pooling, read-only reporting, faster storage, maintenance, and continuous monitoring.
Business Challenge
The client's cloud-hosted application became slow as workload increased, with database CPU usage and query execution times rising during peak periods. Customers experienced delays in key functions such as product searches and purchases. The client needed to improve performance without immediately expanding infrastructure capacity.
Technology Used
Cloud-hosted database
SQL queries
Database indexes
Database connection pooling
Read-only database replica
Cloud storage
Database logs
System and application monitoring
Blueprint
The application connected to the primary database for customer transactions while connection pooling reduced repeated connection creation. Frequently used queries and indexes were optimised, while reporting workloads were moved to a read-only replica. Faster cloud storage supported database operations, with monitoring tracking system and query performance.
Key Outcomes
- Faster page loads during periods of high traffic.
- More consistent query execution times.
- Reduced database inefficiencies.
- More uniform processor utilisation.
- Improved storage performance.
- Reduced delays caused by disk operations.
- Delayed the need for additional infrastructure capacity.
- Earlier identification of potential performance issues through monitoring.
Customer Profile
The client was an average-sized online retail firm operating a cloud-hosted application. The application supported customer activities including product searches, order history access, and purchases. The company had moved to the cloud to improve scalability and reduce the maintenance associated with its on-premises infrastructure. As workload increased, database performance became a constraint for the application.
The Challenges
- Database CPU utilisation increased during peak hours.
- Frequently used queries scanned large tables because appropriate indexes were missing.
- Some queries retrieved more data than the application required.
- Large amounts of data were transferred between the database and application.
- The application created many database connections and kept them open for too long.
- Reporting workloads added pressure to the main database.
- Storage performance contributed to delays in disk operations.
- Database maintenance required regular statistics updates and index reorganisation.
Why Bobcares?
- Investigated the database and application workload before recommending infrastructure expansion.
- Identified query structure, indexing, connection management, and workload distribution as key performance factors.
- Focused on optimising the existing environment rather than immediately increasing infrastructure capacity.
- Separated reporting workloads from customer transactions using the available database architecture.
- Combined database optimisation with ongoing monitoring to identify performance variations early.
What We Delivered
- Analysed database logs, system metrics, and application reports over several weeks.
- Identified and reviewed the slowest SQL statements.
- Simplified queries where possible without changing application functionality.
- Removed unnecessary joins and limited queries to required columns.
- Added indexes to columns frequently used for search and sorting.
- Removed old and unnecessary indexes.
- Implemented connection pooling to reuse database connections.
- Moved reporting jobs to the read-only database replica.
- Improved database storage performance using faster cloud storage.
- Scheduled regular statistics updates and index reorganisation.
- Monitored processor, memory, query execution, storage latency, and database connections.
- Configured automated alerts for performance thresholds.
The Results
Query Performance
- Before: Several frequently used queries took longer to execute because they scanned large tables, performed unnecessary joins, or retrieved more data than required.
- After: Queries executed more efficiently after simplification, column selection, and index optimisation, resulting in more consistent execution times.
Application Performance
- Before: Customers experienced sluggish page loads during periods of higher traffic.
- After: Page loads became faster and more consistent during high-traffic periods.
Database Connections
- Before: The application created many database connections and kept them open for extended periods.
- After: Connection pooling allowed the application to reuse database connections and reduced inefficient connection handling.
Database Workload
- Before: Customer transactions and reporting workloads placed pressure on the main database.
- After: Reporting jobs were moved to a read-only replica, separating reporting activity from customer transactions.
Storage Performance
- Before: Disk operations could contribute to delays in database processing.
- After: Faster cloud storage improved storage performance and reduced delays caused by disk operations.
Infrastructure Capacity
- Before: Poor performance created pressure to consider additional infrastructure capacity.
- After: Addressing the underlying database inefficiencies delayed the need for additional hardware or infrastructure expansion.
The Business Impact
- Improved the customer experience during higher traffic periods.
- Reduced performance pressure on the primary database.
- Allowed customer transactions and reporting workloads to operate more independently.
- Reduced the immediate need for additional infrastructure investment.
- Improved operational visibility into database and system performance.
- Enabled earlier identification of performance variations before they became serious issues.
- Created a more maintainable approach to database performance as application workloads grow.
Ongoing Collaboration
- Monitor processor and memory utilisation.
- Review query execution times.
- Track storage latency and database connections.
- Review monitoring reports for performance variations.
- Maintain database statistics.
- Reorganise indexes regularly.
- Review database performance as application workloads and data volumes increase.
- Use monitoring alerts to identify emerging performance issues.
Conclusion
The client’s cloud application was experiencing database-related performance problems as workload increased. Instead of immediately expanding infrastructure, the environment was analysed and optimised across queries, indexing, connection management, workload distribution, storage, and monitoring. These changes improved application responsiveness and database stability while delaying the need for additional infrastructure capacity.
