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

[Muhammad Usama]

On Mon, Aug 5, 2024 at 4:55 PM Tatsuo Ishii <ishii at postgresql.org> wrote:

> 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.
>

Thanks, Ishii San.
you are right,, the patch is quite a huge one. I tried to divide it into
more chinks, but unfortunately couldn't manage to do that as things were so
tied to each other.

Best regards
Muhammad Usama

>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English:
> https://url.avanan.click/v2/___http://www.sraoss.co.jp/index_en/___.YXAzOnBlcmNvbmE6YTpnOjM4NjU0NTI5OGQ5ZTE2ZjA4YTU4N2YyMDM3MWQ2N2Q5OjY6YTU4NTo2YzMyMzBlYjc2NTA2ZjZiNWI0MWRjYzY4MjYzYmEzZDZlYzU2YjMyMWNkMTlhODlhMzdhM2I4ZDM3ZmYxYzBmOnA6VDpO
> Japanese:
> https://url.avanan.click/v2/___http://www.sraoss.co.jp___.YXAzOnBlcmNvbmE6YTpnOjM4NjU0NTI5OGQ5ZTE2ZjA4YTU4N2YyMDM3MWQ2N2Q5OjY6ZGNhZTpiMzI3MTdmYTM5NWU3NjRhZDFkMzUyNGE1YWYzOGIxZTkxZDkzMDk0MGRmZTIwYTM5NzJkMGU1NjNlMTBjYjljOnA6VDpO
>
> > 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://url.avanan.click/v2/___https://github.com/codeforall/pgpool2/tree/global_cp___.YXAzOnBlcmNvbmE6YTpnOjM4NjU0NTI5OGQ5ZTE2ZjA4YTU4N2YyMDM3MWQ2N2Q5OjY6YWJhNDoxMjZmNWQyMDY4YjE1MTM2MmZjNTBlNjZkODc1NmZjMzdhN2JiYTUxMDA3N2U5NTg2YTEyNTc5OGU1ZTMxZDU1OnA6VDpO
> >
> >
> > 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/f7a8efc9/attachment-0001.htm>