PostgreSQL Buffer Cache 的運作機制

前幾天因為要了解 PostgreSQL Buffer Cache 的運作機制, 所以找了一下資料, 找到一個網站, 分享給大家, Monitoring PostgreSQL Buffer Cache Internals. 其中這個檔案 Presentation slides (application/pdf - 188.9 KB) 可以看一下, 很清楚地說明了 PostgreSQL Buffer Cache 內部的運作機制.

讀過之後再來看 PostgreSQL: Documentation: 9.2: The Statistics Collector 裏的 pg_stat_bgwriter View 的欄位說明, 就可以明白了解. 以下這些表格的內容是取自 PostgreSQL: Documentation: 9.2: The Statistics Collector 加以補充的.

Table 27-4. pg_stat_database View

Column	Type	Description
buffers_checkpoint	bigint	Number of buffers written during checkpoints. 系統每隔一段時間(checkpoint_timeout)或 checkpoint_segments 寫滿時, 會觸發 checkpoint, 此時要將記憶體修改過的 page, 寫回硬碟. 寫入的 block 數目, 就統計在這個數據.
buffers_clean	bigint	Number of buffers written by the background writer. PostgreSQL 設計上就有一個常駐的 bgwriter process 定期地在檢查, 把一些 dirty 的 page 寫回硬碟, 以免 checkpoint 時需要寫入一大堆 block, 造成整個系統 freeze. 這些被 clean 的 block 數目, 就統計在這個數據.
buffers_backend	bigint	Number of buffers written directly by a backend. 當Buffer Cache 找不到要的資料, 需要從硬碟讀入新的 block, 但剛好要 swap out 的資料, 需要寫入硬碟, 就統計在這個數據.

所以 block_write = buffers_checkpoint + buffers_clean + buffers_backend.

我們再來看 read 部份, buffer read 的資訊在 pg_stat_database, 不過要記得 pg_stat_database 是 database level 的, 所以要加總之後, 才是整個 server 的統計數據. 而 pg_stat_writer 是 server level 的.

Table 27-4. pg_stat_database View

Column	Type	Description
blks_read	bigint	Number of disk blocks read in this database. 這個數據統計的是從 disk 讀取的 block 數, 因為在buffer cache 找不到相關的資料.
blks_hit	bigint	Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache). 需要的資料已存在 buffer cache 中, 所以不用再從 disk read.
blk_read_time	double precision	Time spent reading data file blocks by backends in this database, in milliseconds. 從 disk 讀取資料所花的時間.
blk_write_time	double precision	Time spent writing data file blocks by backends in this database, in milliseconds. 這個數據我試過了, checkpoint 循環發生時, 有 dirty page, 這個數據也不會增加, 似乎 bgwriter clean 也不會來更新這個數據, 只有大量新增才會被統計, 所以推估這是只統計 dirty page 不得不 swap out 的 write time.
stats_reset	timestamp with time zone	Time at which these statistics were last reset. 請記得 pg_stat_ 系列的一些 view 是統計一段時間的, 會不斷地自動累加. 除非你 reset 統計起始時間.

Table 27-13. Additional Statistics Functions

Function	Return Type	Description
pg_backend_pid()	integer	Process ID of the server process handling the current session
pg_stat_get_activity(integer)	setof record	Returns a record of information about the backend with the specified PID, or one record for each active backend in the system if NULL is specified. The fields returned are a subset of those in the pg_stat_activity view.
pg_stat_clear_snapshot()	void	Discard the current statistics snapshot
pg_stat_reset()	void	Reset all statistics counters for the current database to zero (requires superuser privileges)
pg_stat_reset_shared(text)	void	Reset some cluster-wide statistics counters to zero, depending on the argument (requires superuser privileges). Calling pg_stat_reset_shared('bgwriter') will zero all the counters shown in the pg_stat_bgwriter view.
pg_stat_reset_single_table_counters(oid)	void	Reset statistics for a single table or index in the current database to zero (requires superuser privileges)
pg_stat_reset_single_function_counters(oid)	void	Reset statistics for a single function in the current database to zero (requires superuser privileges)

所以要 reset 統計起始時間, 要考慮一起 reset, 這樣統計會有一致的起始點, 但也可以只 reset 想 reset 的那一小部分.以下介紹 reset pg_stat_database, pg_stat_bgwriter 的方法.

#!/bin/bash
username=postgres


#reset pg_stat_database, 要每一個 database reset 一次.

OUTPUT=$( psql -U $username -tc "select datname from pg_database;"
)
echo "$OUTPUT" | while read dbname
do
    psql -U $username -d $dbname -c 'select pg_stat_reset();'
done



#reset pg_stat_bgwriter, 只要下一次 command 即可.

psql -U $username -c "select pg_stat_reset_shared('bgwriter');"

11. 參考文章

• Monitoring PostgreSQL Buffer Cache Internals
• Presentation slides (application/pdf - 188.9 KB)
• PostgreSQL: Documentation: 9.2: The Statistics Collector
• PostgreSQL 9.0 High Performance, page 135
• Checkpoints and the Background Writer: PostgreSQL 8.3 Improvements and Migration
• How to Assign the Output of an SQL Query to a Unix Variable