Databases Reference
In-Depth Information
used with success, from open source software such as HAProxy to pretty much any
commercial system you can name.
Figure 11-10. A load balancer that acts as a middleman
Load balancers
There is a wide variety of load-balancing hardware and software on the market, but
few of the offerings are designed specifically for balancing load to MySQL servers.
14
Web servers need load balancing much more often, so many general-purpose load-
balancing devices have special features for HTTP and only a few basic features for
everything else. MySQL connections are just normal TCP/IP connections, so you can
use general-purpose load balancers for MySQL. However, the lack of MySQL-specific
features does add some limitations:
• Unless the load balancer is aware of MySQL's true load, it's unlikely to
balance
load
so much as
distribute requests
. Not all queries are equal, but general-purpose
load balancers usually treat all requests as equal.
• Most load balancers know how to inspect an HTTP request and “stick a session”
to a server to preserve session state on one web server. MySQL connections are
stateful too, but the load balancer is unlikely to know how to “stick” all connection
requests from a single HTTP session to a single MySQL server. This results in a
loss of efficiency (if a single session's requests all go to the same MySQL server, the
server's cache will be more efficient).
• Connection pooling and persistent connections can interfere with a load balancer's
ability to distribute connection requests. For example, suppose a connection pool
opens its configured number of connections, and the load balancer distributes
them among the existing four MySQL servers. Now say you add two more MySQL
servers. Because the connection pool isn't requesting any new connections, they'll
sit idle. The connections in the pool also might end up being unfairly distributed
among the servers, so some are overloaded and others are underloaded. You can
work around these problems by expiring the connections in the pool at various
14. MySQL Proxy is an exception, but it hasn't proved to work all that well in the field, due to problems such
as added latency and scalability bottlenecks.
