Rails adapter solutions for MySQL Cluster (NDB)?

Question

I'm setting up a high-availability environment for a customer. There are a pair of load-balanced hosts serving http requests to our Rails application, which is deployed in parallel on both hosts.

Additionally, there are two MySQL hosts available. I want to run MySQL Cluster (NDB) on both hosts (i.e., multi-master) to have a fully redundant configuration. I'm specifically trying to avoid a master-slave configuration based on database replication; I feel like that makes the writable node a single point of failure.

I'm looking for some guidance on how best to interface our Rails app to a multi-master MySQL cluster like this. Almost all of the database adapters I can find are for master-slave setups. Failover_adapter sounds very promising, but it's woefully outdated. I haven't managed to turn up anything similar developed in the last five years.

Is anyone aware of any gems to use or approaches to take to serve as an adapter between a Rails application and a multi-master MySQL cluster like I've described?

Answer 1

I ultimately was not able to find an adapter solution that did what I wanted.

I settled on using the mysql2 adapter and pointing it at a reverse-proxy (I used haproxy) in front of my database cluster that could handle load-balancing and failover between the master nodes.

Answer 2

I've just recently set this up. There shouldn't be much work to be done.

In the mysqladapter gem it specifies the engine as InnoDB, which is obviously not suitable for clustering. You need to add this to an initialization file:

ActiveRecord::ConnectionAdapters::Mysql2Adapter
class ActiveRecord::ConnectionAdapters::Mysql2Adapter
  def create_table(table_name, options = {})
    super(table_name, options.reverse_merge(:options => "ENGINE=NDB"))
  end
end

This is the original in the adapter.

def create_table(table_name, options = {})
  super(table_name, options.reverse_merge(:options => "ENGINE=InnoDB"))
end

Another key aspect is you don't want migrations taking place at the same time so in the deploy.rb file:

task :migrate, :max_hosts => 1 do
  # sleep 0.5
  run "cd #{release_path} && bundle exec rake db:migrate RAILS_ENV=#{rails_env}"
end

Max hosts prevents cap from running the migrations in parallel. This is important as you don't want the cluster running create table type of things at the same time. It may even be worth putting a delay in I have commented above just for a little extra safety.

One more key aspect. Don't forget to set:

DataMemory = 
IndexMemory =

The default values are supremely low. Typically index size is DataMemory/5-10

One more pitfall I've seen so far is in your mysqld nodes make sure you set:

ndb_autoincrement_prefetch_sz

to at least a 100. Otherwise bulk inserts will take forever. The default is 1.

EDIT:

ndb_autoincrement_prefetch_sz

Leave this variable completely alone. Don't set it. What it can cause is auto increment indexes to become out of sync on the cluster. Which is a nightmare to debug.

Additionally make sure you're NDB nodes don't run on the same server as the NDB MGM nodes.

Happy coding.

Ad