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

CREATE TABLE IF NOT EXISTS tab_ vacuum _record

(sqltext text);

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;

#!/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)

temp_file_limit = -1 #默认-1表示不限制每个进程可使用的最大临时文件限制，单位kb

#max_files_per_process = 1000 #每个子进程允许同时打开文件的最大数量

vacuum_cost_limit = 200

vacuum_cost_delay = 0 # 单位微秒，默认为 0 没有开启

vacuum_cost_page_hit = 1 # 代表从缓存池 查找 共享的hash table 并扫描 该`页`的内容

              #的估计值

vacuum_cost_page_miss = 10   # 0-10000 credits

vacuum_cost_page_dirty = 20

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