mysqlpxc性能
MySQL之PXC集群搭建的方法步骤一、pxc 介绍
1.1 pxc 简介
pxc 是一套 mysql 高可用集群解决方案，与传统的基于主从复制模式的集群架构相比 pxc 最突出特点就是解决了诟病已久的数据复制延迟问题，基本上可以达到实时同步。而且节点与节点之间，他们相互的关系是对等的。pxc 最关注的是数据的一致性，对待事物的行为时，要么在所有节点上执行，要么都不执行，它的实现机制决定了它对待一致性的行为非常严格，这也能非常完美的保证 mysql 集群的数据一致性；
1.2 pxc特性和优点
- 完全兼容 mysql。
- 同步复制，事务要么在所有节点提交或不提交。
- 多主复制，可以在任意节点进行写操作。
- 在从服务器上并行应用事件，真正意义上的并行复制。
- 节点自动配置，数据一致性，不再是异步复制。
- 故障切换：因为支持多点写入，所以在出现数据库故障时可以很容易的进行故障切换。
- 自动节点克隆：在新增节点或停机维护时，增量数据或基础数据不需要人工手动备份提供，galera cluster会自动拉取在线节点数据，集群最终会变为一致；
pxc最大的优势：强一致性、无同步延迟
1.3 pxc的局限和劣势
- 复制只支持innodb 引擎，其他存储引擎的更改不复制
- 写入效率取决于节点中最慢的一台
1.4 pxc与replication的区别
| replication | pxc |
|---|---|
| 数据同步是单向的，master负责写，然后异步复制给slave；如果slave写入数据，不会复制给master。 | 数据同步时双向的，任何一个mysql节点写入数据，都会同步到集群中其它的节点。 |
| 异步复制，从和主无法保证数据的一致性 | 同步复制，事务在所有集群节点要么同时提交，要么同时不提交 |
1.5 pxc 常用端口
- 3306：数据库对外服务的端口号。
- 4444：请求sst的端口。
- 4567：组成员之间进行沟通的一个端口号
- 4568：用于传输ist。
名词解释：
- sst(state snapshot transfer): 全量传输
- ist(incremental state transfer):增量传输
二、实践
2.1 搭建 pxc 集群
与 mysql 不同的是 pxc 官方提供了 docker 镜像，所以我们可以很方便的搭建 pxc 集群。
1）下载 docker 镜像
|
|
docker pull percona/percona-xtradb-cluster:5.7 |
重命名镜像名称
|
|
docker tag percona/percona-xtradb-cluster:5.7 pxc:5.7 |
3）删除原始镜像
|
|
docker rmi percona/percona-xtradb-cluster:5.7 |
创建 docker 网络，用于 pxc 集群独立使用
|
|
docker network create pxc-network |
创建数据卷用于之后挂载
|
|
docker volume create --name v1docker volume create --name v2docker volume create --name v3 |
注：pxc容器只支持数据卷挂载方式，不支持目录挂载
创建第一个节点
|
|
docker run -di --name=pn1 --net=pxc-network -p 9000:3306 -v v1:/var/lib/mysql --privileged -e mysql_root_password=123456 -e cluster_name=cluster1 -e xtrabackup_password=123456 pxc:5.7 |
因为后续节点的添加需要关联到第一个节点，所以需要等待数据库启动完成。通过 docker logs pn1 查看日志，如果出现下面的输出，证明启动成功:
2019-09-04t06:27:30.085880z 0 [note] innodb: buffer pool(s) load completed at 190904 6:27:30
注：cluster_name 名称不要用关键字pxc，否则无法启动。
加入第二个节点
|
|
docker run -di --name=pn2 --net=pxc-network -p 9001:3306 -v v2:/var/lib/mysql --privileged -e mysql_root_password=123456 -e cluster_name=cluster1 -e xtrabackup_password=123456 -e cluster_join=pn1 pxc:5.7 |
需要注意是第二个节点开始需要增加 e cluster_join=pn1 参数，表示与 pn1 节点同步,否则 pn1 容器会自动关闭。
当 pxc集群中存在两个节点以上之后就没有主节点的概念了。集群中最后一个退出的节点就会变为主节点，在 /var/lib/mysql/grastate.dat 文件中属性 safe_to_bootstrap 的值 会从 0 被设置为 1 表示该节点是主节点。
8）加入第三个节点
|
|
docker run -di --name=pn3 --net=pxc-network -p 9002:3306 -v v3:/var/lib/mysql --privileged -e mysql_root_password=123456 -e cluster_name=cluster1 -e xtrabackup_password=123456 -e cluster_join=pn2 pxc:5.7 |
可以看到我们这次我们 cluster_join 的是 pn2 容器，可以证明我们刚刚说的 当 pxc 集群存在两个节点以上之后就没有主节点的概念了 这个说法是正确的。
9）进入 pn1 节点
|
|
docker exec -it pn1 /usr/bin/mysql -uroot -p123456 |
查看状态
|
|
mysql> show status like 'wsrep%';+----------------------------------+-------------------------------------------------+| variable_name | value |+----------------------------------+-------------------------------------------------+| wsrep_local_state_uuid | 068dd5e8-cedd-11e9-904d-466e75bd8fe1 || wsrep_protocol_version | 9 || wsrep_last_applied | 16 || wsrep_last_committed | 16 || wsrep_replicated | 0 || wsrep_replicated_bytes | 0 || wsrep_repl_keys | 0 || wsrep_repl_keys_bytes | 0 || wsrep_repl_data_bytes | 0 || wsrep_repl_other_bytes | 0 || wsrep_received | 10 || wsrep_received_bytes | 800 || wsrep_local_commits | 0 || wsrep_local_cert_failures | 0 || wsrep_local_replays | 0 || wsrep_local_send_queue | 0 || wsrep_local_send_queue_max | 1 || wsrep_local_send_queue_min | 0 || wsrep_local_send_queue_avg | 0.000000 || wsrep_local_recv_queue | 0 || wsrep_local_recv_queue_max | 2 || wsrep_local_recv_queue_min | 0 || wsrep_local_recv_queue_avg | 0.100000 || wsrep_local_cached_downto | 0 || wsrep_flow_control_paused_ns | 0 || wsrep_flow_control_paused | 0.000000 || wsrep_flow_control_sent | 0 || wsrep_flow_control_recv | 0 || wsrep_flow_control_interval | [ 173, 173 ] || wsrep_flow_control_interval_low | 173 || wsrep_flow_control_interval_high | 173 || wsrep_flow_control_status | off || wsrep_cert_deps_distance | 0.000000 || wsrep_apply_oooe | 0.000000 || wsrep_apply_oool | 0.000000 || wsrep_apply_window | 0.000000 || wsrep_commit_oooe | 0.000000 || wsrep_commit_oool | 0.000000 || wsrep_commit_window | 0.000000 || wsrep_local_state | 4 || wsrep_local_state_comment | synced || wsrep_cert_index_size | 0 || wsrep_cert_bucket_count | 22 || wsrep_gcache_pool_size | 1592 || wsrep_causal_reads | 0 || wsrep_cert_interval | 0.000000 || wsrep_open_transactions | 0 || wsrep_open_connections | 0 || wsrep_ist_receive_status | || wsrep_ist_receive_seqno_start | 0 || wsrep_ist_receive_seqno_current | 0 || wsrep_ist_receive_seqno_end | 0 || wsrep_incoming_addresses | 172.19.0.2:3306,172.19.0.3:3306,172.19.0.4:3306|| wsrep_cluster_weight | 3 || wsrep_desync_count | 0 || wsrep_evs_delayed | || wsrep_evs_evict_list | || wsrep_evs_repl_latency | 0/0/0/0/0 || wsrep_evs_state | operational || wsrep_gcomm_uuid | 11ed51e2-cedd-11e9-b362-af453a7ac074 || wsrep_cluster_conf_id | 3 || wsrep_cluster_size | 3 || wsrep_cluster_state_uuid | 068dd5e8-cedd-11e9-904d-466e75bd8fe1 || wsrep_cluster_status | primary || wsrep_connected | on || wsrep_local_bf_aborts | 0 || wsrep_local_index | 0 || wsrep_provider_name | galera || wsrep_provider_vendor | codership oy <info@codership.com> || wsrep_provider_version | 3.37(rff05089) || wsrep_ready | on |+----------------------------------+-------------------------------------------------+71 rows in set (0.06 sec) |
可以看到 wsrep_incoming_addresses 的值就是我们三个容器的ip地址
|
|
| wsrep_incoming_addresses | 172.19.0.2:3306,172.19.0.3:3306,172.19.0.4:3306 | |
集群完整性检查：
| 属性 | 含义 |
|---|---|
| wsrep_cluster_state_uuid | 在集群所有节点的值应该是相同的,有不同值的节点,说明其没有连接入集群. |
| wsrep_cluster_conf_id | 正常情况下所有节点上该值是一样的.如果值不同,说明该节点被临时”分区”了.当节点之间网络连接恢复 的时候应该会恢复一样的值. |
| wsrep_cluster_size | 如果这个值跟预期的节点数一致,则所有的集群节点已经连接. |
| wsrep_cluster_status | 集群组成的状态.如果不为”primary”,说明出现”分区”或是”split-brain”脑裂状况. |
节点状态检查：
| 属性 | 含义 |
|---|---|
| wsrep_ready | 该值为 on,则说明可以接受 sql 负载.如果为 off,则需要检查 wsrep_connected |
| wsrep_connected | 如果该值为 off,且 wsrep_ready 的值也为 off,则说明该节点没有连接到集群.(可能是 wsrep_cluster_address 或 wsrep_cluster_name 等配置错造成的.具体错误需要查看错误日志) |
| wsrep_local_state_comment | 如果 wsrep_connected 为 on,但 wsrep_ready 为 off,则可以从该项查看原因 |
复制健康检查:
| 属性 | 含义 |
|---|---|
| wsrep_flow_control_paused | 表示复制停止了多长时间.即表明集群因为 slave 延迟而慢的程度.值为 0~1,越靠近 0 越好,值为 1 表示 复制完全停止.可优化 wsrep_slave_threads 的值来改善 |
| wsrep_cert_deps_distance | 有多少事务可以并行应用处理.wsrep_slave_threads 设置的值不应该高出该值太多 |
| wsrep_flow_control_sent | 表示该节点已经停止复制了多少次 |
| *wsrep_local_recv_queue_avg | 表示 slave 事务队列的平均长度.slave 瓶颈的预兆. 最慢的节点的 wsrep_flow_control_sent 和 wsrep_local_recv_queue_avg 这两个值最高.这两个值较低的话,相对更好 |
检测慢网络问题：
| 属性 | 含义 |
|---|---|
| wsrep_local_send_queue_avg | 网络瓶颈的预兆.如果这个值比较高的话,可能存在网络瓶颈 |
冲突或死锁的数目:
| 属性 | 含义 |
|---|---|
| wsrep_last_committed | 最后提交的事务数目 |
| wsrep_local_cert_failures 和 wsrep_local_bf_aborts | 回滚,检测到的冲突数目 |
2.2 集群同步验证
在节点一上创建数据库 test
|
|
mysql> create database test;query ok, 1 row affected (0.02 sec) |
节点二上查看：
|
|
mysql> show databases;+--------------------+| database |+--------------------+| information_schema || mysql || performance_schema || sys || test |+--------------------+5 rows in set (0.00 sec) |
在节点二上创建表
|
|
mysql> use test;database changedmysql> create table sys_user(id int ,name varchar(30));query ok, 0 rows affected (0.11 sec) |
4）在节点三上查看表结构
|
|
mysql> use test;reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup with -adatabase changedmysql> show tables;+----------------+| tables_in_test |+----------------+| sys_user |+----------------+1 row in set (0.00 sec) |
在节点三上插入数据
|
|
mysql> insert into sys_user values(1,'a');error 1105 (hy000): percona-xtradb-cluster prohibits use of dml command on a table (test.sys_user) without an explicit primary key with pxc_strict_mode = enforcing or master |
看到没有显示的主键就无法插入数据，我们修改下表结构: