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

[Tatsuo Ishii]

I have created new blog entry for this.

https://pgsqlpgpool.blogspot.com/2022/11/dynamic-spare-process-management-in.html

> 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(-)
>> 
> _______________________________________________
> pgpool-hackers mailing list
> pgpool-hackers at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-hackers