Re: ndb load balancing

[Harrison Fisk]

Hi,

Robert McHugh wrote:
> Just wondering what the api and ndb nodes do with
> respect to load balancing. We have a setup that
> is going to load balance system queries. These
> system queries will potentially make database
> calls. How does an api node know which ndb node
> to query for information? Is it some kind of
> round robin determination or something more
> complex.

In general it needs to pick a certain server, or depending on the query, it might send the information to all of them. Keep in mind generally no single server has all of the data.

There are 3 different access types which resort in 3 different methods of access.

1. Primary key
Since the data is partitioned according to the primary key, this access method goes straight to the node that has the data.

2. Unique hash index
Similar to the primary key, but first goes to the node that has the part of the index, then goes straight to the correct node. So it actually has to access two nodes.

3. Scans
Occurs for full table scans or regular index usage. All nodes get asked for the appropriate data and return it in parallel.

Now since there is replication involved so there are multiple places to get the data even when the above is true. I am not totally sure what happens, but normally it doesn't matter too much since it is all internal. Hopefully one of the devs can confirm or deny what I have said below.

== start not sure piece ==
Some of the methods above will still retrieve data from all nodes (#3 above). In the case of #1 and #2 when it has to choose, it will do it in a round robin fashion. In the future it will do it based on which is the best connection (ie. shared memory instead of TCP/IP). I'm not entirely sure of this but believe that is what happens.
== end not sure piece ==

> We are also concerned that the load balancing
> solution in mysql will "fight" with the load
> balancer for the system calls (ie that the mysql
> will try to rebalance what the higher level load
> balnacer has already balanced)? Is there any
> reason to be concerned about this?

Not really. You only need to be concerned with connecting to the mysqld. Keep in mind some operations are also done on the mysqld, such as sorting if it can't use an index and so on. So there is still some operations that need to be load balanced in front of the mysqld. mysqld will handle all of the load on the data nodes automatically however. As far as load balancing the mysqld's you can go as simple or complex as you want. For example, Connector/J can do it automatically, or you could use hardware load balancers if you really want to.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com