PostgreSQL 监控之拨云见日 (公开课)

大家好,今天要和大家分享的是POSTGRESQL监控的问题, 关于监控可能有一些同学觉得监控无非是针对CPU 内存 以及 磁盘进行一些简单的监控,

实际上针对数据库的监控,不仅仅是这些, 那么数据库的监控到底应该怎么样监控, 今天就聊一聊

实际上POSTGRESQL的监控除了我们谈到的对系统的CPU内存磁盘等项目的监控更多的监控来自于数据库方面,需要对数据库的原理有一定的理解

或者对数据库所操作的业务中的业务逻辑有一定的了解,才能将相关的工作做好.

首先这里有一个观点,可能在数据库监控中并不时常被提到, 这个点是监控是不是也要有性价比的一个观点,

1监控的参数是否有必要百分之百的准确, 个人观点在部分参数和角度上,是没有必要要求监控的参数必须不差毫厘的准确

原因,主要有这几点

1提取数据是有间隔的,即使在间隔中提取到的数据是准确的,但间隔的跨度比较大,也会影响整体的数据的准确性

2监控是否会影响系统的性能,, 因为获取数据的密度过密, 或者提取数据的方式比较复杂, 这都会影响系统的性能

3获得参数的难易程度,也会影响整体的上面两点, 例如我们都是要获取同一个数据库性能的参数, 有几种方式, 有容易,但不准确, 有不容易很准确有十分消耗系统性能的, 这里就要考虑相关的数据的获取和性价比以及是否需要这样的准确度的问题.

另外也要看,这些信息的获得到底是要做什么, 例如形成一个系统的性能曲线图,还是要进行报警触发, 这对整体的监控的性价比和监控的方式都有不同的选择

我们举一个例子上图三种方式都能获得当前的用户连接数

三种方法都可以从某种角度获得当前的POSTGRESQL和用户之间的连接数, 不同点在于,我们上面提到的三个问题, 监控的值是否有必要百分之百要准确

监控和性能之间是否有必要的联系, 获得监控参数的难易程度, 从上面看 如果要获得准确当前有多少个和POSTGRESQL 连接的 自然是 3 更合适, 但我们可以问自己几个问题, 如果要使用3 号, 是不是要获得数据库系统的用户名密码, 是不是要建立和PG数据库之间的连接, 如果有其他的系统也在频繁查询pg_stat_activity是否会影响PG系统的性能.

反观1 是最不准确的, 但也是里面消耗资源最小的系统侵入性最小的, 这就需要看你的需求是什么, 如果仅仅是统计系统的连接数, 1号的方式基本就可以达到需求了

实际上大家可以看到真正的用户的连接有多少,只有5个

这三种方式针对不同的目的和数据提取的性质, 对于系统的优化和性能调优, 大部分的信息会来自于日志系统来进行分析, 通过查询数据库库进行信息获取的方式,多来自需要准确的一些指标获取,或与某些需要报警的参数阀值设定有关. 最后通过操作系统获取数据, 基本上在比较粗浅的系统性能参数的获得,并绘制出相关较底层的性能曲线,这里其实不同的是信息的获取,也是围绕着我们今天的三个点, 通过日志自然是对系统侵入性最小,性能影响最小的方式, 但缺点是不及时,或者分析上比较困难,而通过查询数据库来进行数据的获取,对系统的侵入性大, 缺点是很可能会影响性能,通过操作系统来获得,对于系统的侵入性不大, 但对于提取的系统的数据的准确性却不是太高,或需要程序或shell 来进行进一步的处理.

上面是一些日志的配置,这里简单的一代而过,上面并不是今天要讲的重点,主要是告诉大家,如果需要日志记录信息

配置信息主要分为以下几种

1日志的格式

2日志的输出信息的标准

3日志的位置,及日志的名字

4废弃日志的处理

POSTGRESQL本身日志的提供的数据比较集中,并且相对的配置项也比较多

我们稍微的看一下

例如信息输出的目的地在哪里, 收集是否打开还是关闭, 日志的存储的目录在哪里,日志的数据的文件名格式是什么,以及数据是否要进行rotation等等

还有日志内部的格式是什么, 这都与后面要如何分析日志有关,有些日志分析软件是要指定日志的格式.

