问题背景
之前有提到过, 我们将游戏本地的数据库集中起来进行存储.
在整体方面是有了大幅度的提升, 包括迁移, 监控, 高可用, 以及备份.
尤其时在迁移方面, 我们将单个服10-30分钟的迁移时间压缩到了1-3分钟(不用迁移数据库).
然而, 之前的的数据库瓶颈分布在 5,6 台主机上, 现在却集中在一台 4 核 16G 的主机上.
这明显会产生其他的问题, 比如我们处理过的 CPU 高突发以及 binlog 数据量过大.
以及目前碰到了二个新问题
新增字段
游戏启动时, 会对数据库的表进行检测, 如果不存在则进行新增字段
所以我们更新维护时间会特别长, 之前数据都是分布在游戏服上的
所以整体上能在一个小时内全部解决.
但是将数据库独立出来集中存储后, 突然到了某个临界值, 问题就来了.
因为使用的云数据库, 所以只有看到数据层面的监控信息.
从监控上看是因为 CPU 过高, 所以当时就觉得是 CPU 瓶颈.
看了下慢查询的日志, 发现大多数都是 update 全表的 sql 语句.
找研发问了问什么情况, 得到答复是为了保证数据的一致性.
在新增字段后后对整表进行一次 update 来初始化字段.
因为使用的是 BLOB 字段, 所以无法设置初始值.
当时我还真就以为是全表的 update 导致维护时间边长.
再三的和研发沟通, 看看是否能避免这个操作, 然而并不能.
另外, 有个问题一直没想明白.
我们大概有 100 多个数据库, 同时加字段, 然后全表 update
从 10:30 一直到 16:30, 都没有弄完.
我们只能把全停掉, 然后 10 个 10 个一组, 才能正常的完成启动.
什么原因, 数据的总量是不变的, 而且数据库都是独立的(一台服务器), 所以应该不存在锁的问题.
大概了解了一下 mysql 加字段原理,
1 | 1. 首先, 对原始表结构COPY了一份 |
因为云数据库没法看到 I/O 的情况, 所以我们只能在本地进行测试
发现新增字段的过程涉及到大量的 I/O 操作.
在stackoverflow上看了下, 有几个解决方案
1 | 1. pt-online-schema-change |
最后我们只能将并发的启动改成顺序的启动, 从而避免这个问题.
但是到底为什么会卡死还没有弄明白, 有机会在研究研究.
limit 的瓶颈
需求背景是
A,B 两个数据库, 要将 A,B 数据库某些表的数据全部取出来, 然后将 B 合并到 A 上.
需要对冲突的数据进行一些处理, 冲突处理的时间先忽略不计, 数据提取的逻辑是这样的
1 | total=select count(*) from A |
因为数据合并很慢, 所以需要找找瓶颈.
看了下 mysql 的慢日志, 一条语句需要跑 100s
从云数据库的监控上来看, CPU, qps, 逻辑读和物理读并没有达到瓶颈.
但是在云数据库上又没法看到 I/O 的情况, 所以当时以为就是 I/O 的问题.
因为这个 SQL 写法逻辑上就很奇怪, 明明需要获取所有的数据.
但是却使用 limit 来分批获取, 导致 mysql 每次都要重新读取磁盘的数据.
和研发沟通几次调整修改, 就是不肯, 非说这是上了云数据库导致的问题.
要让我们从云数据库上优化, 这就很尴尬了.
我们只能找云厂商帮忙看看有没其他方法可以优化优化.
专业的 DBA 一下就看出我们这个 sql 语句有内鬼.
这里也触及到我的一个知识盲区.
既然程序一直再跑, 但是 CPU 和 I/O 又没跑满.
那到底是什么原因导致吞吐量一直上不去.
就是资源又没跑满, 但是吞吐量却上不去.
没办法, 只能在本地数据库上进行模拟测试看看.
经过实践发现, 瓶颈确实在 CPU 上, 之所以 CPU 没跑满
是因为一条 SQL 语句只占用一个 CPU, 也就是说
这个这个 sql 分配到一个 CPU 上, 即使这个 SQL 火力全开, 也得 100s 才能执行完成.
但是执行 sql 又不能利用多核, 毕竟人家不是分布式的数据库.
那么问题又来了, 总过也才 20000 条的数据.
将数据从磁盘 load 到内存并没有花费太多的时间
难道你一个 CPU 在内存遍历 20000 条数据还要按秒算?
根据数据库的慢日志发现有意思的现象或许可以解释解释
有 a 表和 b 表, a 表是记录数 20000 左右, b 表记录数 2000000 左右.
a 是宽表, 单条记录很大, b 表窄表, 单条记录很小
但是select * from a limit 18000 10比select * from b limit 1800000 10要更慢.
这就说明 sql 执行的时间不只和offset有关, 还和单条记录的大小有关.
只是有点想不明白, 记录与记录之间不是应该用链表连接的吗?
为啥单条记录大, 遍历时间就长, 而单条记录小, 遍历时间就短呢.
受限于知识水平和时间因素, 暂时搞不明白, 而且还有其他更重要的事, 只能先记录下来.
扯回来, mysql 的 limit 并不是只消耗取 10 条的数据的时间.
他要将所有数据全部取出来, 然后过滤掉前 offset 条数据, 只返回接下来的 10 条数据
可想而知, 当 offset 数越大, 消耗的时候就越多.
原本是个很简单的问题, 结果研发却硬说是云数据库的问题.
因为之前在本地数据库上操作是没有这个问题的, 真的是成功的气到我了.
然后我们只能将数据库复制到本地上, 然后执行同样的合服操作.
结果证明, 本地的更慢, 确当了问题, 就很好处理了.
只要避免全表扫描, 就能提高性能, 比较简单的方式是根据主键使用 where 过滤.
走索引比全表扫描快 N 多倍, 之前一条 sql 需要 100s, 走索引后只需 0.1s
至于为什么不把数据全部取出来处理, 据说是因为框架不支持.
我也没细问, 这又是另一个故事了.
总结
1 | 1. 上云数据库有得有失吧, 感觉是利大于弊, 应该确实暴露出很多不合理的使用 |