8

PG-日常运维_MySQL DBA攻坚之路的技术博客_51CTO博客

 2 years ago
source link: https://blog.51cto.com/u_13874232/5702603
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

PG-日常运维

精选 原创

进击的CJR 2022-09-22 11:04:59 博主文章分类：PostgreSQL ©著作权

文章标签 数据 元组 sql 文章分类 其它 数据库 yyds干货盘点 阅读数166

VACUUM

表膨胀是什么

有效数据量不变，表越来越大，扫描的效率变低。是因为PG的MVCC写数据时，旧数据不删除，把新数据插入，将旧数据标记为无效，清理之前一直占用空间。执行update的话就是insert+delete的原理，依然会导致表膨胀。

vacuum的作用

磁盘清理dead tuple ；更新统计信息；重组数据；解决事务ID回卷问题。

vacuum ： 不要求获得排它锁，找到那些旧的“死”数据，标记为可用状态，不进行空间合并

vacuum full： 就是除了vacuum，进行空间合并，它需要lock table

vacuum analyze： 更新统计信息，使得优化器能够选择更好的方案执行sql

vacuum freeze: 表记录冻结，可解决事务id回卷的问题​
vacuum做了什么
1、清除update或delete 操作后留下的死元组
2、跟踪表块中可用空间，更新free space map
3、更新visibility map，index only scan 以及后续vacuum都会利用到
4、冻结表中的行，防止事务ID回卷
5、配合analyze ，定期更新统计信息
FSM是什么
普通的vacuum只是清理死元组，"腾出"空间，在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑新增页面。但是每个页面的空闲空间又不是固定大小的，所以如果要利用这些空间空间，就需要遍历一遍数据页面来找到它们，这样会造成比较大的开销。因此就设计了用来记录每个页面剩余空间的空闲空间映射表FSM(Free Space Mapping)，以便高效的将空闲空间管理起来，方便查找和重新使用。FSM在第一次vacuum之后会出现，可以使用pg_freespacemap扩展查看。
简单来说，就是vacuum只是清理死元组，利用FSM可以高效的将空间管理起来，方便查找和重新使用。
可以看到表在磁盘中的fsm文件
select pg_relation_filepath('test01');
 pg_relation_filepath 
----------------------
 base/13593/16521
(1 row)
-rw------- 1 postgres postgres     24576 Aug 10 17:25 16521_fsm
-rw------- 1 postgres postgres      8192 Aug 10 17:25 16521_vm
visibility map是什么
all_visible为t表示全部可见，不包含死元组，作用是加快vacuum，进行vacuum时，当扫描到all_visible为t时，则直接跳过。
create extension pg_visibility;
 select * from pg_visibility('test01');
 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t           | f          | t
     1 | t           | f          | t
vacuum full
极端情况下，就会发生每个页面的"填充率"特别低，因此，需要引入vacuum full，vacuum full会对表进行重组，也就意味着表的oid会变，所以不能我们在日常操作中，要定位表的oid的时候，不能通过pg_class的oid来找，得通过pg_class的relfilenode来找，这样才精确。而且，vacuum full最大会占据原来磁盘空间的两倍，所以请预留好磁盘空间。
如下实验，可以看到经过vacuum full后表的OID已经改变
insert into test02 values (generate_series(1,100));
postgres=# vacuum test02;
VACUUM
postgres=# select pg_relation_filepath('test02');
 pg_relation_filepath 
----------------------
 base/13593/16672
(1 row)

postgres=# vacuum full test02;
VACUUM
postgres=# select pg_relation_filepath('test02');
 pg_relation_filepath 
----------------------
 base/13593/16718
(1 row)
AutoVacuum
触发AutoVacuum条件，达到这个数据量死元组变化
reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold
postgres=# show autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold 
-----------------------------
 50
(1 row)


postgres=# show autovacuum_vacuum_scale_factor;               
 autovacuum_vacuum_scale_factor 
--------------------------------
 0.2
(1 row)

postgres=# insert into test02 values (generate_series(1,100));
INSERT 0 100

reltuples*autovacuum_vacuum_scale_factor+autovacuum_vacuum_threshold
触发AutoVacuum条件，达到这个数据量变化

postgres=# select 100*0.2+50;
 ?column? 
----------
     70.0
(1 row)
表存活行数
select reltuples ,relpages from pg_class where relname='test02';
 reltuples | relpages 
-----------+----------
       100 |        1
也可以修改触发AutoVacuum的条件
 alter table test02 set (autovacuum_vacuum_scale_factor=0.001);
在操作系统层面可以看到autovacuum的进程，autovacuum launcher 是守护进程，fork出autovacuum_max_workers进程进行AutoVacuumpg_stat_progress_vacuum可以看到vacuum进行到那一步骤了。
select name,setting from pg_settings where name like '%autovacuum%';
                name                 |  setting  
-------------------------------------+-----------
 autovacuum                          | on
 autovacuum_analyze_scale_factor     | 0.1
 autovacuum_analyze_threshold        | 50
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 autovacuum_naptime                  | 60
 autovacuum_vacuum_cost_delay        | 2
 autovacuum_vacuum_cost_limit        | -1
 autovacuum_vacuum_scale_factor      | 0.2
 autovacuum_vacuum_threshold         | 50
 autovacuum_work_mem                 | -1
 log_autovacuum_min_duration         | -1
(13 rows)
reindex 是8级锁
reindex index concurrently等价于online ddl

            

        
        
        
    

    

        
  • 
                
                    
                    赞
                
            
    • 
            
  • 
                
                    
                    收藏
                
            
    • 
            
  • 
                
                    
                    评论
                
            
    • 
            
  • 
                
                    
                    分享
                
            
    • 
                            
  • 
                    
                        
                        举报
                    
                
    • 
                    

    
    


        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK