What should a solutions architect do to meet these requirements?
Create a read replica. Move reporting queries to the read replica.
Create a read replica. Distribute the ordering application to the primary DB instance and the read replica.
Migrate the ordering application to Amazon DynamoDB with on-demand capacity.
Schedule the reporting queries for non-peak hours.
Explanations:
Creating a read replica allows reporting queries to be offloaded from the primary database instance. This separation can reduce contention for resources during order processing, thereby minimizing the risk of timeouts. Employees can run reporting queries against the read replica without affecting the performance of the primary database handling order transactions.
While creating a read replica can help, distributing the ordering application across the primary DB instance and the read replica is not feasible. The primary instance is designed for write operations and should handle order processing, while read replicas are meant for read operations. This option may complicate the application architecture and does not address the timeout issue effectively.
Migrating the ordering application to Amazon DynamoDB may not be suitable as it would require significant changes to the application logic. Additionally, DynamoDB is a NoSQL database, which may not support all the features used in the current MySQL application. This option does not directly resolve the issue of long-running reporting queries causing timeouts.
Scheduling reporting queries for non-peak hours may alleviate the immediate problem but is not a practical long-term solution. This approach may not be feasible if employees require access to real-time data or if business operations demand timely reporting. It does not address the fundamental issue of contention between order processing and reporting queries.
From my perspective, the answer is:
Create a read replica. Move reporting queries to the read replica.