走過的路: performance

前幾天因為要了解 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. 參考文章

2014年10月25日 星期六

PostgreSQL Buffer Cache 的運作機制

2014年10月25日星期六