Dynamic spare process management in Pgpool-II

Pre-fork architecture in Pgpool-II

Pgpool-II uses fixed number of pre-forked child process which is responsible for accepting and handling each client connection to Pgpool-II. When a client tries to connect to Pgpool-II, one of pre-forked child process (the number of child process is defined by num_init_children parameter) accepts the request and takes care of the connection.

One of the advantages of this method is avoiding the overhead of spawning process each time when a new connection is requested. However, when the number of pre-forked process is large like over 1,000 and concurrent connections to Pgpool-II is relatively small, most the pre-forked process just wakes up when new connection arrives and wastefully consumes CPU resource.

Dynamic spare process management in Pgpool-II 4.4.

To overcome the issue, "Dynamic spare process management" has been implemented for upcoming Pgpool-II 4.4 (Thanks to Jianshen Zho and Muhammad Usama for implementing this feature). Following new parameters are introduced:

• process_management_mode
• process_management_strategy
• min_spare_children
• max_spare_children

 If process_management_mode is 'dynamic', the new feature is enabled. If it is set to 'static', the process management is compatible with pre-4.4 style. The default is 'static'.

With process_management_mode is 'dynamic', Pgpool-II dynamically increases or decreases the number of child process according to the demand. The demand is judged by counting number of 'spare' child process. Here, 'spare' means process state which is just waiting for connections from clients. The idea is, dynamically adjusting the number of child process according to the number of spare process.

process_management_strategy can control the aggressiveness of decreasing the number of spare children by choosing one of 'lazy', 'gentle' and 'aggressive'. If 'lazy', it takes 5 minutes before decreasing the spare process. If 'gentle', it takes 2 minutes. Finally if 'aggressive', 50 seconds. The default is 'gentle'. 

min_spare_children and max_spare_children controls when pgpool increases the number of spare process. If spare process is less than min_spare_process, pgpool will spawn new process until reaching min_spare_children + 25% of max_spare_children. The defaults for min_spare_children and max_spare_children are 5 and 10 respectively.

The benchmark

Ok, so how the feature improves performance?

I did a benchmarking using standard pgbench on a Laptop machine running Ubuntu 20 with 16GB memory and an SSD storage. I used "-C" option of pgbench because the feature should improve the performance while clients connecting to Pgpool-II. If the option is not used, pgbench keeps the connection to Pgpool-II which does not reveal the effect of the feature. The transaction type was read only SELECT (-S) and the number of concurrent connections is 10, which should be small enough comparing with num_init_children = 900. max_connections parameter was 1,000 for each PostgreSQL 15 (I created the environment using pgpool_setup tool to create a 2-node streaming replication cluster). I just changed the process_management_mode parameter to 'dynamic' and 'static' but other parameters were left to the default. I ran pgbench 3 times each with duration 30 seconds and took the median number.

The result

The result was dramatic: I see nearly 5 times speed up with process_management_mode parameter is 'dynamic' comparing with the case when it was 'static'.

As you can see, when the process_management_mode is 'dynamic', the TPS (transactions per second) is nearly 5 times larger than the case when it's 'static'.

Conclusion

New feature "dynamic spare process management" improves the connection performance when concurrent connections to Pgpool-II is small comparing with num_init_children. We had to set num_init_children to up to the highest demand of connection request but this sacrifices performance when the concurrent connection demand is low. With the new feature, we don't need to make a compromise.