PostgreSQL9.5:Parallel VACUUMing

9.5 版本的 vacuumdb 支持并行功能,类似 pg_dump 和 pg_restore 的 -j 参数,做整库 vacuum 时能提高速度,同时资源消耗也会大些,当然之前版也可以通过其它方法实现并行 vacuum 功能,例如开启多个 vacuum 脚本同时 vacuum 不同的表。本文简单演示下 vacuumdb 并行。

并行 Vaccuum

1
vacuumdb -j4 dbname

环境准备

创建一批测试表

1
2
3
4
for a in  {10..19}  
do
psql fdb fdb -c "create table test_$a as select n ,random() from generate_series(1, 1000000) n;"
done

查看创建的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[pg95@db2 tf]$ psql fdb fdb  
psql (9.5alpha1)
Type "help" for help.

fdb=> \dt+ test_*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------+-------+-------+-------+-------------
fdb | test_10 | table | fdb | 19 MB |
fdb | test_11 | table | fdb | 19 MB |
fdb | test_12 | table | fdb | 19 MB |
fdb | test_13 | table | fdb | 19 MB |
fdb | test_14 | table | fdb | 19 MB |
fdb | test_15 | table | fdb | 19 MB |
fdb | test_16 | table | fdb | 19 MB |
fdb | test_17 | table | fdb | 19 MB |
fdb | test_18 | table | fdb | 19 MB |
fdb | test_19 | table | fdb | 19 MB |
(10 rows)

开启并行 Vacuumdb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[pg95@db2 tf]$ vacuumdb -e -j4 fdb  
vacuumdb: vacuuming database "fdb"
SELECT c.relname, ns.nspname FROM pg_class c, pg_namespace ns
WHERE relkind IN ('r', 'm') AND c.relnamespace = ns.oid
ORDER BY c.relpages DESC;
VACUUM fdb.test_10;
VACUUM fdb.test_11;
VACUUM fdb.test_12;
VACUUM fdb.test_13;
VACUUM fdb.test_14;
VACUUM fdb.test_15;
VACUUM fdb.test_16;
VACUUM fdb.test_17;
VACUUM fdb.test_18;
VACUUM fdb.test_19;
...

备注:开启 4 个并行 vacuum 进程,输出中有一段 SQL,取出的表列表根据表大小排序。

查看 Vacuum 进程

1
2
3
4
5
[pg95@db2 pg95]$ ps -ef | grep VACUUM  
pg95 25144 17848 2 11:58 ? 00:00:00 postgres: postgres fdb [local] VACUUM
pg95 25145 17848 1 11:58 ? 00:00:00 postgres: postgres fdb [local] VACUUM
pg95 25146 17848 2 11:58 ? 00:00:00 postgres: postgres fdb [local] VACUUM
pg95 25147 17848 2 11:58 ? 00:00:00 postgres: postgres fdb [local] VACUUM

查看 Vacuum 会话

1
2
3
4
5
6
7
8
[pg95@db2 pg95]$ psql fdb -c "select pid, datname,usename,query_start,client_addr,query ,waiting from pg_stat_activity where state='active' and pid <> pg_backend_pid() order by pid;"  
pid | datname | usename |query_start | client_addr |query | waiting
-------+---------+----------+-------------------------------+-------------+---------------------+---------
25144 | fdb | postgres | 2015-08-09 11:58:05.160335+08 | | VACUUM fdb.test_12; | f
25145 | fdb | postgres | 2015-08-09 11:58:34.790037+08 | | VACUUM fdb.test_14; | f
25146 | fdb | postgres | 2015-08-09 11:58:34.790115+08 | | VACUUM fdb.test_16; | f
25147 | fdb | postgres | 2015-08-09 11:58:51.504252+08 | | VACUUM fdb.test_19; | f
(4 rows)

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%