I've setup a ks to implement sharding:
3 nodes (pxc1, pxc2, pxc3) [don't pay attention to "pxc" these are standalone mysql]
the sharding configuration is the following:
schemas :
-
db : sbtest
nodes: [node1,node2,node3]
rules:
default: node1
shard:
-
table: sbtest1
key: k
type: hash
nodes: [node1, node2, node3]
locations: [1,1,1]
table_row_limit: 10000
I used "hash" (I was not able to use range).
So this is what's going on:
mysql> insert into sbtest1 values (1,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.01 sec)
mysql> insert into sbtest1 values (2,2,'k was 2 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (3,3,'k was 3 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (4,4,'k was 4 at insert','ks');
Query OK, 1 row affected (0.00 sec)
4 records added and in ks log we can see:
2015/10/14 09:00:59 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (1, 1, 'k was 1 at insert', 'ks')
2015/10/14 09:01:07 - INFO - 192.168.56.6:40918->192.168.56.4:3306:insert into sbtest1_0002 values (2, 2, 'k was 2 at insert', 'ks')
2015/10/14 09:01:12 - INFO - 192.168.56.6:40918->192.168.56.2:3306:insert into sbtest1_0000 values (3, 3, 'k was 3 at insert', 'ks')
2015/10/14 09:01:20 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (4, 4, 'k was 4 at insert', 'ks')
So far so good, and I can verify this on each shard directly:
pxc1 mysql> select * from sbtest1_0000;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 3 | 3 | k was 3 at insert | ks |
+----+---+-------------------+-----+
pxc2 mysql> select * from sbtest1_0001;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 4 | 4 | k was 4 at insert | ks |
+----+---+-------------------+-----+
pxc3 mysql> select * from sbtest1_0002;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 2 | 2 | k was 2 at insert | ks |
+----+---+-------------------+-----+
Now, let's insert again some records (a use twice the same key for id 6 & 7):
mysql> insert into sbtest1 values (4,4,'k was 4 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (5,2,'k was 2 at insert','ks');
Query OK, 1 row affected (0.01 sec)
mysql> insert into sbtest1 values (6,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.00 sec)
mysql> insert into sbtest1 values (7,1,'k was 1 at insert','ks');
Query OK, 1 row affected (0.00 sec)
Nothing special, but in ks log:
2015/10/14 09:02:20 - INFO - 192.168.56.6:40918->192.168.56.4:3306:insert into sbtest1_0002 values (5, 2, 'k was 2 at insert', 'ks')
2015/10/14 09:02:33 - INFO - 192.168.56.6:40918->192.168.56.2:3306:insert into sbtest1_0000 values (6, 1, 'k was 1 at insert', 'ks')
2015/10/14 09:02:39 - INFO - 192.168.56.6:40918->192.168.56.3:3306:insert into sbtest1_0001 values (7, 1, 'k was 1 at insert', 'ks')
The last 2 entries go to a different shard ?!
Let's select all the records for k=1:
mysql> select * from sbtest1 where k=1;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 7 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
2 rows in set (0.00 sec)
Where is id 6 ?
Let's check on each shard directly:
pxc1 mysql> select * from sbtest1_0000;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 3 | 3 | k was 3 at insert | ks |
| 6 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
pxc2 mysql> select * from sbtest1_0001;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 1 | 1 | k was 1 at insert | ks |
| 4 | 4 | k was 4 at insert | ks |
| 7 | 1 | k was 1 at insert | ks |
+----+---+-------------------+-----+
pxc3 mysql> select * from sbtest1_0002;
+----+---+-------------------+-----+
| id | k | c | pad |
+----+---+-------------------+-----+
| 2 | 2 | k was 2 at insert | ks |
| 5 | 2 | k was 2 at insert | ks |
+----+---+-------------------+-----+
So that id 6 is indeed stored but in the wrong shard.