MySQL 5.0 FAQ: Replication
2905
2. On the slave, issue the following statement, where the arguments to the
MASTER_POS_WAIT()
[966]
function are the replication coordinate values obtained in the previous
step:
mysql>
SELECT MASTER_POS_WAIT('log_name', log_pos);
The
SELECT
statement blocks until the slave reaches the specified log file and position. At that
point, the slave is in synchrony with the master and the statement returns.
3. On the master, issue the following statement to enable the master to begin processing updates
again:
mysql>
UNLOCK TABLES;
B.13.5: What issues should I be aware of when setting up two-way replication?
MySQL replication currently does not support any locking protocol between master and slave to
guarantee the atomicity of a distributed (cross-server) update. In other words, it is possible for client
A to make an update to co-master 1, and in the meantime, before it propagates to co-master 2, client
B could make an update to co-master 2 that makes the update of client A work differently than it did
on co-master 1. Thus, when the update of client A makes it to co-master 2, it produces tables that
are different from what you have on co-master 1, even after all the updates from co-master 2 have
also propagated. This means that you should not chain two servers together in a two-way replication
relationship unless you are sure that your updates can safely happen in any order, or unless you take
care of mis-ordered updates somehow in the client code.
You should also realize that two-way replication actually does not improve performance very much (if
at all) as far as updates are concerned. Each server must do the same number of updates, just as you
would have a single server do. The only difference is that there is a little less lock contention because
the updates originating on another server are serialized in one slave thread. Even this benefit might be
offset by network delays.
B.13.6: How can I use replication to improve performance of my system?
Set up one server as the master and direct all writes to it. Then configure as many slaves as you have
the budget and rackspace for, and distribute the reads among the master and the slaves. You can
also start the slaves with the
--skip-innodb
[1236]
,
--low-priority-updates
[414]
, and
--
delay-key-write=ALL
[406]
options to get speed improvements on the slave end. In this case, the
slave uses nontransactional
MyISAM
tables instead of
InnoDB
tables to get more speed by eliminating
transactional overhead.
B.13.7: What should I do to prepare client code in my own applications to use performance-
enhancing replication?
See the guide to using replication as a scale-out solution,
Section 16.3.3, “Using Replication for Scale-
Out”
.
B.13.8: When and how much can MySQL replication improve the performance of my system?
MySQL replication is most beneficial for a system that processes frequent reads and infrequent writes.
In theory, by using a single-master/multiple-slave setup, you can scale the system by adding more
slaves until you either run out of network bandwidth, or your update load grows to the point that the
master cannot handle it.
To determine how many slaves you can use before the added benefits begin to level out, and how
much you can improve performance of your site, you must know your query patterns, and determine
empirically by benchmarking the relationship between the throughput for reads and writes on a typical
master and a typical slave. The example here shows a rather simplified calculation of what you can
get with replication for a hypothetical system. Let
reads
and
writes
denote the number of reads and
writes per second, respectively.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...