Cloud
Prowess, a leading e-commerce company, faced significant performance issues with its Oracle database when handling high user loads. Despite a robust infrastructure, including 10 CPUs and 300 GB of RAM, the system struggled to manage peak traffic, resulting in slow query responses, increased latency, and occasional system crashes. These performance bottlenecks directly impacted user experience, leading to lost sales opportunities and frustrated customers.
To resolve these issues, Prowess partnered with A3Logics, a technology solutions provider specializing in database optimization and performance testing. A3Logics conducted an in-depth assessment using HammerDB, DVD Store, and a comprehensive testing strategy to identify weaknesses and implement targeted improvements. The result was a highly optimized Oracle instance capable of handling high traffic efficiently while ensuring seamless user experience.
Prowess encountered multiple technical challenges that affected database performance, cross-platform integration, and real-time data accessibility. The major issues were as follows:
Although the Oracle database was running on a powerful infrastructure, it failed to handle high user concurrency effectively. The primary issue was disk I/O operations, which led to slow query execution times. This resulted in delays during peak shopping hours, impacting order processing and search functionalities.
Performance testing tools used by Prowess were Windows-based, while the Oracle database was running in a Linux environment. This mismatch made it difficult to conduct accurate performance tests and required additional effort to establish compatibility between the two platforms. The lack of seamless communication between the testing tools and the database led to unreliable test results.
Another major challenge was the inability to establish a shareable file system between the Linux and Windows environments. Since performance testing tools required access to database files, the lack of interoperability caused data inconsistencies, making debugging and performance evaluation difficult.
Prowess’s PHP-based application relied on object storage to manage files, logs, and transactional data. However, retrieving these files in real time was slow, affecting both backend operations and customer experience. Additionally, there was no fallback mechanism in place—if object storage access failed, the system did not have an alternative method to retrieve files, leading to downtime and data inaccessibility.
To address these challenges, A3Logics designed a multi-layered optimization strategy, focusing on performance enhancements, file system restructuring, object storage integration, and rigorous performance testing.
A detailed performance audit was conducted to simulate high-load scenarios and pinpoint system bottlenecks. The team used the fio command on remote machines to generate heavy disk I/O and analyzed system behavior using the atop command. The read IOPS (Input/Output Operations Per Second) values were recorded, providing a clear picture of the database’s efficiency before and after optimization.
To ensure smooth data access across Linux and Windows, A3Logics implemented Oracle Cluster File System 2 (OCFS2), enabling seamless file sharing. A Proof of Concept (POC) was conducted to validate cross-platform access, confirming that the new file system could efficiently handle read and write operations in both environments. This eliminated compatibility issues and allowed Windows-based performance testing tools to interact with the Oracle database without restrictions.
Optimizing object storage was a critical component of the solution. A3Logics conducted a POC for real-time file storage and retrieval, ensuring that large files could be accessed instantly without delays. PHP functionality was modified to allow direct file reads from object storage, significantly reducing dependency on traditional disk access. Additionally, a fallback mechanism was implemented, enabling PHP to switch between shareable disks and object storage if retrieval issues occurred, ensuring uninterrupted access to critical files.
Since the PHP-based application relied on D3 Charts for data visualization, A3Logics optimized the performance by enabling direct file reads from Windows shareable disks and reducing lookup times. The D3 charts were also updated to merge and visualize multiple CSV files efficiently, improving the overall user experience.
Terraform scripts were updated to reflect the new infrastructure and application flow, ensuring smooth deployment of the optimized system. The scripts were modified to automate the setup of Oracle instances, clustered file systems, and performance testing tools, reducing manual configuration efforts.
After implementing the optimizations, extensive performance testing was conducted. A 20GB DVD Store dataset was loaded to create realistic test scenarios, and performance stress tests were executed under different user loads. The success of SSH scripts and D3 chart performance was also assessed to ensure stability.
The implemented solutions resulted in significant improvements across various performance metrics:
After optimization, the maximum read IOPS increased from 20,000 to 30,000, reflecting a 50% improvement in input/output operations. The average read IOPS improved by 23%, rising from 15,000 to 18,500. Additionally, average I/O latency dropped from 8 milliseconds to 5 milliseconds, a 38% improvement, reducing the delay in data retrieval.
The introduction of OCFS2 led to a 40% improvement in Linux throughput, which increased from 250 MB/s to 350 MB/s. Similarly, Windows throughput improved by 60%, rising from 200 MB/s to 320 MB/s, ensuring smooth file exchanges between both platforms.
Real-time file retrieval times on Windows improved by 40%, decreasing from 5 seconds to 3 seconds. The PHP direct file read time improved by 37.5%, reducing from 4 seconds to 2.5 seconds, ensuring a faster and more efficient file access experience.
The time taken for shareable disk reads on Windows dropped by 40%, from 3 seconds to 1.8 seconds. Similarly, object storage fallback read times improved by 25%, decreasing from 4 seconds to 3 seconds. The D3 chart rendering speed also saw a 31.25% improvement, reducing from 800 milliseconds to 550 milliseconds, allowing users to visualize data faster.
The DVD Store data load time decreased by 16.7%, reducing from 60 minutes to 50 minutes. The average response time for the 20GB database improved by 16.7%, dropping from 3 seconds to 2.5 seconds. Additionally, the SSH script success rate increased from 90% to 98%, showing a 8.9% improvement in system reliability.
After optimization, the maximum read IOPS increased from 20,000 to 30,000
Windows throughput improved by 60%
The PHP direct file read time improved by 37.5%, reducing from 4 seconds to 2.5 seconds
The DVD Store data load time decreased by 16.7%, reducing from 60 minutes to 50 minutes.
The optimization of Prowess’s Oracle instance by A3Logics resulted in a highly efficient, robust, and scalable system capable of handling large user loads with ease. The performance enhancements led to faster query execution, reduced latency, and improved file access speeds, directly benefiting the e-commerce platform’s customer experience.
With these improvements, Prowess can now support peak traffic efficiently, ensuring smooth order processing and fast data retrieval. The success of this project highlights the importance of structured database optimization, cross-platform compatibility, and real-time data access in large-scale e-commerce operations. By leveraging OCFS2, object storage integration, and optimized performance testing, Prowess has secured a competitive edge in the online retail space.
This case study serves as a blueprint for businesses facing similar database performance issues, demonstrating how a well-executed optimization strategy can lead to significant system improvements and business growth.
Ready to dive deeper into the transformative power of AI in healthcare e-commerce? Download our comprehensive whitepaper for exclusive insights, detailed strategies, and a roadmap to revolutionize your operations. Gain access to additional use cases, future technology trends, and a step-by-step guide to implementing AI solutions effectively.
“All names, personal identifiers, and identifying details referenced herein, including but not limited to those pertaining to the client entity and any individuals described, have been altered, substituted, or otherwise anonymized. These modifications have been undertaken to ensure the protection of personal privacy and confidentiality, consistent with applicable data protection laws and regulations. Notwithstanding these changes to nomenclature and other personal identifiers, the events, situations, and circumstances depicted herein are based on actual, real-time scenarios and occurrences. Accordingly, while every effort has been made to preserve the accuracy and integrity of the factual circumstances, any resemblance of named parties to actual persons, whether living or deceased, is coincidental, unintended, and solely attributable to the anonymization process. All entities and individuals, as represented in this document, are presented in a manner that preserves the substantive essence of their roles, activities, and impacts, while ensuring compliance with legal and ethical standards of privacy and confidentiality.”
Marketing Head & Engagement Manager
