digoal
2023-03-24
PostgreSQL , PolarDB , fillfactor , pg_stat_* , HOT
fillfactor 指标被用于设置数据页的写入填充率, 默认是100%, 如果数据更新频繁, 而且被更新记录的当前数据页没有预留空间, 则需要发生row move page, 将新版本写入到其他有空间的数据页. 也势必发生索引的更新动作.
为了解决这个问题, 我们可以设置 fillfactor, 预留一些空间用于放当前页面内更新的记录, 提高HOT概率, 降低索引膨胀. 《PostgreSQL Heap Only Tuple - HOT (降低UPDATE引入的索引写IO放大)》
但是到底应该设置多大的fillfactor? 什么时候该设置fillfactor? 哪些表该设置fillfactor?
有没有监控数据可以参考呢?
以前没有, PostgreSQL 16提供了一个可以参考的监控指标. Count updates that move row to a new page.
Count updates that move row to a new page.
author Peter Geoghegan <pg@bowt.ie>
Thu, 23 Mar 2023 18:16:17 +0000 (11:16 -0700)
committer Peter Geoghegan <pg@bowt.ie>
Thu, 23 Mar 2023 18:16:17 +0000 (11:16 -0700)
commit ae4fdde1352fa6b2c9123e91435efafc78c370a0
tree 2f0bc32e55b16114a24d14180231a117be526e8b tree
parent 3b50275b12950280fb07193e24a4f400ed8a9fef commit | diff
Count updates that move row to a new page.
Add pgstat counter to track row updates that result in the successor
version going to a new heap page, leaving behind an original version
whose t_ctid points to the new version. The current count is shown by
the n_tup_newpage_upd column of each of the pg_stat_*_tables views.
The new n_tup_newpage_upd column complements the existing n_tup_hot_upd
and n_tup_upd columns. Tables that have high n_tup_newpage_upd values
(relative to n_tup_upd) are good candidates for tuning heap fillfactor.
Corey Huinker, with small tweaks by me.
Author: Corey Huinker <corey.huinker@gmail.com>
Reviewed-By: Peter Geoghegan <pg@bowt.ie>
Reviewed-By: Andres Freund <andres@anarazel.de>
Discussion: https://postgr.es/m/CADkLM=ded21M9iZ36hHm-vj2rE2d=zcKpUQMds__Xm2pxLfHKA@mail.gmail.com
+ pg_stat_get_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd,
+ pg_stat_get_xact_tuples_hot_updated(C.oid) AS n_tup_hot_upd,
+ pg_stat_get_xact_tuples_newpage_updated(C.oid) AS n_tup_newpage_upd
