Continuent Community

Here is the situation: I need to do DB load balancing on a couple of web service my company provides because our DB machine is getting a bunch of reads and the load is killing us. The DB is small and gets updated from external data sources at most 2 or 3 times a day. The vast majority of interaction with our DB is just customers doing reads (SELECT operations) on various tables in our DB. (Actually it is our web service that does the reads on behalf of customers, who are just viewing webpages and want information, to be more precise).

I came across Tungsten Router and Connect and it looks like these might be the solutions top the problem. I say "might" because our web services are written in Perl and PHP. Even after I have read the Wikis and documentation, I still do not see anywhere where the docs tell you how exactly to call the connector from one of these languages. The whole point of Connector is (I think) to make it "easy" to use Tungsten Router when the calling program is not written in Java. Unfortunately the documentation does not say anything about how to actually do this.

So, let me take a guess and hopefully someone can tell me what I am doing wrong if anything.

Suppose I want to have 3 DB machines (1 master and 2 slaves) with exact copies of my DB. I also will have a 4th machine that runs the actual web service code, which for sake of argument I will say is in PHP.

I assume that I need to install a copy of both Tungsten Router and Tungsten Connector on each of these 3 DB machines. Is this correct? Or do I just need to install one copy on my script machine and the config files point to the actual DBs on the DB machines?

And how exactly do I connect with these from my PHP client code? Do I need to get some load balancer IP address that randomly switches between the 3 actual IP addresses of my actual DBs? When I connect to this load balancer IP address, do I have to connect to a special port (9999)?

As I'm sure you can see by now, I really don't understand how this architecture is supposed to work and how one is supposed to connect to it. perhaps someone out there can provide the missing details.

Thanks

Hello Marc!

My answer might not be as deep as you require, but for a start I would recommend you to do the following:

1. From reading your description, it seems to me that you want/need to use the whole Tungsten scale-out stack:

* Tungsten Replicator - for actually replicating MySQL master to slaves,

* Tungsten SQL Router - to route:
a) to the master - your PHP/Perl applications which execute writes to the DB (see RW_STRICT consistency mode in SQL Router documentation);
b) to one of the slaves - your applications which are read only (can these tolerate some possible delay in data? See RO_RELAXED consistency mode).

* Tungsten Connector - to connect your PHP/Perl applications to the SQL Router. To answer your question, Tungsten Connector + SQL Router should be running on your application servers. You should just be able to connect your applications to the Connector's 9999 port, which looks like a normal MySQL instance.

* Tungsten Monitor - to monitor whether DBs and Replicators are online/offline.

* Tungsten Manager on every node to manage everything.

2. I would suggest you participate in one of our free webinars: http://www.continuent.com/news/webinars
I believe these will help you better catch the overall architecture concept.

3. Also, a little recorded demo might shed some light for you: http://www.continuent.com/images/stories/video/continuent%20tungsten% 20performance%20demo.mov

Now, some inline answers to your questions:

QUOTE:
I say "might" because our web services are written in Perl and PHP.

Tungsten works on these.

QUOTE:
I assume that I need to install a copy of both Tungsten Router and Tungsten Connector on each of these 3 DB machines.

No. You install and configure Tungsten Replicator, Tungsten Manager and Tungsten Monitor on DB machines.

QUOTE:
Or do I just need to install one copy on my script machine and the config files point to the actual DBs on the DB machines?

Yes.

QUOTE:
And how exactly do I connect with these from my PHP client code?

You just connect to the Tungsten Connector's 9999 port as to a normal MySQL DB.

QUOTE:
Do I need to get some load balancer IP address that randomly switches between the 3 actual IP addresses of my actual DBs?

If you can use RO_RELAXED consistency mode to connect your mostly-SELECTs applications to the SQL Router, I would say you just try that for a start. This will direct your read only apps to the slaves which will free the master for applications using RW_STRICT mode in their DB connection strings.

QUOTE:
As I'm sure you can see by now, I really don't understand how this architecture is supposed to work and how one is supposed to connect to it.

That's why I encourage you to participate in one of the webinars and have a taste of a demo

Hope this helps some,

Best wishes!
Linas