1 检查现有集群
(venv) [root@my_ob ~]# pxd list
/root/venv/lib64/python3.6/site-packages/paramiko/transport.py:33: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography and will be removed in a future release.
from cryptography.hazmat.backends import default_backend
/root/venv/lib/python3.6/site-packages/deployer
NAME CN DN CDC STATUS
pxc-tryout 1 1 1 running
2 清除现有集群
这一步会清楚已创建的所有Polardb-X集群，要谨慎使用。
(venv) [root@my_ob ~]# pxd cleanup
/root/venv/lib64/python3.6/site-packages/paramiko/transport.py:33: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography and will be removed in a future release.
from cryptography.hazmat.backends import default_backend
/root/venv/lib/python3.6/site-packages/deployer
Prepare to delete all PolarDB-X clusters
All PolarDB-X clusters will be deleted, do you want to continue? [y/N]: y
Prepare to delete PolarDB-X cluster: pxc-tryout
stop and remove container: pxc-tryout-cn-bLzM, id: b4b29949a0 at 127.0.0.1
stop and remove container: pxc-tryout-cdc-LJhR, id: 2fa39a46f4 at 127.0.0.1
stop and remove container: pxc-tryout-gms-Cand-17077, id: e930288c58 at 127.0.0.1
stop and remove container: pxc-tryout-dn-0-Cand-14508, id: 1bd0be8eb6 at 127.0.0.1
3 重新创建一个两个DN节点的集群
(venv) [root@my_ob ~]pxd tryout -cn_replica 1 -cn_version latest -dn_replica 2 -dn_version latest -cdc_replica 1 -cdc_version latest
latest:Pulling from docker.io/polardbx/xstore-tools
Digest: sha256:b061e1dd57380b455804c091df583708204fa292776e02a0e005d617b59b5d82
Status: Image is up to date for docker.io/polardbx/xstore-tools:latest
Processing [#########---------------------------] 25% create gms node
Processing [############------------------------] 33% create gms db and tables
Processing [###############---------------------] 41% create PolarDB-X root account
Processing [##################------------------] 50% create dn
Processing [#####################---------------] 58% register dn to gms
Processing [########################------------] 66% create cn
Processing [###########################---------] 75% wait cn ready
Processing [##############################------] 83% create cdc containers
Processing [#################################---] 91% wait PolarDB-X ready
Processing [####################################] 100%
PolarDB-X cluster create successfully, you can try it out now.
Connect PolarDB-X using the following command:
mysql -h127.0.0.1 -P11058 -upolardbx_root -pHOxcSLDj
4 连接至集群，用的是obclient客户端，创建数据库，测试表，插入数据
obclient -h127.0.0.1 -P11058 -upolardbx_root -pHOxcSLDj
MySQL [(none)]> show databases;
+--------------------+
| DATABASE |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.004 sec)
看一下计算节点状态
MySQL [(none)]> show mpp;
+------------+------------------+------+--------+
| ID | NODE | ROLE | LEADER |
+------------+------------------+------+--------+
| pxc-tryout | 172.17.0.5:11060 | W | Y |
+------------+------------------+------+--------+
1 row in set (0.007 sec)
看一下存储节点状态，有三个存储节点，一个是gms
MySQL [(none)]> show storage;
+-----------------+------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| STORAGE_INST_ID | LEADER_NODE | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT | STATUS | DELETABLE | DELAY | ACTIVE |
+-----------------+------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| pxc-tryout-dn-0 | 172.17.0.3:14308 | true | MASTER | 1 | 2 | 0 | false | null | null |
| pxc-tryout-dn-1 | 172.17.0.4:16306 | true | MASTER | 1 | 1 | 0 | true | null | null |
| pxc-tryout-gms | 172.17.0.2:16456 | true | META_DB | 2 | 2 | 0 | false | null | null |
+-----------------+------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
3 rows in set (0.038 sec)
创建测试库
MySQL [(none)]> create database test partition_mode='partitioning';
Query OK, 1 row affected (1.280 sec)
进入测试库
MySQL [(none)]> use test;
Database changed
创建测试表，用hash分区，分区数同计算节点数
MySQL [test]> create table emp (
`id` bigint(11) auto_increment NOT NULL,
`name` varchar(255) DEFAULT NULL,
`dept_no` int,
`salary` bigint(10),
primary key (`id`) ) engine=InnoDB default charset=utf8
partition by hash(id) partitions 2;
Query OK, 0 rows affected (5.045 sec)
插入四行数据
MySQL [test]> insert into emp(name,dept_no,salary) values ('tom', 1, 3000),('jack', 2, 3100), ('john', 4, 2900),('zhangsan',2,2800);
Query OK, 4 rows affected (0.382 sec)
检查一下数据
MySQL [test]> select * from emp;
+--------+----------+---------+--------+
| id | name | dept_no | salary |
+--------+----------+---------+--------+
| 100001 | tom | 1 | 3000 |
| 100004 | zhangsan | 2 | 2800 |
| 100002 | jack | 2 | 3100 |
| 100003 | john | 4 | 2900 |
+--------+----------+---------+--------+
4 rows in set (0.593 sec)
看一下表拓扑，表数据分布到两个节点上
MySQL [test]> show topology from emp;
+----+-------------------+----------------+----------------+
| ID | GROUP_NAME | TABLE_NAME | PARTITION_NAME |
+----+-------------------+----------------+----------------+
| 0 | TEST_P00001_GROUP | emp_kfYc_00001 | p2 |
| 1 | TEST_P00000_GROUP | emp_kfYc_00000 | p1 |
+----+-------------------+----------------+----------------+
2 rows in set (0.008 sec)
5 查看执行计划
MySQL [test]> explain select * from emp;
+----------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+----------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="emp[p1,p2]", shardCount=2, sql="SELECT `id`, `name`, `dept_no`, `salary` FROM `emp` AS `emp`") |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: d3a01e16 |
+----------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.288 sec)
上面是逻辑执行计划，logicalview下推到存储节点执行，访问了两个分区，在两个分片上执行，然后在计算节点汇总，可以并行执行。
MySQL [test]> explain execute select * from emp;
+----------+-------------+-------+------------+------+---------------+-----+---------+-----+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----------+-------------+-------+------------+------+---------------+-----+---------+-----+----------+----------+-------+
| | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | | 100 | NULL |
+----------+-------------+-------+------------+------+---------------+-----+---------+-----+----------+----------+-------+
1 row in set (0.090 sec)
explain execute 选项用来查看物理执行计划，输出上面如上图，物理执行计划是在存储节点执行的，同MySQL的执行计划相同。
MySQL [test]> explain select * from emp where name='zhangsan';
+-----------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Gather(concurrent=true) |
| LogicalView(tables="emp[p1,p2]", shardCount=2, sql="SELECT `id`, `name`, `dept_no`, `salary` FROM `emp` AS `emp` WHERE (`name` = ?)") |
| HitCache:true |
| Source:PLAN_CACHE |
| TemplateId: decd96f4 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.032 sec)
加个where条件删选一下，name列上没有任何索引，也不是主键，仍然需要访问两个存储节点，数据在计算节点汇总。
MySQL [test]> explain select * from emp where id=100001;
+--------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+--------------------------------------------------------------------------------------------------------------------+
| LogicalView(tables="emp[p1]", sql="SELECT `id`, `name`, `dept_no`, `salary` FROM `emp` AS `emp` WHERE (`id` = ?)") |
| HitCache:false |
| Source:PLAN_CACHE |
| TemplateId: f0ccb8c4 |
+--------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.096 sec)
以主键作为查询条件，只需要访问一下分区，计算节点也不需要做汇总，可以看出polardb-x具备水平分表能力，可以提高并发读写能力。
MySQL [test]> explain execute select * from emp where id=100001;
+----------+-------------+-------+------------+-------+---------------+---------+---------+-------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----------+-------------+-------+------------+-------+---------------+---------+---------+-------+----------+----------+-------+
| | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 8 | const | | 100 | NULL |
+----------+-------------+-------+------------+-------+---------------+---------+---------+-------+----------+----------+-------+
1 row in set (0.011 sec)
节点上执行的物理计划，使用了主键，同MySQL相同。
