Postgresql缓存（内存）性能+如何预热缓存

Question

我有一个包含2500万行的数据库表，每个行大约3K（即约75GB），加上我使用的多个索引（额外的15-20GB），无法完全放入内存中（机器只有64GB）。典型的查询通过索引定位300行，可选地使用其他索引将它们过滤到50-300行，最后获取匹配的行。响应时间在热数据库上为20毫秒，在冷数据库上为20秒。我有两个相关的问题：

1. 在任何给定的时间，如何检查特定表和索引的缓存部分（%）在内存中？

2. 在打开数据库进行查询之前，最好的方法是什么？例如，“select *” 强制进行顺序扫描（在冷数据库上需要约15分钟），但随后的响应时间仍然很差。是否有内置的方式来完成这项任务而不是通过查询？

谢谢，也可以通过电子邮件回复([e-mail])

-- Shaul

- Shaul Dar

Answer 1

关于您提出的第一个问题，contrib模块“pg_buffercache”可以让您检查缓存中的内容。我喜欢这样定义：

create or replace view util.buffercache_hogs as
select case
       when pg_buffercache.reldatabase = 0
            then '- global'
       when pg_buffercache.reldatabase <> (select pg_database.oid from pg_database where pg_database.datname = current_database())
            then '- database ' || quote_literal(pg_database.datname)
       when pg_namespace.nspname = 'pg_catalog'
            then '- system catalogues'
       when pg_class.oid is null and pg_buffercache.relfilenode > 0
            then '- unknown file ' || pg_buffercache.relfilenode
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+$'
            then (substring(pg_class.relname, 10)::oid)::regclass || ' TOAST'::text
       when pg_namespace.nspname = 'pg_toast' and pg_class.relname ~ '^pg_toast_[0-9]+_index$'
            then ((rtrim(substring(pg_class.relname, 10), '_index'))::oid)::regclass || ' TOAST index'
       else pg_class.oid::regclass::text
       end as key,
       count(*) as buffers, sum(case when pg_buffercache.isdirty then 1 else 0 end) as dirty_buffers,
       round(count(*) / (SELECT pg_settings.setting FROM pg_settings WHERE pg_settings.name = 'shared_buffers')::numeric, 4) as hog_factor
from pg_buffercache
     left join pg_database on pg_database.oid = pg_buffercache.reldatabase
     left join pg_class on pg_class.relfilenode = pg_buffercache.relfilenode
     left join pg_namespace on pg_namespace.oid = pg_class.relnamespace
group by 1
order by 2 desc;

此外，“pageinspect”贡献模块允许您从关系中访问特定页面，因此我认为您可以简单地循环遍历关系中的所有页面，并获取它们。

select count(get_raw_page('information_schema.sql_features', n))
from generate_series(0,
        (select relpages-1 from pg_class where relname = 'sql_features')) n;

这将会把所有的information_schema.sql_features加载到缓存中。

- araqnid

Answer 2

2) 我通常通过记录来自实时系统的查询并重放它们来解决这个问题。这样可以预热数据的典型部分而不是不经常使用的部分（否则会浪费 RAM）。

- Thomas

---

问题在于我无法猜测用户的查询，想象一下“亚马逊” - 接下来的10000个查询会是什么？因此，我希望运行一些将特定表和索引加载到缓存中的东西。 - Shaul Dar

不要猜测。获取最近5分钟或最近10,000个查询的实际日志。我曾在“一家领先的搜索引擎提供商”工作过，这种方法非常有效。或者，如果您有正在运行的服务器，并且想要预热一个新服务器，您可以将查询镜像到要预热的服务器上。 - Thomas

Answer 3

广告1 - 我完全不知道。

广告2 - 为什么不随机选择一些你知道很重要的查询，在冷服务器上运行它们？你运行的查询越多，预热过程就会越好。

- user80168

Answer 4

不要试图热身内存，这是postgresql和操作系统的工作。只需将表（和索引）分成分区，并尝试使用较小的数据集进行操作。如果您成功制定了一个良好的分区计划，那么庞大的索引或表就不会成为问题。如果您仍然想要热身表和索引，那么可能可以完全缓存在RAM中，因为它们比以前更小。

- Victor

---

我认为缓存预热和分区是两个不同的问题。分区不能解决初始的“冷启动”问题，这是一个I/O问题。 - Jan