title: 记一次PostgreSQL数据库超级慢故障排除
categories: 数据库
tags:
- PostgreSQL
timezone: Asia/Shanghai
date: 2019-01-06
环境
REHL6.8（128G内存、16路CPU、5T硬盘）
PostgreSQL 9.6
问题描述
数据库查询任何数据时间都需要超长的时间等待
select count(*)一个看空表都需要30分钟
问题排查
1.查看内存占用，发现内存占用不高
free -h
2.查看cpu负载，也不高
top
3.查看硬盘负载
iostat -x 1 5
iostat -m 1 5
-x 选项将用于显示和io相关的扩展数据;
-m 表示单位为m
1表示间隔；10表示次数
tps 每秒钟发送到的I/O请求数
MB_read/s 读取多少MB/s
MB_wrtn/s 写入多少MB/s
MB_read 以上命令设置间隔时间内读取数据总数
MB_wrtn 以上命令设置间隔时间内写入数据总数
此时已经将所有业务全部停掉为分析原因让路。通过观察发现，没有数据库操作的时候服务器的IO非常低，但是只要有一个小小的写入或者查询操作硬盘读取速度能到600M/s。到这里问题初步判定，是由于硬盘IO问题造成了数据库读写缓慢。
经过询问，数据库是大数据平台用数据库。表并不多，也就三四十个表，最多的表数据也就几百万行，按理说不应该出现这样的问题。
继续查找原因。
找到数据库存储目录，发现一共占用磁盘空间1.3T。这样的数据量不应该占用如此之大的硬盘空间。PostgreSQL记得是一个表用就是一个文件。其中有一个表文件有500多个，每个1G，也就是这个表占用了500多G的空间。
4.查询上边占用500G空间的表
pg_class 表
select relname, relnamespace, relfilenode from pg_class where relfilenode = xxx;
xxx是上边占用500多G的文件名（数字）
经过询问这个表有几百万行数据，而且刚执行了delete操作。
继续询问得知，现在的上册数据源有问题，所有做的定时任务每天先将本地所有表使用delete命令清空，然后再重新从上层数据库拉去全部数据。问题到这里基本明朗，delete命令删除数据的同时还会记录相关操作记录。造成了虽然表里所有数据已经清空，但是并没有释放磁盘空间，由此造成查询效率奇低。
5.问题的解决。
既然知道了原因，那问题就好办了，先备份表结果，然后将之前每天delete的表drop后重建，操作完成后数据库已经由1.3T变为30多G，速度问题也就此解决。
总结
delete 命令用来删除数据，但是不适用于以上场景，因为delete会生成大量日志
truncate 如果确认表内所有数据全部没用了，而且不需要日志可以采用此命令。或者干脆drop删除掉后重建。
附录：pg_class表详解
| 名字 | 类型 | 引用 | 描述 |
|---|---|---|---|
| relname | name | 表、索引、视图等的名字。 | |
| relnamespace | oid | pg_namespace.oid | 包含这个关系的名字空间(模式)的 OID |
| reltype | oid | pg_type.oid | 对应这个表的行类型的数据类型(索引为零，它们没有 pg_type 记录)。 |
| relowner | oid | pg_authid.oid | 关系所有者 |
| relam | oid | pg_am.oid | 如果行是索引，那么就是所用的访问模式(B-tree, hash 等等) |
| relfilenode | oid | 这个关系在磁盘上的文件的名字，如果没有则为 0 | |
| reltablespace | oid | pg_tablespace.oid | 这个关系存储所在的表空间。如果为零，则意味着使用该数据库的缺省表空间。如果关系在磁盘上没有文件，则这个字段没有什么意义。 |
| relpages | int4 | 以页(大小为 BLCKSZ)的此表在磁盘上的形式的大小。它只是规划器用的一个近似值，是由 VACUUM, ANALYZE 和几个 DDL 命令，比如 CREATE INDEX 更新。 | |
| reltuples | float4 | 表中行的数目。只是规划器使用的一个估计值，由 VACUUM, ANALYZE 和几个 DDL 命令，比如 CREATE INDEX 更新。 | |
| reltoastrelid | oid | pg_class.oid | 与此表关联的 TOAST 表的 OID ，如果没有为 0 。TOAST 表在一个从属表里"离线"存储大字段。 |
| reltoastidxid | oid | pg_class.oid | 对于 TOAST 表是它的索引的 OID ，如果不是 TOAST 表则为 0 |
| relhasindex | bool | 如果它是一个表而且至少有(或者最近有过)一个索引，则为真。它是由 CREATE INDEX 设置的，但 DROP INDEX 不会立即将它清除。如果 VACUUM现一个表没有索引，那么它将清理 relhasindex 。 | |
| relisshared | bool | 如果该表在整个集群中由所有数据库共享则为真。只有某些系统表(比如 pg_database)是共享的。 | |
| relkind | char | r = 普通表， i = 索引， S = 序列， v = 视图， c = 复合类型， t = TOAST 表 | |
| relnatts | int2 | 关系中用户字段数目(除了系统字段以外)。在 pg_attribute 里肯定有相同数目对应行。又见 pg_attribute.attnum | |
| relchecks | int2 | 表里的检查约束的数目；参阅 pg_constraint 表 | |
| reltriggers | int2 | 表里的触发器的数目；参阅 pg_trigger 表 | |
| relukeys | int2 | 未使用(不是唯一值的数目) | |
| relfkeys | int2 | 未使用(不是表中外键的数目) | |
| relrefs | int2 | 未使用 | |
| relhasoids | bool | 如果为关系中每行都生成一个 OID 则为真 | |
| relhaspkey | bool | 如果这个表有一个(或者曾经有一个)主键，则为真。 | |
| relhasrules | bool | 如表有规则就为真；参阅 pg_rewrite 表 | |
| relhassubclass | bool | 如果有(或者曾经有)任何继承的子表，为真。 | |
| relfrozenxid | xid | 该表中所有在这个之前的事务 ID 已经被一个固定的("frozen")事务 ID 替换。这用于跟踪该表是否需要为了防止事务 ID 重叠或者允许收缩 pg_clog 而进行清理。如果该关系不是表则为零(InvalidTransactionId)。 | |
| relacl | aclitem[] | 访问权限。参阅 GRANT 和 REVOKE 获取详细信息。 | |
| reloptions | text[] | 访问方法特定的选项，使用"keyword=value"格式的字符串 |
