一周碎碎念，2021.7.11，主从延迟、慢SQL优化、Ansible、Docker等

老叶茶馆

发布于 2021-07-30 10:50:30

5370

发布于 2021-07-30 10:50:30

文章被收录于专栏：MySQL修行 | 老叶茶馆

叨叨最近遇到的一些事以及见闻、思考吧。

1. 主从延迟

帮忙处理了个主从延迟的问题，经过诊断，大致定位原因有几个：

频繁跑批产生的大事务，一个事务更新/插入数万条记录。
这些大事务在从库apply时，由于没有合适的索引，导致全表扫描，少则将近百万行记录，多则二千多万行记录。
可能是遇到了AHI方面的BUG，从perf top来看，btr_search_guess_on_hash 函数的CPU占比较高。

综上几种情况，造成了主从延迟很大的问题。

相应的解决方案有几条建议：

从业务上调整，缩小跑批业务，每次数万条记录降到数千条。
找出这些跑批业务对应的SQL，确认是否都有合适的索引，没有的话就新建。
关闭AHI。
升级MySQL版本，采用WRITESET模式，提高从库apply速度。

做好InnoDB大事务/长事务监控，看到下面这种状态就要及时告警了：

关于长事务/大事务的监控建议有以下几点，可以参考下：

2. 慢SQL分析&优化

帮忙处理了个慢SQL的问题。

这个SQL是普米监控系统里自动生成的，看了下执行计划，也确实有优化调整的空间。

尝试新增一个联合索引后，执行效率确实有所提升。不过发现当WHERE条件值换成一个新的取值时，第一次执行总是比较慢。

作为一个有着丰富实战（踩坑）经验的老DBA，首先就想到了是因为buffer pool不足，导致更换条件后需要执行物理读，所以第一次总是比较慢。

检查了一下，确实如此，innodb_buffer_pool_size才设置为134MB，而且innodb_io_capacity也是默认的200，好坑啊。

又看了下版本，还在使用MariaDB 5.5，这也太古董了吧。

.....

古人诚不我欺也，专业的事，还是让专业的人去做吧。突然想到了某位群友对DBA嗤之以鼻，认为现在都是上云时代，只要花钱加机器提高配置，什么都好办，不需要DBA一样耍的飞起。

......

3. 把我的imysql.com后台数据库换成了GreatSQL

不少朋友应该都知道我现在主要推广万里数据库即将开源的GreatSQL分支。

身体力行，先把 imysql.com 服务器后台的数据库换成GreatSQL吧，本想直接跑个MGR集群的，无奈云主机配置太低，运行几天后被搜索引擎一爬数据，给OOM（out of memory）弄挂了，好尴尬......

只好先回退为单节点模式了，暂时还顽强地、稳定地跑着，有“好事”读者的话，可以时不时帮忙看下，时不时又跑挂了，哈哈。

......

围观链接：https://imysql.com/greatsql.php

另外，在切换GreatSQL过程中，因为一开始是想用MGR集群的，因此只能使用InnoDB引擎。

因为用了 WeCenter 系统，该系统中有个表设计不合理，竟然没有显式主键......这会引发类似下面的报错：

ERROR 3098 (HY000) at line 38471: The table does not comply with the requirements by an external plugin.

只好先对该表添加一个自增ID列作为显式主键：

ALTER TABLE users_online ADD aid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

而后修改 models/online.php 文件，大概在代码的第31行附近，把新增的显式主键列定义也加进去：

