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

[Tatsuo Ishii]

Hi Usama,

Thank you for the patch! I will look into the patch but it may take a
while due to the size of patch.

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

> 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