PostgreSQL中的共享命中缓存 [英] Shared hit cache in postgreSQL

问题描述

我正在尝试使用EXPLAIN命令并试图找出共享匹配 c是什么。

I'm experimenting with the EXPLAIN command and trying to find out what the shared hit is.

Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.030..90.500 rows=1000000 loops=1)
  Buffers: shared hit=512 read=7822
Total runtime: 116.080 ms

我注意到更多共享匹配数，我们可以更快地执行查询。那是什么据我了解，共享读取只是从物理存储中读取的，例如 RAID 或 SSD 。但是为什么共享点击更快？它存储在RAM内还是在哪里？

I've noticed that the more shared hit number we have the faster a query is being executed. But what is that? As far as I got, shared read is just reading from a physical storage like RAID or SSD. But why is the shared hit faster? Is it stored inside RAM or where?

推荐答案

共享匹配本质上意味着该值已被缓存在计算机的主内存，因此无需从硬盘读取。

shared hit essentially means the value has already been cached in the main memory of the computer and it was not necessary to read this from the hard disk.

访问主内存（RAM）的速度比从硬盘读取值快 。这就是为什么查询的点击次数越多，查询速度就越快的原因。

Accessing the main memory (RAM) is much faster than reading values from the hard disk. And that's why the query is faster the more share hits it has.

启动Postgres之后，主内存（RAM）中没有可用的数据，所有内容都需要从硬盘读取。

Immediately after starting Postgres, none of the data is available in the main memory (RAM) and everything needs to be read from the hard disk.

请考虑执行计划中的这一步骤：

Consider this step from an execution plan:

  ->  Seq Scan on products.product_price  (cost=0.00..3210.27 rows=392273 width=0) (actual time=0.053..103.958 rows=392273 loops=1)
        Output: product_id, valid_from, valid_to, price
        Buffers: shared read=2818
        I/O Timings: read=48.382

缓冲区：共享读取= 2818部分意味着必须从硬盘读取2818个块（每个8k）（花费了48ms-我有一个SSD）。那2818个块存储在缓存中（ 共享缓冲区），这样，下次需要它们时，数据库就无需（再次）从（慢速）硬盘读取它们。

The part "Buffers: shared read=2818" means that 2818 blocks (each 8k) had to be read from the hard disk (and that took 48ms - I have a SSD). Those 2818 blocks were stored in the cache (the "shared buffers") so that the next time they are needed the database does not need to read them (again) from the (slow) hard disk.

当我重新运行该语句时，计划更改为：

When I re-run that statement the plan changes to:

  ->  Seq Scan on products.product_price  (cost=0.00..3210.27 rows=392273 width=0) (actual time=0.012..45.690 rows=392273 loops=1)
        Output: product_id, valid_from, valid_to, price
        Buffers: shared hit=2818

这意味着前一条语句仍在的那2818个块主内存（= RAM）和Postgres不需要从硬盘读取它们。

Which means that those 2818 blocks that the previous statement were still in the main memory (=RAM) and Postgres did not need to read them from the hard disk.

内存始终是指内置于计算机中并可直接由CPU访问的主内存（RAM），而不是外部存储。

"memory" always refers to the main memory (RAM) built into the computer and directly accessible to the CPU - as opposed to "external storage".

关于Postgres如何管理共享缓冲区的一些演示：

There are several presentations on how Postgres manages the shared buffers:

• < a href = http://de.slideshare.net/EnterpriseDB/insidepostgressharedmemory2015 rel = noreferrer> http://de.slideshare.net/EnterpriseDB/insidepostgressharedmemory2015


• http://momjian.us/main/writings/pgsql/hw_performance/


• https://2ndquadrant.com/media/ pdfs / talks / InsideBufferCache.pdf （非常技术）


• http://raghavt.blogspot.de/2012/04/caching-in-postgresql.html

• http://de.slideshare.net/EnterpriseDB/insidepostgressharedmemory2015
• http://momjian.us/main/writings/pgsql/hw_performance/
• https://2ndquadrant.com/media/pdfs/talks/InsideBufferCache.pdf (very technical)
• http://raghavt.blogspot.de/2012/04/caching-in-postgresql.html

这篇关于PostgreSQL中的共享命中缓存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！