MySQL 5.0 FAQ: Replication
2906
Let's say that system load consists of 10% writes and 90% reads, and we have determined by
benchmarking that
reads
is 1200 - 2 *
writes
. In other words, the system can do 1,200 reads per
second with no writes, the average write is twice as slow as the average read, and the relationship is
linear. Suppose that the master and each slave have the same capacity, and that we have one master
and
N
slaves. Then we have for each server (master or slave):
reads
= 1200 - 2 *
writes
reads
= 9 *
writes
/ (
N
+ 1) (reads are split, but writes replicated to all slaves)
9 *
writes
/ (
N
+ 1) + 2 *
writes
= 1200
writes
= 1200 / (2 + 9/(
N
+ 1))
The last equation indicates the maximum number of writes for
N
slaves, given a maximum possible
read rate of 1,200 per second and a ratio of nine reads per write.
This analysis yields the following conclusions:
• If
N
= 0 (which means we have no replication), our system can handle about 1200/11 = 109 writes
per second.
• If
N
= 1, we get up to 184 writes per second.
• If
N
= 8, we get up to 400 writes per second.
• If
N
= 17, we get up to 480 writes per second.
• Eventually, as
N
approaches infinity (and our budget negative infinity), we can get very close to 600
writes per second, increasing system throughput about 5.5 times. However, with only eight servers,
we increase it nearly four times.
Note that these computations assume infinite network bandwidth and neglect several other factors that
could be significant on your system. In many cases, you may not be able to perform a computation
similar to the one just shown that accurately predicts what will happen on your system if you add
N
replication slaves. However, answering the following questions should help you decide whether and by
how much replication will improve the performance of your system:
• What is the read/write ratio on your system?
• How much more write load can one server handle if you reduce the reads?
• For how many slaves do you have bandwidth available on your network?
B.13.9: How can I use replication to provide redundancy or high availability?
How you implement redundancy is entirely dependent on your application and circumstances. High-
availability solutions (with automatic failover) require active monitoring and either custom scripts or third
party tools to provide the failover support from the original MySQL server to the slave.
To handle the process manually, you should be able to switch from a failed master to a pre-configured
slave by altering your application to talk to the new server or by adjusting the DNS for the MySQL
server from the failed server to the new server.
For more information and some example solutions, see
Section 16.3.6, “Switching Masters During
Failover”
.
B.13.10: How do I tell whether a master server is using statement-based or row-based binary
logging format?
Check the value of the
binlog_format
system variable:
mysql>
SHOW VARIABLES LIKE 'binlog_format';
Содержание 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 ...