PostgreSQL 健康检查

很多人建立了数据库，开始使用它，然后对突然出现的问题感到惊讶。当数据发生变化时，查询和更新的方式也会发生变化。要确保你的数据库是正常的，并且让它在最大极限上运行，是不需要经常进行大规模检查的。实际上，你可以把它当做个人健康来对待。定期检查并做出微小重要的调整，而不必做出重大的改变，让数据库健康运行。

经过多年运行和管理数以百万计的Postgres数据库，在这里我对常规的Postgres健康检查做细致分析。可以考虑每月运行一次，这样就可以进行一些小的调整，避免太多的更改。 

缓存支配着一切

对于许多应用程序来说，并不是所有的数据都会随时访问的。相反，某些数据集被访问一次，然后在一段时间内，你访问的数据发生变化。Postgres实际上非常擅长将频繁访问的数据保存在内存中。

缓存命中率告诉你数据从内存中读取的频率，而不是从磁盘读取的频率。从内存中提供服务与从磁盘上提供服务的速度将快上几个数量级，因此你在内存中保存的越多越好。当然，你可以为实例提供与数据一样多的内存，但是不必这样做。相反，监视缓存命中率并确保它在99%，这是一个很好的性能指标。

你可以监测缓存命中率：

SELECT  sum(heap_blks_read) as heap_read,  sum(heap_blks_hit)  as heap_hit,  sum(heap_blks_hit)  (sum(heap_blks_hit) + sum(heap_blks_read)) as ratioFROM  pg_statio_user_tables;
复制

注意死元组

Postgres本质上是一个巨大的附加日志。当你写入数据时，它会附加到日志中，当你更新数据时，它会将旧数据标记为无效，当你删除数据时，它会将数据标记为无效。稍后Postgres会清空这些死数据(也称为死元组)。

所有这些的没有清理的死元组都称为膨胀。膨胀会降低其写操作的速度并产生其他问题。注意你的膨胀，当失控时调优数据库的vacuum是关键。

WITH constants AS (  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 4 AS ma), bloat_info AS (  SELECT    ma,bs,schemaname,tablename,    (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,    (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2  FROM (    SELECT      schemaname, tablename, hdr, ma, bs,      SUM((1-null_frac)*avg_width) AS datawidth,      MAX(null_frac) AS maxfracsum,      hdr+(        SELECT 1+count(*)/8        FROM pg_stats s2        WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename      ) AS nullhdr    FROM pg_stats s, constants    GROUP BY 1,2,3,4,5  ) AS foo), table_bloat AS (  SELECT    schemaname, tablename, cc.relpages, bs,    CEIL((cc.reltuples*((datahdr+ma-      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta  FROM bloat_info  JOIN pg_class cc ON cc.relname = bloat_info.tablename  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'), index_bloat AS (  SELECT    schemaname, tablename, bs,    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols  FROM bloat_info  JOIN pg_class cc ON cc.relname = bloat_info.tablename  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = bloat_info.schemaname AND nn.nspname <> 'information_schema'  JOIN pg_index i ON indrelid = cc.oid  JOIN pg_class c2 ON c2.oid = i.indexrelid)SELECT  type, schemaname, object_name, bloat, pg_size_pretty(raw_waste) as wasteFROM(SELECT  'table' as type,  schemaname,  tablename as object_name,  ROUND(CASE WHEN otta=0 THEN 0.0 ELSE table_bloat.relpages/otta::numeric END,1) AS bloat,  CASE WHEN relpages < otta THEN '0' ELSE (bs*(table_bloat.relpages-otta)::bigint)::bigint END AS raw_wasteFROM  table_bloat    UNIONSELECT  'index' as type,  schemaname,  tablename || '::' || iname as object_name,  ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS bloat,  CASE WHEN ipages < iotta THEN '0' ELSE (bs*(ipages-iotta))::bigint END AS raw_wasteFROM  index_bloat) bloat_summaryORDER BY raw_waste DESC, bloat DESC;
复制

查询由Heroku的pg-extras提供

过度优化的情况

我们总是希望数据库是高性能的，因此为了做到这一点，我们将数据保存在内存/缓存中(请参阅前面)，并对数据进行索引，这样就不必扫描磁盘上的所有数据。但是，数据库的索引需要进行权衡。系统维护的每个索引都将降低数据库上的写吞吐量。当你确实要加快查询速度时，使用它们是很好的方法。如果你多年前添加了索引，但是应用程序中已经发生了更改不再需要它时，最好删除索引。

Postgres使查询变得简单，所以你可以通过删除不用的索引获得一些性能：

SELECT  schemaname || '.' || relname AS table,  indexrelname AS index,  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,  idx_scan as index_scansFROM pg_stat_user_indexes uiJOIN pg_index i ON ui.indexrelid = i.indexrelidWHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192ORDER BY pg_relation_size(i.indexrelid)  nullif(idx_scan, 0) DESC NULLS FIRST,pg_relation_size(i.indexrelid) DESC;
复制

检查查询性能

在前面的一篇博客文章中，我们讨论了pg_stat_statements在监视数据库查询性能方面是有用的。它记录了很多有价值的数据，关于哪些查询正在运行，它们返回的速度，它们运行了多少次，等等。定期查看这些可以告诉你在哪里添加索引或优化应用程序最好，这样你的查询调用就不会过多。感谢HN在我们之前的帖子上的评论，我们有一个很好的查询，很容易根据所有这些数据来显示不同的视图：

SELECT query,       calls,       total_time,       total_time  calls as time_per,       stddev_time,       rows,       rows  calls as rows_per,       100.0 * shared_blks_hit  nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM pg_stat_statementsWHERE query not similar to '%pg_%'and calls > 500
--ORDER BY calls
--ORDER BY total_time
order by time_per
--ORDER BY rows_per
DESC LIMIT 20;
复制

从长远来看，定期对数据库进行健康检查可以节省大量时间。它允许你逐步地维护和改进，而不需要大量的重写。我个人建议每个月或每两个月检查一次以上所有内容，以确保一切处于良好状态。

本文翻译自：https://www.citusdata.com/blog/2019/03/29/health-checks-for-your-postgres-database/