MySQL Forums :: Router & Proxy :: MySQL Proxy: Scenarios, Use Cases

Advanced Search

Re: MySQL Proxy: Scenarios
Posted by: Gerry Reno ()
Date: July 10, 2007 11:21AM

I have to agree with Rick regarding MySQL Cluster (NBD). We tried using this as a backend to our webapps and the result was disasterous. There are many architectural limitations with MySQL Cluster that make it unusable in a webapp scenario. Just for example, all the system tables are stored in MyISAM and not NDB therefore all your user tables remain strictly local to each SQL node. So when a user wants to change a password it only gets changes with respect to the one SQL node where he is currently connected. Another serious limitation is when a user install a new webapp and runs its setup page which in turn creates the databases for the webapp. Well, if one of the nodes is down for maintenance when this webapp is installed it will never know about this database even when it comes back online so if the user is directed to this node by the load balancer then everything fails. I don't mean to pick on MySQL Cluster but it appears it was not architected with anything web related in mind and that is where the world is headed today.

So after we backed out MySQL Cluster we went looking for a way to cluster MySQL in more traditional ways such as replication. This is what we have now but there were some serious tradeoffs. The main one is that the vast majority of webapps have not been written with separate read and write connection handles so therefore you are forced to direct these webapps to only the master mysql server. This severely limits your ability to be able to balance traffic over a replication cluster. I was investigating writing a proxy for MySQL when I read an article on MySQL about Universal Server which was talking about DBIx::MyServer. At the end of the article there was an announcement regarding MySQL Proxy so I began investigating it. Turns out that MySQL Proxy is exactly the right tool for bringing load balancing to non-replication aware applications. So it will solve the load-balancing and HA as far as mysqld is concerned. But what about HA for the proxy itself. I would have preferred that the proxy was an implementation of a VRRP router. This way it would take care of HA for itself automatically. The idea is that all upstream clients would use a VIP to address the proxy. You would install two proxies on different machines and assign one of these the VIP. Being a VRRP router the failover router would monitor the active router and if it could not make contact it would then take over the VIP and continue service for upstream clients. The upstream clients would never know that they had been switched to the failover proxy. So this is what MySQL Proxy needs for its own HA. Now I've only been working with MySQL Proxy for two days so maybe it has a way to do this that is transparent to upstream clients. The issue is that it is not enough that MySQL Proxy provide HA to mysqld. It must also make sure that it does not become a SPOF and therefore must provide HA for itself. In the alternative, I have been mapping out some ways that I could externally provide HA for MySQL Proxy but things get ugly quickly when you have to provide complete transparency to upstream clients.


Options: ReplyQuote

Subject Views Written By Posted
MySQL Proxy: Scenarios, Use Cases 20776 Edwin DeSouza 07/09/2007 11:35PM
Re: MySQL Proxy: Scenarios 7620 Rick Blundell 07/10/2007 12:01AM
Re: MySQL Proxy: Scenarios 7658 Gerry Reno 07/10/2007 11:21AM
Re: MySQL Proxy: Scenarios 5286 Edwin DeSouza 07/10/2007 12:05PM
Re: MySQL Proxy: Scenarios 5247 Gerry Reno 07/10/2007 12:17PM
Re: MySQL Proxy: Scenarios 4851 Edwin DeSouza 07/10/2007 02:37PM
Re: MySQL Proxy: Scenarios 4729 Gerry Reno 07/10/2007 04:07PM
Re: MySQL Proxy: Scenarios 4311 Jason Burnett 02/21/2008 11:26PM
Re: MySQL Proxy: Scenarios 4649 Edwin DeSouza 07/10/2007 11:55AM
Re: MySQL Proxy: Scenarios, Use Cases 7525 Reinis Rozitis 07/20/2007 03:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4650 Jonathon Suggs 07/30/2007 01:21PM
Re: MySQL Proxy: Scenarios, Use Cases 4666 Reinis Rozitis 08/01/2007 05:58AM
Re: MySQL Proxy: Scenarios, Use Cases 4271 Jan Kneschke 08/01/2007 06:03AM
Re: MySQL Proxy: Scenarios, Use Cases 3961 Edwin DeSouza 02/13/2008 02:04PM
Re: MySQL Proxy: Scenarios, Use Cases 3893 Pete Clapham 04/08/2008 04:25AM
Re: MySQL Proxy: Scenarios, Use Cases 4880 Clint Alexander 07/28/2008 09:32PM
Re: MySQL Proxy: Scenarios, Use Cases 3525 Simon Mudd 09/24/2008 02:44AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.