Amazon RDS FAQs stated that it is up my application to distribute traffic to the read replica.
My websites have mostly visitors – non-logged in users. So, I suppose most database activities are read. I am supposed to set the MySQL database connection to the read replica for non-logged in users and the source RDS instance for logged-in ones?
My single RDS instance which power several websites just went bottleneck so I'm a little panicking and don't know where to start right now.
If the direction I'm going is not correct, kindly point me to the correct path then
Best Answer
That all depends on what your application(s) are and how they go about connecting to the database and running their queries.
Most applications I have seen setup two database connection pools/strings. One for reading and one for writing and the application code directs it's queries to whichever pool suits the query. Most applications are not written like this until they need failover though so this is probably going to need some coding.
You might be able to use mysql-proxy to filter particular queries to a read only back end.
You also might have more immediate gains by running a separate database instance for each of the applications rather than splitting the load via reads, if your load profile allows it.