Fillfactor参数

  • Z时代
  • 2024-01-10
  • 分类：综合

database

介绍Fillfactor前先帮助大家回顾一下，Postgresql数据库的物理结构。

 

在没有非默认表空间的情况下，Postgresql的每个数据库均存放在一个目录中，以数据库oid命名，该目录中存放每个表对应的数据文件，文件名以该数据表对应的relfilenode_oid命名。当表中的数据量足够大，导致表文件的大小大于1GB的时候，Postgresql会自动创建新的数据文件用于存放新插入的数据。新文件的名称为： relfilenode_iod.1, relfilenode_iod.2 等。使用该策略是为了防止在某些文件系统中，最大支持文件尺寸不能大于1GB的情形（--with-segsize=SEGSIZE 可修改数据文件大小）。

 

PostgreSQL将数据文件中的空间从逻辑上划分成一个个页面(数据块)。页面是数据库I/O的基本单位，即只能整页读写数据文件, 页面的大小默认是8K。PostgreSQL中在编译时可通过（--with-blocksize=BLOCKSIZE）来修改页面大小，更大的页面在一些特殊数据库应用中可能会很有用。

 

数据库共享缓存中的空间划分也是按页为基本单位, 一个页的大小与数据文件中页的大小一致, 这样便于整页读取数据文件,并放入到数据库Buffer中, 从Buffer写入数据文件也同理，保证了缓存与数据文件结构和内容上的一致性。

 

在PostgreSQL中，表文件中的tuple之间不进行关联，这样的表文件称为堆文件，每个堆文件都是由多个page组成，PostgreSQL中对数据进行操作一般都是通过追加数据来实现的，例如 insert 时向页中添加一条数据，update 将历史数据标记为删除,然后向页中添加新数据，delete 将历史数据标记为删除，假如存在索引，还涉及到索引的修改。这部分如果不太明白，可以了解一下PostgreSQL的MVCC。

 

在了解了以上概念以及Postgresql数据库的物理结构后，我们可以更好地理解Fillfactor。

 

1. Fillfactor是一个表存储参数，对于性能方面，这是最重要的参数之一。

 

表的Fillfactor是介于10和100之间的百分比。默认值是100。

 

由于默认值为100，这意味着数据库会在页面中写入数据直到它没有剩余空间。因此在执行一个UPDATE时，旧元组将被标记为已删除，并且新元组将插入另一个页面。另外一个影响是，需要修改新记录的相关索引，因为索引必须指向新的位置。

 

还记得前面我们提到过的，对于一个UPDATE实际上有三个操作，首先标记已删除的元组，在新页面中写入新元组，以及索引修改。这不仅仅和UPDATE相关，实际上SELECT查询的性能也会因内部碎片而降低。

 

--查看表或者索引的Fillfactor值

SELECT
pc.relname AS ObjectName,pc.reloptions 
AS ObjectOptions
FROM pg_class AS pc
INNERJOIN pg_namespace AS pns
ON pns.oid = pc.relnamespace
WHERE pns.nspname = "public"
AND pc.relname = "table_name";     --替换为实际表名

如果设置了Fillfactor，你可以找到“{fillfactor = value}”，否则它只是NULL。

默认的Fillfactor是100，这个设置相对来说比较稳妥，但不是在所有情况下都是如此。

当表频繁更新时，你需要更多的CPU和IO用于不同的数据页操作这势必会影响性能。

解决方案是首先测量元组的大小，如果元组大小不是那么大，我们可以减少默认Fillfactor的值。

 

2. 如何衡量PostgreSQL中元组的大小？

 

在更改Fillfactor的默认值之前，我们应该测量表行的大小。如果表行较大，我们不应该更改Fillfactor的默认值。当我们进行性能优化时，知道page和堆表行的大小非常重要，否则我们需要处理高碎片并一次又一次地执行VACUUM FULL或VACUUM。

 

如果总行大小低于8kb，则可以尝试更改表存储参数。

使用下面的SQL查看行大小： 

WITH cteTableInfo AS
(
SELECT
COUNT(1) AS ct
,SUM(length(t::text)) AS TextLength  
,"public.table_name"::regclass AS TableName      --替换表名
FROM public.table_name AS t                      --替换表名
)
,cteRowSize AS
(
   SELECTARRAY [pg_relation_size(TableName)
               , pg_relation_size(TableName, "vm")
               , pg_relation_size(TableName, "fsm")
               , pg_table_size(TableName)
               , pg_indexes_size(TableName)
               , pg_total_relation_size(TableName)
               , TextLength
             ] AS val
        , ARRAY ["Total Relation Size"
               , "Visibility Map"
               , "Free Space Map"
               , "Table Included Toast Size"
               , "Indexes Size"
               , "Total Toast and Indexes Size"
               , "Live Row Byte Size"
             ] ASName
   FROM cteTableInfo
)
SELECT
unnest(name) AS Description
,unnest(val) ASBytes
,pg_size_pretty(unnest(val)) AS BytesPretty
,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
UNION ALL SELECT"------------------------------", NULL, NULL, NULL
UNION ALL SELECT"TotalRows", ct, NULL, NULLFROM cteTableInfo
UNION ALL SELECT"LiveTuples", pg_stat_get_live_tuples(TableName), NULL, NULLFROM cteTableInfo
UNION ALL SELECT"DeadTuples", pg_stat_get_dead_tuples(TableName), NULL, NULLFROM cteTableInfo;

3. 如何在PostgreSQL中更改Table Fillfactor的当前值？

ALTERTABLE table_name SET ( fillfactor = 50);
VACUUM FULL table_name;

 

请注意：

更改Fillfactor的值后，只在特定表上执行VACUUM FULL。

更改Fillfactor的值后，它仅适用于新元组。

当我们减少Fillfactor的默认值时，每个页面都有一些空闲空间，UPDATE生成的新元组可以存储在同一页面上，索引页面就不需要任何修改，但是这也会导致数据文件比正常情况要更大，当做全表扫描时会导致访问更多的page，这些就需要运维人员进行性能测试，自行判断是否应该修改该参数。

 

参考文献：

 

http://www.postgres.cn/news/viewone/1/220                                                 数据库系统概念笔记之存储和文件系统及PostgreSQL实现

https://blog.csdn.net/weixin_39747511/article/details/112923862                 pg数据库表存放在哪里_超详细的PG数据存储结构

以上是 Fillfactor参数 的全部内容， 来源链接： utcz.com/z/535385.html

回到顶部