Mysql – Multi Replications Plan on 2 servers

MySQLreplication

I need some advice about our new database deployment plan.

We have 6GB of total mysql data(all innoDB) and innoDB_buffer_pool is set to be 16GB
Ans we have 2 database servers.(plus 1 server for the nginx and php). Mysql Version is 5.6.20

  • Server #1 – 1 Qual Core CPU , 48GB ram
  • Server #2 – 2 Quad core CPUs, 96GB ram

We currently have 1 master for server #1, and 1 slave for server #2 and using mysqlnd_ms plugin to do real/write splitting for better performance.

But since we do have plenty of ram spaces we are seeking some ideas to boost our database performance up.

Our question is if we have plenty of ram space like this, would it be beneficial if we have multiple mysql replications each server and use mysqlnd_ms plugin to distribute traffics?

For instance,

  • for Server #1 – 1 replication slave and 1 master.
  • for Server #2 – 4 replication slaves
  • Plus load balance using mysqlnd_ms plugin.

We just assume it could bring some performace advantage, but this is unser production so we can't tweak these servers freely.

If this idea is meaningless, could you provide some ideas?

Best Answer

Let me give you a very broad piece of advise: I think you are trying optimisation in the wrong way. You are doing "We have spare memory, let's use it for something else" (which is otherwise very lucky and many people will envy you) instead of "let's identify our bottleneck and fix that with our resources".

I am not telling you that your idea is wrong, but in general, scaling out with slaves does not provide a better latency (and in many other cases, clustering it reduces it), but a better throughput (which if you were under high load, can sometimes mean better latency). I do not know if that is a problem that must be solved in your case. While having multiple mysql servers in the same machine is common, for example, in the hosting industry, they do not do it for performance, but for a cost-saving reason (consolidation). Also have into account that you may be more than doubling your maintenance/administration costs.

My real advice would be to identify your bottleneck: latency, throughput on where? Memory? Disk? Network? And try to use the extra resources in the best way- Are temporary tables being created? Can we use ramdisk for that? Are many queries too slow even if they are executed on memory? Can we setup a on-memory key-value cache system (memcache, by the way, integrated on MySQL 5.6-InnoDB) for caching results of frequent queries? Your write throughput is maximised? Maybe you need sharding. Your read throughput is maximised while your write throughput, network and disk bandwidth is low? Maybe your idea is ok. But you need metrics in order to support such an important architecture decision.

Related Question