Databases Reference
In-Depth Information
Weighted
The load balancer can combine and weight several of the other algorithms. For
example, you might have single- and dual-CPU machines. The dual-CPU machines
are roughly twice as powerful, so you can tell the load balancer to send them an
average of twice as many requests.
The best algorithm for MySQL depends on your workload. The least-connections al-
gorithm, for example, might flood new servers when you add them to the pool of avail-
able servers—before when their caches are warmed up. The authors of this topic's first
edition experienced that problem firsthand.
You'll need to experiment to find the best performance for your workload. Be sure to
consider what happens in extraordinary circumstances as well as in the day-to-day
norm. It is in those extraordinary circumstances—e.g., during times of high load, when
you're doing schema changes, or when an unusual number of servers go offline—that
you can least afford something going terribly wrong.
We've described only instant-provisioning algorithms here, which don't queue con-
nection requests. Sometimes algorithms that use queuing can be more efficient. For
example, an algorithm might maintain a given concurrency on the database server, such
as allowing no more than
N
active transactions at the same time. If there are too many
active transactions, the algorithm can put a new request in a queue and serve it from
the first server that becomes “available” according to the criteria. Some connection
pools support queuing algorithms.
Adding and removing servers in the pool
Adding a new server to the pool is usually not as simple as plugging it in and notifying
the load balancer of its existence. You might think it'll be OK as long as it doesn't get
flooded with connections, but that's not always true. Sometimes you can add load to
a server slowly, but some servers whose caches are cold might be so slow that they
shouldn't get
any
user queries for a while. If it takes 30 seconds to return the data a
user needs to see for a page view, the server is unusable even for a small amount of
traffic. You can avoid this problem by mirroring
SELECT
traffic from an active server for
a while before you notify the load balancer about the new server. You can do this by
reading and replaying the active server's log files on the newly started server, or by
capturing the production server's network traffic and replaying a portion of its queries.
The
pt-query-digest
tool from Percona Toolkit can help with this. Another tactic that
can work well is to use the fast warmup features in Percona Server or MySQL 5.6.
You should configure the servers in the connection pool so that there is enough unused
capacity to let you take servers out for maintenance, or to handle the load when servers
fail. You need more than just “enough” capacity on each server.
Make sure your configuration limits are high enough to work when servers are out of
the pool. For example, if you find that each MySQL server typically has 100 connec-
tions, you should set
max_connections
to
200
on each server in the pool. Then, even if
