使用数据库健康仪表板监控 PostgreSQL 性能

Postgredaxiang

225人浏览 · 2022-08-15 06:29:22

Postgredaxiang · 2022-08-15 06:29:22 发布

[](https://res.cloudinary.com/practicaldev/image/fetch/s--K8d7Yn3i--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype.com/blog /content/images/size/w1750/2021/06/alexander-sinn-KgLtFCgfC28-unsplash--1-.jpg)

为什么要监控 PostgreSQL 数据库健康?

就像监控我们的应用程序一样,监控我们的数据库系统是非常有必要的。我们希望监控比系统级流程更精细的事物——例如:

实际使用索引的查询有多少?
数据库缓存的效果如何?
打开的连接数。

等等。

硬件指标是必不可少的,但只能让我们做到这一点,并且不会帮助我们做出具体的决策——比如要优化哪些查询以及我们如何使用我们的数据库。由于 PostgreSQL 是一个开源数据库,它可以免费提供很多见解,而无需依赖昂贵的工具。我们要跟踪的大多数指标在默认情况下都是可用的,因为它们是查询计划器所必需的,但是还有其他关键领域,我们必须启用与 PostgreSQL 一起提供的某些扩展/贡献模块。

Arctype 工具介绍

Arctype是一个非常简洁的数据库工具,它不仅可以用作 SQL 编辑器,还可以用作平台,在该平台上可以自定义构建复杂的仪表板并与开发人员/用户共享。为了了解它的真正威力,我们将考虑一个实际用例——监控 PostgreSQL 数据库的健康状况。

Arctype 仪表板入门

为了熟悉仪表板功能,我们将构建一个非常简单的仪表板,其中只有三个组件。 Arctype 仪表板直接内置于 Arctype 工具本身。要访问该功能,只需单击Dashboards按钮,如下所示。

仪表板创建

单击加号图标并创建一个名为 -Postgres Health Monitoring的仪表板。

[ 创建名为的仪表板](https://res.cloudinary.com/practicaldev/image/fetch/s--4QVPZYer--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype. com/blog/content/images/2021/05/Arctype_Dashboard_2.png)

[ 仪表板命名窗格 ](https://res.cloudinary.com/practicaldev/image/fetch/s--g56M-9_w--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype .com/blog/content/images/2021/05/Arctype_Dashboard_3.png)

现在仪表板已创建,让我们创建一些组件。

从 Information_Schema 创建 PostgreSQL 表列表

添加表格组件/图表组件在我们之前的文章](https://dev.to/blog/analytics-dashboard-django/#creating-a-dashboard-for-your-database)中的一篇[中进行了介绍。将Table拖放到空的仪表板窗格中。接下来,我们将使用一个简单的查询与底层数据库 (PostgreSQL) 对话,并获取所有表及其模式,不包括information_schema和系统表。

SELECT
  table_schema, table_name
FROM
  information_schema.tables
WHERE
  table_schema <> 'information_schema'
  AND table_name NOT LIKE 'pg_%'
ORDER BY
  table_schema,
  table_name;

进入全屏模式退出全屏模式

可视化数据库模式中表的行数

让我们在使用以下查询的表组件旁边添加一个图表组件(类似于表):

SELECT
  relname as table_name,
  reltuples as rows
FROM
  pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema')
  AND relkind = 'r'
ORDER BY
  reltuples DESC;

进入全屏模式退出全屏模式

此查询仅计算我们数据库中所有表的记录。在右窗格中,为 X 轴选择table_name,为 Y 轴选择rows。

将图像添加到 Arctype 仪表板

添加表格和图表后,让我们将来自PostgreSQL 网站的图像添加到我们的仪表板以及用于说明的文本字段。添加上述所有步骤后,我们的仪表板将如下所示:

仪表板预览

此时,您应该对仪表板有一个不错的感觉,所以现在,让我们构建一个真正的健康仪表板,看看它如何帮助我们可视化我们的数据库健康状况。

识别 PostgreSQL 数据库中的关键区域

PostgreSQL 有很多我们可以关注的领域,以便了解数据库的健康状况。在下面的部分中,我们将看到其中的一些,并演示如何使用 Arctype 来可视化它们。

查询PostgreSQL打开连接

监控我们的数据库集群中当前存在多少打开的连接总是一个好主意。我们可以从pg_stat_activity表中得到:

SELECT
  COUNT(*) as connections,
  backend_type
FROM
  pg_stat_activity
GROUP BY
  backend_type
ORDER BY
  connections DESC

进入全屏模式退出全屏模式

确定 PostgreSQL 数据库的大小

随着我们的数据库随着时间的推移而扩展,控制数据库大小很重要。我们可以使用以下查询获取数据库列表及其各自的大小:

SELECT
  datname as database_name,
  pg_database_size(datname)/1024/1024 as size
FROM
  pg_database
WHERE
  datistemplate = false;

进入全屏模式退出全屏模式

我们可以使用pg_size_pretty而不是直接计算 MB,但我们希望将其绘制为条形图以查看和比较大小。密切关注各个数据库的大小有助于我们全面了解情况。

注意:这些大小可能因磁盘大小而异,因为 PostgreSQL 在后台进行了大量压缩。

在 PostgreSQL 中计算数据库缓存命中率

任何关系数据库/操作数据库都从其缓存中提供大部分流量。如果这没有发生,我们肯定需要调查一下。 PostgreSQL 缓存区被称为shared_buffers,它充当操作系统提供的缓存之上的数据库层缓存。我们可以使用以下查询来了解缓存命中率:

SELECT
  sum(heap_blks_read) as reads,
  sum(heap_blks_hit) as hits,
  ROUND(
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)),
    4
  ) as hit_ratio
FROM
  pg_statio_user_tables;

进入全屏模式退出全屏模式

从查询计划博客中,有几个元组来自堆读取和堆命中。pg_statio_user_tables有这方面的信息。在上面的查询中,我们只是获取命中和读取的值(来自磁盘)并计算四舍五入的比率。需要注意的是,在一个健康的生产数据库中,这个比率应该达到 97+ 或接近它——97% 的命中来自缓存。

扫描未使用的索引

PostgreSQL 中的统计信息收集器记录了哪些索引被访问的频率。我们可以从名为pg_stat_all_indexes的统计表之一构造查询。

SELECT
  COUNT(*) as count,
  relname as table_name
FROM
  pg_stat_all_indexes
WHERE
  idx_scan = 0
  and schemaname = 'public'
GROUP BY
  table_name

进入全屏模式退出全屏模式

这种类型为我们提供了具有最多未使用索引的表的指示。idx_scan=0为我们提供了从未使用过的索引。我们可以根据我们的数据库流量模式自定义此值。

检查PostgreSQL数据库缓存

为了更深入地了解我们的缓存,我们需要启用另一个名为pg_buffercache的贡献模块。启用它并运行一些查询后,我们现在可以检查缓存:

SELECT
  c .relname AS entity_name,
  count(*) AS buffers
FROM
  pg_buffercache b
  INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c .oid)
  AND b.reldatabase IN (
    0,
    (
      SELECT
        oid
      FROM
        pg_database
      WHERE
        datname = current_database()
    )
  )
WHERE
  c .relname NOT LIKE 'pg_%'
GROUP BY
  c .relname
ORDER BY
  2 DESC;

进入全屏模式退出全屏模式

这个查询取自官方文档,然后稍作修改。它显示了shared_buffers中有多少页被不同的表和索引占用。您可以假设页面作为与行数成正比的指标——查询的表越大,移动到缓存中的页面就越多。这也意味着可以将某些表移动到应用程序缓存或快速键值存储(例如 Redis),以加快查询速度,并为索引释放数据库缓存。

监控平均Postgres查询性能

对于这部分,我们需要启用pg_stat_statements扩展。它是一个内置的扩展/贡献模块,在一些云提供商中,它默认是启用的。如果没有,可以使用shared_preload_libraries轻松启用。此扩展帮助我们捕获大量有关查询性能的信息。使用此扩展,我们将构建一个查询,该查询根据查询已扫描的行数输出查询性能:

SELECT
  mean_time as "timing in ms",
  rows
FROM
  pg_stat_statements
ORDER BY
  mean_time DESC
LIMIT
  100;

进入全屏模式退出全屏模式

限制行并执行order by为我们提供了前 100 个最常用的查询,而rows列表示_检索或受语句影响的总行数_。我们还可以使用上表通过运行时间获得前 10 个最差查询。这可以用作慢速日志分析的替代方法,因为它更实时。

