《PostgreSQL的"天气预报"》由会员分享，可在线阅读，更多相关《PostgreSQL的"天气预报"（6页珍藏版）》请在金锄头文库上搜索。
1、WORD（可编辑版本）PostgreSQL的"天气预报" 还记得我写的这篇文档吗？ PostgreSQL 大表自动 freeze 优化思路 文章主要针对如何优化大表的freeze调度来削减IO风暴的疑问，请留意只不过是削减，不是避开。 作为一名有追求的PGer，要时刻保持警惕，生于忧患、死于安乐； 本文要给各位讲的是猜测风暴，把握了猜测力量，才能未雨绸缪，淡定的面对暴风雨。 猜测的结论包括 将来一段时候的总Freeze IO量，以及分时的Freeze IO量。 猜测结果范例Freeze IO 时段总量Freeze IO 分时走势 采集1天的数据大概是这么样的 postgre
2、s=# select * from xids ; crt_time | xids -+- 2021-06-12 12:36:13.202115 | 2021 2021-06-12 12:37:13.216002 | 9021 2021-06-12 12:38:13.240739 | 21022 2021-06-12 12:39:13.259203 | 32023 2021-06-12 12:40:13.300604 | 42024 2021-06-12 12:41:13.325874 | 52025 2021-06-12 12:42:13.361152 | 62026 2021-06-12 1
3、2:43:15.481609 | 72027. .2. 表的大小以及距离它需要被强制vacuum freeze prevent wrap的年龄 (由于freeze是全集群的，因此需要把全部库得到的数据汇总到一块) vi pred_io.sh#!/bin/bashexport PATH=/home/digoal/pgsql9.5/bin:$PATHexport PGHOST=127.0.0.1export PGPORT=1921export PGDATABASE=postgresexport PGUSER=postgresexport PGPASSWORD=postgrespsql -c dro
4、p table pred_io; create table pred_io(crt_time timestamp, bytes int8, left_live int8);for db in psql -A -t -q -c select datname from pg_database where datname template0dopsql -d $db -c copy (select now(), bytes, case when max_ageage then max_age-age else 0 end as xids from (select block_size*relpage
5、s bytes, case when d_max_age is not null and d_max_agemax_age then d_max_age else max_age end as max_age,age from(select (select setting from pg_settings where name=block_size):int8 as block_size, (select setting from pg_settings where name=autovacuum_freeze_max_age):int8 as max_age, relpages, subst
6、ring(reloptions:text,autovacuum_freeze_max_age=(d+):int8 as d_max_age,age(relfrozenxid) agefrom pg_class where relkind in (r, t) t) t) to stdout; | psql -d $PGDATABASE -c copy pred_io from stdindone. ./pred_io.sh 得到的数据大概是这么样的 postgres=# select * from pred_io limit 10; crt_time | bytes | left_live -+
7、-+- 2021-06-12 13:24:08.666995 | 131072 | 199999672 2021-06-12 13:24:08.666995 | 65536 | 199999672 2021-06-12 13:24:08.666995 | 0 | 199999672 2021-06-12 13:24:08.666995 | 0 | 199999672 2021-06-12 13:24:08.666995 | 0 | 199999672 2021-06-12 13:24:08.666995 | 0 | 199999672. .3. 猜测XIDs走势(略)，本文直接取昨天的同一时候
8、点开头后的数据。 create view v_pred_xids as with b as (select min(crt_time) tbase from pred_io), a as (select crt_time + interval 1 day as crt_time, xids from xids,b where crt_time = b.tbase - interval 1 day) select crt_time, xids - (select min(xids) from a) as xids from a ; 数据大概是这么样的，猜测将来分时的相对XIDs消耗量 crt_t
9、ime | xids -+- 2021-06-13 12:36:13.202115 | 0 2021-06-13 12:37:13.216002 | 100 2021-06-13 12:38:13.240739 | 200 2021-06-13 12:39:13.259203 | 300 2021-06-13 12:40:13.300604 | 400 .4. 结合pred_io与v_pred_xids 进行 io风暴猜测基准视图，后面的数据通过这一个基准视图得到 create view pred_tbased_io aswith a as (select crt_time, xids as
10、s_xids, lead(xids) over(order by crt_time) as e_xids from v_pred_xids)select a.crt_time, sum(b.bytes) bytes from a, pred_io b where b.left_live =a.s_xids and b.left_live a.e_xids group by a.crt_time order by a.crt_time; 将来一天的总freeze io bytes猜测 postgres=# select min(crt_time),max(crt_time),sum(bytes)
11、 from pred_tbased_io ; min | max | sum -+-+- 2021-06-13 12:36:13.202115 | 2021-06-14 12:35:26.104025 | 19685376(1 row) 将来一天的freeze io bytes分时走势 得到的结果大概是这么样的 postgres=# select * from pred_tbased_io ; crt_time | bytes -+- 2021-06-13 12:36:13.202115 | 65536 2021-06-13 12:37:13.216002 | 581632 2021-06-1
12、3 12:38:13.240739 | 0 2021-06-13 12:39:13.259203 | 0 2021-06-13 12:40:13.300604 | 0 2021-06-13 12:41:13.325874 | 0 2021-06-13 12:43:15.481609 | 106496 2021-06-13 12:43:24.133055 | 8192 2021-06-13 12:45:24.193318 | 0 2021-06-13 12:46:24.225559 | 16384 2021-06-13 12:48:24.296223 | 13434880 2021-06-13 12:49:24.325652 | 24576 2021-06-13 12:50:24.367232 | 401408 2021-06-13 12:51:24.426199 | 0 2021-06-13 12:52:24.457375 | 393216. 小结 主要用到什么? .1. 线性回归.2. with语法.3. 窗口函数.4. xid分时消耗统计.5. 强制prevent wrap freeze vacuum的剩余XIDs统计 6
