PostgreSQL troubleshooting系列之九-处理troubleshooting的标准方法

  • 数据库杂记

    2023-12-16 05:58:00
  • 数据库开发技术
  • 原创

PostgreSQL troubleshooting系列之九-处理troubleshooting的标准方法

前言

本文的内容来源于电子书《Troubleshooting PostgreSQL》。那本书虽老，但是里边的内容依然很有参考价值。俺这里尝试对其简译，对某些地方进行修正和补充，以符合近年来新版本的要求。希望对学习和深入了解使用PG有帮助。

本文来自原书第10章：处理troubleshooting的标准方法。这也是这本书的最后一章，至此全部结束。Enjoy it!!!

在本章中，我们的目标是总结你在本书中看到的一些方法，并将它们结合成一个适用于典型日常场景的总体方法。客户可能会提出以下任何一种或全部说法:

  • 我的数据库好慢啊

  • CPU使用率和I/O正在飙升

  • 它从来没有这么慢过

  • 有时候，复制失败

  • 我正在添加硬件

这里的目标是为您提供解决问题的系统方法并指出解决方案。请注意，本章中概述的示例是全球客户面临的典型案例。

10、处理troubleshooting的标准方法

10.1、获得问题的概况

在开始之前，最重要的是对问题有一个概述。假设周围没有损坏，首先要做的是详细查看pg_stat_activity的内容。正如本书中已经概述的那样，pg_stat_ activity包含所有打开的数据库连接的列表。这里有几件事需要注意:

•打开的数据库连接的数量是否合理?
•是否有很多空闲事务?  是否有某个查询一次又一次地出现?
•是否存在明显运行时间过长的查询?

许多中间件组件打开了大量的数据库连接。请记住，每个无用的连接都会占用一些内存，这些内存本来可以用于更高效的事情，比如缓存。然而，数据库连接的数量本身通常不会带来巨大的开销。活动快照的数量更危险，因为活动快照直接转化为速度的降低。活动快照会导致一些内部开销。在http://www.cybertec.at/max_connections-的性能影响/的基准将证明这一点。

一旦检查了连接数量，就有必要查看其中有多少是空闲的。闲置了很长一段时间的开放事务可能是一种危险。为什么?VACUUM命令只能在没有事务能够看到数据的情况下清理死行。旧的、空闲的事务可能会延迟行清理，并可能导致不良的性能。即使定期执行VACUUM命令，事情也可能保持缓慢，因为VACUUM根本无法完成它的工作。因此，密切关注不合理的长事务是有意义的。

但还有更多的情况是：如果相同的查询一次又一次地出现在pg_stat_ activity中，那么就应该检查一下这个查询并确保它执行良好。这同样适用于已经显示出高执行时间并显示在清单中的查询。

10.2、攻破低性能

在进行了这些基本检查之后，是时候消除一个非常常见的问题了:错误和缺失的索引。

10.2.1、审阅一下索引

在这一点上，你们大多数人可能会说，“索引?来吧! 重点是什么?我们知道;让我们来做真正的东西吧!” 根据经验，我可以绝对肯定地告诉您，索引损坏是世界上导致性能差的最常见原因。因此，在关注系统中的其他内容之前，执行安全检查以确保索引绝对正确是有意义的。

检查缺失索引的最佳方法是什么?在我作为顾问的日常生活中，我使用这个查询:

test=# SELECT relname, seq_scan, seq_tup_read,
         idx_scan AS idx,
         seq_tup_read / seq_scan AS ratio
      FROM  pg_stat_user_tables
    WHERE seq_scan > 0
   ORDER BY seq_tup_read DESC
   LIMIT 10;
 relname | seq_scan |   seq_tup_read | idx | ratio
---------+----------+----------------+-----+----------
 t_user  |  4564324 | 18563909843245 |   2 |  4067176
...
(10 rows)

查询的输出显示以下字段:表的名称、在表上发生的顺序扫描次数、在这些顺序扫描中找到的行数、使用的索引扫描次数以及在顺序扫描期间读取的平均行数。
考虑到这里的列表，下一个问题是，为什么有人想要阅读400万行，450万次? 没有任何相同的情况证明如此大的表应该被读取数百万次。想象一下，如果一个包含400万行的表被反复读取，这对系统的总体负载意味着什么。这就像阅读整个电话簿来找到一个号码。
在大多数情况下，在查看表结构时，缺失的索引已经很明显了。在本例中，用户表可能用于身份验证，但是人们登录时使用的电子邮件地址可能没有被索引。

请记住，顺序扫描并不总是坏的，它们永远无法完全避免。重要的是要避免大量昂贵且无意义的顺序扫描。

经验表明，在大多数表现不佳的系统中，索引缺失。很有可能对你的索引策略做一个简单的审核就能解决你的大部分问题。

