• 售前

  • 售后

热门帖子
入门百科

PostgreSQL 如何查找必要网络的vacuum 表信息

[复制链接]
形腿望舞 显示全部楼层 发表于 2021-8-14 15:05:22 |阅读模式 打印 上一主题 下一主题
媒介

通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间。
在 PostgreSQL中,利用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未利用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作体系,因此需要利用 VACUUM FULL,才可以开释空间,并立即将空间返还给操作体系。
实现脚本

纪录网络表创建
  1. CREATE TABLE IF NOT EXISTS tab_vacuum_record
  2. (sqltext text);
复制代码
网络需要VACUUM 表函数
  1. CREATE OR REPLACE FUNCTION f_vacuum_tables()
  2. RETURNS void AS
  3. $FUNCTION$
  4. DECLARE
  5. v_tablename text;
  6. v_dead_cond bigint;
  7. v_sql    text;
  8. cur_tablename REFCURSOR;
  9. v_vacuum_record text;
  10. BEGIN
  11. v_vacuum_record := 'tab_vacuum_record';
  12. OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';
  13. LOOP
  14.   FETCH cur_tablename INTO v_tablename;
  15.    SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
  16.      IF v_dead_cond > 0 THEN
  17.       v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';
  18.     EXECUTE v_sql;
  19.      END IF;
  20.    EXIT WHEN NOT FOUND;
  21. END LOOP;
  22. CLOSE cur_tablename;
  23. END;
  24. $FUNCTION$
  25. LANGUAGE PLPGSQL;
复制代码
SHELL脚本
  1. #!/bin/bash
  2. #获取环境变量
  3. CURRDIR=$(cd "$(dirname $0)";pwd)
  4. TOPDIR=$(cd $CURRDIR/..;pwd)
  5. CONFIG=$TOPDIR/conf/host.ini
  6. CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
  7. CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
  8. source $CONFIG
  9. CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"
  10. function check_status()
  11. {
  12.     echo "检查数据库服务器状态是否正常 !"
  13.     stat=`$CONNINFO -Aqt -c 'SELECT 1'`
  14.     if [ "${stat}" == "1" ];then
  15.         echo "服务器连接正常"
  16.     else
  17.         echo "服务器连接异常,退出"
  18.         exit -1;
  19.     fi
  20. }
  21. function create_table()
  22. {
  23.     echo "创建收集需要vacuum的表"
  24.     $CONNINFO -f $CT_FILE
  25. }
  26. function create_function()
  27. {
  28.     echo "创建收集需要 vacuum 表的函数"
  29.     $CONNINFO -f $CT_FUNCTION
  30. }
  31. check_status
  32. create_table
  33. create_function
复制代码
执行方式
  1. postgres=# SELECT * FROM f_vacuum_tables();
  2. f_vacuum_tables
  3. -----------------
  4. (1 row)
  5. --创建测试表
  6. postgres=# CREATE TABLE tab_test(id int);
  7. --插入数据
  8. postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
  9. INSERT 0 100000
  10. --删除数据
  11. postgres=# DELETE FROM tab_Test WHERE id <= 10000;
  12. DELETE 10002
  13. postgres=# SELECT * FROM tab_vacuum_record ;
  14.     sqltext   
  15. -----------------------
  16. VACUUM FULL tab_test;
  17. (1 row)
复制代码
该脚本也可以自己根据需要进行修改,具体见github
补充:PostgreSQL中 Vacuum 略谈
VACUUM doc
路由整理

PostgreSQL 需要定期维护整理,一样平常都是由保卫历程自动整理的,我们只是需要参数调优,也可以执行脚本定时去整理接纳。
Vacuumming Basics

PG不得不对每张表进行 Vacuum 命令,原因如下:
1、为了接纳和再利用通过更新大概删除行所占用的磁盘空间
2、为了更新被PG查询筹划所利用的数据分析
3、为了更新只读索引扫描的可见的聚集
4、避免由于变乱ID大概混合变乱ID丢失汗青数据
由于这些原因,在进行频繁的 VACUUM 操作时进行规定:
标准 VACUUM
进行接纳时,生产情况不影响数据库库的正常利用(SELECT、INSERT、UPDATE、DELETE),并行利用,整理时不答应对表布局进行修改(ALTER TABLE)保举利用该方案
VACUUM FULL
a、可以接纳大量空间,但是比标准接纳执行慢
b、运行时需要锁表
VACUUM 运行会导致读写性能比力差,所以需要调整一些参数低落影响
  1. temp_file_limit = -1 #默认-1表示不限制每个进程可使用的最大临时文件限制,单位kb
  2. #max_files_per_process = 1000 #每个子进程允许同时打开文件的最大数量
复制代码
在执行 VACUUM 和 ANYLYZE 期间,体系会维护一个用于估算各种I/O操作所消耗的内部计数器,当该值达到vacuum_cost_limit的值时,该历程会休眠 vacuum_cost_delay 指定的时间,并重置计数器的值,继续运行 VACUM 大概 ANYLYZE 操作
  1. vacuum_cost_limit = 200
  2. vacuum_cost_delay = 0 # 单位微秒,默认为 0 没有开启
复制代码
该参数 vacuum_cost_delay 重要用于并发时低落I/O的影响,保举为10
  1. vacuum_cost_page_hit = 1 # 代表从缓存池查找共享的hash table并扫描 该`页`的内容
  2.              #的估计值
  3. vacuum_cost_page_miss = 10   # 0-10000 credits
  4. vacuum_cost_page_dirty = 20
复制代码
NOTE

当一张表中包罗了大量数据时,同时进行删除大概更新操作时,VACUUM 并不是最好的方案,
假如有该情况,则应该利用 VACUU FULL ,当执行 ALTER TABLE 时,会重新 COPY整
个表和重新构建索引,会进行执行锁,暂时占用和原始表大小的磁盘空间,直到新数据COPY完成。
升级执行筹划

执行筹划通过自己大概 VACUUM调用命令 ANALYZE 网络统计,
创建 表达式索引 可以或许提高查询执行筹划
  1. default_statistics_target = 100 #提高查询的 析计划
复制代码
以上为个人履历,希望能给各人一个参考,也希望各人多多支持草根技能分享。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作