2 Advanced Read/Write Splitting with PHP’s MySQLnd
原文地址需FQ才能看 https://blog.engineyard.com/2014/advanced-read-write-splitting-with-phps-mysqlnd
In part one in this series we took our first look at mysqlnd_ms, the the MySQLnd plugin for read/write splitting. In this article we’ll look at more advanced usage.
在本系列第一部分中我们把第一次看mysqlnd_ms,读/写分离MySQLnd插件。在本文中,我们将查看更高级的用法。
Multiple Configurations --多个配置
Keen eyed readers of part one may have noticed that the mysqlnd_ms configuration has a top-level key named (in our examples) “appname” which contains all of our configuration. This allows you to specify multiple configurations in one configuration file.
读第一部分时敏锐的读者可能已经注意到,mysqlnd_ms配置顶级关键命名(在我们的示例中)“appname”,包含了我们所有的配置。这允许您指定多个配置在一个配置文件。
So you have:
{
"APP-SPECIFIC-KEY": {
"master": {
...
},
"slave": {
...
}
}
}
To specify which configuration to use you use this key as the hostname to connect to.
如果想指定使用某个配置，只需要连接时 把 配置的 top-level key当做 hostname就行了。
Using mysqli:
$mysqli = new mysqli("appname", "user", "password", "dbname");
Or using PDO:
$pdo = new PDO("mysql:host=appname;dbname=dbname", "user", "password");
Note: You need to do this even if you only have one configuration, otherwise the mysqlnd_ms configuration will not be used at all.
你需要这样做,即使你只有一个配置,否则mysqlnd_ms配置将不被使用。
Failover --故障转移
The mysqlnd_ms plugin allows for automatic failover should a connection fail while trying to connect to a server (which happens transparently when issuing the first query to it) — it is disabled by default, as there are issues with state (e.g. SQL user variables). However, it is easy to set up, by setting the failover.strategy configuration option.
mysqlnd_ms插件允许自动故障转移 会在试图连接到服务器连接失败时 (发生透明当发出第一个查询)——它在缺省情况下是禁用的,与state有相关(例如SQL用户变量)。然而,很容易设置,通过设置故障转移。策略配置选项。
This option has three possible values:
disabled: Never failover automatically. This is the default.- 不开启自动故障转移，默认选项
master: Always failover to the master- 发生故障时转移到主服务器
loop_before_master: If a slave request is being attempted, it will first loop through the slaves, before trying the master.- 先遍历从服务器，尝试转移！最后尝试转移主服务器
{
"appname": {
"master": {
...
},
"slave": {
...
}
"failover": {
"strategy": "loop_before_master"
}
}
}
In addition to setting the strategy, you can additionally set two other settings:
除了设置策略,您可以另外设置两个其他设置:
remember_failed: This setting will tell the plugin to remember failed servers for the remainder of the session. This defaults to false, but is recommended.- 这个设置会告诉插件要记住失败服务器会话的其余部分。这个默认值为false,但建议开启。
max_retries: The number of retries that should be attempted on each server before considering it failed. Each server will only be tried once per iteration of the list, and will be removed only after failing N times. This defaults to 0 which means unlimited, so a server will never be removed; this conflicts with theremember_failedconfiguration option.- 重试的次数,应该在每个服务器上尝试再考虑它失败了。每个服务器将只尝试一次每个迭代的列表,并失败后N次后将删除。这个默认值为0,这意味着无限的,所以服务器永远不会被删除,这冲突remember_failed配置选项。
{
"appname": {
"master": {
...
},
"slave": {
...
}
"failover": {
"strategy": "loop_before_master",
"remember_failed": true,
"max_retries": 1
}
}
}
Note: when in transactions (as discussed below), failover is implicitly disabled.
在事务时(下面讨论),故障转移被隐式地禁用了。
Transactions
One of the pitfalls of splitting queries, and load balancing between multiple read servers is that transactions could end up with different queries hitting different servers.
分离查询 和 多个读服务器之间的负载均衡 的陷阱之一 是 事务可能 结束于这种情况：不同的查询最命中不同的服务器。
This is a particular issue when you call INSERT/UPDATE/DELETE and want to then SELECT on that modified data set before completing the transaction.
这是一个特殊的问题 当你调用INSERT/UPDATE/DELETE之后想要在完成事务之前查询修改过的数据集。
To solve this, you can either use the MYSQLND_MS_MASTER_SWITCH or MYSQLND_MS_LAST_USED_SWITCH SQL hints, or if you are using mysqli, or PDO, you can let the plugin automatically track transactions.
为了解决这个问题,您可以使用MYSQLND_MS_MASTER_SWITCH或MYSQLND_MS_LAST_USED_SWITCH SQL提示,或者如果你正在使用mysqli,或PDO,你可以让插件自动跟踪事务。
To do the latter, you must either turn auto-commit off at the start of your transaction, and on at the end, or it can hook into the transaction methods.
后者,你必须关闭自动提交的事务,并在最后,或者它可以钩到事务的方法。
First you need to setup the plugin configuration to enable sticky transactions:
首先你需要安装插件配置启用棘手的事务:
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
"trx_stickiness": "master"
}
}
To use auto-commit you should do:
// MySQLi extension
$mysqli->autocommit(false); // disable autocommit, implicitly starts a transaction
$mysqli->query('BEGIN');
// queries
$mysqli->query('COMMIT'); // or ROLLBACK
$mysqli->autocommit(true); // enable autocommit
// PDO extension
$pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', false); // disable autocommit, implicitly starts a transaction
$pdo->exec('BEGIN');
// queries
$pdo->exec('COMMIT'); // or ROLLBACK
$pdo->setAttribute('PDO::ATTR_AUTOCOMMIT', true); // disable autocommit
The plugin also tracks the begin transaction, commit, and rollback methods for both extensions, and will send all requests to the master when you call PDO->beginTransaction(), or mysqli->beginTransaction(), while PDO->commit(), PDO->rollback(), mysqli->commit(), or mysqli->rollback() will end the transaction.
插件还跟踪事务开始、提交和回滚方法的扩展,并将所有请求发送到主服务器当你调用PDO - > beginTransaction(),或mysqli - > beginTransaction(),而PDO - > commit(),PDO - > rollback(),mysqli - > commit(),或mysqli - > rollback()将结束事务。
Filters
mysqlnd_ms supports a filter chain for determining which server will determining be used to execute the query.
mysqlnd_ms支持过滤器链 来 确定哪些服务器最终将用于执行查询。
There are two types of filters:
multifilters: these return a list of master, slave, or both servers from which one should ultimately be picked- 这些返回的主,从,或两个服务器最终应该选哪一个的列表
singlefilters: these return a single server
You can specify multiple multi filters, and multiple single filters, each one is passed the result of the previous filter.
您可以指定多个multi filters,和多个single filters器,每一个都通过了前面的过滤器的结果。
Filters are executed in the order they are defined in the configuration file. You must end with a single filter, which will typically be one of the load balancing filters — random, random_once, or roundrobin — or user.
过滤器的顺序执行他们在配置文件中定义。你必须用一个过滤器,它通常是一个负载平衡的过滤器——random, random_once, or roundrobin——或用户。
Replication Lag --复制滞后
The first filter we will look at is the quality of service (qos) multi filter.
One of the primary reasons to use the MYSQLND_MS_LAST_USED_SWITCH SQL hint is to send reads to the master once a write has occurred so that you are not affected by replication lag; but otherwise to use the slave to distribute the load.
主要的原因之一是使用MYSQLND_MS_LAST_USED_SWITCH SQL提示发送读取到master发生一次写,这样你不受复制滞后;否则使用从服务器分发负载。
This is also possible to achieve transparently by using the master_on_write configuration setting:
这也是能够实现 通过使用master_on_write配置设置:
{
"appname": {
"master": {
...
},
"slave": {
...
}
"master_on_write": 1
}
}
However, this would mean that all remaining queries in the request go to the master when this isn’t necessary for queries unaffected by the write that triggered the switch.
然而,这意味着所有剩余查询请求会去master查询 即使没必要在master执行的查询没有出发提示语句。
Newer versions of mysqlnd_ms have tried to solve this with the qos filter. This feature allows you to be more granular when it comes to handling replication lag in the write-then-read scenario.
新版本的mysqlnd_ms试图通过qos过滤器解决这个问题。这个特性允许您更细粒度的处理复制时滞后write-then-read场景。
There are three types of service level:
- eventual consistency: this is the default, all masters/slaves are considered for read queries. You can additionally automatically filter out slaves that lag N seconds behind the master.
- 这是默认的,所有的 masters/slaves 都考虑阅读查询。另外你可以自动过滤掉N秒落后于master的slaves。
- session consistency: this is the similar as
master_on_write=1, however you can additionally automatically include servers that have already replicated the data by using Global Transaction IDs (GTIDs)- 这和master_on_write = 1相似的,但是您可以另外自动包括服务器已经复制的数据通过使用全局事务id(GTIDs)
- strong consistency: this is only used when using a multi-master synchronous MySQL setup, for example MySQL Cluster.
- 他只是使用多主机同步MySQL安装时使用,例如MySQL集群。
To configure mysqlnd_ms you can either add it as a filter in the config:
{
"appname": {
"master": {
...
},
"slave": {
...
}
"filters": {
"quality_of_service": {
"TYPEOF_consistency": {
"OPTION": VALUE
}
},
"random": 1
},
}
}
Or, you can also set this programmatically using:
mysqlnd_ms_set_qos($connection, MYSQLND_MS_QOS_CONSISTENCY_[TYPE], MYSQLND_MS_QOS_OPTION_[OPTION], $value);
EVENTUAL CONSISTENCY --最终一致性
The simplest of these is eventual consistency, which will transparently check the Seconds_Behind_Master value on each slave and any that is less than or equal to the accepted value is considered as a read candidate. However, this value increments in whole seconds which means you can still be caught by replication lag issues. It is also dependant on faster networks.
其中最简单的是最终一致性,将透明地检查Seconds_Behind_Master值在每个slave和任何小于或等于接受的值被认为是一个读查询的候选服务器。然而,这个值增量是整秒-这意味着你仍然可以被复制滞后问题困扰。它也依赖于更快的网络。
With eventual consistency, we tell mysqlnd_ms to only consider slaves that are within N seconds of the master. To configure the plugin we either use the config:
最终一致性,我们告诉mysqlnd_ms只考虑那些slaves在的maste的N秒内的。我们要么使用配置来配置插件:
{
"appname": {
"master": {
...
},
"slave": {
...
}
"filters": {
"quality_of_service": {
"eventual_consistency": {
"age": 1
}
}
}
}
}
Or:
mysqlnd_ms_set_qos($connection, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL, MYSQLND_MS_QOS_OPTION_AGE, 1);
This sets the the plugin to consider all nodes that are less than or equal to one second behind the master.
这个设置的插件需要考虑所有节点延迟master小于或等于一秒。
SESSION CONSISTENCY --会话一致性
Session consistency will ensure that we only use servers that are definitely in sync with the master, this can be slower, but provides a more robust solution that eventual consistency. Session consistency achives this by using Global Transaction IDs or GTIDs. In MySQL 5.6.5m8 and up, MySQL supports server-side GTIDs, which the plugin can use automatically — this is the preferred solution. If you are using older versions of MySQL, or GTIDs are not enabled, you will need to implement client-side GTIDs, which are discussed in the manual.
会话一致性将确保我们只使用服务器,绝对是与主保持同步,这可以慢,但提供了一个更健壮的解决方案,最终一致性。会话一致性通过使用全局事务id或GTIDs achives这。在MySQL 5.6.5m8,MySQL支持服务器端GTIDs,插件可以使用自动——这是首选的解决方案。如果您使用的是旧版本的MySQL或GTIDs不启用,您将需要实现客户端GTIDs,手册中讨论。
Because GTIDs may not be available in MySQL, the plugin will fallback to master only.
To configure the plugin for this we use:
{
"appname": {
"master": {
...
},
"slave": {
...
}
"filters": {
"quality_of_service": {
"session_consistency": 1
}
}
"global_transaction_id_injection": {
"select" => "SELECT @@GLOBAL.GTID_EXECUTED",
"fetch_last_gtid" => "SELECT @@GLOBAL.GTID_EXECUTED AS trx_id FROM DUAL",
"check_for_gtid" => "SELECT GTID_SUBSET('#GTID', @@GLOBAL.GTID_EXECUTED) AS trx_id FROM DUAL",
"report_errors" => true,
},
}
}
In this case we add a new section to the configuration global_transaction_id_injection this tells the plugin the queries to use to check the GTID on the server.
在这种情况下,我们添加一个新的部分配置global_transaction_id_injection这告诉插件查询使用检查GTID在服务器上。
Note: These queries only work from MySQL 5.6.17+
Now the plugin will check and consider all servers (for SELECTs) that are in-sync with the master. For other types of queries, it will still only use the master.
现在,插件将检查和考虑所有服务器(选择)与主同步。对于其他类型的查询,它仍将只使用主。
If you do not add this additional section to the configuration, it will function in the same was as setting the master_on_write earlier.
如果你不添加这个额外的部分配置,它将在相同的功能是早设置的master_on_write。
It is not possible to set this additional configuration programmatically. However you can enable the master_on_write behavior using:
是不可能以编程方式设置该额外的配置。但是您可以启用master_on_write行为使用:
mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION);
Partitioning and Sharding
Due to the fact that it is very hard to scale writes to multiple machines (e.g. multi-master replication) with traditional MySQL (i.e. not MySQL Cluster) it is fairly common to to use partitioning, or sharding, to enable you to scale across multiple masters by writing different data sets to each one.
因为很难写扩展到多台机器(如多主机复制)与传统MySQL(即不是MySQL集群)是相当普遍使用分区,或切分,使您能够跨多个规模master通过编写每一个不同的数据集。
For example, your user profile data might be on master A, while shopping cart transactions might be on master B.
Not all partitioning and sharding is done to scale writes however, sometimes replication topologies will choose to replicate subsets of the master database to specific slaves, this allows you to scale hardware more effectively around specific data sets.
并不是所有分区和分片完成了扩展写,有时复制拓扑会选择复制主数据库的子集到特定的salve,这允许您扩展硬件更有效地在特定的数据集。
The mysqlnd_ms plugin allows you to setup both multiple masters using the mysqlnd_ms.multi_master INI settings, and partitioning using the node_groups multi filter.
mysqlnd_ms插件允许您同时设置多个 master使用mysqlnd_ms.multi_master INI 设置 和 分区使用node_groups多过滤器。
Unlike a lot of the other features in mysqlnd_ms partitioning is not an entirely transparent process, in that partitions must be determined either when writing the query in your editor, or programmatically when running the query.
不像很多其他特性mysqlnd_ms分区不是一个完全透明的过程,在编写查询时,必须确定分区在你的编辑器,或通过编程的方式运行时查询。
If you require it, to enable multi-master support you should add the following to your PHP.ini:
mysqlns_ms.multi_master=1
Then to setup your node groups, you will add the following to your JSON configuration:
{
"appname": {
"master": {
"master_0": {
"host": "mysql.master.0",
"db": "dbname"
},
"master_1": {
"host": "mysql.master.1",
"db": "dbname"
},
},
"slave": {
"slave_0": {
"host": "mysql.slave.0",
"db": "dbname"
},
"slave_1": {
"host": "mysql.slave.1",
"db": "dbname"
},
}
"filters": {
"node_groups": {
"Group_A" : {
"master": ["master_0"],
"slave": ["slave_0", "slave_1"]
},
"Group_B" : {
"master": ["master_1"],
"slave": ["slave_0", "slave_1"]
}
}
}
}
}
This adds two node groups, Group_A and Group_B, with both using all of the slaves, but only one master.
You can then route queries to a specific node group by adding a SQL hint containing it’s name to the beginning of the query:
/*Group_A*/SELECT * FROM users;
Which you can chain with other SQL hints like so:
/*Group_B*//*ms=last_used*/SELECT * FROM transactions;
How you prepend the SQL hint is up to you. As mentioned, you can do that in the editor when writing the code, or you can do it programmatically when constructing the query.
如何将SQL的提示是取决于你。正如前面提到的,你可以在编辑器中编写代码时,或者你可以通过编程的方式构建查询。
Custom Routing--定制路由
The final feature we will cover is custom routing, which allows you to write your own logic for determining which server the query should be routed to.
我们将讨论的最终功能是自定义路由,它允许您编写自己的逻辑来确定应该被路由到哪个服务器执行查询。
This is done by using the user, or user_multi filters. These filters must define a callback, which must return either a single server, or a list of both master and slave servers respectively.
这是通过使用用户,或user_multi过滤器。这些过滤器必须定义一个回调,它必须返回单个服务器或者分别两个主、从服务器的列表。
This callback must be a function, it cannot be a static or object method call (or any other type of callable, such as a closure). However, it can be a wrapper around an object or any other code.
这个回调必须是一个函数,它不能被一个静态或对象方法调用(或任何其他类型的可调用,如一个闭包)。然而,它可以是一个包装器对象或任何其他代码。
With this feature, we are able to programmatically detect some of the edge cases that might otherwise be missed by the default mechanism.
有了这个特性,我们能够以编程方式检测的一些边界情况可能错过由默认机制。
First, let’s setup the configuration:
{
"appname": {
"master": {
...
},
"slave": {
...
}
"filter": {
"user": "select_db_server"
}
}
}
Next, we can define our callback function:
function select_db_server($connection, $sql, $masters, $slaves, $last_used, $in_transaction)
{
// Always switch to master if it was last used:
if (in_array($masters, $last_used)) {
return $last_used;
}
// Always use the last used connection if we're in a transaction:
if ($in_transaction === true) {
return $last_used;
}
// Check for the SQL hints:
if (strpos($sql, '/*' .MYSQLND_MS_MASTER_SWITCH. '*/') !== false) {
// Force master:
return array_rand($masters); // Use array_rand just in case we have multiple masters
} elseif (strpos($sql, '/*' .MYSQLND_MS_SLAVE_SWITCH. '*/') !== false) {
// Force slave:
return array_rand($slaves);
} elseif (strpos($sql, '/*' .MYSQLND_MS_LAST_USED_SWITCH. '*/') !== false) {
// Force last used:
return $last_used;
}
// Check for some edge-cases:
// Allow both queries that start with SELECT or (SELECT to go the slaves:
if (stripos($sql, 'SELECT') === 0 || stripos($sql, '(SELECT') === 0) {
// But not those with SELECT... INTO:
// Remember, if we're already in a transaction then last_used is used
if (pcre_match('/^\(?SELECT (.*) INTO (@|OUTFILE|DUMPFILE)(.*) FROM (.*)$/i', $sql) == 1) {
return array_rand($masters);
}
return array_rand($slaves);
}
// Send CREATE TEMPORARY TABLE to last_used:
if (stripos($sql, 'CREATE TEMPORARY TABLE') === 0) {
return $last_used;
}
// We have to pick one server by the end of this, so default to master:
return array_rand($masters);
}
Please note that this is an example and probably shouldn’t be used in production. However it illustrates the power of the user filter.
请注意,这是一个例子,也许不应该用于生产。但它说明了用户过滤的力量。
We do a number of checks to ultimately arrive at which server should be used:
在决定最终到达并使用哪个服务器之前我们做了许多检查:
- Much like the
master_on_writeflag, and the session consistency qos filter, we switch to the master for all future queries once the master has been used.- 就像master_on_write标记,和会话一致性qos过滤器,一旦master被使用之后所有查询我们都切换到master。
- Next, we ensure that we respect transactions and use the last used connection if we are currently in a transaction
- 接下来,我们确保我们尊重事务和使用最后一个连接如果我们目前正在一个事务当中
- Then we check and follow the SQL hints
- 然后我们检查和遵循SQL提示
- Finally we get to our edge-cases: queries starting with
(SELECT, andSELECT… INTOqueries, and thenCREATE TEMPORARY TABLEqueries- 最后我们到达边界情况:查询带有(
SELECT, andSELECT… INTOqueries, and thenCREATE TEMPORARY TABLEqueries
- 最后我们到达边界情况:查询带有(
- If nothing else was returned, we simply default the master — this ensures we are less likely to send write queries to a slave by accident.
- 如果没有其他返回,我们只是默认master——这样可以确保我们不太可能偶然发送写查询到一个奴隶。
Now, you can do anything you like within this function, so long as you only return one server.
Alternatively, you can use the user_multi filter which is identical, except that you would return an array with two values, each of which is an array of master, or slave servers respectively.
或者,您可以同样使用user_multi过滤器,唯一区别是你将返回一个包含2个值的数组,一个值是master服务器数组,一个值是slave服务器数组。
Conclusion
The mysqlnd_ms plugin has a lot of advanced features, that can allow you to effectively scale your application much more easily using traditional MySQL replication topologies.
mysqlnd_ms插件有很多高级特性,可以让你有效地扩展您的应用程序更容易使用传统MySQL的复制拓扑。
There is still other features which we haven’t covered, such as cache support, distributed transactions, and MySQL Fabric support.
In the next installment of this series we will take a look MySQL’s InnoDB Memcache Interface, as well as how we can easily (and transparently) utilize it with another MySQLnd plugin: mysqlnd_memcache.
在本系列的下一篇文章中,我们将看看MySQL的InnoDB Memcache接口,以及我们如何可以轻松(透明)利用它与另一个MySQLnd插件:mysqlnd_memcache。
P.S. Are you using any of these advanced techniques already? We’d love to hear your experiences with the mysqlnd_ms plugin via twitter or reddit.
