Maximising Cloud Database Performance for an Online Retail Application
The client was an online retail business with an application recently moved to the cloud to achieve scalability and decrease on-premises maintenance. Customers started to have slow performance when searching for products, accessing the order history and making purchases, especially in peak hours after a few months of stability.This slow performance was noted during product searches, access to order history and purchasing, particularly in peak hours after several months of stability.
Bobcares analyzed database logs, system metrics and application reports to find out what caused the bottlenecks. The team improved performance of the SQL queries, indexes, implemented connection pooling, shifted reporting workloads to a read-only replica, optimized database storage performance and set up continuous monitoring and alerting.
Business Challenge
The cloud-hosted database was experiencing performance degradation as workload increased. High CPU utilization, slow queries, excessive data retrieval, and prolonged database connections affected customer-facing operations. The client needed to improve database performance without immediately increasing infrastructure capacity or incurring additional hardware costs.
Technology Used
Blueprint
The application database was optimized at multiple layers, from SQL queries and indexes to connection management and storage. Reporting workloads were separated onto a read-only replica, while connection pooling reduced repeated connection creation. Monitoring and alerts tracked database health and performance after the changes.
Key Outcomes
- Improved database query execution efficiency.
- Faster page loads during periods of high traffic.
- More consistent CPU utilization.
- Reduced delays associated with storage operations.
- Reduced load on the primary database through reporting workload separation.
- Delayed the need for additional infrastructure capacity.
Customer Profile
The Challenges
- Database CPU utilization increased during peak hours.
- Frequently used queries scanned large tables because of missing indexes.
- Some queries retrieved more data than the application required.
- Excessive database connections remained open for extended periods.
- Reporting workloads added pressure to the primary database.
- Storage performance contributed to delays during database operations.
- Growing workload increased the impact of existing database inefficiencies.
Why Bobcares?
- Analyzed database logs, system metrics, and application reports to identify performance bottlenecks.
- Focused on query and database-level inefficiencies before recommending additional infrastructure.
- Optimized indexing based on frequently used search and sort criteria.
- Separated reporting workloads from customer transactions using a read-only replica.
- Addressed connection management through connection pooling.
- Combined optimization with ongoing monitoring to identify future performance issues.
What We Delivered
- Identified and analyzed the slowest SQL statements.
- Simplified queries by removing unnecessary joins and selecting only required columns.
- Reviewed and optimized database indexes.
- Added indexes to frequently searched and sorted columns.
- Removed old and unnecessary indexes.
- Implemented connection pooling for database connections.
- Moved reporting jobs to a read-only database replica.
- Improved database storage performance using faster cloud storage.
- Established regular statistics updates and index reorganization.
- Configured monitoring for CPU, memory, query execution time, storage latency, and database connections.
- Implemented automated performance alerts.
The Results
Query Performance
- Before: Frequently used queries scanned large tables and some retrieved more data than required, increasing execution time and data transfer.
- After: Queries were simplified and optimized with appropriate indexes, resulting in more consistent execution times and improved application responsiveness.
Database Load
- Before: Customer transactions and reporting workloads placed additional load on the primary database.
- After: Reporting jobs were moved to a read-only replica, separating reporting activity from customer transactions.
Database Connections
- Before: The application created numerous database connections and kept them open for extended periods.
- After: Connection pooling allowed the application to reuse database connections and reduced unnecessary connection creation.
Storage Performance
- Before: Storage operations contributed to delays during database activity.
- After: Faster cloud storage improved storage performance and reduced delays associated with disk operations.
Infrastructure Capacity
- Before: Database performance issues created pressure to consider additional infrastructure capacity.
- After: Database-level inefficiencies were addressed without an immediate infrastructure upgrade, delaying the need for additional hardware.
The Business Impact
- Improved application responsiveness during periods of high traffic.
- Supported smoother customer interactions across product searches, order history, and purchases.
- Reduced pressure on the primary database by separating reporting workloads.
- Delayed the need for additional infrastructure expenditure.
- Reduced operational risk associated with database performance degradation.
- Provided earlier visibility into performance changes through monitoring and alerts.
Ongoing Collaboration
- Monitor database CPU and memory utilization.
- Review query execution performance.
- Monitor storage latency and database connections.
- Maintain database statistics and indexes.
- Review monitoring reports for performance variations.
- Assess emerging database performance issues as application usage and data volumes grow.
- Troubleshoot performance issues when required.
