FPI与ckpt
两次Checkpoint过程中，将第一次更改全页写入(Full Page Image),而不管后续改动多少。常见FPI过多场景：
--UUID主键无序，写入随机多个page合并
--宽表索引过多，单列上存在多个索引
1.写入WAL的数据远远超过真实数据（FPI>Records）
2.更新索引列，所有相关索引都会更新，产生大量日志
3.pg_waldump --stats
--总的统计
pg_waldump 00000001000000140000007E | grep rmgr | awk '{print $2}'|sort|uniq -c
[postgres@postgre12 data]$ pg_waldump 000000010000001400000081 | grep mgr |awk '{print $2}'|sort|uniq -c
11158 Btree
22581 Heap
6 Heap2
6 MultiXact
168 Standby
32 Transaction
12 XLOG
--FPI统计
[postgres@postgre12 data]$ pg_waldump --stats=record 00000001000000140000007E
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG/SWITCH 1 ( 50.00) 24 ( 32.43) 0 ( 0.00) 24 ( 32.43)
Standby/RUNNING_XACTS 1 ( 50.00) 50 ( 67.57) 0 ( 0.00) 50 ( 67.57)
-------- -------- -------- --------
Total 2 74 [100.00%] 0 [0.00%] 74 [100%]
--分类
[postgres@postgre12 data]$ pg_waldump --stats 00000001000000140000007E
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG 1 ( 50.00) 24 ( 32.43) 0 ( 0.00) 24 ( 32.43)
Transaction 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Storage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
CLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Database 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Tablespace 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
MultiXact 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
RelMap 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Standby 1 ( 50.00) 50 ( 67.57) 0 ( 0.00) 50 ( 67.57)
Heap2 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Heap 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Btree 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Hash 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Gin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Gist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Sequence 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
SPGist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
BRIN 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
CommitTs 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
ReplicationOrigin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Generic 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
LogicalMessage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
-------- -------- -------- --------
Total 2 74 [100.00%] 0 [0.00%] 74 [100%]
FPI=0%放大数据
Record真实数据
FPI=86%
Records=13%
放大率(86%)/(13%)=6.5
集中在heap/insert索引插入
4.计算fillfactor
对于宽表，要进一步减小fillfactor使得至少可以保留一个tuple的空闲空间
with a as(
select
c.relname,
c.reltuples,
c.relpages
from
pg_class c),
b as(
select
c.relname,
sum(s.stawidth) as col_len
from
pg_catalog.pg_statistic s,
pg_class c
where c.oid=s.starelid
group by c.relname)
select
a.relname as table_name,
--tupe
--header 24 bytes
--interger waster 6bytes
a.reltuples,a.relpages,
(b.col_len+24+6)*a.reltuples/8192/a.relpages*100::float as table_fillfactor
from a,b
where a.relname=b.relname
order by (b.col_len+24+6)*a.reltuples/8192/a.relpages*100::float desc limit 50;
--
alter table pgbench_accounts set (fillfactor=90);
psql -c "checkpoint;select pg_current_wal_lsn()";
pgbench -i #初始化
pgbench -n -c 100 -j 100 -t 10000 ;
psql -c "select pg_current_wal_lsn()";
pg_waldump --stats 00000001000000140000007E
附录
postgres=# select pg_current_wal_lsn(), pg_current_wal_insert_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_current_wal_insert_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+---------------------------+--------------------------+-------------------------------------
14/84CF60F0 | 14/84CF60F0 | 000000010000001400000084 | (000000010000001400000084,13590768)
(1 row)
pg_current_wal_lsn
--------------------
14/84CF61A0
(1 row)
[postgres@postgre12 data]$ pgbench -n -c 100 -j 100 -t 60 ;
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 60
number of transactions actually processed: 6000/6000
latency average = 62.444 ms
tps = 1601.447328 (including connections establishing)
tps = 1614.954665 (excluding connections establishing)
[postgres@postgre12 data]$ psql -c "select pg_current_wal_lsn()";
pg_current_wal_lsn
--------------------
14/869159A8
(1 row)
postgres=# select pg_current_wal_lsn(), pg_current_wal_insert_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_current_wal_insert_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+---------------------------+--------------------------+------------------------------------
14/86915B20 | 14/86915B20 | 000000010000001400000086 | (000000010000001400000086,9526048)
(1 row)
pg_waldump --stats 000000010000001400000084
[postgres@postgre12 data]$ pg_waldump --stats 000000010000001400000084
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG 3 ( 0.00) 342 ( 0.00) 0 ( 0.00) 342 ( 0.00)
Transaction 8 ( 0.00) 2556 ( 0.02) 0 ( 0.00) 2556 ( 0.02)
Storage 8 ( 0.00) 336 ( 0.00) 0 ( 0.00) 336 ( 0.00)
CLOG 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Database 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Tablespace 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
MultiXact 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
RelMap 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Standby 21 ( 0.01) 1070 ( 0.01) 0 ( 0.00) 1070 ( 0.01)
Heap2 499 ( 0.24) 3071677 ( 18.58) 0 ( 0.00) 3071677 ( 18.42)
Heap 51734 ( 24.90) 3267514 ( 19.76) 73044 ( 52.66) 3340558 ( 20.04)
Btree 155474 ( 74.84) 10189830 ( 61.63) 65664 ( 47.34) 10255494 ( 61.51)
Hash 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Gin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Gist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Sequence 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
SPGist 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
BRIN 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
CommitTs 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
ReplicationOrigin 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
Generic 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
LogicalMessage 0 ( 0.00) 0 ( 0.00) 0 ( 0.00) 0 ( 0.00)
-------- -------- -------- --------
Total 207747 16533325 [99.17%] 138708 [0.83%] 16672033 [100%]
06-23
2005
2005
07-25
860
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
