【PostgreSQL】PostgreSQL零宕机变更schema：lock_timeout和retries(设置超时和重试)
2022-06-11 08:59 abce 阅读(870) 评论(0) 编辑 收藏 举报
问题描述
当你部署数据库schema变更时，即使有非常高级别的自动化，但是没有使用非常地的lock_timeout（或 statement_timeout）值来获取对可能发生变化且不实现某种重试逻辑的数据库对象进行变更时，也无法保证系统不会宕机。
创建一张只有单行记录的表作为演示：
create table test as select 1 as i;
接下来，我们需要打开三个psql终端：
1.第一个，开启事务(测试的事务隔离级别是已提交读)，读取表中的数据，保持会话处于"idle in transaction"状态：
begin; select * from test;
2.第二个，我们会尝试部署一些DDL操作。例如，增加not null约束(显然这个操作会被阻塞，等待上一个select事务结束：提交或者回滚)：
alter table test alter column i set not null;
3.最后，第三个psql终端，我们再次尝试读数据：
select * from test;
\timing on
set statement_timeout to '400ms';
with recursive activity as (
select
pg_blocking_pids(pid) blocked_by,
*,
age(clock_timestamp(), xact_start)::interval(0) as tx_age,
age(clock_timestamp(), state_change)::interval(0) as state_age
from pg_stat_activity
where state is distinct from 'idle'
), blockers as (
select
array_agg(distinct c order by c) as pids
from (
select unnest(blocked_by)
from activity
) as dt(c)
), tree as (
select
activity.*,
1 as level,
activity.pid as top_blocker_pid,
array[activity.pid] as path,
array[activity.pid]::int[] as all_blockers_above
from activity, blockers
where
array[pid] <@ blockers.pids
and blocked_by = '{}'::int[]
union all
select
activity.*,
tree.level + 1 as level,
tree.top_blocker_pid,
path || array[activity.pid] as path,
tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above
from activity, tree
where
not array[activity.pid] <@ tree.all_blockers_above
and activity.blocked_by <> '{}'::int[]
and activity.blocked_by <@ tree.all_blockers_above
)
select
pid,
blocked_by,
tx_age,
state_age,
replace(state, 'idle in transaction', 'idletx') state,
datname,
usename,
format('%s:%s', wait_event_type, wait_event) as wait,
(select count(distinct t1.pid) from tree t1 where array[tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd,
format(
'%s %s%s',
lpad('[' || pid::text || ']', 7, ' '),
repeat('.', level - 1) || case when level > 1 then ' ' end,
query
)
from tree
order by top_blocker_pid, level, pid
\watch 1
可以看到类似这样的结果：
pid | blocked_by | tx_age | state_age | state | datname | usename | wait | blkd | format
-----+------------+----------+-----------+--------+----------+----------+-------------------+------+---------------------------------------------------------
48 | {} | 14:26:52 | 14:26:50 | idletx | postgres | postgres | Client:ClientRead | 2 | [48] select * from test;
78 | {48} | 14:26:43 | 14:26:43 | active | postgres | postgres | Lock:relation | 1 | [78] . alter table test alter column i set not null;
224 | {78} | 14:26:40 | 14:26:40 | active | postgres | postgres | Lock:relation | 0 | [224] .. select * from test;
(3 rows)
我们遇到了什么问题？我们的DDL无法在表上获得锁。它在等待其它事务完成。有趣的是，事务(pid=48)没有修改数据，在表上持有了一个access share锁，通过pg_locks可以看出来：
=# select locktype, relation, virtualxid, transactionid, virtualtransaction, pid, mode, granted from pg_locks where pid = 48; locktype | relation | virtualxid | transactionid | virtualtransaction | pid | mode | granted ------------+----------+------------+---------------+--------------------+-----+-----------------+--------- virtualxid | | 3/11 | | 3/11 | 48 | ExclusiveLock | t relation | 24869 | | | 3/11 | 48 | AccessShareLock | t (2 rows)
DDL需要在表上施加access exclusive锁，它与access share锁冲突了。可以看下图：
pid | blocked_by | tx_age | state_age | state | datname | usename | wait | blkd | format
-----+------------+----------+-----------+--------+----------+----------+-------------------+------+---------------------------------------------------------
224 | {78} | 14:26:40 | 14:26:40 | active | postgres | postgres | Lock:relation | 0 | [224] .. select * from test;
(3 rows)
如果你在繁忙的库上执行了DDL，如果没有使用严格的超时设置来保护，某天你可能会发现你的系统宕机了。如何想避免该问题的，都要施加超时保护，这个保护可以通过超时设置来实现(要么lock_timeout、要么statement_timeout)
优雅的schema变更：lock_timeout和retries
set statement_timeout = '50ms';
2.现在既然DDL可能会失败，就增加尝试的机会
while true; do date psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break sleep 1 done
当然，某些情况下，我们需要允许我们的DDL执行时间超过50ms，在这种场景下，我们可以显式地在表上加锁(lock table ...)，然后修改statement_timeout的值为0，或者：
begin;
set statement_timeout = 50;
lock table only test in ACCESS EXCLUSIVE MODE;
set statement_timeout = 0;
alter table test ....;
-- do whatever you want, timeout is removed.
commit;
从9.3开始，我们可以使用lock_timeout代替statement_timeout，lock_timeout只会影响我们尝试获得的锁，相当方便。
如果我们需要将多个DDL合并到一个事务中呢？这一点，PostgreSQL很强大，支持将任何DDL放到一个事务内执行，实现原子性。但是，在这种场景下，如果我们成功的完成了一个操作，但是下一个ddl没有获得锁，意味着我们整个事务要回滚。
可以通过以下方式来改进这一点：
·在事务的开通，获取所有的锁，显式加锁。如果不能获取任何一个，事务就失败，重启尝试。这样可以避免回滚操作发生，以下是示例：
begin; set local lock_timeout to '50ms'; lock table only t1 in access exclusive mode; lock table only t2 in access exclusive mode; alter table t1 ...; alter table t2 ...; commit;
显式地使用关键子savepoint、或者在pl/pgsql中使用语句块begin/exception when ../end，
DO
$do$
DECLARE
lock_timeout CONSTANT text := '50ms';
max_attempts CONSTANT INT := 1000;
ddl_completed BOOLEAN := FALSE;
BEGIN
PERFORM set_config('lock_timeout', lock_timeout, FALSE);
FOR i IN 1..max_attempts LOOP
BEGIN
EXECUTE 'ALTER TABLE test add column whatever2 INT4';
ddl_completed := TRUE;
EXIT;
EXCEPTION
WHEN lock_not_available THEN
NULL;
END;
END LOOP;
IF ddl_completed THEN
RAISE INFO 'DDL has been successfully completed';
ELSE
RAISE EXCEPTION 'Failed to perform DDL';
END IF;
END
$do$;
子事务可能存在的风险
有些情况下，子事务可能会导致性能下降。简单点说，即使很小的事务id(xid，32位的事务id)被分配给未完成的子事务，伴随着很长时间的主事务，可能会导致standby发生灾难性的性能降级。
test=# create table test(i int8);
CREATE TABLE
test=# select pg_current_xact_id(); -- txid_current() in older PG versions
pg_current_xact_id
--------------------
test=# drop table test;
DROP TABLE
test=# create table test(i int8);
CREATE TABLE
test=# select pg_current_xact_id(); -- txid_current() in older PG versions
pg_current_xact_id
--------------------
1551685670
(1 row)
test=# begin;
BEGIN
test=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
1551685671
(1 row)
test=*# savepoint s1;
SAVEPOINT
test=*# insert into test select 1;
INSERT 0 1
test=*# rollback to s1;
ROLLBACK
test=*# insert into test select 2;
INSERT 0 1
test=*# rollback to s1;
ROLLBACK
test=*# insert into test select 3;
INSERT 0 1
test=*# commit;
COMMIT
test=# select pg_current_xact_id();
pg_current_xact_id
--------------------
1551685675
(1 row)
可以看到，全局的xid值在我们的事务期间增长超过1，子事务每次都执行回滚操作，都会分配一个新的xid。让我们来检查一下隐藏列xmin，使用扩展pageinspect，还可以看到死的元组：
test=# select xmin, * from test;
| i
------------+---
1551685674 | 3
(1 row)
test=# create extension pageinspect;
CREATE EXTENSION
test=# select lp, t_xmin, t_xmax, t_ctid, t_data from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | t_data
----+------------+--------+--------+--------------------
1 | 1551685672 | 0 | (0,1) | \x0100000000000000
2 | 1551685673 | 0 | (0,2) | \x0200000000000000
3 | 1551685674 | 0 | (0,3) | \x0300000000000000
(3 rows)
索引，使用子事务的分线是，即使我们只有一个保存点，也会使用多个xid。不过只会对tps比较的系统才会造成影响，跟环境有关。
基于子事务多次尝试的另一个副作用是，相比整个事务尝试，前者对autovacuum清理死的元组影响更大。
总结一下,潜在的弊端是：1.浪费xid；2.影响autovacuum
话虽如此，我不能说我会建议每个人都完全不使用子事务。在部署DDL时，我仍然发现部分回滚以重试的想法有用且可行。但是，例如，如果你有一个系统10,000 TPS 和长时间运行的事务（例如，1 分钟或更长时间）的风险——我肯定会非常小心使用子事务。如果你有 PostgreSQL 13+，好消息是pg_stat_slru会很有帮助——如果你在standby上看到读Subtrans SLRU，这是一个强烈的信号，表明SLRU已溢出，并且standby服务器的性能下降（如果有的话）是由它引起的。
假设我们有一个只读负载库，且所有的读都是对test表，时间都是亚毫秒级别的。也就是说，平均是0.1ms。我们打开一个事务，执行对test表的select，并将该事务处于“idle in transaction”状态。现在我们开始做一些DDL操作，并将lock_time设置为50ms。对平均亚秒级别的只读查询会有什么影响呢？会被阻止50毫秒。
但是，如果我们每次尝试都加个delay呢？比如说，1秒，这样就只会有部分select被阻塞。
但是，如果我们在每次尝试后添加延迟(例如1秒),则只有部分SELECT会受到影响。粗略地说，我们将每秒引入一次“有压力的”锁定获取尝试，时间仅为50毫秒(更准确地说，这将每约1050毫秒发生一次)所以大部分SELECT根本不会受到影响，它们的平均持续时间只会略有上升。
这些延迟也有助于我们增加成功的机会。我们有有限的尝试次数，之后我们将事务中止，因此我们声明部署失败并将其推迟一段时间（或立即重复 - 取决于我们定义的策略）。
·对于负载高、或者有长时间运行的事务的系统，要谨慎执行DDL。建议使用一个较低的lock_timeout设置和重新尝试来优雅地执行DDL变更。
·对于子事务要小心使用。子事务会非常有用，但是：
--可能会突然引起性能问题。如果你使用了子事务，且是PostgreSQL13或更新的版本，可以使用pg_stat_slru来监控系统，并观测subtrans slru读
