溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“PostgreSQL中關(guān)于xid freeze的腳本有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“PostgreSQL中關(guān)于xid freeze的腳本有哪些”吧！
Monitor Database
數(shù)據(jù)庫監(jiān)控腳本
[local:/data/run/pg12]:5120 pg12@testdb=# show autovacuum_freeze_max_age; autovacuum_freeze_max_age --------------------------- 200000000 (1 row) [local:/data/run/pg12]:5120 pg12@testdb=# WITH max_age AS ( pg12@testdb(# SELECT 2000000000 as max_old_xid pg12@testdb(# , setting AS autovacuum_freeze_max_age pg12@testdb(# FROM pg_catalog.pg_settings pg12@testdb(# WHERE name = 'autovacuum_freeze_max_age' ) pg12@testdb-# , per_database_stats AS ( pg12@testdb(# SELECT datname pg12@testdb(# , m.max_old_xid::int pg12@testdb(# , m.autovacuum_freeze_max_age::int pg12@testdb(# , age(d.datfrozenxid) AS oldest_current_xid pg12@testdb(# FROM pg_catalog.pg_database d pg12@testdb(# JOIN max_age m ON (true) pg12@testdb(# WHERE d.datallowconn ) pg12@testdb-# SELECT max(oldest_current_xid) AS oldest_current_xid pg12@testdb-# , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound pg12@testdb-# , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac pg12@testdb-# FROM per_database_stats; oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac --------------------+----------------------------+----------------------------------- 3844 | 0 | 0 (1 row) [local:/data/run/pg12]:5120 pg12@testdb=#
percent_towards_wraparound=數(shù)據(jù)庫年齡/20億，如接近100%，那么需要特別注意；
percent_towards_emergency_autovac=數(shù)據(jù)庫年齡/autovacuum_freeze_max_age，autovacuum_freeze_max_age參數(shù)一般為2億。
下面的腳本列出了每個(gè)數(shù)據(jù)庫的年齡和autovacuum_freeze_max_age參數(shù)的設(shè)定。
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT datname
pg12@testdb-# , age(datfrozenxid)
pg12@testdb-# , current_setting('autovacuum_freeze_max_age')
pg12@testdb-# FROM pg_database
pg12@testdb-# ORDER BY 2 DESC;
datname | age | current_setting
-----------+------+-----------------
postgres | 3844 | 200000000
template1 | 3844 | 200000000
template0 | 3844 | 200000000
db1 | 3844 | 200000000
db2 | 3844 | 200000000
db3 | 3844 | 200000000
testdb | 3844 | 200000000
(7 rows)Monitor relation
監(jiān)控關(guān)系（數(shù)據(jù)表）
[local:/data/run/pg12]:5120 pg12@testdb=# SELECT c.oid::regclass
pg12@testdb-# , age(c.relfrozenxid)
pg12@testdb-# , pg_size_pretty(pg_total_relation_size(c.oid))
pg12@testdb-# FROM pg_class c
pg12@testdb-# JOIN pg_namespace n on c.relnamespace = n.oid
pg12@testdb-# WHERE relkind IN ('r', 't', 'm')
pg12@testdb-# AND n.nspname NOT IN ('pg_toast')
pg12@testdb-# ORDER BY 2 DESC LIMIT 100;
oid | age | pg_size_pretty
--------------------------------------------+------+----------------
pg_policy | 3844 | 24 kB
pg_init_privs | 3844 | 72 kB
pg_seclabel | 3844 | 16 kB
pg_shseclabel | 3844 | 16 kB
pg_collation | 3844 | 384 kB
pg_partitioned_table | 3844 | 16 kB
pg_range | 3844 | 56 kB
pg_transform | 3844 | 16 kB
pg_sequence | 3844 | 8192 bytes
pg_publication | 3844 | 16 kB
pg_publication_rel | 3844 | 16 kB
pg_subscription_rel | 3844 | 8192 bytes
information_schema.sql_packages | 3844 | 48 kB
information_schema.sql_features | 3844 | 104 kB
information_schema.sql_implementation_info | 3844 | 48 kB
information_schema.sql_parts | 3844 | 48 kB
information_schema.sql_languages | 3844 | 48 kB
information_schema.sql_sizing | 3844 | 48 kB
pg_statistic | 3844 | 312 kB
pg_type | 3844 | 192 kB
pg_foreign_server | 3844 | 24 kB
pg_authid | 3844 | 48 kB
pg_statistic_ext_data | 3844 | 16 kB
--More--以上列出了每個(gè)relation的age以及relation的大小。
Auto Generate Script
該腳本自動(dòng)創(chuàng)建清理腳本。
[local:/data/run/pg12]:5120 pg12@testdb=# \t
Tuples only is on.
[local:/data/run/pg12]:5120 pg12@testdb=# \o /tmp/vacuum.sql
[local:/data/run/pg12]:5120 pg12@testdb=# select 'vacuum freeze analyze verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't', 'm') order by age(relfrozenxid) desc limit 100;
[local:/data/run/pg12]:5120 pg12@testdb=# \o
[local:/data/run/pg12]:5120 pg12@testdb=# \t
Tuples only is off.
[local:/data/run/pg12]:5120 pg12@testdb=# \set ECHO all
[local:/data/run/pg12]:5120 pg12@testdb=# \! cat /tmp/vacuum.sql
vacuum freeze analyze verbose pg_ts_parser;
vacuum freeze analyze verbose pg_collation;
vacuum freeze analyze verbose pg_partitioned_table;
vacuum freeze analyze verbose pg_range;
vacuum freeze analyze verbose pg_transform;
vacuum freeze analyze verbose pg_sequence;
vacuum freeze analyze verbose pg_publication;
vacuum freeze analyze verbose pg_publication_rel;
vacuum freeze analyze verbose pg_subscription_rel;
vacuum freeze analyze verbose information_schema.sql_packages;
vacuum freeze analyze verbose pg_toast.pg_toast_13426;
vacuum freeze analyze verbose information_schema.sql_features;
vacuum freeze analyze verbose pg_toast.pg_toast_13431;
vacuum freeze analyze verbose pg_toast.pg_toast_13446;
vacuum freeze analyze verbose information_schema.sql_implementation_info;
vacuum freeze analyze verbose pg_toast.pg_toast_13436;
vacuum freeze analyze verbose information_schema.sql_parts;
vacuum freeze analyze verbose information_schema.sql_languages;
vacuum freeze analyze verbose pg_toast.pg_toast_13441;
vacuum freeze analyze verbose information_schema.sql_sizing;
vacuum freeze analyze verbose pg_toast.pg_toast_13451;
vacuum freeze analyze verbose pg_statistic;
vacuum freeze analyze verbose pg_type;
vacuum freeze analyze verbose pg_toast.pg_toast_2600;
vacuum freeze analyze verbose pg_toast.pg_toast_2604;
vacuum freeze analyze verbose pg_toast.pg_toast_3456;
vacuum freeze analyze verbose pg_toast.pg_toast_2606;
vacuum freeze analyze verbose pg_toast.pg_toast_826;
vacuum freeze analyze verbose pg_toast.pg_toast_2609;
vacuum freeze analyze verbose pg_toast.pg_toast_3466;
vacuum freeze analyze verbose pg_toast.pg_toast_3079;
vacuum freeze analyze verbose pg_toast.pg_toast_2328;
vacuum freeze analyze verbose pg_toast.pg_toast_1417;
vacuum freeze analyze verbose pg_toast.pg_toast_3118;
vacuum freeze analyze verbose pg_toast.pg_toast_3394;
vacuum freeze analyze verbose pg_toast.pg_toast_2612;
vacuum freeze analyze verbose pg_toast.pg_toast_2615;
vacuum freeze analyze verbose pg_foreign_server;
vacuum freeze analyze verbose pg_toast.pg_toast_3350;
vacuum freeze analyze verbose pg_toast.pg_toast_3256;
vacuum freeze analyze verbose pg_toast.pg_toast_1255;
vacuum freeze analyze verbose pg_toast.pg_toast_2618;
vacuum freeze analyze verbose pg_toast.pg_toast_3596;
vacuum freeze analyze verbose pg_toast.pg_toast_2619;
vacuum freeze analyze verbose pg_toast.pg_toast_3381;
vacuum freeze analyze verbose pg_toast.pg_toast_3429;
vacuum freeze analyze verbose pg_toast.pg_toast_2620;
vacuum freeze analyze verbose pg_toast.pg_toast_3600;
vacuum freeze analyze verbose pg_toast.pg_toast_1247;
vacuum freeze analyze verbose pg_toast.pg_toast_1418;
vacuum freeze analyze verbose pg_toast.pg_toast_1260;
vacuum freeze analyze verbose pg_toast.pg_toast_1262;
vacuum freeze analyze verbose pg_toast.pg_toast_2964;
vacuum freeze analyze verbose pg_toast.pg_toast_1136;
vacuum freeze analyze verbose pg_toast.pg_toast_6000;
vacuum freeze analyze verbose pg_toast.pg_toast_2396;
vacuum freeze analyze verbose pg_toast.pg_toast_3592;
vacuum freeze analyze verbose pg_toast.pg_toast_6100;
vacuum freeze analyze verbose pg_toast.pg_toast_1213;
vacuum freeze analyze verbose pg_authid;
vacuum freeze analyze verbose pg_statistic_ext_data;
vacuum freeze analyze verbose pg_user_mapping;
vacuum freeze analyze verbose pg_subscription;
vacuum freeze analyze verbose pg_attribute;
vacuum freeze analyze verbose pg_proc;
vacuum freeze analyze verbose pg_class;
vacuum freeze analyze verbose pg_attrdef;
vacuum freeze analyze verbose pg_constraint;
vacuum freeze analyze verbose pg_inherits;
vacuum freeze analyze verbose pg_index;
vacuum freeze analyze verbose pg_operator;
vacuum freeze analyze verbose pg_opfamily;
vacuum freeze analyze verbose pg_opclass;
vacuum freeze analyze verbose pg_am;
vacuum freeze analyze verbose pg_amop;
vacuum freeze analyze verbose pg_amproc;
vacuum freeze analyze verbose pg_language;
vacuum freeze analyze verbose pg_largeobject_metadata;
vacuum freeze analyze verbose pg_aggregate;
vacuum freeze analyze verbose pg_largeobject;
vacuum freeze analyze verbose pg_statistic_ext;
vacuum freeze analyze verbose pg_rewrite;
vacuum freeze analyze verbose pg_trigger;
vacuum freeze analyze verbose pg_event_trigger;
vacuum freeze analyze verbose pg_description;
vacuum freeze analyze verbose pg_cast;
vacuum freeze analyze verbose pg_enum;
vacuum freeze analyze verbose pg_namespace;
vacuum freeze analyze verbose pg_conversion;
vacuum freeze analyze verbose pg_depend;
vacuum freeze analyze verbose pg_database;
vacuum freeze analyze verbose pg_db_role_setting;
vacuum freeze analyze verbose pg_tablespace;
vacuum freeze analyze verbose pg_pltemplate;
vacuum freeze analyze verbose pg_auth_members;
vacuum freeze analyze verbose pg_shdepend;
vacuum freeze analyze verbose pg_shdescription;
vacuum freeze analyze verbose pg_ts_config;
vacuum freeze analyze verbose pg_ts_config_map;
vacuum freeze analyze verbose pg_ts_dict;
[local:/data/run/pg12]:5120 pg12@testdb=#感謝各位的閱讀，以上就是“PostgreSQL中關(guān)于xid freeze的腳本有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)PostgreSQL中關(guān)于xid freeze的腳本有哪些這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