5日志需要记录的信息

Checkpoints信息

Connection信息

Disconnection信息

Lock信息

临时表在系统中的产生的信息

例如我们收集信息的错误类型, 慢查询日志, checkpoint connection的一些信息,主机名,锁信息 等等

在说完相关的日志的配置方面的信息后,实际上也有相关工具Pgbadger,pgbadger是一个开源的分析POSTGRESQL日志的工具,通过这个工具可以对POSTGRESQL 日志进行分析,从图中我们可以看到刚刚生成的页面, pgbadger是通过perl语言撰写的根据固定格式日志,来产生WEB 分析报告的一个开源的软件其中主要对连接, checkpoint 临时文件, vacuum 以及锁 慢查询等等进行一个页面展示,并进行一些分析.

我们可以看一下图,相关的展示还是比较详细的更多的好处是可以进行二次开发将这些信息 通过网站发布,方便查看

通过日志可以分析更多的信息,这里就不在展开了, 另外我们其实是可以通过数据库系统本身来获取信息, 数据库本身的提供的信息也分两种

1与数据库底层有关的信息 ,也就是数据库与系统有关的信息2与数据库本身有关的信息, 这里PG中有一部分是pg_catalog schema 信息,其中包含了大量 与PG有关的信息

这条语句获取的信息, 有两个点 1系统的内存是否有短缺的可能, 2是否缺少索引pg_statio_user_indexes是一个视图其中包含了数据库中的表中的index的读取和命中的数字, 将这两个数字进行一个加工就可以得到一个比率,通过这个比率就可以, 下边的是pg_statio_user_tables这里也是展示在内存中获取到信息和整体读取数据的数字, 这两个的比率也是可以展示表数据读取 在内存中HIT 的情况.

熟悉POSTGRESQL的同学大多理解了POSTGRESQL 的原理,就会知道 BLOAT膨胀这个词在postgresql中是一个比较敏感的词, 我们这里不提如何解决,但你的数据库中的表的是否膨胀你是要清楚了,如果POSTGRESQL 中一个表任意膨胀.

1会占据大量的数据库存储的空间

2会影响对此表的数据查询性能

所以表膨胀一直是对POSTGRESQL 的监控中的一个点

在执行完脚本后,我们就可以观察到bloat的比率 和膨胀占用的空间, 如果我们的可以将这些数据,例如将一些关键表的数据进行历史留存,并且使用一些通过一些前端程序展示某些曲线, 就很容易发现潜在的问题,

例如经常有大型的SQL 占用某些核心表, 导致无法进行有效的 dead tuple 回收,造成某个表的 waste 空间一涨再涨

例如我们可以扩展CREATE EXTENSION pgstattuple; 对 dead_tuple_count /tuple_count*100 来看一下当前POSTGRESQL的 dead_typle_count的一个百分比, 也可以对这些关键的表设定一些警告,当超过多少百分比后

我们就进行相关的报警或触发一些操作.

与其他的数据库比较, POSTGRESQL 在buffer利用上的统计和展示是比较明确的,也是比较方便的, 这里上面的脚本我们使用POSTGRESQL的扩展 pg_buffercache , 通过这个插件配合系统表,我们可以实时的查看postgresql在buffer hit 方面的状态, 这里解释一下buffer hit 大致的意思就是 在数据处理时 数据库中的处理的数据在内存中是否都能被命中, 如果这个命中比较低的情况下,说明我们的内存短缺,或者我们有一些系统的实际SQL 不合理的.就需要我们更深层次的分析了.

同时通过延伸, 对整体的buffer_percent进行一个累计,后就可以得到我们的内存和数据之间的BUFFER HIT 的比率

通过postgresql的命令pg_isready来判断是否可以和POSTGRESQL数据库进行连接,并通过返回的数字来判断释放可以连接 还是不可以连接 0 可以连接 1 拒绝连接2 无响应

大家可以注意到,与系统的状态, 简单的信息的获取可以通过 系统的命令 + 简单的过滤 就可以了而详细需要分析的以及历史数据分析等等 大多是要通过其他的方式来进行

图中是通过PSQL 命令执行简单的SQL 语句获得当前PG的连接占总的运行连接的比率, 所以大多数简单的信息大部分都是要提供给图形化或监控报警的.

