[HyperDB] Replication and Failover

[Callum Macdonald]

G'day,

Are you perhaps over complicating things?

Is your aim to provide high availability or high performance? The system
you've described doesn't seem to achieve either as far as I can see. Is
your primary aim fault tolerance (one server fails, whole system stays
online) or high performance (spread the load between as many servers as
possible)?

Are you suggesting to run two separate instances of MySQL on each
server? One as a master and one as a slave for the other server?

If you're looking for a high performance system, I'd suggest
partitioning your data between the servers using HyperDB. No need to
over complicate things with multiple mysql instances or replication.

If you want to create a highly available, fault tolerant system, would
simple master-master replication work for you?

I'm not sure about round robin DNS as a method of spreading load. If you
only have a small number of web nodes, then you may end up with a very
uneven load spread. Also, if DNS fails, your whole db system goes down,
thereby negating the advantages of being highly available.

How will you take an entry out of the dns list if a server goes down?

In terms of spreading filesystem writes between two machines, there are
a few options. Are you running the web nodes on separate servers from
the database? Or all on the same machines?

We have a cluster of 3 web nodes which all serve files from an NFS share
which is hosted by a SAN. It works relatively well in production.

I'm not an expert in these matters by any means, but if you outline your
overall aim I'll be happy to share my thoughts. :)

Cheers - Callum.

On Thu, 2008-10-23 at 14:02 +0200, Mustafa Suphi Yilmaz wrote:
> Dear Masters,
> We are planning to setup two servers acting both as master and slave. To do
> so, we were planning to manually configure both servers but we had a lot of
> worries like auto-increment values, failover and so on. Then, we found out
> about HyperDB, you might imagine that we got really excited. But the
> installation guide is quite poor, so, I want to share my concerns and await
> for your help on this matter.
> 
> Our plan is the following :
> 
> 1)  2 IP addresses in DNS, one for each server.  When DNS is set up with
> more than 1 IP address, they are given out by the DNS servers in round robin
> order.  This will spread the load randomly over both servers.
> 
> 2)  Both servers set up as a MySQL master and as a slave to the other one.
> As I mentioned earlier, the problem with this is only auto-increment
> columns. Each server will check the other once per minute and obtain any
> updates (only new MySQL writes).
> 
> 3)  There needs to be some mechanism to synchronize file system writes and
> uploads.  There are many ways to do this.  The most sophisticated way would
> be to set up server to server mirroring of one partition, where the Word
> Press files are.  I would use the same software already in use for disk
> mirroring, ggated.  However, I have never worked with this and there may be
> pitfalls. rsync might be a better idea.  One minor disadvantage of all the
> synchronization software I know about is that a master - slave relationship
> is always assumed.
> 
> When I looked up on the db_settings.php of HyperDB, add_server() values
> don't include any options about failover, and when I look into the code of
> db.php, I don't see how HyberDB handles auto-increment. Should the following
> configuration works for this kinda setup? What do you think?
> 
> add_db_server( 'global', 0, 'nyc1',  0, 1,  'global.db.example.com:3722',
>    'global.db.example.lan:3722',      'global-db1',  'global-db1-user',
> password');
> add_db_server( 'slave',  0, 'nyc1,  1, 1,  'global.db.example.com:3722',
>    'global.db.example.lan:3722',      'slave-db1',  'slave-db1-user',
> 'password');
> add_db_server( 'global', 0, 'nyc2', 0, 1,  'slave.db.example.com:3509',
> 'slave.db.example.lan:3509',       'global-db2', 'global-db2-user',
> 'password');
> add_db_server( 'slave',  0, 'nyc2', 1, 1,  'slave.db.example.com:3509',
> 'slave.db.example.lan:3509',       'slave-db2',   'slave-db2-user',
> 'password');
> 
> I also want to add a way of disabling writes to the databases when one of
> the servers is down. Any ideas?
> 
> Best Wishes,
> Mustafa Suphi Yilmaz
> http://paulocoelhoblog.com
> _______________________________________________
> HyperDB mailing list
> HyperDB at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/hyperdb