9.5 版本的 vacuumdb 支持并行功能，类似 pg_dump 和 pg_restore 的 -j 参数，做整库 vacuum 时能提高速度，同时资源消耗也会大些，当然之前版也可以通过其它方法实现并行 vacuum 功能，例如开启多个 vacuum 脚本同时 vacuum 不同的表。本文简单演示下 vacuumdb 并行。
并行 Vaccuum
环境准备
创建一批测试表
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)
|
参考
原创文章，作者：bd101bd101，如若转载，请注明出处：https://blog.ytso.com/239637.html
