统计日志数据库bingostat有一个gameplay表，用于记录玩家游戏行为。该表最近2个月（2020年1、2月）每月会积累200多万条记录。从游戏上线运营开始一共产生了1200多万条日志。使用的是PostgreSQL数据库。通常的说法是单表达到千万级记录就算大表了。PM向我反映查询该表响应速度变慢了。和表的记录数多有很大关系，同时跟查询语句也有关。做分表是最简单直接的优化查询效率的方式。
经过一番操作，完成了分表。原来的表有唯一索引，基于若干个字段创建 （pid, room_id, round_id, theme_id）。PostgreSQL分表之后，是不能创建唯一索引的。然后该表就没有创建任何索引。日志解析工具的实现代码中使用到了唯一索引，所以需要改代码。将日志写入gameplay表的逻辑是这样的：
- 根据（pid, room_id, round_id, theme_id）查询是否有记录
- 如果没有，就插入一条日志
- 如果有，就根据（pid, room_id, round_id, theme_id）更新日志的某些字段
在实现细节上我还使用了事务。日志解析工具中只有一个线程基于同步I/O操作数据库。所以事务实际上是多余的。
部署了修改后的日志解析工具后，监控系统报警：统计数据库所在机器的CPU使用率多高。
日志解析工具是在机器A上解析日志，并写入机器B上的统计数据库。统计数据库的硬件配置是：两核CPU，4G内存。
监控系统显示：
-
CPU占有率持续处于高位。
CPU占有率.jpg
-
之前的系统负载一直在0.1以下，现在的负载达到4.5。
系统平均负载.jpg
我的第一反应是事务导致了CPU飙高。然后我去掉了日志解析工具中的多余的事务使用。再次部署后，发现CPU占有率依然居高不下。
然后我想到索引的问题。原来有索引，现在没有索引。原来的代码使用到了索引相关的字段，现在的代码依然使用了索引相关的字段，也就是说涉及的SQL语句中的SELECT和UPDATE都带有WHERE子句。没有索引，会导致全表扫描。千万级记录数，全表扫描效率极低。我立即写了一个脚本给每一个分表创建了基于（pid, room_id, round_id, theme_id）四个字段的唯一索引，之后系统负载很快就回落到0.1以下。
网友评论