[pgpool-hackers: 4496] Introducing Global Connection Pooling Feature for Pgpool-II

[Muhammad Usama]

Hi Pgpool-II Community,

I've been working on global connection pooling for Pgpool-II.
Traditionally, Pgpool-II supports local connection pooling, where each
child process maintains its own connection pool. However, this approach has
several limitations:

-- Inefficiency in Connection Utilization: There are always (max_pool - 1)
* num_init_children pooled connections that remain unusable. This
inefficiency requires setting a higher number for max_connections on
PostgreSQL than needed.
-- Random Child Process Selection: Since child process selection for new
client connections is random, we can't ensure that a child with a
particular backend connection gets the incoming connection. This often
results in existing pooled connections being dropped and new ones created,
especially with multiple DB-USER pairs.
-- Limited Pooling Modes: The current architecture restricts connection
pooling to session pooling only.
-- Backend Connection Requirement: The per-child connection pool
architecture requires more backend connections than the application needs,
countering the purpose of a connection pooler.

I've added a new global connection pooling option to Pgpool-II to address
these issues.

Global Connection Pooling

In Pgpool-II, global connection pooling means the main process maintains
the entire connection pool instead of individual child processes. Pooled
connections are leased to child processes as needed. This approach
addresses the shortcomings mentioned above and enhances Pgpool-II's
connection pooling capabilities, offering features like load balancing and
failover.

Technical Challenges

Implementing global connection pooling in Pgpool-II's multi-process
architecture required significant technical effort, particularly in
managing backend sockets between Pgpool children. We also ensured minimal
performance impact when pooled processes were acquired or released.

Benefits

-- Reduced max_connections Requirement: We need fewer max_connections on
PostgreSQL side to handle the same number of application connections,
improving overall performance.
-- Elimination of Wasted Connections: No more unusable pooled connections.
-- Efficient Connection Utilization: Pooled connections are leased to child
processes with precise backend connection requirements.
-- Fine-Grained Control: We can set maximum connections for specific
DB-USER pairs and implement separate bucket sizes.
-- Support for Transaction Pooling: The new architecture allows
implementing transaction pooling modes.

Architecture Notes

New Configuration Parameters:

-- connection_pool_type: Allows selection between classic and global
connection pool strategies for Pgpool-II.
-- max_pool_size: Configures the size of the global connection pool.

Internals

-- Main Process Pool Management: The Pgpool-II main process maintains the
global connection pool in shared memory and leases connections to child
processes upon request.
-- Unix Domain Sockets: The main process uses Unix domain sockets to
receive client requests and transfer backend sockets to and from child
processes.
-- Backend Connection Handling: The main process never creates or
terminates backend connections, ensuring it never blocks and continues
serving children's requests. When a child process leases an empty pool
slot, it connects and authenticates the backend and returns it to the main
pool after use. Thus, the child processes handle all backend communication,
while the main process manages the registry and connected sockets.
-- Efficient Data Transfer: Only sockets are transferred to child processes
through IPC sockets, while other connection data is read and written
directly by the child process in shared memory. This speeds up the lease
and return operations.
-- Minimal Locking: Since the main process moderates pooled connections, no
locks are required on the connection pool.
-- Failover Handling: In the event of a failover, the main process marks
affected connections as need_cleanup. The actual cleanup occurs when the
pooled connection is leased to a child process, which processes the cleanup.
-- New Backend Node Addition: When a new backend node is added, the child
process checks the backend_status of each node in the pooled connection and
establishes any missing backend node connections as needed.
-- Consistent Interface: Pgpool's internal interface to the connection pool
remains consistent for both classic and global connection pools. The code
calls connection pool functions that internally invoke the respective
function pointers for classic or global pooling, based on the currently
active connection pool.
-- Shared Memory Management: Enhanced shared memory management to
accommodate the connection pool data and support efficient data operations.
-- Concurrency Handling: The architecture ensures concurrency control,
allowing multiple child processes to request and release pooled connections
efficiently without significant performance penalties.

Additional Changes

-- Enhanced Regression Tests: Allow selection of connection pool mode for
regression testing
-- Shared Memory Data: Moved all connection pool-related data to shared
memory.
-- Updated Structures: Renamed and restructured connection-related data for
clarity.
-- Compilation Fixes: Fixed issues with compilation when cassert is enabled.

Remaining TODOs

-- Handle the drop database command.
-- Implement recycling of idle database connections.
-- Restructure SSL context handling for pooled connections to resolve
issues with second-time use.
-- Documentation and remove any remaining debugging or dead code.

Next Steps

Please review the global connection pool's architecture, design, and
functionality. Your feedback is invaluable for thorough testing and
refinement. The next step is to implement transaction connection pooling,
fine-grained control over pooled connections, and potentially a pre-warm
pool feature.

You can find the detailed implementation and patch set attached or see the
implementation at
https://github.com/codeforall/pgpool2/tree/global_cp


Thank you for your time and consideration.
Best Regards
Muhammad Usama
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20240805/4d263466/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0002-Enhance-Regression-Script-to-Support-Pooling-Mode-Se.patch
Type: application/octet-stream
Size: 3868 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20240805/4d263466/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-Implement-Global-Connection-Pooling-in-Pgpool-II.patch
Type: application/octet-stream
Size: 394919 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20240805/4d263466/attachment-0003.obj>