主頁 > 知識庫 > PostgreSQL 如何查找需要收集的vacuum 表信息

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

熱門標簽:江西穩(wěn)定外呼系統(tǒng)供應商 無錫梁溪公司怎樣申請400電話 北京智能外呼系統(tǒng)供應商家 孝感銷售電銷機器人廠家 高德地圖標注電話怎么沒了 智能電銷機器人教育 電話機器人錄音師薪資 中國地圖標注省份用什么符號 奧維地圖標注字體大小修改

前言

通常,在PostgreSQL中,由于經(jīng)常需要對表進行 UPDATE 和 DELETE,因此表會產(chǎn)生碎片空間。

在 PostgreSQL中,使用VACUUM 僅僅對需要執(zhí)行 VACUUM 表將已刪除的空間標識為未使用,以便以后重用這些空間,但是不能立即將占用的空間返還給操作系統(tǒng),因此需要使用 VACUUM FULL,才可以釋放空間,并立即將空間返還給操作系統(tǒng)。

實現(xiàn)腳本

記錄收集表創(chuàng)建

CREATE TABLE IF NOT EXISTS tab_vacuum_record
(sqltext text);

收集需要VACUUM 表函數(shù)

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
#獲取環(huán)境變量
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 "檢查數(shù)據(jù)庫服務(wù)器狀態(tài)是否正常 !"
    stat=`$CONNINFO -Aqt -c 'SELECT 1'`
    if [ "${stat}" == "1" ];then
        echo "服務(wù)器連接正常"
    else
        echo "服務(wù)器連接異常,退出"
        exit -1;
    fi
}
function create_table()
{
    echo "創(chuàng)建收集需要vacuum的表"
    $CONNINFO -f $CT_FILE
}
function create_function()
{
    echo "創(chuàng)建收集需要 vacuum 表的函數(shù)"
    $CONNINFO -f $CT_FUNCTION
}
check_status
create_table
create_function

執(zhí)行方式

postgres=# SELECT * FROM f_vacuum_tables();
 f_vacuum_tables 
-----------------
 
(1 row)
--創(chuàng)建測試表
postgres=# CREATE TABLE tab_test(id int);
--插入數(shù)據(jù)
postgres=# INSERT INTO tab_test SELECT id FROM generate_series(1,100000) as id;
INSERT 0 100000
--刪除數(shù)據(jù)
postgres=# DELETE FROM tab_Test WHERE id = 10000;
DELETE 10002
postgres=# SELECT * FROM tab_vacuum_record ;
    sqltext    
-----------------------
 VACUUM FULL tab_test;
(1 row)

該腳本也可以自己根據(jù)需要進行修改,詳細見github

補充:PostgreSQL中 Vacuum 略談

VACUUM doc

路由清理

PostgreSQL 需要定期維護清理,一般都是由守護進程自動清理的,我們只是需要參數(shù)調(diào)優(yōu),也可以執(zhí)行腳本定時去清理回收。

Vacuumming Basics

PG不得不對每張表進行 Vacuum 命令,原因如下:

1、為了回收和再利用通過更新或者刪除行所占用的磁盤空間

2、為了更新被PG查詢計劃所使用的數(shù)據(jù)分析

3、為了更新只讀索引掃描的可見的集合

4、避免由于事務(wù)ID或者混合事務(wù)ID丟失歷史數(shù)據(jù)

由于這些原因,在進行頻繁的 VACUUM 操作時進行規(guī)定:

標準 VACUUM

進行回收時,生產(chǎn)環(huán)境不影響數(shù)據(jù)庫庫的正常使用(SELECT、INSERT、UPDATE、DELETE),并行使用,清理時不允許對表結(jié)構(gòu)進行修改(ALTER TABLE)推薦使用該方案

VACUUM FULL

a、可以回收大量空間,但是比標準回收執(zhí)行慢

b、運行時需要鎖表

VACUUM 運行會導致讀寫性能比較差,所以需要調(diào)整一些參數(shù)降低影響

temp_file_limit = -1 #默認-1表示不限制每個進程可使用的最大臨時文件限制,單位kb
#max_files_per_process = 1000 #每個子進程允許同時打開文件的最大數(shù)量

在執(zhí)行 VACUUM 和 ANYLYZE 期間,系統(tǒng)會維護一個用于估算各種I/O操作所消耗的內(nèi)部計數(shù)器,當該值達到vacuum_cost_limit的值時,該進程會休眠 vacuum_cost_delay 指定的時間,并重置計數(shù)器的值,繼續(xù)運行 VACUM 或者 ANYLYZE 操作

vacuum_cost_limit = 200 
vacuum_cost_delay = 0 # 單位微秒,默認為 0 沒有開啟

該參數(shù) vacuum_cost_delay 主要用于并發(fā)時降低I/O的影響,推薦為10

vacuum_cost_page_hit = 1 # 代表從緩存池查找共享的hash table并掃描 該`頁`的內(nèi)容
             #的估計值
vacuum_cost_page_miss = 10   # 0-10000 credits
vacuum_cost_page_dirty = 20

NOTE

當一張表中包含了大量數(shù)據(jù)時,同時進行刪除或者更新操作時,VACUUM 并不是最好的方案,

如果有該情況,則應該使用 VACUU FULL ,當執(zhí)行 ALTER TABLE 時,會重新 COPY整

個表和重新構(gòu)建索引,會進行執(zhí)行鎖,臨時占用和原始表大小的磁盤空間,直到新數(shù)據(jù)COPY完成。

升級執(zhí)行計劃

執(zhí)行計劃通過自己或者 VACUUM調(diào)用命令 ANALYZE 收集統(tǒng)計,

創(chuàng)建 表達式索引 能夠提高查詢執(zhí)行計劃

default_statistics_target = 100 #提高查詢的 析計劃

以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方,望不吝賜教。

您可能感興趣的文章:
  • PostgreSQL對GROUP BY子句使用常量的特殊限制詳解
  • PostgreSQL中的VACUUM命令用法說明
  • PostgreSQL備份工具 pgBackRest使用詳解
  • postgresql數(shù)據(jù)合并,多條數(shù)據(jù)合并成1條的操作
  • postgresql數(shù)據(jù)添加兩個字段聯(lián)合唯一的操作
  • postgreSQL使用pgAdmin備份服務(wù)器數(shù)據(jù)的方法
  • Postgresql 查看SQL語句執(zhí)行效率的操作

標簽:齊齊哈爾 臨滄 通化 阜陽 海北 荊州 那曲 泰州

巨人網(wǎng)絡(luò)通訊聲明:本文標題《PostgreSQL 如何查找需要收集的vacuum 表信息》,本文關(guān)鍵詞  PostgreSQL,如何,查找,需要,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《PostgreSQL 如何查找需要收集的vacuum 表信息》相關(guān)的同類信息!
  • 本頁收集關(guān)于PostgreSQL 如何查找需要收集的vacuum 表信息的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章