☰

我如何检查我的数据库是否需要更多 RAM？

tag-icon • tag-icon database performance postgresql memory
我如何检查我的数据库是否需要更多 RAM？

如何检查您的 postgresql DB 实例是否需要更多 RAM 内存来处理其当前工作数据？

答案1

如果您使用的是 Linux，则总物理 RAM 应大于磁盘上的数据库大小，以尽量减少 I/O。最终，整个数据库将位于 OS 读取缓存中，并且 I/O 将仅限于将更改提交到磁盘。我更喜欢通过运行“du -shc $PGDATA/base”来查找数据库大小 - 该方法将所有数据库聚合为一个数字。只要大于这个数字，就没问题。

此外，您可以查看堆和索引块提取的缓存命中率。这些指标衡量了 PostgreSQL 共享缓冲区的命中率。这些数字可能有点误导——即使它可能在共享缓冲区缓存中未命中，但仍可能是 OS 读取缓存中的命中。不过，共享缓冲区中的命中仍然比 OS 读取缓存中的命中成本低（反过来，这比必须返回磁盘的成本低几个数量级）。

为了查看共享缓冲区命中率，我使用以下查询：

SELECT relname, heap_blks_read, heap_blks_hit,
    round(heap_blks_hit::numeric/(heap_blks_hit + heap_blks_read),3)
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY 4
LIMIT 25;

这将为您提供所有表中缓冲区缓存丢失的最严重前 25 个违规者，其中至少有一个块必须从“磁盘”中获取（再次说明，这可能是 OS 读取缓存或实际磁盘 I/O）。您可以增加 WHERE 子句中的值或为 heap_blks_hit 添加另一个条件以筛选出很少使用的表。

可以使用相同的基本查询来检查每个表的总索引命中率，方法是将字符串“heap”全局替换为“idx”。查看 pg_statio_user_indexes 以获取每个索引的细分。

关于共享缓冲区的简要说明：在 Linux 中，一个好的经验法则是设置配置参数共享缓冲区到 RAM 的 1/4，但不超过 8GB。这不是硬性规定，而是调整服务器的一个很好的起点。如果您的数据库只有 4GB，而您的服务器有 32GB，那么 8GB 的​​共享缓冲区实际上就有点过头了，您应该能够将其设置为 5 或 6 GB，并且仍然有未来增长的空间。

答案2

我编写了这个 SQL 来显示表与磁盘命中率：

-- perform a "select pg_stat_reset();" when you want to reset counter statistics
with 
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name, 
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
),
tables as 
(
SELECT  *
FROM    (
    SELECT  relname as table_name, 
        ( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        ( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a
WHERE   (from_disk + from_cache) > 0 -- discard tables without hits
)
SELECT  table_name as "table name",
    from_disk as "disk hits",
    round((from_disk::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% disk hits",
    round((from_cache::numeric / (from_disk + from_cache)::numeric)*100.0,2) as "% cache hits",
    (from_disk + from_cache) as "total hits"
FROM    (SELECT * FROM all_tables UNION ALL SELECT * FROM tables) a
ORDER   BY (case when table_name = 'all' then 0 else 1 end), from_disk desc

在此处输入图片描述

答案3

它也可以工作，正如 Heroku 文档中所说：

SELECT
    'cache hit rate' AS name,
     sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

相关内容