30                 $online_data = array(
31                         'aid' => 0,   #<-- 新增这行
32                         'uid' => $uid,

之后就可以了。

4. Ansible一键安装

这周还折腾用Ansible一键安装GreatSQL并构建MGR集群。

此前并没有用过Ansible，可谓完全小白一枚。在同事给的一个Ansible脚本基础上不断进行完善优化，也在众多群友帮助下，终于是基本上搞定了。

虽然相比Ansible官网上看到的其他优秀项目，我做的还是非常low。不过已经实现先从0到1的突破，而且目的也基本达到了，以后有需要再慢慢优化吧。

我使用的Ansible版本是2.9.21，怀疑是遇到了BUG了，有个小地方折腾了挺久，简直快怀疑人生了，具体表现是例如下面这段代码，第一个变量不能被识别，而第二个变量却可以，需要对第一个变量前面任意加个前导字符才行。

#不会报错的写法
- name: mgr primary init
  command: /{{ base_dir }}/bin/mysql -uroot -f -S {{ data_dir }}/mysql.sock

#会报错的写法，{{ base_dir }}前面没有前导的 / 字符就会报错
- name: mgr secondary init
  command: {{ base_dir }}/bin/mysql -uroot -f -S {{ data_dir }}/mysql.sock

后面改成了类似下面的写法，不加前导字符也没事了：

- name: mgr primary init
  shell: >
         {{ base_dir }}/bin/mysql -uroot -f -S {{ data_dir }}/mysql.sock

脑阔被搞的好大。

......

5. Docker里部署MGR集群遇到的"灵异"事件

一位群友帮忙做GreatSQL的docker镜像测试时，发现MGR集群总是拉不起来，查了下日志，发现有类似下面的报错提示：

[Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Connection attempt from IP address ::ffff: 172.33.0.2 refused. Address is not in the IP allowlist.'
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error connecting to the local group communication engine instance.'
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] The member was unable to join the group.  Local port: 33061'

查看了下MGR白名单设置：

mysqladmin var|grep group_replication_ip_whi

| group_replication_ip_whitelist                           | AUTOMATIC

看起来设置应该没问题。

反复重试几次后还是无果，抱着死马当活马医的想法，试试手动设置白名单：

SET GLOBAL group_replication_ip_whitelist = '172.33.0.0/16';

再启动MGR服务就OK了，看起来还真是白名单设置的问题。

翻看MySQL官方文档，发现下面这段话：

If you do not specify an allowlist explicitly, the group communication engine (XCom) automatically scans active interfaces on the host, and identifies those with addresses on private subnetworks, together with the subnet mask that is configured for each interface. These addresses, and the localhost IP address for IPv4 and (from MySQL 8.0.14) IPv6 are used to create an automatic Group Replication allowlist. The automatic allowlist therefore includes any IP addresses that are found for the host in the following ranges after the appropriate subnet mask has been applied:

IPv4 (as defined in RFC 1918)
10/8 prefix       (10.0.0.0 - 10.255.255.255) - Class A
172.16/12 prefix  (172.16.0.0 - 172.31.255.255) - Class B
192.168/16 prefix (192.168.0.0 - 192.168.255.255) - Class C

IPv6 (as defined in RFC 4193 and RFC 5156)
fc00:/7 prefix    - unique-local addresses
fe80::/10 prefix  - link-local unicast addresses

127.0.0.1 - localhost for IPv4
::1       - localhost for IPv6

文档链接：18.6.1 Group Replication IP Address Permissions, https://dev.mysql.com/doc/refman/8.0/en/group-replication-ip-address-permissions.html

现在知道问题原因了，docker容器使用的网络是 172.33.0.0/16 网段，已经超出了B类保留私网地址范围，因此并不在MGR默认的白名单里，所以无法启动，需要手动设置白名单。不过比较诡异的是，docker创建私有子网时，竟然自动创建出 172.33.0.0/16 网段，是不是算BUG呢......

总之又涨姿势了。

......

6. 关于自媒体及其他

直接说几点感受吧：

在众多社会热点事件中，绝大多数个人自媒体，不说99%，95%估计是有的，基本上都是在瞎带节奏，听风就是雨。不少还是营销号，文章中经常夹带私货。但网民对这些文章却似乎更欢迎，因为更符合人性中让人“爽”的那种感觉......
流量越来越贵，越来越难获取。纯技术分享推文的阅读量一直不高，但如果标题党通常可以获得更高流量。最近拼多多的拉新用户即可兑换现金活动中，如果是老用户助力则效果一般，而如果是拉新用户来注册，则助力效果就较好。之前看到过一个说法，互联网平台获取一个新用户的成本大概是150元左右，真的是太贵了......

最后要特别感谢近期在折腾docker、ansible中提供帮助的多位朋友，分别是谢恒忠、吕保成、Fan()、Coral、戴先森等（排名不分先后）。

继续推GreatSQL......

GreatSQL是源于Percona Server的分支版本，在其原来已有的稳定可靠、高效、管理更方便等优势基础上，进一步提升了MGR（MySQL Group Replication）的性能及可靠性，新增金融级应用场景需求特性并修复数个影响可靠性的严重bug。GreatSQL即将正式开源，我在负责这个事情。

GreatSQL可以作为MySQL或Percona Server的可选替代方案，用于线上生产环境。

GreatSQL完全免费并兼容MySQL或Percona Server。