5

We currently run two Drupal instances (www1 and www2) behind a load balancer, and both instances are reading/writing the same mysql database. This seems like the wrong setup to me. Shouldn't we have it set up so that www1 reads and writes to db1, www2 reads and writes to db2, and db2 is a slave of db1?

6

db2 should never be written to if it is a slave of db1, because any write to db2 will never be propagated to db1, and they will quickly get out of sync. The database must be shared and consistent.

There are 3 ways of getting a shared and consistent database experience: (1) Only have one database for all reads and writes. This is the easiest, but provides a single point of failure, and unless the db is on a separate machine, it can result in unequal response times for the different servers. (2) Use a Master-Slave setup and split your read and write requests between the master and slave. Ideally, a master and slave databases are on different machines than the application servers. (3) Use a multi-master so that writes to one are propagated to the other databases automatically, and vice-versa. Again, usually the database machines are separate from the application servers.

The question is on how big does your system need to be? If you are big enough to need more than one database server, then you should be looking at a cluster solution (MariaDB with Multi-Master Galera cluster, for example), but for that you need an odd number (minimum of 3) machines, which should be separate from your application servers. Furthermore, in order to minimize bandwidth and cache rebuilding, you should also use a shared memcached server for cache tables.

As a point of reference, I implemented our system with a MariaDB Galera Cluster setup as a Multi-Master, but I treat it as a Master-Slave (which minimizes the chance of write conflicts). I use AutoSlave to separate read and write requests, and send the read and write requests to different ports using HAProxy. HAProxy then sends them to either a read or write server. The "read" and "write" servers are really just part of the same Multi-Master cluster, but if my write master goes down, I have configured HAProxy to failover to the "read" servers until the "write" server comes back up. The same thing happens for the "read" requests: Read requests are load balanced between the available read servers, but should they ever go down, HAProxy is configured to redirect all reads to the write server. The end result is that Drupal thinks it is splitting up reads and writes, the cluster thinks that it is a multi-master, and it performs like a Master-Slave, with the advantage that it does not fail if either the master or the slaves go down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.