postgresql参数化查询_一个能融会贯通PostgreSQL监控的人，大概率是高手

有一些同学觉得监控无非是针对CPU、内存 、磁盘进行一些简单的监控，其实不仅仅如此，监控涵盖了众多知识的融合，能融会贯通PostgreSQL监控的人，大概率是PostgreSQL高手。

POSTGRESQL的监控除了对系统的CPU内存磁盘等项目的监控，更多是对数据库的监控，因此需要对数据库的原理有一定的理解，或者对数据库所操作的业务逻辑有一定的了解,才能将相关工作做好.

监控的主要目的

1、在预订的问题(阈值)发生时或某个预订时间发生时，进行报警

2、针对一段系统运行历史时期的某项值进行跟踪，对系统的未来进行评估

3、通过监控的值或收集到的信息，解决系统在运行中发生的问题。

监控的性价比问题，也就是监控成本考量

1、监控的参数不一定追求百分之百的精准，需要与监控目标，占用资源等情况一起综合考量

2、监控和性能之间有着密切的联系

3、获得监控参数的难度和复杂度，也决定了监控的成本

以下是详细说明：

1、提取数据是有间隔的,即使在间隔中提取到的数据是准确的,但间隔的跨度，将影响整体数据的准确性。过密影或者提取数据的方式复杂，将影响系统性能，间隔跨度过大又影响分析的准确性。

2、获取同一个数据库性能的参数, 可以选择不同方法，难易程度、数据准确性、系统耗能等因素都需要考量。有容易但不准确的方法、也有难度大很准确又十分消耗系统性能的方法。

3、获取信息的目的各有不同，目标不同，对监控方式的选择不同。是要形成一个系统的性能曲线图,还是要进行报警触发，显然对信息获取的要求是完全不同的。

举一个例子，想要获得当前的用户连接数，方法有三：

三种方法都可以从某种角度获得当前的POSTGRESQL和用户之间的连接数, 哪种最适合？

如果要获得最准确的当前与POSTGRESQL 的连接用户数，应该用方法3。可使用方法3，就需要获得数据库系统的用户名密码, 要建立和PG数据库之间的连接, 还要考虑到如果其他的系统也在频繁查询pg_stat_activity，是否会影响PG系统的性能的问题?

方法1 虽然最不准确, 但消耗资源最小、系统侵入性最小！ 如果仅仅是统计系统的连接数, 1号的方式基本可以达到需求了。

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

监控中信息获得方式与目的通常有三种

1、通过日志进行分析。对于系统的优化和性能调优, 大部分的信息会来自于日志系统来进行分析, 通过日志获取是对系统侵入性最小,性能影响最小的方式, 缺点是不及时或者分析上比较困难。

2、通过查询数据库进行相关数据的获取。多来自需要准确指标的获取,或与某些报警的参数阀值设定有关. 通过查询数据库来进行数据的获取,对系统的侵入性大, 缺点是很可能会影响性能。

3、通过操作系统获取。基本上在比较粗浅的系统性能参数,并绘制出相关较底层的性能曲线。此方式对于系统的侵入性不大。

接下来我们就分别说说这三种方式。。。

1、通过日志进行分析：

如果需要日志记录信息，配置信息主要分为以下几种

1、日志的格式

2、日志的输出信息的标准

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

4、废弃日志的处理

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

例如信息输出的目的地, 收集是否打开还是关闭, 日志的存储的目录,日志数据的文件名格式,以及数据是否要进行rotation等等。还有日志内部的格式是什么, 这都与后面要如何分析日志有关,有些日志分析软件是要指定日志的格式.

5、日志需要记录的信息

Checkpoints信息

Connection信息

Disconnection信息

Lock信息

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

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

介绍个相关工具Pgbadger,Pgbadger是一个开源的分析POSTGRESQL日志的工具,通过这个工具可以对POSTGRESQL 日志进行分析,Ppgbadger是通过perl语言撰写的根据固定格式日志,来产生WEB 分析报告的一个开源的软件。其中主要对连接, checkpoint 临时文件, vacuum 以及锁慢查询等等进行一个页面展示,并进行一些分析.

Postgresql 如何分析日志 -- Pgbadger

Postgresql 如何分析日志 -- Pgbadger

上图的相关展示还是比较详细的。还可以进行二次开发将信息通过网站发布,方便查看。

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

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

2、通过语句获取postgresql 信息：

SELECT'index hit rate' AS name,(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratioFROM pg_statio_user_indexesUNION ALLSELECT'table hit rate' AS name,sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratioFROM pg_statio_user_tables;

这条语句获取的信息, 有两个点 ：

1系统的内存是否有短缺的可能。

2是否缺少索引。

pg_statio_user_indexes是一个视图，其中包含了数据库中的表中的index的读取和命中的数字, 将这两个数字进行加工就可以得到一个比率,通过这个比率就可以, 下边的是pg_statio_user_tables这里也是展示在内存中获取到信息和整体读取数据的数字, 这两个的比率也是可以展示表数据读取 在内存中HIT 的情况.

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 的比率。

3、通过系统获得监控数据:

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

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

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

监控误区

误区1、人家监控哪里我就监控哪里。例如某保险公司的监控参数, 我直接拿来, 可能部分常规的监控参数是可以通用的,但与特性有关的监控指标照搬就有点多此一举了。业务量及业务内容，业务需求都不同，照搬来的监控，有些内容即耗费你的系统的性能,来提取无用的性能点, 又耗费你的精力,导致后期监控疲劳.

误区2、监控的内容要全。一个数据库监控的指标可能有上百,甚至上千个。都要监控,毫无重点,最终出了事情,不知道哪个监控点应该被响应.

误区3、监控的阈值要低。越早报警越好。如果你的系统中你负责的数据库只有几个,十几个还好说,实际上如果你有上百个数据库要负责,这样的做法,只能是狼来了,最终导致监控没人看，出了事情再后悔莫及。

误区4、监控软件越新越好。监控本身就是获取监控端的数据为基础的, 新的监控软件是否在这方面有更改革新, 如果仅仅是展示方式或者其他附属功能上的提升,应考虑升级的花费以及相关精力的付出。

监控原理

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 监控的工具：

•PG_ADMIN

•Solarwinds

•Pganalyze

•PGWATCH

•PMM2

•PGHERO

•PGCLUU

•PGBADGER

•PGTOP

以上内容有对应视频授课内容，请近期关注，我剪辑完就上传。

以上内容由东方瑞通资深讲师 Austin原创，Austin老师13年专业DBA经验，曾任互联网金融公司Senior DBA、500强制药企业Senior DBA，精通Mysql、PostgreSQL、Mongo DB、SQLServer

#PostgreSQL#