前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL读写分离一遇高并发访问,所有服务瞬间集体罢工,问题解决如坐过山车,太刺激了

MySQL读写分离一遇高并发访问,所有服务瞬间集体罢工,问题解决如坐过山车,太刺激了

作者头像
猿芯
发布2021-05-27 18:11:31
1.1K0
发布2021-05-27 18:11:31
举报
文章被收录于专栏:Wooola的技术博客

前言

最近系统(基于 SpringCloud + K8s)上线,运维团队早上 8 点左右在群里反馈,系统登录无反应!我的第一反应是 MySQL 数据库扛不住了。

排查问题也是一波三折,有网络问题,也有 MySQL 读写分离后数据库参数优化问题。

问题回顾

1、运维团队早上 8 点左右在群里反馈,系统登录无反应。

2、DevOps 团队通过查看 Kibana 日志

发现 ELKK8s 集群、RedisMongodbNginx、文件服务器全部报:”Connect Unknown Error“,中间件服务集体挂彩,团队成员惊出一身冷汗。。。一想到某联大领导每日混迹于办公门户,瑟瑟发抖呀。。。

心里嘀咕难道 K8s 容器也挂了?那还怎么玩?

3、查看监控短信,连续收到数据库读写分离Master-Slave警告信息

问题定位

1、Connect Unknown Error

经过从 K8s 团队确认,在早上 8 点左右出现了网络中断,持续了大概 1 分钟左右,导致 K8s 平台剔除响应超时的微服务节点,同时不断的启动新的容器。通过日志分析,8 点半左右容器平台恢复正常,但是前台页面查询数据很慢(后来定位是 MySQL 数据库服务器 CPU 占用 92% ,导致数据库服务器处理应用请求很慢)。

2、MySQL 读写分离 Master-Slave 警告信息

MHA架构

MySQL 读写分离是采用 MHA 架构,一主两从(Master-Slave)。

Master 负责数据的写操作,同时通过 binlog 日志同步到两个 Slave 从库,从库负责应用程序的查询操作。

在报 Connect Unknown Error 异常后,我们检查了 MySQL 服务器,发现 Master 节点 CPU 占用 92%(应用层读写请求全部路由到了 Master节点原因导致),而两个 Slave 节点全部处于空闲状态,并且主从数据不同步了。

3、数据库DBA通过查看 MySQL 的 show processlist 命令,发现有大量的 “create sort index(排序索引)” SQL 语句(约 36 个)

经排查发现有个 cms_article 表有几百万的数据,客户端分页查询请求,虽然只取 10 条数据行,但是实际查询了几百万行数据,而且要在数据库内存中进行了几百万数据内存排序。所以出现了大量的 create sort index 排序索引。而且频繁执行 Create Sort Index 会造成 MySQL 占满服务器 CPU,导致服务器请求无响应,甚至假死状态!

解决办法

1、Connect Unknown Error

K8s 平台自动剔除响应超时的微服务节点,同时启动新的容器,直至恢复到故障前的容器节点水平,依靠 K8s 平台自我修复。

2、MySQL 读写分离 Master-Slave 警告信息

恢复步骤

  1. 重启 Master-Slave 节点,应用层读写请求正常,但是主从数据还是不同步,经定位是 mysql 同步线程 Slave_IO_RunningSlave_SQL_Running 都为 No
  2. 晚上重启 Slave_IO_RunningSlave_SQL_Running binlog 日志同步线程

只有 Slave_IO_RunningSlave_SQL_Running 都为 yes ,则表示同步成功。

3、数据库 DBA 通过查看 MySQL 的 show processlist 命令,发现有大量的 “create sort index (排序索引)” SQL 语句(约 36 个)

innodb_buffer_pool_size500M 调整为 300G(服务器共 500G 内存)。

innodb_buffer_pool_size 用于缓存索引和数据的内存大小,这个当然是越多越好,数据读写在内存中非常快,减少了对磁盘的读写。

当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用,一般设置buffer pool 大小为总内存的 1/51/4。若设置不当,内存使用可能浪费或者使用过多。

对于繁忙的服务器, buffer pool 将划分为多个实例以提高系统并发性,减少线程间读写缓存的争用。

buffer pool 的大小首先受 innodb_buffer_pool_instances 影响, 当然影响较小。

MySQL 性能调优总结

预计 44W 用户 峰值在线人数 5 万左右。

1、innodb_buffer_pool_size=500M

太小,严重影响数据库性能。

服务器共 500G 内存,但只给 MySQL 缓冲池分配了 500M,非常影响数据库性能,且造成资源浪费。

建议设置为服务器内存的 60%

2、expire_logs_days=7

太短,只能保留 7 天的 binlog,只能恢复 7 天内的任意数据。建议设置为参数文件里被覆盖的 90 天的设置。

3、long_query_time=10

太长,建议设置为 2 秒,让慢查询日志记录更多的慢查询。

4、transaction-isolation = read-committed

建议注释掉,使用数据库默认的事务隔离级别

5、innodb_lock_wait_timeout = 5

设置得太小,会导致事务因锁等待超过5秒,就被回滚。建议和云门户设置得保持一致,云门户大小为 120

6、autocommit = 0

#建议改为 mysql 默认的自动提交( autocommit=1 ),提升性能,方便日常操作

往期推荐

  1. 肝九千字长文 | MyBatis-Plus 码之重器 lambda 表达式使用指南,开发效率瞬间提升80%
  2. 用 MHA 做 MySQL 读写分离,频繁爆发线上生产事故后,泪奔分享 Druid 连接池参数优化实战
  3. 微服务架构下,解决数据库跨库查询的一些思路
  4. 一文读懂阿里大中台、小前台战略

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-04-19,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 架构荟萃 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • 问题回顾
    • 1、运维团队早上 8 点左右在群里反馈,系统登录无反应。
      • 2、DevOps 团队通过查看 Kibana 日志
        • 3、查看监控短信,连续收到数据库读写分离Master-Slave警告信息
        • 问题定位
          • 1、Connect Unknown Error
            • 2、MySQL 读写分离 Master-Slave 警告信息
              • 3、数据库DBA通过查看 MySQL 的 show processlist 命令,发现有大量的 “create sort index(排序索引)” SQL 语句(约 36 个)
              • 解决办法
                • 1、Connect Unknown Error
                  • 2、MySQL 读写分离 Master-Slave 警告信息
                    • 3、数据库 DBA 通过查看 MySQL 的 show processlist 命令,发现有大量的 “create sort index (排序索引)” SQL 语句(约 36 个)
                    • MySQL 性能调优总结
                      • 1、innodb_buffer_pool_size=500M
                        • 2、expire_logs_days=7
                          • 3、long_query_time=10
                            • 4、transaction-isolation = read-committed
                              • 5、innodb_lock_wait_timeout = 5
                                • 6、autocommit = 0
                                相关产品与服务
                                容器服务
                                腾讯云容器服务(Tencent Kubernetes Engine, TKE)基于原生 kubernetes 提供以容器为核心的、高度可扩展的高性能容器管理服务,覆盖 Serverless、边缘计算、分布式云等多种业务部署场景,业内首创单个集群兼容多种计算节点的容器资源管理模式。同时产品作为云原生 Finops 领先布道者,主导开源项目Crane,全面助力客户实现资源优化、成本控制。
                                领券
                                问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档