题 如何检查我的数据库是否需要更多内存?


你如何检查你的postgresql数据库实例是否需要更多的RAM内存来处理他们当前的工作数据?


9
2018-06-04 19:36




无需检查,您总是需要更多RAM。 :) - Alex Howansky
不是编程问题,我投票将其移至ServerFault。 - GManNickG
我不是DBA,但我首先看到任何常见的查询都是在哈希加入的边缘而不是合并嵌套的循环。您可以执行一些db配置调优,这可能会影响任何特定查询可用的内存量[检查文档或通过电子邮件发送邮件列表是我的建议]。查看是否有足够的RAM来保持常用的表缓存也很有用。但最终,除非您的整个数据库适合RAM,否则您可以使用更多。 :)


答案:


如果您使用的是Linux,那么您的总物理RAM应该大于磁盘上的数据库大小,以便最大限度地减少I / O.最终,整个数据库将位于OS读取缓存中,I / O将限于将更改提交到磁盘。我更喜欢通过运行“du -shc $ PGDATA / base”找到数据库大小 - 该方法将所有数据库聚合为一个数字。只要你比那个大,它应该没问题。

此外,您可以查看堆和索引块提取的缓存命中率。这些测量了PostgreSQL共享缓冲区的命中率。这些数字可能有点误导 - 即使它可能是共享缓冲区缓存中的错过,它仍然可能是操作系统读取缓存中的命中。尽管如此,共享缓冲区中的命中数仍然比操作系统读取缓存中的命中数要便宜(反过来,它们比不必返回磁盘要便宜几个数量级)。

为了查看共享缓冲区命中率,我使用此查询:

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中的一个好的经验法则是设置配置参数 的shared_buffers 到1/4的RAM,但不超过8GB。这不是一个严格的规则,而是调整服务器的良好起点。如果您的数据库只有4GB而且您有32GB的服务器,那么8GB的共享缓冲区实际上是过度的,您应该可以将其设置为5或6 GB,并且仍有未来增长的空间。


12
2018-06-05 15:07





我做了这个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

enter image description here


7
2017-08-19 15:45





它也有效,正如Heroku doc中所述:

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;

1
2017-09-04 23:20