使用 PostgreSQL 指标构建仪表板

如果我们结合上述图表类型中的所有查询,它应该看起来像一个可以轻松可视化/使用的适当分析仪表板:

[ PG Health Dashboard Preview ](https://res.cloudinary.com/practicaldev/image/fetch/s--d8AMZxNa--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype. com/blog/content/images/2021/05/Arctype_Dashboard_7.png)

每个组件的映射如下表所示。 Arctype 不仅限于下面的图表,它还有一个功能极其丰富的平台,我们可以在上面展示我们的可视化。

模块

类型

打开连接

饼形图

缓存命中率

普通表

未使用的索引

条形图

数据库大小

条形图

共享缓冲区

条形图

查询响应时间

散点图

此处的每个小部件都有自己的描述,并且图表包含有关 X 轴和 Y 轴中存在的内容的详细信息。重要的是仪表板是不言自明的,毕竟一张图片胜过一千个字。根据基础数据,这些图表可能看起来不同。它们可以通过在编辑模式下简单地拖动它们的边框来扩展和缩小,如果数据集较大,这将非常有用——图表可能会溢出。

上面的仪表板是在我之前使用的示例数据库中创建的。让我们在更真实的数据库上创建仪表板,例如 Arctype 内置的Covid DB(在注册流程中可用)。由于数据集很大,我将仪表板分为两部分:

[ PG Health Dashboard Preview 1 ](https://res.cloudinary.com/practicaldev/image/fetch/s--UqtMYBl7--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype .com/blog/content/images/2021/05/Arctype_Dashboard_14.png)

[ PG Health Dashboard Preview 2 ](https://res.cloudinary.com/practicaldev/image/fetch/s--oO3Q2NVM--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype .com/blog/content/images/2021/05/Arctype_Dashboard_15.png)

随着我们有更多的数据点,仪表板看起来更加丰富和有意义。目标不是成为“灵丹妙药”的解决方案,而是提供一些关于操作数据库的潜在关注领域的观点。有很多工具可以提供不同的功能,但归根结底,我相信数据库开发人员应该更深入地了解他们的数据库。有什么比一个工具可以让我们轻松地将查询转换为非常漂亮的图表和图形而几乎没有学习开销更好呢?答:应该不多。

来自健康仪表板的见解和情报

该仪表板使我们能够实时监控数据库,并帮助我们更全面地了解 PostgreSQL 独有的一些特性。在进行数据库优化时,始终有一条规则是“你无法改进你无法衡量的东西”,而这个仪表板解决了第一步(但经常被忽视),这实际上是衡量事物。Covid DB演示数据库有 99% 的缓存命中率,这意味着几乎所有的流量都是从缓存(shared_buffers)中提供的,并且会非常快速地响应。这是一个良好优化的数据库的一个好兆头。

在交通高峰期监控我们的数据库将使我们深入了解哪些领域可以改进以及如何衡量这种改进。我们可以根据这些指标提高数据库性能的一些方法是:

使用客户端/服务器端连接池。
代码优化以在工作完成后将连接释放回池/关闭连接。
进行适当的数据库建模并在不同集群之间拆分流量。
增加共享缓冲区的大小以在内存中容纳更多数据/索引。
通过添加索引或完全重写它们来优化编写不佳的查询。
删除未使用的索引以加快写入速度。

到目前为止,我们所看到的只是冰山一角。数据库维护和改进本身就是一门艺术,需要几年的实践经验。但是,使用友好、具有成本效益并且可以帮助我们轻松实现目标的工具链始终很重要。

使仪表板保持最新

在构建它之后,保持仪表板是最新的很重要。这可以根据我们的需要使用各种方式来完成。

手动刷新 Arctype Dashboards

[ 刷新整个仪表板 ](https://res.cloudinary.com/practicaldev/image/fetch/s--c6cvoiy1--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype.com /blog/content/images/2021/05/Arctype_Dashboard_8-1.png)

在编辑模式按钮旁边的右上角,有一个重新加载按钮,我们可以单击它来重新加载整个仪表板。

自动刷新整个仪表盘

我们还可以将整个仪表板配置为定期刷新。

刷新率

自动刷新单个组件

通过单击单个组件并单击Advanced,我们可以将相关组件配置为以指定的自动间隔刷新。

[ 刷新单个组件 ](https://res.cloudinary.com/practicaldev/image/fetch/s--1iPSc3lW--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype.com /blog/content/images/2021/05/Arctype_Dashboard_10.png)

我们可以选择我们想要的刷新类型,但请记住,监控数据库本身的查询可能是资源密集型的,因此会降低性能。

共享仪表板(和结束思想)

许多数据库工具中缺少的另一个非常重要的功能,但 Arctype 中存在的一个功能是共享仪表板的能力。

通过单击仪表板查看器右上角屏幕上的Share按钮,我们可以共享各种选项。

通过电子邮件共享 Arctype 工作区

[ 通过电子邮件共享仪表板 ](https://res.cloudinary.com/practicaldev/image/fetch/s--NMak3N3K--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype. com/blog/content/images/2021/05/Arctype_Dashboard_11.png)

这将邀请用户成为工作区的一部分。有关更高级的控件,请查看团队功能。

链接到您的工作区

[ 仪表板通过链接共享 ](https://res.cloudinary.com/practicaldev/image/fetch/s--Rpr-Yk2---/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https:/ /arctype.com/blog/content/images/2021/05/Arctype_Dashboard_12.png)

这与上面的相同,只是知道链接的任何人都可以加入工作区。

创建公共只读链接

[ 仪表板通过公共链接共享 ](https://res.cloudinary.com/practicaldev/image/fetch/s--m2264a4o--/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880/https://arctype .com/blog/content/images/2021/05/Arctype_Dashboard_13.png)

这将发送一个只读链接,该链接也可以在浏览器中查看。这对于与团队中的不同(可能是非技术)利益相关者共享仪表板非常方便。

注意:您不能共享本地托管的数据库(或任何附属仪表板)

结论

这些仪表板在以下情况下非常有用:

运行性能测试。
在指定时间段内监控实时数据库。
常规数据库监控。

Arctype 提供的细粒度控制使这个仪表板可以轻松地在同事、团队甚至一般互联网上共享。如果您还没有,请继续下载 Arctype,并开始创建这些仪表板——了解数据库运行状况并对其进行监控不应再仅限于某些工具或人员!

PostgreSQL

PostgreSQL社区为您提供最前沿的新闻资讯和知识内容

更多推荐

PostgreSQL 计数查询效率,物化视图 [重复]

问题:PostgreSQL 计数查询效率,物化视图 [重复] 可能重复: PostgreSQL 计数查询优化使用 PostgreSQL 9.2,我们试图弄清楚是否有一种方法可以跟踪查询的结果数量,并以有效的方式返回该数字。这个查询应该每秒执行几次(可能几十到几百甚至几千次)。我们现在的查询看起来像这样,但我们想知道这是否效率低下: -- Get # of rows that do not hav

PostgreSQL

多对多中的唯一性

问题:多对多中的唯一性我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。我相信我有一个典型的多对多关系: CREATE TABLE groups ( id integer PRIMARY KEY); CREATE TABLE elements ( id integer PRIMARY KEY); CREATE TABLE groups_elements

PostgreSQL

Django 与 postgresql - manage.py syncdb 返回错误

问题:Django 与 postgresql - manage.py syncdb 返回错误我从 Django 开始。我设置了一些使用 SQLite 工作的站点,但是在将 DB 引擎更改为 postgresql manage.py syncdb 后返回错误。我已经用谷歌搜索了 2 天,但对我仍然没有任何作用。Postgres 用户 'joe' 具有超级用户权限和本地 'joe ' 数据库存在。