1人家监控哪里 我监控哪里, 例如某保险公司的监控参数, 我直接拿来, 可能部分常规的监控参数是可以通用的,但与特性有关的监控指标照搬就有点多此一举了,可能由于业务量, 或软件开发过程中某个原因,导致某些表vacuum出现问题,人家监控某些表,或者对某些平时不用监控的值,进行了监控, 这样照搬来的监控就部分不适合你, 1 耗费你的系统的性能,来提取无用的性能点, 耗费你的精力,导致后期监控疲劳.

2监控的内容要全, 这就是继承上面的继续的内容,什么都要监控,一个数据库监控的指标可能有有上百,甚至上千个,都要监控,毫无重点,最终出了事情,不知道那个监控点应该被响应,

3监控的阈值并没有按照业务或者程序对数据库使用的特性设定,而是有一种思维, 将阈值设置的越低越好, 越低越早报警,越早处理问题, 如果你的系统中你负责的数据库只有几个,十几个还好说,实际上如果你有上百个数据库要负责,这样的做法,只能是狼来了,最终导致监控没人看,出了事情后悔的结果,监控的软件越新越好,监控本身就是是获取监控端的数据为基础的, 新的监控软件是否在这方面有更改革新, 如果仅仅是展示方式或者其他附属功能上的提升,应考虑升级是否有必要,花费相关精力,

1复制的服务是否持续的进行2复制是否有延迟

那么我想问一个为, 如果逻辑复制停止了, 我们要不要当做一个紧急的任务来报警, 这里如果我们不考虑业务,或者说如果复制停止了, 业务在一定时间是可以承受的,或不是很在乎这里就要介入到PG的数据库的原理, 如果逻辑复制停止了, 则会最终导致主库的wal无法被清除, 沾满磁盘空间, 最终导致主库停库的问题, 说到这里如果此时有逻辑复制的PG ,我们并未监控逻辑复制是否中断后立即报警, 但这台机器的WALLOG 磁盘空间报警了, 可能第一就会想看逻辑复制是否还正常那么就会继续这个问题问, 如果是standby的库不稳定, 经常DOWN 掉, 那针对逻辑复制, 如果我设置了报警, 怎么办, 经常性的报警那就需要

1 增大WAL LOG 的空间, 设置相关的逻辑复制停止后的 多长时间进行报警 比如 5分钟以后报警还是 1分钟以后报警 这都要看 standby经常多长时间内恢复,并正常工作.

在知道监控什么, 并且知道一些如果logical replication 停止后会触发什么的情况下, 你可能会选择 ,当逻辑复制停止后,选择报警,并开始关注磁盘空间尤其是涉及 wal log 的那部分,但事情并没有到此为止, 如果你的客户告诉你, 经常获取的的数据和主库有不同的时候,怎么来解决,通过pg_stat_replication对你所在的通道中的sent_lsn write_lsn flush_lsn replay_lsn 这四个参数进行比对

通过对比这四个参数的的diff 就可以得出几种情况

1sent_lsn和write_lsn之间有延迟

2write_lsn和flush_lsn之间有延迟

3replya_lsn和flush_lsn有延迟

4sent_lsn和replay_lsn之间有没有延迟

Sent_lsn和write_lsn之间有延迟是不是网络方面有问题, 可以着重关注

Write_lsn和flush_lsn之间有延迟查看I/O 方面的压力大不大

Replay_lsn和flush之间有延迟,可以关注是否经常有批操作或大事务的存在

Sent_lsn和 replay之间没有延迟说明复制正常性能OK

这里总结出一个相关的PG 数据库或者说是数据库监控方面的一个思维导图

分别从监控的模式,监控的目的,监控的方式以及监控与性能之间的关系进行了一个初步的总结.

PG_ADMIN本身是操控POSTGRESQL的一个工具,但是Pgadmin也可以有一些简单的实施监控的功能Sloarwinds是老牌的监控工具厂商, 对于PG 也是有相关的监控, 如果有相关的投入的情况下,是可以考虑solarwinds for postgresql 其中可以Monitoring and optimization , 还有详细的detailed postgresql metrics如果对于整体的PG的执行计划语句执行等感兴趣