清理您的Postgres数据库

上周我正在呼吁有人概述嘎吱嘎吱的桥梁，我们的多云完全托管数据库作为服务。在呼吁期间，他们询问了解他们的数据库如何做的最佳方式是什么，如果您愿意，健康检查。我提到了一些监测工具，如Pgganalyze和PGMonitor，这是良好的选择，但我们也可以在生活中挖掘，看看事情是如何。

我正在谈论的人与我们的许多客户相似，我们为应用程序开发人员运行数据库，而没有全职DBA员工。他们需要一点额外的指导和支持，他们正在超越现有的托管提供商，所以我们挖掘了一些在他们的数据库中进行了一些春季清洁和评估。

虽然我一般很高兴与有兴趣谈论谈话有兴趣的人花几分钟，但我也可以和你一起挖掘你的数据库，但我也是自助服务的粉丝。以下是我们查看的每个碎片（几分钟之内），帮助提供一些可操作的步骤如何提高数据库性能。

您的第一件事＆＃39;重新想要查看的是您的缓存命中率和索引命中率。您的缓存命中率将提供您在内存与内存中服务的时间百分比。必须转到磁盘。一般从内存与磁盘服务的数据进入幅度的顺序更快，因此您可以从内存中服务得更好。对于典型的Web应用程序，制作很多短请求i＆＃39; m将要瞄准＆gt;这里有99％。

SQL选择＆＃39;索引命中率＆＃39;作为名称，（SUM（idx_blks_hit））/ nullif（sum（idx_blks_hit + idx_blks_read），0）为pg_statio_user_indexes联合的比例全部选择＆＃39;表命中率＆＃39;作为名称，sum（heap_blks_hit）/ nullif（sum（eacp_blks_hit）+ sum（eacp_blks_read），0）为pg_statio_user_tables的比率;

在他们的情况下，缓存命中比率看起来很好，健康。如果它有类似的80％或90％，那么解决方案很简单：通过升级到下一步，通过升级来获取更多内存。

从这里我们继续看看索引，他们经常使用频率。查询数据库时，默认行为将是：扫描整个表以查找匹配的记录。当您的表是10条记录时，这会很好，但对于扫描数百万记录的任何相同的应用程序根本不可行。你很可能是＆＃39; ve添加了一些索引，因为你已经走了一些索引。在下面查询您＆＃39; ll查看您在查询每个相应表时使用索引的频率：

SQL选择Relname，案例IDX_SCAN 0然后＆＃39;数据＆＃39不足;否则（100 * idx_scan /（seq_scan + idx_scan））:: text exp percent_of_times_Under，n_live_tup rows_in_table从pg_stat_user_tables顺序由n_live_tup desc排序; Relname | percent_of_times_index_used | Rows_in_table ------------------------------------------ ---- + --------------帐户| 11 | 5409部署| 69 | 58276 App | 93 | 5345团队| 98 | 1801防火墙_rule | 9 | 984 ......

在这里，我有几件事是值得进一步的东西来增加一些索引。第一个是帐户表。虽然帐户表只有5,409条记录，可能会继续生长，并且显然缺少一些所需的索引，因为只有11％的时间在查询该表时使用索引。其他兴趣表是部署表。我们可以看到部署表是最大的。 Deploy表具有一些索引，因为它们是69％的时间，但表中的一些工作流程是查询的，而不是有帮助的索引。

一旦我们看到他们的索引状况良好，我们就会转向我们可以清理的东西。随着应用程序的增长和成熟，您可能会在现在和不必要的税收上进行一些事情。这里要查找的是您未使用的索引 - 这些是数据库中存在的索引，但您的查询不利用它们。这意味着你＆＃39;重新获得较慢的吞吐量，没有索引现有的索引。

SQL选择Schemaname || ＆＃39;＆＃39; || Relname作为表，indexrelname作为index，pg_size_pretty（pg_relation_size（i.indexrelid））作为index_size，idx_scan为来自pg_stat_user_indexes ui的index_scans加入pg_index i上的ui.indexrelid = i.indexrelid，其中not IndisUnique和Idx_scan＆lt; 50和pg_relation_size（Relid）＆gt;通过pg_relation_size（i.indexrelid）/ nullif（idx_scan，0）desc null，pg_relation_size（i.indexrelid）desc; 5 * 8192

虽然您要删除这些索引，但谨慎地删除这些索引，您通常会非常安全地删除没有使用的额外索引，并且只是放慢吞吐量。

当您开始时，一个非常常见的习惯是将所有内容放在您的数据库中。这不是坏事，postgres是相当多样的，可以处理很多你可以抛弃的东西。但是，一些非常特定的表通常可以在其他数据存储中有意义，如果您有一个名为消息，日志，日志，事件数据库的表，则会有很好的机会，它会消耗大部分存储，是您最大的桌子，并且可以受益从Main Postgres数据库之外移动。

我已经说过多年PG_Stat_Statements是Postgres最有用的扩展之一。它记录了对数据库运行的查询以及关于它们的许多统计数据。每月一次左右的地方是一个很好的地方，并查找Qualies的速度最慢，以及消耗最多时间对系统的疑虑。

我们将来更多地挖掘pg_stat_statements，但现在如果您想要快速外观，则此查询可以为您提供预览：

选择（total_time / 1000/60）作为total_minutes，（total_time / calual）作为sultial_time，从pg_stat_statements outder qual qual desc limit 50查询。

您无需了解MVCC如何下潜的Postgres，或者如何在故障转移期间确定它是安全的故障转移才能进行良好调整的Postgres数据库。对于您的数据库可能发生的大部分调整和改进不是事务日志和Linux巨大页面调整的结果，而是可以识别n + 1查询，或者只是注意浪费像“日志”的浪费空间不提供价值。走过上面，没有先进的Postgres知识应该给你一个良好的起点来清理。

如果您在Postgres旅程中发现自己想要额外的支持和指导，我们总是很乐意在这里聊天@CrunchyData。