另一方面，过多的索引也可能导致性能问题。指数并不总是解决问题的办法——它也可能造成伤害。这样的性能问题不像缺少索引那样明显。除此之外，使用太多索引通常比缺少索引更容易被容忍(当然，这取决于应用程序的类型)。

10.2.2、解决Update命令问题

一旦固定了索引，就可以攻击第二个非常常见的问题。许多人忘记了一个叫做FILLFACTOR的东西。在更新密集型工作负载的情况下，缺少FILLFACTOR设置可能会导致性能问题。
如何发现这些问题?这里是一个查询显示它是如何工作的:

SELECT schemaname, relname, n_tup_ins,
   n_tup_upd, n_tup_hot_upd, n_tup_del
FROM   pg_stat_user_tables
ORDER BY n_tup_upd DESC;

这个查询返回那些面对系统中大多数UPDATE命令的表。现在重要的比率是n_tup_upd与n_tup_hot_upd的比率。假设一行被更新了。如果包含该行的页面已满，则必须将该行复制到其他块中。额外的、昂贵的I/O是必然的结果。如果选择了适当的FILLFACTOR设置(例如，FILLFACTOR = 70表示70%)，则将执行所谓的HOT-UPDATE命令。简而言之，该行的副本可以与原始行放在同一个块中。HOT-UPDATE命令比传统的、普通的UPDATE命令便宜得多。因此，面对许多UPDATE命令的表需要一个相当低的FILLFACTOR选项来加快速度并减少I/O。
本节中显示的查询有助于识别那些需要特别注意的表。从上到下遍历列表并在索引和表上设置FILLFACTOR是有意义的。

请记住，完美的FILLFACTOR选项永远无法计算。然而，你可以做一个有根据的猜测，并提出一个相当好的设置。如果FILLFACTOR必须降低，75%可能是一个好的开始。

要将FILLFACTOR更改为70%，可以使用以下命令:

ALTER TABLE foo SET (FILLFACTOR=70);

如果你想改变索引的FILLFACTOR，使用这个:

ALTER INDEX foo_index SET (FILLFACTOR=70);

请记住，这个设置不会对表的物理布局产生直接影响;这仅仅是一个暗示。然而，随着时间的推移，系统将尝试达到所需的状态。

一旦定义了索引和FILLFACTORs，大多数问题都应该得到解决。

10.2.3、检查慢查询

接下来，可以执行一个重要的步骤——查找慢速查询。有些人更喜欢先检查慢速查询。然而，我个人更喜欢做一些基本的检查，比如这里所示的检查，因为这些检查不需要重新启动。激活pg_stat_statements需要重新启动，因此，在选择容易实现的目标后激活它是有意义的。
当在postgresql.conf中激活pg_stat_statements时，如本书前面所述，将track_io_timing设置为true非常有意义。启用track_io_timing将为用户提供有关哪个查询执行了哪种I/O的详细信息。

要弄清楚系统中正在发生什么，下面的查询可能很有用。
查找最耗时的查询，如下所示:

SELECT (SELECT datname
    FROM   pg_database
    WHERE   dbid = oid),
   query, calls, total_time
FROM   pg_stat_statements AS x
ORDER BY total_time DESC;

最耗时的查询将显示在列表的顶部，并且可以一次修复一个。但是，在某些情况下，总执行时间不是您的问题。如果I/O是主要的限制因素呢?在这种情况下，需要特别注意以下字段:

temp_blks_read  | bigint           |
temp_blks_written  | bigint           |
blk_read_time      | double precision |
blk_write_time  | double precision |

如果work_mem设置过低，可能会发生将许多临时文件写入磁盘的情况，从而导致昂贵的磁盘写入，这很容易避免。这些temp_*可以很好地指示导致磁盘等待的查询。

blk_*设置可以让您了解查询需要多少I/O以及I/O花费了多少时间。

处理最昂贵的查询对于跟踪系统上遗留的最后瓶颈至关重要。如果以同样的方式配置postgresql.conf，那么按照本章概述的基本步骤进行配置将会消除大部分最常见的性能问题。

剩下的问题通常需要更详细的查看，可能还需要在应用程序内部进行更改。到目前为止所涵盖的所有内容都可以使用PostgreSQL的板载方式进行修复。

10.3、解决常见的复制问题

鉴于本章设定的目标，最后要讨论的主题是客户对复制的抱怨，这种抱怨有时会中断。在PostgreSQL中，复制永远不会因为技术原因而中断; 只有配置错误才会导致意外行为。以下是最常见的问题:
•复制停止并落后
•由于某些原因，从服务器上的查询失败
让我们一次解决一个问题。

10.3.1、解决复制终止问题

