PostgreSQL 9.4 支持 background workers 后台进程动态注册，启动，停止， 这块工作是并行查询的基础， 从而实现大查询可以分布到多 cpu 上; 但是目前并不支持并行查询，只是支持动态注册，启动，停止后台进程，动态的意思是可以在数据库启动以后动态加载，不需要重启，下面简单演示。

手册中的说明

Allow background workers to be dynamically registered, started and terminated

创建 Worker_Spi 模块

1
2
3
4
5
6
7
8
9
10
11
12

[pg94@db1 ~]$ psql francs
psql (9.4beta1)
Type  "help"  for help.
  
francs=# create extension worker_spi ;
CREATE EXTENSION
  
francs=# /df worker_spi_launch
               List of functions
Schema |    Name    | Result data type | Argument data types | Type 
--------+-------------------+------------------+---------------------+--------
public | worker_spi_launch | integer     | integer       | normal

备注： worker_spi 模块现在可以以 extension 方式创建了, 创建后会生成 worker_spi_launch 函数，此函数用来动态注册后台进程。

注册后台进程

1
2
3
4
5
6
7
8
9
10
11

francs=# select worker_spi_launch(1);
worker_spi_launch
-------------------
      31047
(1 row)
  
francs=# select worker_spi_launch(2);
worker_spi_launch
-------------------
      31055
(1 row)

备注：函数返回的是后台进程号，可以从下面的输出验证。

验证后台进程

查看后台进程：

1
2
3
4

[pg94@db1 pg_root]$ ps -ef | grep bgworker
pg94 31047  29721 0  10:27  ? 00:00:00 postgres: bgworker: worker 1 
pg94 31055  29721 0  10:27  ? 00:00:00 postgres: bgworker: worker 2 
pg94 31058  30597 0  10:27 pts/1 00:00:00 grep bgworker

备注： 现在可以看到两个 bgworker 进程，想试下是否支持并行查询，编写一个代码断，让它不停地运行。

测试代码断

1
2
3
4
5
6
7
8
9
10

do $$
declare
i int4;
begin
 i:=0;
 while i<  1 loop
 perform *  from test_1 order by id desc limit 100;
 end loop;
end;
$$ language 'plpgsql';

备注：开启一个会话执行上面的代码断.

观察数据库 cpu 使用情况，top 输出如下：

备注：结果还是在用一个 cpu 核， 而没有将负载分担到其它 cpu 核上，这里纯属 yy。

调高主库的 max_worker_processes 设置后，从库的日志如下

1
2
3
4

2014-05-22  12:58:33.176 PDT,,,6547,,537e569c.1993,7,,2014-05-22  12:57:16 PDT,1/0,0,FATAL,22023,"hot standby is not possible because max_worker_processes = 10 is a lower setting than on the master server (its value was 12)",,,,,"xlog redo parameter change: max_connections=500 max_worker_processes=12 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=hot_standby",,,,""
2014-05-22  12:58:33.177 PDT,,,6545,,537e569c.1991,3,,2014-05-22  12:57:16 PDT,,0,LOG,00000,"startup process (PID 6547) exited with exit code 1",,,,,,,,,""
2014-05-22  12:58:33.177 PDT,,,6545,,537e569c.1991,4,,2014-05-22  12:57:16 PDT,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
2014-05-22  12:58:33.177 PDT,"postgres","postgres",6563,"[local]",537e56b0.19a3,1,"idle",2014-05-22  12:57:36 PDT,2/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"psql"

备注：将主库的 max_worker_processes 调整成 12 并重启主库，之后从库宕机并报以上错误，解决方法： 这时可能需要重做从库了。

调低从库的 max_worker_processes 设置后，从库重启的日志如下

1
2

2014-05-22  12:51:22.027 PDT,,,6443,,537e553a.192b,3,,2014-05-22  12:51:22 PDT,,0,FATAL,22023,"hot standby is not possible because max_worker_proces
ses = 9 is a lower setting than on the master server (its value was 10)",,,,,,,,,""

备注： 将从库的 max_worker_processes调整成 9 并重启从库，这时从库日志报以上错误; 解决方法： 只需要将从库的这个参数值调成比主库大或者相等，之后重启从库即可。

注意事项

1. 后台进程的最大数由参数 max_worker_processes 限制，默认值为 8，修改后需重启生效。
2. 流复制环境从库的 max_worker_processes 设置值必须大于或等于主库设置的值，否则从库不可查询，下面分别针对两种情形做了测试: 初始状态主库和从库的 max_worker_processes 参数值设置成 10.。

关于 max_worker_processes (integer) 参数

Sets the maximum number of background processes that the system can support. This parameter can only be set at server start. When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server.

参考

• Postgres 9.4 feature highlight: dynamic background workers
• Background Worker Processes