简介
pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. pg_repack is efficient to boot, with performance comparable to using CLUSTER directly.
下载与安装
下载
https://pgxn.org/dist/pg_repack/
安装
- root用户下
unzip pg_repack-xx.zip
chown -R postgres.postgres pg_repack-xx
- postgres用户下
make && make instll
使用
- session1
drop table if exists test cascade;
create table test(id bigserial primary key, info text);
insert into test (info) select 'info' ||n from generate_series(1,10000) n;
begin;
update test set info='info_test' where id=1000;
- session2
/home/postgres/pgsql/bin/pg_repack -U postgres -d test -t test
- 查看死锁情况
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when query is null then 'NULL' else query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;
- 结果
locktype | datname | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | lock_conflict
----------+---------+----------+------+-------+------------+---------------+---------+-------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------
relation | test | test | | | | | | | | Pid: 42115 +
| | | | | | | | | | Lock_Granted: false , Mode: AccessExclusiveLock , FastPath: false , VirtualTransaction: 4/379 , Session_State: active +
| | | | | | | | | | Username: postgres , Database: test , Client_Addr: NULL , Client_Port: -1 , Application_Name: pg_repack +
| | | | | | | | | | Xact_Start: 2021-04-05 23:01:32.399257+08 , Query_Start: 2021-04-05 23:01:32.399997+08 , Xact_Elapse: 00:00:00.916549 , Query_Elapse: 00:00:00.915809+
| | | | | | | | | | SQL (Current SQL in Transaction): +
| | | | | | | | | | LOCK TABLE public.test IN ACCESS EXCLUSIVE MODE +
| | | | | | | | | | -------- +
| | | | | | | | | | Pid: 41946 +
| | | | | | | | | | Lock_Granted: true , Mode: RowExclusiveLock , FastPath: false , VirtualTransaction: 3/11295 , Session_State: idle in transaction +
| | | | | | | | | | Username: postgres , Database: test , Client_Addr: NULL , Client_Port: -1 , Application_Name: psql +
| | | | | | | | | | Xact_Start: 2021-04-05 23:00:59.074912+08 , Query_Start: 2021-04-05 23:01:00.851088+08 , Xact_Elapse: 00:00:34.240894 , Query_Elapse: 00:00:32.464718+
| | | | | | | | | | SQL (Current SQL in Transaction): +
| | | | | | | | | | update test set info='test' where id=1000;
(1 row)
出现死锁
- 查看进程
[postgres@node_205 ~]$ps -ef | grep postgres
postgres 18711 1 0 10:39 ? 00:00:07 /home/postgres/pgsql/bin/postgres -D /home/postgres/pgsql/data
postgres 18712 18711 0 10:39 ? 00:00:00 postgres: logger
postgres 18714 18711 0 10:39 ? 00:00:00 postgres: checkpointer
postgres 18715 18711 0 10:39 ? 00:00:22 postgres: background writer
postgres 18716 18711 0 10:39 ? 00:00:26 postgres: walwriter
postgres 18717 18711 0 10:39 ? 00:00:11 postgres: autovacuum launcher
postgres 18718 18711 0 10:39 ? 00:00:23 postgres: stats collector
postgres 18719 18711 0 10:39 ? 00:00:00 postgres: logical replication launcher
root 38192 130632 0 22:55 pts/1 00:00:00 su - postgres
postgres 38193 38192 0 22:55 pts/1 00:00:00 -bash
root 41834 35047 0 23:00 pts/2 00:00:00 su - postgres
postgres 41835 41834 0 23:00 pts/2 00:00:00 -bash
postgres 49423 38193 0 23:12 pts/1 00:00:00 psql -d test
postgres 49433 18711 0 23:12 ? 00:00:00 postgres: postgres test [local] idle in transaction
root 49616 24815 0 23:12 pts/7 00:00:00 /home/postgres/pgsql/bin/pg_repack -U postgres -d test -t test
postgres 49629 18711 0 23:12 ? 00:00:00 postgres: postgres test [local] LOCK TABLE waiting
postgres 49631 18711 0 23:12 ? 00:00:00 postgres: postgres test [local] idle
postgres 50152 41835 0 23:13 pts/2 00:00:00 ps -ef
postgres 50153 41835 0 23:13 pts/2 00:00:00 grep --color=auto postgres
root 120866 24920 0 18:16 pts/0 00:00:00 su - postgres
postgres 120867 120866 0 18:16 pts/0 00:00:00 -bash
postgres 130593 18711 0 21:58 ? 00:00:00 postgres: postgres test 192.168.0.106(49411) idle
[root@node_205 ~]# /home/postgres/pgsql/bin/pg_repack -U postgres -d test -t test
Password:
INFO: repacking table "public.test"
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
...
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: terminating conflicted backends
You have new mail in /var/spool/mail/root
经过60次 cancel 后台进程， 然后将其 terminate
如何避免此问题
添加 -D
- session1
test=# begin;
BEGIN
test=# update test set info='info_test' where id=1000;
UPDATE 1
test=#
- session2
[root@node_205 ~]# /home/postgres/pgsql/bin/pg_repack -U postgres -d test -t test -D
Password:
INFO: repacking table "public.test"
WARNING: timed out, do not cancel conflicting backends
INFO: Skipping repack public.test due to timeout
You have new mail in /var/spool/mail/root
