【重新發現PostgreSQL之美】- 50 一粒老鼠屎

背景

場景:

• 在正常業務使用期間, DBA、開發者、分析師在資料庫中跑大查詢, 某些大表采用了全表掃描.

挑戰:

• 大表的全表掃描會占用buffer pool, 進而将shared buffer中的熱資料擠出去, 導緻其他業務的SQL變慢, 嚴重的導緻雪崩.

PG 解決方案:

• 《PostgreSQL 大表掃描政策 - BAS_BULKREAD , synchronize_seqscans , ring buffer 代替 buffer pool》
• 超過1/4 shared buffer的table , 全表掃描會使用ring buffer (256KB)代替buffer pool
• page 标記為BAS_BULKREAD, 優先淘汰出buffer.

除了全表掃描, PG的bulk - write, vacuum都有類似機制:

bulk - write 16MB ring buffer

COPY FROM command.

CREATE TABLE AS command.

CREATE MATERIALIZED VIEW or REFRESH MATERIALIZED VIEW command.

ALTER TABLE command.

vacuum 256KB ring buffer.

When reading or writing a huge table, PostgreSQL uses a ring buffer rather than the buffer pool. The ring buffer is a small and temporary buffer area. When any condition listed below is met, a ring buffer is allocated to shared memory:

Bulk-reading

When a relation whose size exceeds one-quarter of the buffer pool size (shared_buffers/4) is scanned. In this case, the ring buffer size is 256 KB.

Bulk-writing

When the SQL commands listed below are executed. In this case, the ring buffer size is 16 MB.

Vacuum-processing

When an autovacuum performs a vacuum processing. In this case, the ring buffer size is 256 KB.

The allocated ring buffer is released immediately after use.

The benefit of the ring buffer is obvious. If a backend process reads a huge table without using a ring buffer, all stored pages in the buffer pool are removed (kicked out); therefore, the cache hit ratio decreases. The ring buffer avoids this issue.

Why the default ring buffer size for bulk-reading and vacuum processing is 256 KB?

Why 256 KB? The answer is explained in the README located under the buffer manager's source directory.

For sequential scans, a 256 KB ring is used. That's small enough to fit in L2 cache, which makes transferring pages from OS cache to shared buffer cache efficient. Even less would often be enough, but the ring must be big enough to accommodate all pages in the scan that are pinned concurrently. (snip)

https://github.com/digoal/blog/blob/master/202108/20210827_03.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76