We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS.
At the beginning, when there were few users, we deployed using one EC2 instance and an RDS instance with a PostgreSQL database. After some time the number of users increased and we were facing problems like very slow response, time-out in different pages etc. Due to performance issue we took the following measures:
- We started using Redis cache for frequently accessed data (ElasticCache in AWS)
- We deployed in two different EC2 instances (one for web platform and another one for API for mobile application).
- We created a read-replica in RDS and added a Database router which chooses to use master database only for write operations and all read operations are performed in read-replica database.
This solution was working good for fast few weeks. After some time, all read operations became too slow. At this time, number of database connections to master database was on average 2-3 and spiked to 5-7 at times. But due to slow query execution in read replica database, 30-50 connections were common in read-replica database.
Queries using JOIN and Aggregates often failed with the following error in read-replica:
canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.
But generally all queries were very slow in read-replica compared to master database, even the simplest SELECT queries.
To be sure that the problem is not with a specific read-replica instance, we created another read-replica RDS instance (say read-replica-2) and pointed all read operations to read-replica-2 DB. This configuration began to perform well at the beginning but performance declined significantly within one day (for the first read-replica, it took 3-4 weeks).
After that, we modified our database router to randomly peak either one of the read-replica and read-replica-2 for any read operations, but still all queries to both of the read-replica databases are being executed very slowly. We checked by switching read operations to master database, and same read operations are being executed smoothly in master database without any problem.
Some server load related information:
- At peak hours, 500-1000 users use the system, most of them are from mobile application (through API EC2 instance).
- At peak hours, few users access web platform. But they often perform heavy DB intensive tasks (like bulk import and export of data).
- At off-peak hours (in a 6 hours window at local night time), some heavy DB intensive cron jobs are performed in the system for report generation and maintenance.
What should be a suitable architecture for us given the scenario? Are we missing something obvious? What can cause same queries run fast in master database and very slowly in read-replica database in RDS?
