<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <title>Partitioning and Sharding</title> </head> <body><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.failover.html">Failover</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.concepts.html">Concepts</a></div> <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div><hr /><div id="mysqlnd-ms.quickstart.partitioning" class="section"> <h2 class="title">Partitioning and Sharding</h2> <p class="para"> Database clustering is done for various reasons. Clusters can improve availability, fault tolerance, and increase performance by applying a divide and conquer approach as work is distributed over many machines. Clustering is sometimes combined with partitioning and sharding to further break up a large complex task into smaller, more manageable units. </p> <p class="para"> The mysqlnd_ms plugin aims to support a wide variety of MySQL database clusters. Some flavors of MySQL database clusters have built-in methods for partitioning and sharding, which could be transparent to use. The plugin supports the two most common approaches: MySQL Replication table filtering, and Sharding (application based partitioning). </p> <p class="para"> MySQL Replication supports partitioning as filters that allow you to create slaves that replicate all or specific databases of the master, or tables. It is then in the responsibility of the application to choose a slave according to the filter rules. You can either use the mysqlnd_ms <em><a href="mysqlnd-ms.plugin-ini-json.html#ini.mysqlnd-ms-plugin-config-v2.filter-node-groups" class="link">node_groups</a></em> filter to manually support this, or use the experimental table filter. </p> <p class="para"> Manual partitioning or sharding is supported through the node grouping filter, and SQL hints as of 1.5.0. The node_groups filter lets you assign a symbolic name to a group of master and slave servers. In the example, the master <em>master_0</em> and <em>slave_0</em> form a group with the name <em>Partition_A</em>. It is entirely up to you to decide what makes up a group. For example, you may use node groups for sharding, and use the group names to address shards like <em>Shard_A_Range_0_100</em>. </p> <p class="para"> <div class="example" id="example-1790"> <p><strong>Example #1 Cluster node groups</strong></p> <div class="example-contents"> <div class="inicode"><pre class="inicode">{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "simulate_slave_failure", "port": "0" }, "slave_1": { "host": "127.0.0.1", "port": 3311 } }, "filters": { "node_groups": { "Partition_A" : { "master": ["master_0"], "slave": ["slave_0"] } }, "roundrobin": [] } } }</pre> </div> </div> </div> </p> <p class="para"> <div class="example" id="example-1791"> <p><strong>Example #2 Manual partitioning using SQL hints</strong></p> <div class="example-contents"> <div class="phpcode"><code><span style="color: #000000"> <span style="color: #0000BB"><?php<br /></span><span style="color: #007700">function </span><span style="color: #0000BB">select</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #0000BB">$msg</span><span style="color: #007700">, </span><span style="color: #0000BB">$hint </span><span style="color: #007700">= </span><span style="color: #DD0000">''</span><span style="color: #007700">) {<br /> </span><span style="color: #FF8000">/* Note: weak test, two connections to two servers may have the same thread id */<br /> </span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT CONNECTION_ID() AS _thread, '%s' AS _hint FROM DUAL"</span><span style="color: #007700">, </span><span style="color: #0000BB">$msg</span><span style="color: #007700">);<br /> if (</span><span style="color: #0000BB">$hint</span><span style="color: #007700">) {<br /> </span><span style="color: #0000BB">$sql </span><span style="color: #007700">= </span><span style="color: #0000BB">$hint </span><span style="color: #007700">. </span><span style="color: #0000BB">$sql</span><span style="color: #007700">;<br /> }<br /> if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #0000BB">$sql</span><span style="color: #007700">))) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br /> return </span><span style="color: #0000BB">false</span><span style="color: #007700">;<br /> }<br /> </span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"%d - %s - %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_thread'</span><span style="color: #007700">], </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_hint'</span><span style="color: #007700">], </span><span style="color: #0000BB">$sql</span><span style="color: #007700">);<br /> return </span><span style="color: #0000BB">true</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #0000BB">$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/* All slaves allowed */<br /></span><span style="color: #0000BB">select</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #DD0000">"slave_0"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">select</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #DD0000">"slave_1"</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* only servers of node group "Partition_A" allowed */<br /></span><span style="color: #0000BB">select</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #DD0000">"slave_1"</span><span style="color: #007700">, </span><span style="color: #DD0000">"/*Partition_A*/"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">select</span><span style="color: #007700">(</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">, </span><span style="color: #DD0000">"slave_1"</span><span style="color: #007700">, </span><span style="color: #DD0000">"/*Partition_A*/"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span> </span> </code></div> </div> <div class="example-contents screen"> <div class="cdata"><pre> 6804 - slave_0 - SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL 2442 - slave_1 - SELECT CONNECTION_ID() AS _thread, 'slave2' AS _hint FROM DUAL 6804 - slave_0 - /*Partition_A*/SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL 6804 - slave_0 - /*Partition_A*/SELECT CONNECTION_ID() AS _thread, 'slave1' AS _hint FROM DUAL </pre></div> </div> </div> </p> <p class="para"> By default, the plugin will use all configured master and slave servers for query execution. But if a query begins with a SQL hint like <em>/*node_group*/</em>, the plugin will only consider the servers listed in the <em>node_group</em> for query execution. Thus, <em>SELECT</em> queries prefixed with <em>/*Partition_A*/</em> will only be executed on <em>slave_0</em>. </p> </div><hr /><div class="manualnavbar" style="text-align: center;"> <div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.failover.html">Failover</a></div> <div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.concepts.html">Concepts</a></div> <div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div> <div class="home"><a href="index.html">PHP Manual</a></div> </div></body></html>