手册说明

Allow reindexdb to operate in parallel (Julien Rouhaud)

Parallel mode is enabled with the new –jobs option.

关于-j选项

reindexdb命令的-j选项，手册说明如下:

-j njobs
--jobs=njobs
Execute the reindex commands in parallel by running njobs commands simultaneously. This option reduces the time of the processing but it also increases the load on the database server.

reindexdb will open njobs connections to the database, so make sure your max_connections setting is high enough to accommodate all connections.

Note that this option is incompatible with the --index and --system options.

Reindexdb测试

测试环境为笔记本的的一台4C/1GB虚机，计划对mydb库进行全库索引重建，并行度分别为1、2、3、4。

为测试数据相对准确，首先对mydb库做一次全库索引重建，如下:

[pg13@ydtf01 ~]$ time reindexdb -h 127.0.0.1 -p 1922 -U postgres mydb

real    0m47.914s
user    0m0.001s
sys     0m0.008s

将并行度-j分别设置为1、2、3、4进行索引重建，如下:

[pg13@ydtf01 ~]$ time reindexdb -h 127.0.0.1 -p 1922 -U postgres mydb

real    0m39.287s
user    0m0.002s
sys     0m0.005s

[pg13@ydtf01 ~]$ time reindexdb -h 127.0.0.1 -p 1922 -U postgres -j 2 mydb
NOTICE:  table "tbl_log" has no indexes to reindex

real    0m29.080s
user    0m0.002s
sys     0m0.018s

[pg13@ydtf01 ~]$ time reindexdb -h 127.0.0.1 -p 1922 -U postgres -j 3 mydb
NOTICE:  table "tbl_log" has no indexes to reindex

real    0m29.886s
user    0m0.007s
sys     0m0.021s

[pg13@ydtf01 ~]$ time reindexdb -h 127.0.0.1 -p 1922 -U postgres -j 4 mydb
NOTICE:  table "tbl_log" has no indexes to reindex

real    0m38.539s
user    0m0.003s
sys     0m0.028s

time命令的输出取real时间，以上索引重建时间汇总如下:

命令	并行度	执行时间
reindexdb	1	39.287s
reindexdb	2	29.080s
reindexdb	3	29.886s
reindexdb	4	38.539s

当并行度为4时，可看到系统层面有4个并行REINDEX进程，如下:

以上看出，当并行度为2时执行时间最短，说明并不是将并行度设置成设备的CPU逻辑核数时效率最高，由于硬件环境为笔记本虚机，测试数据仅供参考。

同时，pg_stat_activity视图可查询到多个REINDEX TABLE命令，如下:

mydb=# SELECT pid,query,state FROM pg_stat_activity where state='active' and usename<>'repuser' and pid<>pg_backend_pid();
  pid  |                 query                  | state
-------+----------------------------------------+--------
 36117 | REINDEX TABLE pguser.user_reg_log_his; | active
 36116 | REINDEX TABLE pguser.uuid_01;          | active
 36115 | REINDEX TABLE pguser.user1;            | active
 36114 | REINDEX TABLE pguser.big;              | active
 36113 | REINDEX TABLE pguser.uuid_01;          | active
 36112 | REINDEX TABLE pguser.user_reg_log_his; | active
 36111 | REINDEX TABLE pguser.user1;            | active
 36110 | REINDEX TABLE pguser.big;              | active
(8 rows)

总结

尽管reindexdb命令在生产运维过程中并不常用，了解下reindexdb命令的-j选项对PostgreSQL的运维工作是有帮助的。

reindexdb命令设置-j选项后给数据库主机带来较大压力，需根据服务器配置设置并行度，并在业务低谷期操作。