[pgpool-hackers: 4225] Re: [pgpool-committers: 8859] pgpool: [New-Feature] Dynamic spare process management

[Tatsuo Ishii]

Hi Usama,

Thank you for the commit. I have tested the performance aspect of the
feature and observed over 5 times improvement in certain test case. I
ran pgbench with following parameters:

pgbench -S -n -C -c 10 -T 30 test

- the tested SQL is read only.
- creates connection to pgpool each time when a transaction starts.
- number of concurrent connections is 10.
- benchmark duration is 30 seconds.

important parameter with pgpool and PostgreSQL:
num_init_children = 900
max_connections = 1000

The pgpool cluster is two-node async streaming replication cluster
created by pgpool_setup.

I ran pgbench 3 times for each case and memorize median number.

1) process_management_mode = dynamic

TPS = 2410.531473

2) process_management_mode = static

TPS = 415.991514

As you can see when process_management_mode = dynamic, the TPS
(transations per second) is over 5 times larger than the case when
process_management_mode is static. I think this is a great
improvement. Below is my theory why the features gives such
difference:

When num_init_children is large (for example 900) and number of
concurrent connections made to pgpool is relatively small (for example
10), then the OS wakes up all of the child process and it will
wastefully consume CPU resources. With the dynamic spare process
management, number of child process involved is limited to relatively
small number.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> [New-Feature] Dynamic spare process management
> 
> This feature allows selecting between static and dynamic process management modes.
> Static process management is the same as the existing behavior of Pgpool-II, where it
> spawns all child processes at startup. The new Dynamic mode keeps track of idle
> processes and forks or kills processes to keep this number within
> the specified boundaries.
> 
> Four new settings, process_management_mode,  process_management_strategy,
> min_spare_children, and max_spare_children are added to configure the process
> management behavior, while  process_management_strategy allows selecting
> between three possible scaling-down strategies.
> 
> The first version of the patch was shared by "zhoujianshen at highgo.com" and reworked by me
> Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2022-September/004189.html
> Reviewed by: Bo Peng and Tatsuo Ishii
> 
> Branch
> ------
> master
> 
> Details
> -------
> https://git.postgresql.org/gitweb?p=pgpool2.git;a=commitdiff;h=ce5e529c5bd9f19e3a23f92f7832f361ae361c48
> 
> Modified Files
> --------------
> doc/src/sgml/connection-settings.sgml       | 159 +++++++++++++++++++
> doc/src/sgml/runtime.sgml                   |  33 ++++
> src/auth/pool_auth.c                        |   3 +-
> src/config/pool_config_variables.c          |  68 +++++++-
> src/context/pool_process_context.c          |  25 +--
> src/include/main/pool_internal_comms.h      |   1 -
> src/include/pcp/libpcp_ext.h                |   8 +-
> src/include/pool.h                          |   1 +
> src/include/pool_config.h                   |  20 ++-
> src/include/protocol/pool_connection_pool.h |   1 +
> src/main/pgpool_main.c                      | 236 +++++++++++++++++++++++++++-
> src/protocol/child.c                        |  31 +++-
> src/protocol/pool_connection_pool.c         |  15 +-
> src/sample/pgpool.conf.sample-stream        |  38 ++++-
> src/tools/pcp/pcp_frontend_client.c         |   4 +-
> src/utils/pool_process_reporting.c          |  25 ++-
> src/watchdog/wd_json_data.c                 |  18 ++-
> 17 files changed, 648 insertions(+), 38 deletions(-)
>