记一次PostgreSQL数据库超级慢故障排除

随笔 专栏收录该内容
37 篇文章 0 订阅

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表详解

名字类型引用描述
relnamename表、索引、视图等的名字。
relnamespaceoidpg_namespace.oid包含这个关系的名字空间(模式)的 OID
reltypeoidpg_type.oid对应这个表的行类型的数据类型(索引为零,它们没有 pg_type 记录)。
relowneroidpg_authid.oid关系所有者
relamoidpg_am.oid如果行是索引,那么就是所用的访问模式(B-tree, hash 等等)
relfilenodeoid这个关系在磁盘上的文件的名字,如果没有则为 0
reltablespaceoidpg_tablespace.oid这个关系存储所在的表空间。如果为零,则意味着使用该数据库的缺省表空间。如果关系在磁盘上没有文件,则这个字段没有什么意义。
relpagesint4以页(大小为 BLCKSZ)的此表在磁盘上的形式的大小。它只是规划器用的一个近似值,是由 VACUUM, ANALYZE 和几个 DDL 命令,比如 CREATE INDEX 更新。
reltuplesfloat4表中行的数目。只是规划器使用的一个估计值,由 VACUUM, ANALYZE 和几个 DDL 命令,比如 CREATE INDEX 更新。
reltoastrelidoidpg_class.oid与此表关联的 TOAST 表的 OID ,如果没有为 0 。TOAST 表在一个从属表里"离线"存储大字段。
reltoastidxidoidpg_class.oid对于 TOAST 表是它的索引的 OID ,如果不是 TOAST 表则为 0
relhasindexbool如果它是一个表而且至少有(或者最近有过)一个索引,则为真。它是由 CREATE INDEX 设置的,但 DROP INDEX 不会立即将它清除。如果 VACUUM现一个表没有索引,那么它将清理 relhasindex 。
relissharedbool如果该表在整个集群中由所有数据库共享则为真。只有某些系统表(比如 pg_database)是共享的。
relkindcharr = 普通表, i = 索引, S = 序列, v = 视图, c = 复合类型, t = TOAST 表
relnattsint2关系中用户字段数目(除了系统字段以外)。在 pg_attribute 里肯定有相同数目对应行。又见 pg_attribute.attnum
relchecksint2表里的检查约束的数目;参阅 pg_constraint
reltriggersint2表里的触发器的数目;参阅 pg_trigger
relukeysint2未使用(不是唯一值的数目)
relfkeysint2未使用(不是表中外键的数目)
relrefsint2未使用
relhasoidsbool如果为关系中每行都生成一个 OID 则为真
relhaspkeybool如果这个表有一个(或者曾经有一个)主键,则为真。
relhasrulesbool如表有规则就为真;参阅 pg_rewrite
relhassubclassbool如果有(或者曾经有)任何继承的子表,为真。
relfrozenxidxid该表中所有在这个之前的事务 ID 已经被一个固定的(“frozen”)事务 ID 替换。这用于跟踪该表是否需要为了防止事务 ID 重叠或者允许收缩 pg_clog 而进行清理。如果该关系不是表则为零(InvalidTransactionId)。
relaclaclitem[]访问权限。参阅 GRANTREVOKE 获取详细信息。
reloptionstext[]访问方法特定的选项,使用"keyword=value"格式的字符串
  • 0
    点赞
  • 2
    评论
  • 5
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

小六的昵称已被使用

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值