最常见的问题之一是，在某些时候，复制就停止了。备库节点将开始落后，从备库返回的结果将是旧的和过时的。这种行为有几个原因:
•带宽持续不足
•复制冲突
在许多情况下，带宽是一个严重的问题。如果两台服务器使用100 MB的互连连接，如果写负载持续高于每秒10 MB，那么从服务器很容易落后并最终死亡。请记住，主服务器只保留几个xlog文件，这是恢复时以防主机宕机所需要的。在默认配置中，它不会为了处理备库上的问题而永远对事务日志进行排队。如果在系统基础上的负荷过高，这无论如何也无济于事。

在PostgreSQL中，从服务器向主服务器请求它需要的事务日志。如果主服务器不再拥有所需的xlog文件，则从服务器无法继续运行。对于许多用户来说，这看起来像是复制失败，但实际上并非如此。这显然是人们想要的行为。为了直接减少这个问题，有两种选择:

  • Set wal_keep_segments  （现在叫wal_keep_size ? )

  • 使用复制槽(PostgreSQL 9.4中引入的一个崩溃安全特性) 可用于避免过早删除xlog)

通常，除非使用了复制槽，否则总是建议使用wal_keep_segments。否则，这样做风险太大，从服务器很可能远远落后于主服务器。在配置wal_keep_segments时，请注意不要使用过低的设置。一个良好的xlog留存池会带来真正的回报。

请记住，如果您经常使用比可用带宽更多的带宽，wal_keep_segments将不起作用。

复制槽是wal_keep_segments的一个很好的替代方案。在使用wal_keep_segments进行正常设置的情况下，主机将保留管理员配置的xlog。但是管理员是怎么知道的呢?通常，没有办法知道。

复制槽可以提供帮助，因为它可以在需要时保留xlog。下面是它的工作原理:

postgres=# SELECT * FROM   pg_create_physical_replication_slot('rep_
   slot');
     slot_name  | xlog_position
   -------------+---------------
rep_slot |

查看已存在的复制槽位非常简单:

postgres=# SELECT slot_name, slot_type FROM pg_replication_slots;
     slot_name  | slot_type
-------------+-----------
 rep_slot    | physical
(1 row)

在副本中，必须将复制槽位的名称添加到recover .conf （现在，应该是放到postgresql.auto.conf)中:

primary_slot_name = 'rep_slot'

记住，如果备库倒下了，主库可能会填满。因此，必须时刻监视这种设置。

10.3.2、解决失败的查询问题

另一个重要问题与查询失败有关。由于复制冲突，从服务器上的查询可能会被取消。如果你在一个查询频繁的系统上工作，检查hot_standby_feedback会很有帮助。

其思想是让从服务器不时报告其最旧的事务ID。然后，主服务器可以做出反应，并通过延迟VACUUM来防止它本身的冲突。实际上，在从服务器上设置hot_standby_feedback会给我们留下与在主服务器上实际运行查询相同的行为(当然不是性能方面的)。
如果在这些从属系统上工作的人是可信的，那么打开hot_standby_feedback通常是有意义的。如果从属服务器上的用户被认为没有足够的能力，那么坚持使用默认值off会更安全。

【补充：】hot_standby_feedback：

Allows feedback from a hot standby to the primary that will avoid query conflicts

指定一个热后备机是否将会向主服务器或上游后备机发送有关于后备机上当前正被执行的查询的反馈。这个参数可以被用来排除由于记录清除导致的查询取消，但是可能导致在主服务器上用于某些负载的数据库膨胀。反馈消息的发送频度不会高于每个wal_receiver_status_interval周期发送一次。默认值是off。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

如果使用级联复制，反馈将被向上游传递直到它最后到达主服务器。后备机在接收到反馈之后除了传递给上游不会做任何其他操作。

这个设置不会覆盖主服务器上的old_snapshot_threshold的行为，后备服务器上一个超过了主服务器年龄阈值的快照可能会变得不可用，导致后备服务器上事务的取消。这是因为old_snapshot_threshold是为了对死亡行能够存在的时间给出一个绝对限制，不然就会因为一个后备服务器的配置而被违背。

小结

在本章中，对一个典型的客户需求进行了概述和一步一步的讨论。目标是系统地概述处理面临典型日常问题的现有系统的步骤。

请记住，我的个人策略并不适用于所有目的，但它有助于解决世界上任何一个典型的问题系统。

参考：

《Troubleshooting PostgreSQL》第10章

写在结束处

至此，整本书匆匆整理完毕。共10章，除第一章用处不大节略掉以外。全部按顺序整理完毕。希望对该大PGer（DBA甚至应用开发人员）有一定帮助。版本虽老，内容大部分还是有参考意义的。

当然在真实的生产环境当中，遇到的需要troubleshooting的问题远不止这些。多总结总是没有坏处的。



请使用浏览器的分享功能分享到微信等