• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    PostgreSQL 如何查找需要收集的vacuum 表信息

    前言

    通常,在PostgreSQL中,由于经常需要对表进行 UPDATE 和 DELETE,因此表会产生碎片空间。

    在 PostgreSQL中,使用VACUUM 仅仅对需要执行 VACUUM 表将已删除的空间标识为未使用,以便以后重用这些空间,但是不能立即将占用的空间返还给操作系统,因此需要使用 VACUUM FULL,才可以释放空间,并立即将空间返还给操作系统。

    实现脚本

    记录收集表创建

    CREATE TABLE IF NOT EXISTS tab_vacuum_record
    (sqltext text);

    收集需要VACUUM 表函数

    CREATE OR REPLACE FUNCTION f_vacuum_tables()
    RETURNS void AS
    $FUNCTION$
     DECLARE
     v_tablename text;
     v_dead_cond bigint;
     v_sql    text;
     cur_tablename REFCURSOR;
     v_vacuum_record text;
     BEGIN
     v_vacuum_record := 'tab_vacuum_record';
     OPEN cur_tablename FOR SELECT tablename FROM pg_tables WHERE tablename !~ '^pg|^sql';
     LOOP
      FETCH cur_tablename INTO v_tablename;
       SELECT n_dead_tup INTO v_dead_cond FROM pg_stat_user_tables WHERE relname = v_tablename;
         IF v_dead_cond > 0 THEN
          v_sql := 'INSERT INTO ' || v_vacuum_record || ' VALUES(' || chr(39) ||'VACUUM FULL ' || v_tablename ||';'|| chr(39) ||')';
        EXECUTE v_sql;
         END IF;
       EXIT WHEN NOT FOUND;
     END LOOP;
     CLOSE cur_tablename;
     END;
    $FUNCTION$
    LANGUAGE PLPGSQL;

    SHELL脚本

    #!/bin/bash
    #获取环境变量
    CURRDIR=$(cd "$(dirname $0)";pwd)
    TOPDIR=$(cd $CURRDIR/..;pwd)
    CONFIG=$TOPDIR/conf/host.ini
    CT_FILE=${TOPDIR}/sql/CREATE_VACCUM_TABLE_RECORD.sql
    CT_FUNCTION=${TOPDIR}/sql/CHECK_NEEDS_VACUUM_TABLE_FUNCTION.sql
    source $CONFIG
    CONNINFO="psql -U $USER -d $DBNAME -h $HOSTADDR -p $PORT"
    function check_status()
    {
        echo "检查数据库服务器状态是否正常 !"
        stat=`$CONNINFO -Aqt -c 'SELECT 1'`
        if [ "${stat}" == "1" ];then
            echo "服务器连接正常"
        else
            echo "服务器连接异常,退出"
            exit -1;
        fi
    }
    function create_table()
    {
        echo "创建收集需要vacuum的表"
        $CONNINFO -f $CT_FILE
    }
    function create_function()
    {
        echo "创建收集需要 vacuum 表的函数"
        $CONNINFO -f $CT_FUNCTION
    }
    check_status
    create_table
    create_function
    

    执行方式

    postgres=# SELECT * FROM f_vacuum_tables();
     f_vacuum_tables 
    -----------------
     
    (1 row)
    --创建测试表
    postgres=# CREATE TABLE tab_test(id int);
    --插入数据
    postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
    INSERT 0 100000
    --删除数据
    postgres=# DELETE FROM tab_Test WHERE id = 10000;
    DELETE 10002
    postgres=# SELECT * FROM tab_vacuum_record ;
        sqltext    
    -----------------------
     VACUUM FULL tab_test;
    (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 运行会导致读写性能比较差,所以需要调整一些参数降低影响

    temp_file_limit = -1 #默认-1表示不限制每个进程可使用的最大临时文件限制,单位kb
    #max_files_per_process = 1000 #每个子进程允许同时打开文件的最大数量

    在执行 VACUUM 和 ANYLYZE 期间,系统会维护一个用于估算各种I/O操作所消耗的内部计数器,当该值达到vacuum_cost_limit的值时,该进程会休眠 vacuum_cost_delay 指定的时间,并重置计数器的值,继续运行 VACUM 或者 ANYLYZE 操作

    vacuum_cost_limit = 200 
    vacuum_cost_delay = 0 # 单位微秒,默认为 0 没有开启

    该参数 vacuum_cost_delay 主要用于并发时降低I/O的影响,推荐为10

    vacuum_cost_page_hit = 1 # 代表从缓存池查找共享的hash table并扫描 该`页`的内容
                 #的估计值
    vacuum_cost_page_miss = 10   # 0-10000 credits
    vacuum_cost_page_dirty = 20

    NOTE

    当一张表中包含了大量数据时,同时进行删除或者更新操作时,VACUUM 并不是最好的方案,

    如果有该情况,则应该使用 VACUU FULL ,当执行 ALTER TABLE 时,会重新 COPY整

    个表和重新构建索引,会进行执行锁,临时占用和原始表大小的磁盘空间,直到新数据COPY完成。

    升级执行计划

    执行计划通过自己或者 VACUUM调用命令 ANALYZE 收集统计,

    创建 表达式索引 能够提高查询执行计划

    default_statistics_target = 100 #提高查询的 析计划

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。如有错误或未考虑完全的地方,望不吝赐教。

    您可能感兴趣的文章:
    • PostgreSQL对GROUP BY子句使用常量的特殊限制详解
    • PostgreSQL中的VACUUM命令用法说明
    • PostgreSQL备份工具 pgBackRest使用详解
    • postgresql数据合并,多条数据合并成1条的操作
    • postgresql数据添加两个字段联合唯一的操作
    • postgreSQL使用pgAdmin备份服务器数据的方法
    • Postgresql 查看SQL语句执行效率的操作
    上一篇:PostgreSQL备份工具 pgBackRest使用详解
    下一篇:PostgreSQL中的VACUUM命令用法说明
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    PostgreSQL 如何查找需要收集的vacuum 表信息 PostgreSQL,如何,查找,需要,