0

My question is simple. Is it possible to load balance rds (master and read-replicas) using the same haproxy instance used for application load balancing?

This would mean that the IP of the application and the IP of the database would be the same. What would be a sample configuration for the mysql part?

For example for application load balancing there is something like this:

backend php_app_servers
  balance roundrobin
  option redispatch
  option forwardfor
  option httpchk GET /url.php
  server php-app1 10.100.2.40:80 weight 16 maxconn 160 check inter 10s
  server php-app2 10.81.4.104:80 weight 16 maxconn 160 check inter 10s
  server php-app3 10.100.129.162:80 weight 16 maxconn 160 check inter 10s

What settings should i pay attention to for mysql.

Also another issue I had is rds is performing pretty poorly compared to usual opsworks instances. The problem might be caused by the difference of availability zone between application servers and db server but the performance drop doesn't seem justified. We have a LOT of databases on a m3.2xlarge server. CloudWatch metrics show only low to average CPU, IO and memory usage metrics. The number of connections is also between 40 and 60 at any moment. Can such a difference be caused by the availability zone?

spdionis
  • 101
  • 1

1 Answers1

0

The short answer is no.

To split load in an RDS environment, you need to use multiple connections from your application - normally one connection to the read-replica for reads, one connection to the master for writes.

This is because a read replica is just that - a read replica, using MySQL master/slave replication - which by default cannot accept write requests like INSERT, UPDATE or DELETE SQL statements, and even if you flip the read_only switch, you will almost certainly break (perhaps irreversibly) your database.

If you absolutely must have multi-master replication in Amazon, your only choice is to run your own MySQL installations on top of EC2 instances, and use something like Percona XtraDB - a fork of MySQL - to achieve bi-directional replication.

The performance sub-question is better aimed at Amazon themselves as they will be able to look at your exact set-up and instance configurations - if you have "a lot" of databases with them, then you will at the very least have a basic support contract, check here for support options.

Craig Watson
  • 9,370
  • 3
  • 30
  • 46
  • But i do not want master-master replication. I only need master-slave replication. What i want tough is a way for my application code to not know about it, meaning a proxy that redirects reads to the slaves and writes/updates to the master. – spdionis Oct 03 '14 at 14:54
  • @spdionis - what you describe is what a DBAL is for, and this is required to be built into your application. In any case, the short answer to your question is still **no**, at least not with standard Amazon tooling. – Craig Watson Oct 03 '14 at 14:56