Mysqlnd replication and load balancing plugin (
mysqlnd_ms
)
2570
Example 20.234. Maximum age/slave lag
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
"failover" : "master"
}
}
Example 20.235. Limiting slave lag
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* Read from slaves lagging no more than four seconds */
$ret = mysqlnd_ms_set_qos($mysqli,
MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
MYSQLND_MS_QOS_OPTION_AGE, 4);
if (!$ret)
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
/* Plugin picks any slave, which may or may not have the changes */
if (!$res = $mysqli->query("SELECT item, price FROM daytrade"))
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
/* Back to default: use of all slaves and masters permitted */
if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))
die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));
?>
The eventual consistency service level can be used with an optional parameter to set a maximum slave
lag for choosing slaves. If set, the plugin checks
SHOW SLAVE STATUS
for all configured slaves. In
case of the example, only slaves for which
Slave_IO_Running=Yes
,
Slave_SQL_Running=Yes
and
Seconds_Behind_Master <= 4
is true are considered for executing the statement
SELECT
item, price FROM daytrade
.
Checking
SHOW SLAVE STATUS
is done transparently from an applications perspective. Errors, if
any, are reported as warnings. No error will be set on the connection handle. Even if all
SHOW SLAVE
STATUS
SQL statements executed by the plugin fail, the execution of the users statement is not
stopped, given that master fail over is enabled. Thus, no application changes are required.
Expensive and slow operation
Checking
SHOW SLAVE STATUS
for all slaves adds overhead to the application.
It is an expensive and slow background operation. Try to minimize the use of it.
Unfortunately, a MySQL replication cluster does not give clients the possibility to
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
